douzen1896
douzen1896
2019-01-14 09:41

从SQL查询获取表名

已采纳

I am using golang SQL parser to fetch query related information from actual SQL query string. I am able to find the type of query using following code:

queryType := sqlparser.StmtType(sqlparser.Preview(sql))
fmt.Println(queryType)

but i am not sure how to get actual table names from sql query. The documentation is not clear as well. Only information i get from parse function is a statement

Can someone guide me how can get this information using golang sqlparser?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • douyanzan9145 douyanzan9145 2年前

    To get all the table names, you will have to pull them out from the Statement returned by Parse, possibly using reflection. If you run the following code:

    stmt, _ := sqlparser.Parse("insert into my_table set my_column=1")
    fmt.Printf("%#v
    ", stmt)
    

    you get the output (indented for readability):

    &sqlparser.Insert{
        Action:"insert", 
        Comments:sqlparser.Comments(nil), 
        Ignore:"", 
        Table:sqlparser.TableName{
            Name:sqlparser.TableIdent{v:"my_table"}, 
            Qualifier:sqlparser.TableIdent{v:""}
        }, 
        Partitions:sqlparser.Partitions(nil), 
        Columns:sqlparser.Columns{sqlparser.ColIdent{_:[0]struct { _ []uint8 }{}, val:"my_column", lowered:""}}, 
        Rows:sqlparser.Values{sqlparser.ValTuple{(*sqlparser.SQLVal)(0xc00000a0c0)}}, 
        OnDup:sqlparser.OnDup(nil)
    }
    

    as you can see, this contains a (sub)field of type TableIdent which contains the requested table from the statement.

    点赞 评论 复制链接分享
  • dongtuoleng8624 dongtuoleng8624 2年前

    I wrote some string manipulation library for SQL Query to get table names:

    queryString := sqlstr.NewQueryString(`SELECT column_name(s)
    FROM table1
    LEFT JOIN table2
    ON table1.column_name = table2.column_name;`)
    
    tableNames := queryString.TableNames()
    
    fmt.Println(tableNames)
    
    // Output:
    // [table1 table2]
    
    点赞 评论 复制链接分享
  • doufuhao0566 doufuhao0566 2年前

    I took the snippet from the comments by @rob74 which was fantastic and modified it to only return table names. The original snippet also returned aliases of tables. E.g.

    select * from my_table as mt join other_table using(my_key)
    original snippet returns: [my_table, mt, other_table]
    new snippet returns:      [my_table, other_table]
    

    original snippet by rob74: play.golang.org/p/B31wr2w1AL8

    package main
    
    import (
        "fmt"
        "github.com/xwb1989/sqlparser"
        "reflect"
    )
    
    func main() {
        stmt, _ := sqlparser.Parse("select * from my_table as mt join other_table using(my_key)")
        var tables []string
        tables = getTableNames(reflect.Indirect(reflect.ValueOf(stmt)), tables, 0, false)
        fmt.Printf("%s", tables)
    }
    
    func getTableNames(v reflect.Value, tables []string, level int, isTable bool) []string {
        switch v.Kind() {
        case reflect.Struct:
            if v.Type().Name() == "TableIdent" {
                // if this is a TableIdent struct, extract the table name
                tableName := v.FieldByName("v").String()
                if tableName != "" && isTable{
                    tables = append(tables, tableName)
                }
            } else {
                // otherwise enumerate all fields of the struct and process further
                for i := 0; i < v.NumField(); i++ {
                    tables = getTableNames(reflect.Indirect(v.Field(i)), tables, level+1, isTable)
                }
            }
        case reflect.Array, reflect.Slice:
            for i := 0; i < v.Len(); i++ {
                // enumerate all elements of an array/slice and process further
                tables = getTableNames(reflect.Indirect(v.Index(i)), tables, level+1, isTable)
            }
        case reflect.Interface:
            if v.Type().Name() == "SimpleTableExpr" {
                isTable = true
            }
            // get the actual object that satisfies an interface and process further
            tables = getTableNames(reflect.Indirect(reflect.ValueOf(v.Interface())), tables, level+1, isTable)
        }
    
        return tables
    }
    
    点赞 评论 复制链接分享

为你推荐