提取sql中涉及到的表名?

Question

请问ruby中如何获取到一条sql中涉及到的所有表

比如sql是 select * from users
结果是 users

简单的用正则可以匹配，但是复杂的好像匹配不了
在mysql层面有啥语句会查询得到吗

看了explain，里面好像没有列举出涉及到的表名

Answer 1

https://github.com/greenlion/PHP-SQL-Parser 可以用这个解析

SELECT a, b, c 
        FROM some_table an_alias
        JOIN `another` AS `another table` USING(id)
        WHERE d > 5

输出结果：

Array
(
    [SELECT] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [base_expr] => a
                    [sub_tree] => 
                )

            [1] => Array
                (
                    [expr_type] => colref
                    [base_expr] => b
                    [sub_tree] => 
                )

            [2] => Array
                (
                    [expr_type] => colref
                    [base_expr] => c
                    [sub_tree] => 
                )

        )

    [FROM] => Array
        (
            [0] => Array
                (
                    [expr_type] => table
                    [table] => some_table
                    [alias] => Array
                        (
                            [as] => 
                            [name] => an_alias
                        )

                    [join_type] => 
                    [ref_type] => 
                    [ref_clause] => 
                    [base_expr] => some_table
                    [sub_tree] => 
                )

        )

    [JOIN] => Array
        (
            [0] => Array
                (
                    [expr_type] => table
                    [table] => another
                    [alias] => Array
                        (
                            [as] => AS
                            [name] => another table
                        )

                    [join_type] => JOIN
                    [ref_type] => USING
                    [ref_clause] => Array
                        (
                            [0] => Array
                                (
                                    [expr_type] => colref
                                    [base_expr] => id
                                    [sub_tree] => 
                                )

                        )

                    [base_expr] => JOIN `another` AS `another table` USING(id)
                    [sub_tree] => 
                )

        )

    [WHERE] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [base_expr] => d
                    [sub_tree] => 
                )

            [1] => Array
                (
                    [expr_type] => operator
                    [base_expr] => >
                    [sub_tree] => 
                )

            [2] => Array
                (
                    [expr_type] => const
                    [base_expr] => 5
                    [sub_tree] => 
                )

        )

)

Answer 2

java的话可以看看jsqlparsr,这个是mybatisPlus在使用的，将sql解析成AST抽象语法树，然后你就可以拿到你想要拿到的任何部分

Answer 3

使用关键字From作为分割符号取右侧 通过空格分割取第0个