解析mysql创建表的语句

parse_sql.ts

interface Column {
    name: string;
    kind: string;
    // 约束
    constraints: string[];
    comment: string;
}

interface Table {
    name: string;
    columns: Column[];
    comment: string;
}

const symbols = ['(', ')', ',', ';']

class MysqlScanner {

    private sql: string
    private index: number

    constructor(sql: string) {
        this.sql = sql.trim()
        this.index = -1
    }

    private next(): string | undefined {
        if (this.index < this.sql.length) {
            return this.sql.charAt(++this.index)
        }
        return
    }

    public scan(): string[]{
        const tokens: string[] = []
        while (this.index < this.sql.length) {
            const c = this.next()
            if (this.isWhiteSpace(c)) {
                continue
            }
            let token = this.parseSymbol(c)
            if (token) {
                tokens.push(token)
                continue
            }
            token = this.parseString(c)
            if (token) {
                tokens.push(token)
                continue
            }
            token = this.parseToken()
            if (token) {
                tokens.push(token)
                continue
            }
        }
        return tokens
    }

    private isWhiteSpace(c: string): boolean {
        return c.trim() === ''
    }

    private parseSymbol(c: string): string | undefined {
        if (symbols.indexOf(c) > -1) {
            return c
        }
        return
    }

    private parseString(c: string): string | undefined {
        if (c !== "'") {
            return
        }
        let token = "'"
        let i = this.index + 1
        while (i < this.sql.length) {
            const v = this.sql.charAt(i)
            if (v === "'") {
                token += "'"
                break
            }
            token += v
            i++
         }
        if (token.length === 1) {
            return
        }
        this.index = i
        return token
    }

    private parseToken(): string | undefined {
        let token: string = ''
        let i = this.index
        while (i < this.sql.length) {
            const c = this.sql.charAt(i)
            if (this.isWhiteSpace(c)) {
                break
            }
            if (c === '(') {
                let j = i + 1
                while (j < this.sql.length) {
                    const n = this.sql.charAt(j)
                    if (n === ')') {
                        break
                    }
                    if (/[0-9]/.test(n)) {
                        j ++
                        continue
                    }
                    break
                }
                // 属于数字
                if (j - i >= 1) {
                    token += this.sql.slice(i, j + 1)
                    i = j + 1
                    continue
                }
                this.index --
                break
            }
            if (/[0-9a-zA-Z_\$`]/.test(c)) {
                token += c
                i ++
                continue
            }
            break
        }
        if (token.length) {
            this.index = i
            return token
        }
        return
    }
}

class MySQLParser {
    private tokens: string[];
    private index: number;

    constructor(sql: string) {
        const scanner = new MysqlScanner(sql)
        this.tokens = scanner.scan()
        this.index = 0;
    }

    private getNextToken(): string | undefined {
        if (this.index < this.tokens.length) {
            return this.tokens[this.index++];
        }
        return undefined;
    }

    public parse(): Table {
        const table: Table = {
            name: '',
            columns: [],
            comment: ''
        }

        let columnOpen = false

        const columns: Column[] = []

        let column: Column = {
            name: '',
            kind: '',
            constraints: [],
            comment: ''
        }

        while (this.index < this.tokens.length) {
            let token = this.getNextToken()
            if (!token) {
                continue
            }
            if (token.toUpperCase() === 'CREATE') {
                continue
            }
            if (token.toUpperCase() === 'TABLE') {
                table.name = this.getNextToken()!
                continue
            }

            if (token === '(') {
                columnOpen = true
                continue
            }

            if (token === ')') {
                columns.push(column)
                if (this.index + 1 < this.tokens.length) {
                    this.index += 1
                    table.comment = this.tokens[this.index]
                }
                columnOpen = false
                continue
            }

            if (token === ',') {
                columns.push(column)
                column = {
                    name: '',
                    kind: '',
                    constraints: [],
                    comment: ''
                }
                continue
            }

            if (columnOpen) {
                column.name = token
                column.kind = this.getNextToken()
                const constraints:string[] = []
                let i = this.index

                while (i < this.tokens.length) {
                    const t = this.tokens[i]
                    if (t === ',' || t === ')') {
                        i --
                        break
                    }
                    if (t.toUpperCase() === 'COMMENT') {
                        i ++
                        column.comment = this.tokens[i]
                        i ++
                        continue
                    }
                    constraints.push(t)
                    i++
                }
                column.constraints = constraints
                this.index = i
            }
            this.index ++
        }
        table.columns = columns
        return table
    }
}

const sql = `
CREATE TABLE account ( 
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(40) NOT NULL DEFAULT '' COMMENT '用户名',
) COMMENT '账号表';
`;

const parser = new MySQLParser(sql)

const table = parser.parse()

console.log(table)