转载

phpMyAdmin中sql-parser组件的使用


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.git
margin@margin-MB1:~/tmp$ cd sql-parser && sudo composer install

组件安装完毕,下面介绍具体的调用

解析普通语句

<?php
require_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,
"fu nction": 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,
"fu nction": 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语句:

<?php

require_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 "

组装触发器语句:

<?php
require_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再加工

多条语句一起加工处理:

<?php
require_once '../sql-parser/vendor/autoload.php';

use SqlParser\Parser;
use SqlParser\Components\Expression;

$query = <<<STR
ALTER 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 = 3
STR;

$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组件功能比较丰富和完备,本文限了篇幅不能详尽,有兴趣的读者可以通过阅读源码来了解更多高级的用法。

composer:

https://packagist.org/packages/phpmyadmin/sql-parser


正文到此结束
本文目录