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包管理工具
1 margin@margin-MB1:~/tmp$ sudo git clone https://github.com/phpmyadmin/sql-parser.git 2 3 margin@margin-MB1:~/tmp$ cd sql-parser && sudo composer install 组件安装完毕,下面介绍具体的调用
解析普通语句
1 require_once '../sql-parser/vendor/autoload.php'; 2 use SqlParser\Parser; 3 4 $query = 'SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ' 5 . 'ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)'; 6 7 $parser = new Parser($query); 8 $stmt = $parser->statements[0]; 9 echo json_encode($stmt); 运行结果中$parser变量是一个大对象,里面存储有对$query语句的词法分析结果$query->list,语法分析结果$query-statements,以及错误信息等。 其中$query-statements的结构如下:
01 {"expr": 02 [{"database":null,"table":null,"column":null,"expr":"*","alias":null,"function":n 03 ull,"subquery":null}],"from": 04 [{"database":null,"table":"t1","column":null,"expr":"t1","alias":null,"function": 05 null,"subquery":null}],"partition":null,"where":null,"group":null,"having":null," 06 order":null,"limit":null,"procedure":null,"into":null,"join": 07 [{"type":"LEFT","expr":{"database":null,"table":null,"column":null,"expr":"(t2, 08 t3, t4)","alias":null,"function":null,"subquery":null},"on":[{"identifiers": 09 ["t2","a","t1"],"isOperator":false,"expr":"(t2.a=t1.a"},{"identifiers": 10 [],"isOperator":true,"expr":"AND"},{"identifiers": 11 ["t3","b","t1"],"isOperator":false,"expr":"t3.b=t1.b"},{"identifiers": 12 [],"isOperator":true,"expr":"AND"},{"identifiers": 13 ["t4","c","t1"],"isOperator":false,"expr":"t4.c=t1.c)"}],"using":null}],"union": 14 [],"options":{"options":[]},"first":0,"last":50} 解析事务
01 require_once '../sql-parser/vendor/autoload.php'; 02 use SqlParser\Parser; 03 04 $query = 'START TRANSACTION;' . 05 'SELECT @A:=SUM(salary) FROM table1 WHERE type=1;' . 06 'UPDATE table2 SET summary=@A WHERE type=1;' . 07 'COMMIT;'; 08 09 $parser = new Parser($query); 10 $stmt = $parser->statements[0]; 11 echo json_encode($stmt); 输出结果:
01 {"type":1,"statements":[{"expr": 02 [{"database":null,"table":null,"column":null,"expr":"@A:=SUM(salary)","alias":nul 03 l,"function":"SUM","subquery":null}],"from": 04 [{"database":null,"table":"table1","column":null,"expr":"table1","alias":null,"fu 05 nction":null,"subquery":null}],"partition":null,"where":[{"identifiers": 06 ["type"],"isOperator":false,"expr":"type=1"}],"group":null,"having":null,"order": 07 null,"limit":null,"procedure":null,"into":null,"join":null,"union":[],"options": 08 {"options":[]},"first":1,"last":19},{"tables": 09 [{"database":null,"table":"table2","column":null,"expr":"table2","alias":null,"fu 10 nction":null,"subquery":null}],"set":[{"column":"summary","value":"@A"}],"where": 11 [{"identifiers": 12 ["type"],"isOperator":false,"expr":"type=1"}],"order":null,"limit":null,"options" 13 :{"options":[]},"first":20,"last":35}],"end": 14 {"type":2,"statements":null,"end":null,"options":{"options": 15 {"1":"COMMIT"}},"first":36,"last":37},"options":{"options":{"1":"START 16 TRANSACTION"}},"first":0,"last":0} 除以上两种语句之外,sql-parser还支持解析存储过程等几乎所有MySQL语法,不再一一举例。下面是其SQL构造器的用法举例。
组装SQL语句
组装select语句:
01 require_once '../sql-parser/vendor/autoload.php'; 02 03 use SqlParser\Components\OptionsArray; 04 use SqlParser\Components\Expression; 05 use SqlParser\Components\Condition; 06 use SqlParser\Components\Limit; 07 use SqlParser\Statements\SelectStatement; 08 09 $stmt = new SelectStatement(); 10 11 $stmt->options = new OptionsArray(array('DISTINCT')); 12 13 $stmt->expr[] = new Expression('sakila', 'film', 'film_id', 'fid'); 14 $stmt->expr[] = new Expression('COUNT(film_id)'); 15 $stmt->from[] = new Expression('', 'film', ''); 16 $stmt->from[] = new Expression('', 'actor', ''); 17 $stmt->where[] = new Condition('film_id > 10'); 18 $stmt->where[] = new Condition('OR'); 19 $stmt->where[] = new Condition('actor.age > 25'); 20 $stmt->limit = new Limit(1, 10); 21 22 var_dump($stmt->build()); 输出结果:
1 margin@margin-MB1:~/code/parserTest$ php build.php 2 string(137) "SELECT DISTINCT `sakila`.`film`.`film_id` AS `fid`, COUNT(film_id) 3 FROM `film`, `actor` WHERE film_id > 10 OR actor.age > 25 LIMIT 10, 1 " 组装触发器语句:
01 require_once '../sql-parser/vendor/autoload.php'; 02 03 use SqlParser\Components\Expression; 04 use SqlParser\Components\OptionsArray; 05 use SqlParser\Statements\CreateStatement; 06 07 $stmt = new CreateStatement(); 08 09 $stmt->options = new OptionsArray(array('TRIGGER')); 10 $stmt->name = new Expression('ins_sum'); 11 $stmt->entityOptions = new OptionsArray(array('BEFORE', 'INSERT')); 12 $stmt->table = new Expression('account'); 13 $stmt->body = 'SET @sum = @sum + NEW.amount'; 14 15 var_dump($stmt->build()); 输出结果:
1 margin@margin-MB1:~/code/parserTest$ php build.php 2 string(89) "CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum 3 = @sum + NEW.amount" SQL再加工
多条语句一起加工处理:
01 require_once '../sql-parser/vendor/autoload.php'; 02 03 use SqlParser\Parser; 04 use SqlParser\Components\Expression; 05 06 $query = <<<str id="3" from="" change="" $statement_0="$parser-" 处理第一条语句="" parser($query);="" $parser="new" str;="" where="" tbl3="" *="" select="" null;="" not="" unsigned="" )="" 10="" int(="" `field_2`="" `field_1`="" `tbl`="" table="" alter="" auto_increment;="" null="" `uid`="">statements[0]; 07 $statement_0->table = new Expression( 08 'db2', 'tb2', '' 09 ); 10 var_dump($statement_0->build()); 11 12 //处理第二条语句 13 $statement_1 = $parser->statements[1]; 14 $statement_1->table = new Expression( 15 'db3', 'tb3', '' 16 ); 17 var_dump($statement_1->build());</str> 输出结果:
1 margin@margin-MB1:~/code/parserTest$ php build.php 2 string(85) "ALTER TABLE `db2`.`tb2` CHANGE `uid` `uid` INT( 10 ) UNSIGNED NOT 3 NULL AUTO_INCREMENT" 4 string(78) "ALTER TABLE `db3`.`tb3` CHANGE `field_1` `field_2` INT( 10 ) UNSIGNED 5 NOT NULL" 以上是sql-parser组件一些基本的用法示例,phpMyAdmin的sql-parser组件功能比较丰富和完备,本文限了篇幅不能详尽,有兴趣的读者可以通过阅读源码来了解更多高级的用法。 |