phpsql语句解析技巧_PythonPHP MySQL语句解析器解决营业分表

本日为大家先容Python/PHP两种MySQL语句解析器。
当时网盘项目用的是PHP编程措辞开拓。

Python的SQL语句解析器。
个人推举利用moz_sql_parser库。
经调研官方的sqlparse库解析出来的语句段无法知足需求也很难明得。

1、Python moz_sql_parser库安装

pip install moz_sql_parser

2、Python moz_sql_parser SQL语句解析

from moz_sql_parser import parseimport json#用例1sql = 'select id,name from t1 where id > 1'tree = parse(sql)print(json.dumps(tree))实行结果：{ "select": [ { "value": "id" }, { "value": "name" } ], "from": "t1", "where": { "gt": [ "id", 1 ] }} #用例2sql = 'select id,name from t1 where id > 1 and id < 1'tree = parse(sql)print(json.dumps(tree)) 实行结果： { "select": [ { "value": "id" }, { "value": "name" } ], "from": "t1", "where": { "and": [ { "gt": [ "id", 1 ] }, { "lt": [ "id", 1 ] } ] }}#用例3sql = "select id,name from t1 where id in (select id from t2 WHERE content = 'xxxxxx')"tree = parse(sql)print(json.dumps(tree))实行结果：{ "select": [ { "value": "id" }, { "value": "name" } ], "from": "t1", "where": { "in": [ "id", { "select": { "value": "id" }, "from": "t2", "where": { "eq": [ "content", { "literal": "xxxxxx" } ] } } ] }}#用例4 sql = "select t1.id, t1.name from t1 join t2 on t1.id = t2.id where t2.id > 1"tree = parse(sql)print(json.dumps(tree))实行结果：{ "select": [ { "value": "t1.id" }, { "value": "t1.name" } ], "from": [ "t1", { "join": "t2", "on": { "eq": [ "t1.id", "t2.id" ] } } ], "where": { "gt": [ "t2.id", 1 ] }}

3、Python moz_sql_parser总结

PHP的SQL语句解析器。
个人推举利用PhpMyAdmin的sql-parser组件。
PhpMyAdmin是经由历史考验可信赖的。

1、PHP PhpMyAdmin/sql-parser安装

composer require phpmyadmin/sql-parser

2、PHP PhpMyAdmin/sql-parser SQL语句解析

<?phprequire_once 'vendor/autoload.php';use PhpMyAdmin\SqlParser\Parser;$query = 'SELECT t1.id, t1.name FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.name = "xxxx"';$parser = new Parser($query);$stmt = $parser->statements[0];var_dump($stmt);实行结果：object(PhpMyAdmin\SqlParser\Statements\SelectStatement)#46 (17) { ["expr"]=> array(2) { [0]=> object(PhpMyAdmin\SqlParser\Components\Expression)#48 (7) { ["database"]=> NULL ["table"]=> string(2) "t1" ["column"]=> string(2) "id" ["expr"]=> string(5) "t1.id" ["alias"]=> NULL ["function"]=> NULL ["subquery"]=> NULL } [1]=> object(PhpMyAdmin\SqlParser\Components\Expression)#49 (7) { ["database"]=> NULL ["table"]=> string(2) "t1" ["column"]=> string(4) "name" ["expr"]=> string(7) "t1.name" ["alias"]=> NULL ["function"]=> NULL ["subquery"]=> NULL } } ["from"]=> array(1) { [0]=> object(PhpMyAdmin\SqlParser\Components\Expression)#50 (7) { ["database"]=> NULL ["table"]=> string(2) "t1" ["column"]=> NULL ["expr"]=> string(2) "t1" ["alias"]=> NULL ["function"]=> NULL ["subquery"]=> NULL } } ["index_hints"]=> NULL ["partition"]=> NULL ["where"]=> array(1) { [0]=> object(PhpMyAdmin\SqlParser\Components\Condition)#54 (3) { ["identifiers"]=> array(3) { [0]=> string(2) "t2" [1]=> string(4) "name" [2]=> string(4) "xxxx" } ["isOperator"]=> bool(false) ["expr"]=> string(16) "t2.name = "xxxx"" } } ["group"]=> NULL ["having"]=> NULL ["order"]=> NULL ["limit"]=> NULL ["procedure"]=> NULL ["into"]=> NULL ["join"]=> array(1) { [0]=> object(PhpMyAdmin\SqlParser\Components\JoinKeyword)#51 (4) { ["type"]=> string(4) "LEFT" ["expr"]=> object(PhpMyAdmin\SqlParser\Components\Expression)#52 (7) { ["database"]=> NULL ["table"]=> string(2) "t2" ["column"]=> NULL ["expr"]=> string(2) "t2" ["alias"]=> NULL ["function"]=> NULL ["subquery"]=> NULL } ["on"]=> array(1) { [0]=> object(PhpMyAdmin\SqlParser\Components\Condition)#53 (3) { ["identifiers"]=> array(3) { [0]=> string(2) "t1" [1]=> string(2) "id" [2]=> string(2) "t2" } ["isOperator"]=> bool(false) ["expr"]=> string(13) "t1.id = t2.id" } } ["using"]=> NULL } } ["union"]=> array(0) { } ["end_options"]=> NULL ["options"]=> object(PhpMyAdmin\SqlParser\Components\OptionsArray)#47 (1) { ["options"]=> array(0) { } } ["first"]=> int(0) ["last"]=> int(39)}

3、PHP PhpMyAdmin/sql-parser总结

大家有什么问题可以发评论沟通。

感谢大家的评论、点赞、分享、关注。
。
。

标签：34gt