寫SQL對於PHP來說是常的事，那麼如何對SQL語句進行分析來最佳化SQL呢？ 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
元件安裝完畢，下面介紹具體的呼叫
解析普通語句
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":n
ull,"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":nul
l,"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語句：
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 "
組裝觸發器語句：
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再加工
多條語句一起處理：
require_once '../sql-parser/vendor/autoload.php';
use SqlParser\Parser;
use SqlParser\Components\Expression;
$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];
$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());</str>輸出結果：
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元件功能比較豐富和完備，本文限了篇幅不能詳盡，有興趣的讀者可以透過閱讀原始碼來了解更多進階的用法。
相關推薦：
#以上是PHPMyAdmin中使用sql-parser進行SQL語法分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！
