phpMyAdmin中sql-parser组件的使用
来源:互联网 发布:每天一杯速溶咖啡 知乎 编辑:程序博客网 时间:2024/05/01 22:53
作者介绍:陈苗 腾讯云工程师
phpMyAdmin是一款基于Web端运行的开源数据库管理工具,支持管理MySQL和MariaDB两种数据库。 phpMyAdmin的程序主要使用php和javascript开发,它的安装使用都比较简单而且已有很多相关介绍不再重复,今天要介绍的是源码中的一个核心组件sql-parser。
sql-parser简介
sql-parser组件的主要用途是对SQL语句进行词法分析、语法分析,继而可以实现对SQL语句的解构、加工、替换、再组装等需求,另外也可以对SQL进行highlight等处理。sql-parser由纯PHP语言实现,同时也是整个phpMyAdmin源码中为数不多的代码架构比较清晰且符合当前PHP界PSR标准规范的模块。
sql-parser组件安装
需事先安装好php,git客户端,以及composer php包管理工具
margin@margin-MB1:~/tmp$ sudo git clone https://github.com/phpmyadmin/sql-parser.gitmargin@margin-MB1:~/tmp$ cd sql-parser && sudo composer install
组件安装完毕,下面介绍具体的调用
解析普通语句
<?phprequire_once '../sql-parser/vendor/autoload.php';use SqlParser\Parser;$query = 'SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ' . 'ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)';$parser = new Parser($query);$stmt = $parser->statements[0];echo json_encode($stmt);
运行结果中$parser
变量是一个大对象,里面存储有对$query
语句的词法分析结果$query->list
,语法分析结果$query-statements
,以及错误信息等。
其中$query-statements
的结构如下:
{"expr":[{"database":null,"table":null,"column":null,"expr":"*","alias":null,"function":null,"subquery":null}],"from":[{"database":null,"table":"t1","column":null,"expr":"t1","alias":null,"function":null,"subquery":null}],"partition":null,"where":null,"group":null,"having":null,"order":null,"limit":null,"procedure":null,"into":null,"join":[{"type":"LEFT","expr":{"database":null,"table":null,"column":null,"expr":"(t2, t3, t4)","alias":null,"function":null,"subquery":null},"on":[{"identifiers":["t2","a","t1"],"isOperator":false,"expr":"(t2.a=t1.a"},{"identifiers":[],"isOperator":true,"expr":"AND"},{"identifiers":["t3","b","t1"],"isOperator":false,"expr":"t3.b=t1.b"},{"identifiers":[],"isOperator":true,"expr":"AND"},{"identifiers":["t4","c","t1"],"isOperator":false,"expr":"t4.c=t1.c)"}],"using":null}],"union":[],"options":{"options":[]},"first":0,"last":50}
解析事务
require_once '../sql-parser/vendor/autoload.php';use SqlParser\Parser;$query = 'START TRANSACTION;' . 'SELECT @A:=SUM(salary) FROM table1 WHERE type=1;' . 'UPDATE table2 SET summary=@A WHERE type=1;' . 'COMMIT;';$parser = new Parser($query);$stmt = $parser->statements[0];echo json_encode($stmt);
输出结果:
{"type":1,"statements":[{"expr":[{"database":null,"table":null,"column":null,"expr":"@A:=SUM(salary)","alias":null,"function":"SUM","subquery":null}],"from":[{"database":null,"table":"table1","column":null,"expr":"table1","alias":null,"function":null,"subquery":null}],"partition":null,"where":[{"identifiers":["type"],"isOperator":false,"expr":"type=1"}],"group":null,"having":null,"order":null,"limit":null,"procedure":null,"into":null,"join":null,"union":[],"options":{"options":[]},"first":1,"last":19},{"tables":[{"database":null,"table":"table2","column":null,"expr":"table2","alias":null,"function":null,"subquery":null}],"set":[{"column":"summary","value":"@A"}],"where":[{"identifiers":["type"],"isOperator":false,"expr":"type=1"}],"order":null,"limit":null,"options":{"options":[]},"first":20,"last":35}],"end":{"type":2,"statements":null,"end":null,"options":{"options":{"1":"COMMIT"}},"first":36,"last":37},"options":{"options":{"1":"START TRANSACTION"}},"first":0,"last":0}
除以上两种语句之外,sql-parser还支持解析存储过程等几乎所有MySQL语法,不再一一举例。下面是其SQL构造器的用法举例。
组装SQL语句
组装select语句:
<?phprequire_once '../sql-parser/vendor/autoload.php';use SqlParser\Components\OptionsArray;use SqlParser\Components\Expression;use SqlParser\Components\Condition;use SqlParser\Components\Limit;use SqlParser\Statements\SelectStatement;$stmt = new SelectStatement();$stmt->options = new OptionsArray(array('DISTINCT'));$stmt->expr[] = new Expression('sakila', 'film', 'film_id', 'fid');$stmt->expr[] = new Expression('COUNT(film_id)');$stmt->from[] = new Expression('', 'film', '');$stmt->from[] = new Expression('', 'actor', '');$stmt->where[] = new Condition('film_id > 10');$stmt->where[] = new Condition('OR');$stmt->where[] = new Condition('actor.age > 25');$stmt->limit = new Limit(1, 10);var_dump($stmt->build());
输出结果:
margin@margin-MB1:~/code/parserTest$ php build.php string(137) "SELECT DISTINCT `sakila`.`film`.`film_id` AS `fid`, COUNT(film_id) FROM `film`, `actor` WHERE film_id > 10 OR actor.age > 25 LIMIT 10, 1 "
组装触发器语句:
<?phprequire_once '../sql-parser/vendor/autoload.php';use SqlParser\Components\Expression;use SqlParser\Components\OptionsArray;use SqlParser\Statements\CreateStatement;$stmt = new CreateStatement();$stmt->options = new OptionsArray(array('TRIGGER'));$stmt->name = new Expression('ins_sum');$stmt->entityOptions = new OptionsArray(array('BEFORE', 'INSERT'));$stmt->table = new Expression('account');$stmt->body = 'SET @sum = @sum + NEW.amount';var_dump($stmt->build());
输出结果:
margin@margin-MB1:~/code/parserTest$ php build.php string(89) "CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount"
SQL再加工
多条语句一起加工处理:
<?phprequire_once '../sql-parser/vendor/autoload.php';use SqlParser\Parser;use SqlParser\Components\Expression;$query = <<<STRALTER TABLE `tbl` CHANGE `uid` `uid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;ALTER TABLE `tbl` CHANGE `field_1` `field_2` INT( 10 ) UNSIGNED NOT NULL;select * from tbl3 where id = 3STR;$parser = new Parser($query);//处理第一条语句$statement_0 = $parser->statements[0];$statement_0->table = new Expression( 'db2', 'tb2', '');var_dump($statement_0->build());//处理第二条语句$statement_1 = $parser->statements[1];$statement_1->table = new Expression( 'db3', 'tb3', '');var_dump($statement_1->build());
输出结果:
margin@margin-MB1:~/code/parserTest$ php build.php string(85) "ALTER TABLE `db2`.`tb2` CHANGE `uid` `uid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT"string(78) "ALTER TABLE `db3`.`tb3` CHANGE `field_1` `field_2` INT( 10 ) UNSIGNED NOT NULL"
以上是sql-parser组件一些基本的用法示例,phpMyAdmin的sql-parser组件功能比较丰富和完备,本文限了篇幅不能详尽,有兴趣的读者可以通过阅读源码来了解更多高级的用法。
相关推荐
什么是云数据库MySQL?
存储总量达20T的MySQL实例,如何完成迁移?
CDB for MySQL相关文档
- phpMyAdmin中sql-parser组件的使用
- phpMyAdmin中sql-parser组件的使用
- phpMyAdmin中sql-parser组件的使用
- MY SQL phpMyAdmin的使用
- 使用phpMyAdmin中出现的错误
- express中cookie的使用和cookie-parser的解读
- NLTK中使用Stanford parser
- PhpMyAdmin中无法导入sql文件的解决办法
- PhpMyAdmin中无法导入sql文件的解决办法
- PhpMyAdmin中无法导入sql文件的解决办法
- 在phpmyadmin中添加数据的sql示例
- PhpMyAdmin中无法导入sql文件的解决办法
- PhpMyAdmin中无法导入sql文件的解决办法
- sql parser
- XML Parser(Tinyxml)的使用
- EasyUI parser 的使用场景
- 基于 C 的 SQL Parser 实现
- Python中使用stanford parser(一)
- RN多文件上传
- 网易2017年秋招编程题之暗黑的字符串
- 统计时,时间格式如何获取
- 高通平台修改LK(bootloader)开机logo
- noi-7805-配对碱基链
- phpMyAdmin中sql-parser组件的使用
- iOS对象属性详解
- Cloud云深商学院内部频道剪辑上篇-社会大学
- 整数分解为若干项之和
- 222. Count Complete Tree Nodes
- uboot下函数run_main_loop分析
- jQuery选择器之层级选择器
- Git rebase和 Git merge的区别
- 单例模式