ShowDoc 技术栈探秘php-sql-parser 复杂 SELECT 语句解析树全解【免费下载链接】showdocShowDoc is a tool greatly applicable for an IT team to share documents online一个非常适合IT团队的在线API文档、技术文档工具项目地址: https://gitcode.com/gh_mirrors/sh/showdoc本篇文章以 php-sql-parserPHP-SQL-Parser纯 PHP 实现的 MySQL 方言 SQL 解析器官方 Wiki 中的Complex-Example文档为核心通过一段五脏俱全的复杂 SELECT 语句完整拆解其解析树parse tree的每一个节点结构。在 ShowDoc 项目中这个解析器正是数据库文档功能里把CREATE TABLE语句自动转换成 Markdown 表格的底层引擎见 Convert.php理解它的输出结构既能帮你读懂 SQL 词法/语法分析结果也能为基于该解析器二次开发如 SQL 转文档、SQL 静态分析、SQL 格式化打下扎实基础。读完本文你将掌握 php-sql-parser 的调用方式、expr_type/base_expr/sub_tree/position/alias等核心字段的语义以及 SELECT 全子句表达式、CASE、子查询、多表 JOIN、IN、EXISTS、GROUP/HAVING/ORDER/LIMIT、INTO、锁选项在解析树中的真实形态。一、文档与解析器背景Complex-Example.md是 php-sql-parser 官方 Wiki 的一篇核心示例文档位于本仓库的 server/vendor/greenlion/php-sql-parser/wiki/Complex-Example.md。它不同于简单的SELECT a FROM t WHERE d 5而是刻意把 SELECT 语法中几乎所有常见甚至冷门的子句塞进一条语句里去重选项、算术表达式、反引号别名、聚合函数、CASE WHEN 分支、标量子查询、INTO 变量、INTO OUTFILE、四种表连接方式普通 JOIN、LEFT OUTER JOIN USING、JOIN ON、JOIN USING、多层 WHERE 条件IN 列表、EXISTS 子查询、括号表达式、GROUP BY 位置参数、HAVING 聚合过滤、ORDER BY 双向排序、LIMIT 偏移、FOR UPDATE与LOCK IN SHARE MODE行锁选项——几乎覆盖了官方 Parser-Manual.md 中提到的 SELECT 全部语法面。按照 README.md 的定位说明该解析器有以下三个关键特性纯 PHP 实现、无外部依赖不依赖 PECL 扩展整个解析逻辑由 PHPSQLParser.php 及其processors44 个处理器、lexer、positions、utils等子模块完成非校验型non-validating解析器它假设你传入的是语法上基本正确的 SQL专注点是完整、准确地解析 MySQL 方言而不是做语法校验也不以性能优化为首要目标The focus is not on optimizing for performance完整支持 SELECT / INSERT / UPDATE / DELETE / REPLACE / RENAME / SHOW / SET / DROP / CREATE INDEX / CREATE TABLE / EXPLAIN / DESCRIBE等语句类型并支持 UNION、子查询与复合语句。二、调用方式构造函数与 parse() 方法在深入解析树之前先确认官方推荐的两类用法详见 Parser-Manual.md// 方式一构造函数直接传入 SQL内部自动调用 parse() $parser new PHPSQLParser(select 1); print_r($parser-parsed); // 方式二先实例化再显式调用 parse() $parser new PHPSQLParser(); $parsed $parser-parse(select 2); print_r($parsed);构造函数签名与parse()方法的真实实现在 PHPSQLParser.php 中一目了然public function __construct($sql false, $calcPositions false, array $options array()) { $this-options new Options($options); if ($sql) { $this-parse($sql, $calcPositions); } } public function parse($sql, $calcPositions false) { $processor new DefaultProcessor($this-options); $queries $processor-process($sql); // calc the positions of some important tokens if ($calcPositions) { $calculator new PositionCalculator(); $queries $calculator-setPositionsWithinSQL($sql, $queries); } $this-parsed $queries; return $this-parsed; }由此可以确认三个重要事实第二个参数$calcPositions决定输出中是否携带[position]字段——它由 PositionCalculator.php 负责把每个base_expr在原始 SQL 字符串中的字符偏移量计算出来。官方手册明确提示position 的计算需要额外时间如果应用不需要可以传 false第三个参数$options由 Options.php 承载目前支持两个开关consistent_sub_trees让子树结构保持一致性和ansi_quotes按 ANSI 标准把双引号当作标识符引用符而非字符串常量解析结果保存在公开属性$parser-parsed中parse()同时返回它。此外解析器还提供自定义函数注册能力addCustomFunction()/removeCustomFunction()/getCustomFunctions()同样定义在 PHPSQLParser.php用于让解析器识别业务自定义的 SQL 函数名。三、核心示例一段覆盖全部 SELECT 语法的 SQL这是Complex-Example.md的灵魂部分。下面这段 SQL 刻意在同一语句中混合了别名、表达式、聚合、CASE、子查询、多表连接、IN/EXISTS、分组、排序、分页、变量/文件导出和行锁选项require_once(php-sql-parser.php); $sql select DISTINCT 12 c1, 1 2 as c2, sum(c2),sum(c3) as sum_c3,Status CASE WHEN quantity 0 THEN \in stock\ ELSE \out of stock\ END case_statement , t4.c1, (select c1c2 from t1 inner_t1 limit 1) as subquery into a1, a2, a3 from t1 the_t1 left outer join t2 using(c1,c2) join t3 as tX ON tX.c1 the_t1.c1 join t4 t4_x using(x) where c1 1 and c2 in (1,2,3, apple) and exists ( select 1 from some_other_table another_table where x 1) and (zebra orange or 1 1) group by 1, 2 having sum(c2) 1 ORDER BY 2, c1 DESC LIMIT 0, 10 into outfile /xyz FOR UPDATE LOCK IN SHARE MODE; $parser new PHPSQLParser($sql, true); print_r($parser-parsed);注意这里使用了new PHPSQLParser($sql, true)——第二个参数为true因此输出中每个节点都带有[position]字段。下面第四节先给出该文档附带的完整输出第五八节再逐段剖析。四、完整解析树输出原文档原文Array ( [SELECT] Array ( [0] Array ( [expr_type] expression [alias] Array ( [as] [name] c1 [base_expr] c1 [position] 22 ) [base_expr] 12 [sub_tree] Array ( [0] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 16 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 17 ) [2] Array ( [expr_type] const [base_expr] 2 [sub_tree] [position] 18 ) ) [position] 16 ) [1] Array ( [expr_type] expression [alias] Array ( [as] 1 [name] c2 [base_expr] as c2 [position] 31 ) [base_expr] 1 2 [sub_tree] Array ( [0] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 26 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 27 ) [2] Array ( [expr_type] const [base_expr] 2 [sub_tree] [position] 29 ) ) [position] 26 ) [2] Array ( [expr_type] aggregate_function [alias] [base_expr] sum [sub_tree] Array ( [0] Array ( [expr_type] colref [base_expr] c2 [sub_tree] [position] 44 ) ) [position] 40 ) [3] Array ( [expr_type] aggregate_function [alias] Array ( [as] 1 [name] sum_c3 [base_expr] as sum_c3 [position] 56 ) [base_expr] sum [sub_tree] Array ( [0] Array ( [expr_type] colref [base_expr] c3 [sub_tree] [position] 52 ) ) [position] 48 ) [4] Array ( [expr_type] expression [alias] Array ( [as] [name] case_statement [base_expr] case_statement [position] 164 ) [base_expr] Status CASE WHEN quantity 0 THEN in stock ELSE out of stock END [sub_tree] Array ( [0] Array ( [expr_type] const [base_expr] Status [sub_tree] [position] 66 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 75 ) [2] Array ( [expr_type] reserved [base_expr] CASE [sub_tree] [position] 77 ) [3] Array ( [expr_type] reserved [base_expr] WHEN [sub_tree] [position] 90 ) [4] Array ( [expr_type] colref [base_expr] quantity [sub_tree] [position] 95 ) [5] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 104 ) [6] Array ( [expr_type] const [base_expr] 0 [sub_tree] [position] 106 ) [7] Array ( [expr_type] reserved [base_expr] THEN [sub_tree] [position] 108 ) [8] Array ( [expr_type] const [base_expr] in stock [sub_tree] [position] 113 ) [9] Array ( [expr_type] reserved [base_expr] ELSE [sub_tree] [position] 132 ) [10] Array ( [expr_type] const [base_expr] out of stock [sub_tree] [position] 137 ) [11] Array ( [expr_type] reserved [base_expr] END [sub_tree] [position] 160 ) ) [position] 66 ) [5] Array ( [expr_type] colref [alias] [base_expr] t4.c1 [sub_tree] [position] 181 ) [6] Array ( [expr_type] expression [alias] Array ( [as] 1 [name] subquery [base_expr] as subquery [position] 228 ) [base_expr] (select c1c2 from t1 inner_t1 limit 1) [sub_tree] Array ( [0] Array ( [expr_type] subquery [base_expr] (select c1c2 from t1 inner_t1 limit 1) [sub_tree] Array ( [SELECT] Array ( [0] Array ( [expr_type] expression [alias] [base_expr] c1c2 [sub_tree] Array ( [0] Array ( [expr_type] colref [base_expr] c1 [sub_tree] [position] 196 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 198 ) [2] Array ( [expr_type] colref [base_expr] c2 [sub_tree] [position] 199 ) ) [position] 196 ) ) [FROM] Array ( [0] Array ( [expr_type] table [table] t1 [alias] Array ( [as] [name] inner_t1 [base_expr] inner_t1 [position] 210 ) [join_type] JOIN [ref_type] [ref_clause] [base_expr] t1 inner_t1 [sub_tree] [position] 207 ) ) [LIMIT] Array ( [offset] [rowcount] 1 ) ) [position] 188 ) ) [position] 188 ) ) [OPTIONS] Array ( [0] DISTINCT [1] FOR UPDATE [2] LOCK IN SHARE MODE ) [INTO] Array ( [0] a1 [1] a2 [2] a3 [3] outfile [4] /xyz ) [FROM] Array ( [0] Array ( [expr_type] table [table] t1 [alias] Array ( [as] [name] the_t1 [base_expr] the_t1 [position] 267 ) [join_type] JOIN [ref_type] [ref_clause] [base_expr] t1 the_t1 [sub_tree] [position] 264 ) [1] Array ( [expr_type] table [table] t2 [alias] [join_type] LEFT [ref_type] USING [ref_clause] Array ( [0] Array ( [expr_type] colref [base_expr] c1 [sub_tree] [position] 299 ) [1] Array ( [expr_type] colref [base_expr] c2 [sub_tree] [position] 302 ) ) [base_expr] t2 using(c1,c2) [sub_tree] [position] 290 ) [2] Array ( [expr_type] table [table] t3 [alias] Array ( [as] 1 [name] tX [base_expr] as tX [position] 314 ) [join_type] JOIN [ref_type] ON [ref_clause] Array ( [0] Array ( [expr_type] colref [base_expr] tX.c1 [sub_tree] [position] 323 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 329 ) [2] Array ( [expr_type] colref [base_expr] the_t1.c1 [sub_tree] [position] 331 ) ) [base_expr] t3 as tX ON tX.c1 the_t1.c1 [sub_tree] [position] 311 ) [3] Array ( [expr_type] table [table] t4 [alias] Array ( [as] [name] t4_x [base_expr] t4_x [position] 349 ) [join_type] JOIN [ref_type] USING [ref_clause] Array ( [0] Array ( [expr_type] colref [base_expr] x [sub_tree] [position] 360 ) ) [base_expr] t4 t4_x using(x) [sub_tree] [position] 346 ) ) [WHERE] Array ( [0] Array ( [expr_type] colref [base_expr] c1 [sub_tree] [position] 369 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 372 ) [2] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 374 ) [3] Array ( [expr_type] operator [base_expr] and [sub_tree] [position] 376 ) [4] Array ( [expr_type] colref [base_expr] c2 [sub_tree] [position] 380 ) [5] Array ( [expr_type] operator [base_expr] in [sub_tree] [position] 383 ) [6] Array ( [expr_type] in-list [base_expr] (1,2,3, apple) [sub_tree] Array ( [0] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 387 ) [1] Array ( [expr_type] const [base_expr] 2 [sub_tree] [position] 389 ) [2] Array ( [expr_type] const [base_expr] 3 [sub_tree] [position] 391 ) [3] Array ( [expr_type] const [base_expr] apple [sub_tree] [position] 394 ) ) [position] 386 ) [7] Array ( [expr_type] operator [base_expr] and [sub_tree] [position] 403 ) [8] Array ( [expr_type] reserved [base_expr] exists [sub_tree] [position] 407 ) [9] Array ( [expr_type] subquery [base_expr] ( select 1 from some_other_table another_table where x 1) [sub_tree] Array ( [SELECT] Array ( [0] Array ( [expr_type] const [alias] [base_expr] 1 [sub_tree] [position] 423 ) ) [FROM] Array ( [0] Array ( [expr_type] table [table] some_other_table [alias] Array ( [as] [name] another_table [base_expr] another_table [position] 447 ) [join_type] JOIN [ref_type] [ref_clause] [base_expr] some_other_table another_table [sub_tree] [position] 430 ) ) [WHERE] Array ( [0] Array ( [expr_type] colref [base_expr] x [sub_tree] [position] 467 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 469 ) [2] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 471 ) ) ) [position] 414 ) [10] Array ( [expr_type] operator [base_expr] and [sub_tree] [position] 474 ) [11] Array ( [expr_type] bracket_expression [base_expr] (zebra orange or 1 1) [sub_tree] Array ( [0] Array ( [expr_type] const [base_expr] zebra [sub_tree] [position] 479 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 487 ) [2] Array ( [expr_type] const [base_expr] orange [sub_tree] [position] 489 ) [3] Array ( [expr_type] operator [base_expr] or [sub_tree] [position] 498 ) [4] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 501 ) [5] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 503 ) [6] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 505 ) ) [position] 478 ) ) [GROUP] Array ( [0] Array ( [expr_type] pos [base_expr] 1 [position] 517 ) [1] Array ( [expr_type] pos [base_expr] 2 [position] 520 ) ) [HAVING] Array ( [0] Array ( [expr_type] aggregate_function [base_expr] sum [sub_tree] Array ( [0] Array ( [expr_type] colref [base_expr] c2 [sub_tree] [position] 533 ) ) [position] 529 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 537 ) [2] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 539 ) ) [ORDER] Array ( [0] Array ( [expr_type] pos [base_expr] 2 [direction] ASC [position] 550 ) [1] Array ( [expr_type] alias [base_expr] c1 [direction] DESC [position] 553 ) ) [LIMIT] Array ( [offset] 0 [rowcount] 10 ) )说明SELECT[1]别名节点中的base_expr as \c2在 Wiki 源码中因换行被拆成两行展示实际语义为别名子句as c2 的原始文本。五、解析树的顶层骨架11 个关键分区从上面的完整输出可以看到php-sql-parser 把整条 SELECT 语句切分成顶层分区键associative array 的 key官方 Parser-Manual.md 称之为 sections本例中一共出现 11 个顶层键含义本例内容SELECT查询列表达式列表7 个元素算术表达式、聚合、CASE、列引用、子查询OPTIONS查询级修饰选项DISTINCT、FOR UPDATE、LOCK IN SHARE MODEINTO结果写入目标a1/a2/a3用户变量与outfile /xyzFROM表与连接4 张表t1/t2/t3/t4及各自 JOIN 条件WHERE过滤条件12 个线性节点比较、IN、EXISTS、括号表达式GROUP分组2 个位置参数1,2HAVING分组后过滤sum(c2) 1ORDER排序位置参数2 ASC与别名c1 DESCLIMIT分页offset 0, rowcount 10每个分区下的条目item是解析树的基本单元它们共享一套公共字段expr_type条目类型本示例中出现了expression表达式、const常量、operator操作符、colref列引用、aggregate_function聚合函数、reserved保留字如 CASE/WHEN/THEN/ELSE/END/EXISTS、in-listIN 列表、bracket_expression括号表达式、subquery子查询、table表、pos位置参数、alias别名引用共 12 种base_expr该条目在 SQL 中的原始文本切片sub_tree子节点数组叶子节点为null/空用于承载嵌套表达式positionbase_expr在原始 SQL 字符串中的起始字符偏移量仅在$calcPositions true时出现alias别名信息可能是空、字符串或数组部分类型有专属字段例如table类型还有table/join_type/ref_type/ref_clauseLIMIT分区有offset/rowcount。六、SELECT 分区深读七种列表达式的解析形态SELECT分区最能体现解析器的表达式子树设计本例 7 个元素逐一说明1. 算术表达式 隐式别名12 c1[expr_type] expression [base_expr] 12 [sub_tree] [const:1] [operator:] [const:2]12是一个二元运算表达式sub_tree按中缀顺序线性展开为常量 → 操作符 → 常量三个叶子节点。alias节点记录了省略AS的隐式别名c1[as]为空表示没有AS关键字。2. 反引号别名1 2 as \c2第二个元素与第一个几乎一致但alias节点的[as] 1表示显式书写了AS关键字且别名c2使用了 MySQL 反引号。值得注意的是输出中base_expr as \c2连在一起——这是 Wiki 源码换行造成的展示效果实际取值是as c2。反引号别名是 MySQL 方言的标志性语法官方测试 [backtickTest.php](https://link.gitcode.com/i/51b056b20a08b2c77debcdcb48909187) 专门覆盖了c1.some column as an alias、GROUP BY an alias 等场景验证别名与带空格列名的解析。3. 聚合函数sum(c2)sum(c2)被识别为aggregate_functionbase_expr只保留函数名sum真正的实参c2放在sub_tree里类型为colref。sum(c3) as sum_c3则在aggregate_function节点上附加了带AS的alias。这说明解析器把函数调用与调用实参分层建模——后续HAVING中的sum(c2)也是同样的结构。4. CASE WHEN 表达式Status CASE ... END case_statement这是 SELECT 分区里最复杂的单个节点。外层是一个expression左侧是常量Status注意Status被解析为const而非列名这是 MySQL 默认把双引号当字符串常量处理的结果如需把双引号当标识符可开启ansi_quotes选项见 Options.php中间是操作符右侧则是 CASE 表达式。CASE 表达式的sub_tree是一串11 个线性节点reserved(CASE) → reserved(WHEN) → colref(quantity) → operator() → const(0) → reserved(THEN) → const(in stock) → reserved(ELSE) → const(out of stock) → reserved(END)。解析器没有为 CASE 发明专门类型而是用reserved保留字把CASE/WHEN/THEN/ELSE/END的骨架标记出来条件分支和取值分支则作为普通表达式节点穿插其中——这正体现了该解析器非校验型、语法面完整的设计取向它忠实记录 token 顺序与角色而不做语义校验。5. 限定列引用t4.c1t4.c1是最简单的colref叶子节点base_expr保留完整限定名没有sub_treeposition为 181。6. 标量子查询(select c1c2 from t1 inner_t1 limit 1) as subqueryexpr_type为expression其sub_tree内嵌一个subquery节点而subquery的sub_tree又是一棵完整的迷你解析树拥有自己的SELECT、FROM、LIMIT分区[sub_tree] Array ( [SELECT] [expression: c1c2] // 同样展开为 colref operator colref [FROM] [table: t1, alias: inner_t1, join_type: JOIN] [LIMIT] [offset , rowcount 1] )子查询是递归解析的DefaultProcessor会对括号包裹的语句再次走完整解析流程因此在读取解析树时需要递归遍历sub_tree才能还原完整结构。WHERE分区里的EXISTS子查询也是同样的递归形态。七、FROM / WHERE / 分组排序分区深读FROM 分区四种连接的统一建模FROM分区包含 4 个table节点官方手册特别提示Every table item is a join, but it may not have any join criteria——即每个表条目本质上都是一个 join区别只在于有无连接条件。本例恰好展示了四种组合表join_typeref_typeref_clause说明t1 the_t1JOIN空空首表无连接条件alias为隐式别名the_t1t2 using(c1,c2)LEFTUSING[colref:c1, colref:c2]LEFT OUTER JOIN简写为LEFTref_clause是列引用数组t3 as tX ON tX.c1 the_t1.c1JOINON[colref, operator, colref]ON 条件复用 SELECT 的表达式节点模型t4 t4_x using(x)JOINUSING[colref:x]隐式别名 USING 单列要点join_type只记录连接类型的核心词JOIN/LEFTLEFT OUTER JOIN中的OUTER被归一化掉ref_type二选一USING值是一组 colref或ON值是一个完整表达式子树表别名与列别名共用同一个alias数组结构as/name/base_expr/position。WHERE 分区12 个线性节点组成的条件链WHERE 是一个扁平数组4 组条件用and操作符串起来c1 1→colref(c1) → operator() → const(1)c2 in (1,2,3, apple)→colref(c2) → operator(in) → in-list(...)。in-list的sub_tree是 4 个const叶子数字与字符串混排base_expr保留了完整的(1,2,3, apple)原文exists ( select 1 from some_other_table another_table where x 1)→reserved(exists) → subquery(...)。EXISTS被当作保留字标记其后紧跟递归解析的子查询树内含 SELECT/FROM/WHERE 三个分区(zebra orange or 1 1)→ 单个bracket_expression节点sub_tree内是 7 个线性子节点const → operator → const → operator(or) → const → operator → const。这种整条 WHERE 平铺成线性节点数组的设计意味着解析结果不区分优先级、不建二叉语法树括号用bracket_expression单独打包。消费方如果要做优先级推导需要自行处理官方定位本来就是记录而非校验。GROUP / HAVING / ORDER / LIMITGROUPgroup by 1, 2里的1、2是按输出列位置分组的语法解析器专门用expr_type pospositional reference位置引用来标记base_expr为数字本身HAVINGsum(c2) 1与 SELECT 中聚合函数的建模完全一致aggregate_function(sum(c2)) → operator() → const(1)说明同一套表达式节点模型在多个子句间复用ORDERORDER BY 2, c1 DESC的第一个元素是expr_type pos位置参数2默认方向ASC第二个是expr_type alias按别名c1排序方向DESC——排序依据的类型列/别名/位置被显式区分每个节点还带direction字段LIMIT没有走通用节点模型而是扁平化为offset 0与rowcount 10两个字段子查询中的limit 1同样如此offset为空。OPTIONS 与 INTO 分区OPTIONS收集查询级修饰符本例把DISTINCT、FOR UPDATE、LOCK IN SHARE MODE三个词按出现顺序放入一个纯字符串数组。官方 README 的入门示例里STRAIGHT_JOIN也出现在该分区INTO同样是一个扁平数组into a1, a2, a3 into outfile /xyz被拆成a1、a2、a3、outfile、/xyz五项——用户变量名与outfile关键字、文件路径字符串处于同一层级进一步印证忠实记录、不做深度归并的解析风格。八、position 字段定位原始 SQL 的利器由于调用时传入了$calcPositions true输出中每个节点都带有position。对照原始 SQL 可以验证其语义select DISTINCT 12中1位于第 16 个字符0 起始所以expression(12)的position为 16、const(1)为 16、operator()为 17、const(2)为 18c1别名位置 22、c2位置 44 等全部与 SQL 字符串逐字符对应。该字段由 PositionCalculator.php 在解析后统一回填setPositionsWithinSQL($sql, $queries)对把解析结果映射回原始 SQL 做高亮/改写这类需求至关重要但官方明确说明位置计算有额外开销纯做结构分析时建议传false关闭。九、在 ShowDoc 中的真实落地CREATE TABLE → Markdown 数据库文档回到本文开头的应用场景ShowDoc 的数据库文档功能依赖同一个解析器。在 server/app/Common/Helper/Convert.php 的convertSqlToArray()方法中项目正是基于本文剖析的解析树结构来提取建表信息的$parser new PHPSQLParser(); $parsed $parser-parse($sql); // 1. 通过顶层 CREATE 分区判断是否为建表语句 if (!isset($parsed[CREATE]) || ($parsed[CREATE][expr_type] ?? ) ! table) { return $result; } // 2. 从 TABLE 分区的 create-def 子树逐列读取字段 $fields $tableNode[create-def][sub_tree]; foreach ($fields as $field) { // 跳过 PRIMARY KEY / UNIQUE 等 constraint 行 if (($field[sub_tree][0][expr_type] ?? ) constraint) { continue; } // 在列定义中寻找 contenteditable="false">【免费下载链接】showdocShowDoc is a tool greatly applicable for an IT team to share documents online一个非常适合IT团队的在线API文档、技术文档工具项目地址: https://gitcode.com/gh_mirrors/sh/showdoc创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考 ShowDoc 技术栈探秘：php-sql-parser 复杂 SELECT 语句解析树全解
ShowDoc 技术栈探秘php-sql-parser 复杂 SELECT 语句解析树全解【免费下载链接】showdocShowDoc is a tool greatly applicable for an IT team to share documents online一个非常适合IT团队的在线API文档、技术文档工具项目地址: https://gitcode.com/gh_mirrors/sh/showdoc本篇文章以 php-sql-parserPHP-SQL-Parser纯 PHP 实现的 MySQL 方言 SQL 解析器官方 Wiki 中的Complex-Example文档为核心通过一段五脏俱全的复杂 SELECT 语句完整拆解其解析树parse tree的每一个节点结构。在 ShowDoc 项目中这个解析器正是数据库文档功能里把CREATE TABLE语句自动转换成 Markdown 表格的底层引擎见 Convert.php理解它的输出结构既能帮你读懂 SQL 词法/语法分析结果也能为基于该解析器二次开发如 SQL 转文档、SQL 静态分析、SQL 格式化打下扎实基础。读完本文你将掌握 php-sql-parser 的调用方式、expr_type/base_expr/sub_tree/position/alias等核心字段的语义以及 SELECT 全子句表达式、CASE、子查询、多表 JOIN、IN、EXISTS、GROUP/HAVING/ORDER/LIMIT、INTO、锁选项在解析树中的真实形态。一、文档与解析器背景Complex-Example.md是 php-sql-parser 官方 Wiki 的一篇核心示例文档位于本仓库的 server/vendor/greenlion/php-sql-parser/wiki/Complex-Example.md。它不同于简单的SELECT a FROM t WHERE d 5而是刻意把 SELECT 语法中几乎所有常见甚至冷门的子句塞进一条语句里去重选项、算术表达式、反引号别名、聚合函数、CASE WHEN 分支、标量子查询、INTO 变量、INTO OUTFILE、四种表连接方式普通 JOIN、LEFT OUTER JOIN USING、JOIN ON、JOIN USING、多层 WHERE 条件IN 列表、EXISTS 子查询、括号表达式、GROUP BY 位置参数、HAVING 聚合过滤、ORDER BY 双向排序、LIMIT 偏移、FOR UPDATE与LOCK IN SHARE MODE行锁选项——几乎覆盖了官方 Parser-Manual.md 中提到的 SELECT 全部语法面。按照 README.md 的定位说明该解析器有以下三个关键特性纯 PHP 实现、无外部依赖不依赖 PECL 扩展整个解析逻辑由 PHPSQLParser.php 及其processors44 个处理器、lexer、positions、utils等子模块完成非校验型non-validating解析器它假设你传入的是语法上基本正确的 SQL专注点是完整、准确地解析 MySQL 方言而不是做语法校验也不以性能优化为首要目标The focus is not on optimizing for performance完整支持 SELECT / INSERT / UPDATE / DELETE / REPLACE / RENAME / SHOW / SET / DROP / CREATE INDEX / CREATE TABLE / EXPLAIN / DESCRIBE等语句类型并支持 UNION、子查询与复合语句。二、调用方式构造函数与 parse() 方法在深入解析树之前先确认官方推荐的两类用法详见 Parser-Manual.md// 方式一构造函数直接传入 SQL内部自动调用 parse() $parser new PHPSQLParser(select 1); print_r($parser-parsed); // 方式二先实例化再显式调用 parse() $parser new PHPSQLParser(); $parsed $parser-parse(select 2); print_r($parsed);构造函数签名与parse()方法的真实实现在 PHPSQLParser.php 中一目了然public function __construct($sql false, $calcPositions false, array $options array()) { $this-options new Options($options); if ($sql) { $this-parse($sql, $calcPositions); } } public function parse($sql, $calcPositions false) { $processor new DefaultProcessor($this-options); $queries $processor-process($sql); // calc the positions of some important tokens if ($calcPositions) { $calculator new PositionCalculator(); $queries $calculator-setPositionsWithinSQL($sql, $queries); } $this-parsed $queries; return $this-parsed; }由此可以确认三个重要事实第二个参数$calcPositions决定输出中是否携带[position]字段——它由 PositionCalculator.php 负责把每个base_expr在原始 SQL 字符串中的字符偏移量计算出来。官方手册明确提示position 的计算需要额外时间如果应用不需要可以传 false第三个参数$options由 Options.php 承载目前支持两个开关consistent_sub_trees让子树结构保持一致性和ansi_quotes按 ANSI 标准把双引号当作标识符引用符而非字符串常量解析结果保存在公开属性$parser-parsed中parse()同时返回它。此外解析器还提供自定义函数注册能力addCustomFunction()/removeCustomFunction()/getCustomFunctions()同样定义在 PHPSQLParser.php用于让解析器识别业务自定义的 SQL 函数名。三、核心示例一段覆盖全部 SELECT 语法的 SQL这是Complex-Example.md的灵魂部分。下面这段 SQL 刻意在同一语句中混合了别名、表达式、聚合、CASE、子查询、多表连接、IN/EXISTS、分组、排序、分页、变量/文件导出和行锁选项require_once(php-sql-parser.php); $sql select DISTINCT 12 c1, 1 2 as c2, sum(c2),sum(c3) as sum_c3,Status CASE WHEN quantity 0 THEN \in stock\ ELSE \out of stock\ END case_statement , t4.c1, (select c1c2 from t1 inner_t1 limit 1) as subquery into a1, a2, a3 from t1 the_t1 left outer join t2 using(c1,c2) join t3 as tX ON tX.c1 the_t1.c1 join t4 t4_x using(x) where c1 1 and c2 in (1,2,3, apple) and exists ( select 1 from some_other_table another_table where x 1) and (zebra orange or 1 1) group by 1, 2 having sum(c2) 1 ORDER BY 2, c1 DESC LIMIT 0, 10 into outfile /xyz FOR UPDATE LOCK IN SHARE MODE; $parser new PHPSQLParser($sql, true); print_r($parser-parsed);注意这里使用了new PHPSQLParser($sql, true)——第二个参数为true因此输出中每个节点都带有[position]字段。下面第四节先给出该文档附带的完整输出第五八节再逐段剖析。四、完整解析树输出原文档原文Array ( [SELECT] Array ( [0] Array ( [expr_type] expression [alias] Array ( [as] [name] c1 [base_expr] c1 [position] 22 ) [base_expr] 12 [sub_tree] Array ( [0] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 16 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 17 ) [2] Array ( [expr_type] const [base_expr] 2 [sub_tree] [position] 18 ) ) [position] 16 ) [1] Array ( [expr_type] expression [alias] Array ( [as] 1 [name] c2 [base_expr] as c2 [position] 31 ) [base_expr] 1 2 [sub_tree] Array ( [0] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 26 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 27 ) [2] Array ( [expr_type] const [base_expr] 2 [sub_tree] [position] 29 ) ) [position] 26 ) [2] Array ( [expr_type] aggregate_function [alias] [base_expr] sum [sub_tree] Array ( [0] Array ( [expr_type] colref [base_expr] c2 [sub_tree] [position] 44 ) ) [position] 40 ) [3] Array ( [expr_type] aggregate_function [alias] Array ( [as] 1 [name] sum_c3 [base_expr] as sum_c3 [position] 56 ) [base_expr] sum [sub_tree] Array ( [0] Array ( [expr_type] colref [base_expr] c3 [sub_tree] [position] 52 ) ) [position] 48 ) [4] Array ( [expr_type] expression [alias] Array ( [as] [name] case_statement [base_expr] case_statement [position] 164 ) [base_expr] Status CASE WHEN quantity 0 THEN in stock ELSE out of stock END [sub_tree] Array ( [0] Array ( [expr_type] const [base_expr] Status [sub_tree] [position] 66 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 75 ) [2] Array ( [expr_type] reserved [base_expr] CASE [sub_tree] [position] 77 ) [3] Array ( [expr_type] reserved [base_expr] WHEN [sub_tree] [position] 90 ) [4] Array ( [expr_type] colref [base_expr] quantity [sub_tree] [position] 95 ) [5] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 104 ) [6] Array ( [expr_type] const [base_expr] 0 [sub_tree] [position] 106 ) [7] Array ( [expr_type] reserved [base_expr] THEN [sub_tree] [position] 108 ) [8] Array ( [expr_type] const [base_expr] in stock [sub_tree] [position] 113 ) [9] Array ( [expr_type] reserved [base_expr] ELSE [sub_tree] [position] 132 ) [10] Array ( [expr_type] const [base_expr] out of stock [sub_tree] [position] 137 ) [11] Array ( [expr_type] reserved [base_expr] END [sub_tree] [position] 160 ) ) [position] 66 ) [5] Array ( [expr_type] colref [alias] [base_expr] t4.c1 [sub_tree] [position] 181 ) [6] Array ( [expr_type] expression [alias] Array ( [as] 1 [name] subquery [base_expr] as subquery [position] 228 ) [base_expr] (select c1c2 from t1 inner_t1 limit 1) [sub_tree] Array ( [0] Array ( [expr_type] subquery [base_expr] (select c1c2 from t1 inner_t1 limit 1) [sub_tree] Array ( [SELECT] Array ( [0] Array ( [expr_type] expression [alias] [base_expr] c1c2 [sub_tree] Array ( [0] Array ( [expr_type] colref [base_expr] c1 [sub_tree] [position] 196 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 198 ) [2] Array ( [expr_type] colref [base_expr] c2 [sub_tree] [position] 199 ) ) [position] 196 ) ) [FROM] Array ( [0] Array ( [expr_type] table [table] t1 [alias] Array ( [as] [name] inner_t1 [base_expr] inner_t1 [position] 210 ) [join_type] JOIN [ref_type] [ref_clause] [base_expr] t1 inner_t1 [sub_tree] [position] 207 ) ) [LIMIT] Array ( [offset] [rowcount] 1 ) ) [position] 188 ) ) [position] 188 ) ) [OPTIONS] Array ( [0] DISTINCT [1] FOR UPDATE [2] LOCK IN SHARE MODE ) [INTO] Array ( [0] a1 [1] a2 [2] a3 [3] outfile [4] /xyz ) [FROM] Array ( [0] Array ( [expr_type] table [table] t1 [alias] Array ( [as] [name] the_t1 [base_expr] the_t1 [position] 267 ) [join_type] JOIN [ref_type] [ref_clause] [base_expr] t1 the_t1 [sub_tree] [position] 264 ) [1] Array ( [expr_type] table [table] t2 [alias] [join_type] LEFT [ref_type] USING [ref_clause] Array ( [0] Array ( [expr_type] colref [base_expr] c1 [sub_tree] [position] 299 ) [1] Array ( [expr_type] colref [base_expr] c2 [sub_tree] [position] 302 ) ) [base_expr] t2 using(c1,c2) [sub_tree] [position] 290 ) [2] Array ( [expr_type] table [table] t3 [alias] Array ( [as] 1 [name] tX [base_expr] as tX [position] 314 ) [join_type] JOIN [ref_type] ON [ref_clause] Array ( [0] Array ( [expr_type] colref [base_expr] tX.c1 [sub_tree] [position] 323 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 329 ) [2] Array ( [expr_type] colref [base_expr] the_t1.c1 [sub_tree] [position] 331 ) ) [base_expr] t3 as tX ON tX.c1 the_t1.c1 [sub_tree] [position] 311 ) [3] Array ( [expr_type] table [table] t4 [alias] Array ( [as] [name] t4_x [base_expr] t4_x [position] 349 ) [join_type] JOIN [ref_type] USING [ref_clause] Array ( [0] Array ( [expr_type] colref [base_expr] x [sub_tree] [position] 360 ) ) [base_expr] t4 t4_x using(x) [sub_tree] [position] 346 ) ) [WHERE] Array ( [0] Array ( [expr_type] colref [base_expr] c1 [sub_tree] [position] 369 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 372 ) [2] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 374 ) [3] Array ( [expr_type] operator [base_expr] and [sub_tree] [position] 376 ) [4] Array ( [expr_type] colref [base_expr] c2 [sub_tree] [position] 380 ) [5] Array ( [expr_type] operator [base_expr] in [sub_tree] [position] 383 ) [6] Array ( [expr_type] in-list [base_expr] (1,2,3, apple) [sub_tree] Array ( [0] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 387 ) [1] Array ( [expr_type] const [base_expr] 2 [sub_tree] [position] 389 ) [2] Array ( [expr_type] const [base_expr] 3 [sub_tree] [position] 391 ) [3] Array ( [expr_type] const [base_expr] apple [sub_tree] [position] 394 ) ) [position] 386 ) [7] Array ( [expr_type] operator [base_expr] and [sub_tree] [position] 403 ) [8] Array ( [expr_type] reserved [base_expr] exists [sub_tree] [position] 407 ) [9] Array ( [expr_type] subquery [base_expr] ( select 1 from some_other_table another_table where x 1) [sub_tree] Array ( [SELECT] Array ( [0] Array ( [expr_type] const [alias] [base_expr] 1 [sub_tree] [position] 423 ) ) [FROM] Array ( [0] Array ( [expr_type] table [table] some_other_table [alias] Array ( [as] [name] another_table [base_expr] another_table [position] 447 ) [join_type] JOIN [ref_type] [ref_clause] [base_expr] some_other_table another_table [sub_tree] [position] 430 ) ) [WHERE] Array ( [0] Array ( [expr_type] colref [base_expr] x [sub_tree] [position] 467 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 469 ) [2] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 471 ) ) ) [position] 414 ) [10] Array ( [expr_type] operator [base_expr] and [sub_tree] [position] 474 ) [11] Array ( [expr_type] bracket_expression [base_expr] (zebra orange or 1 1) [sub_tree] Array ( [0] Array ( [expr_type] const [base_expr] zebra [sub_tree] [position] 479 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 487 ) [2] Array ( [expr_type] const [base_expr] orange [sub_tree] [position] 489 ) [3] Array ( [expr_type] operator [base_expr] or [sub_tree] [position] 498 ) [4] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 501 ) [5] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 503 ) [6] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 505 ) ) [position] 478 ) ) [GROUP] Array ( [0] Array ( [expr_type] pos [base_expr] 1 [position] 517 ) [1] Array ( [expr_type] pos [base_expr] 2 [position] 520 ) ) [HAVING] Array ( [0] Array ( [expr_type] aggregate_function [base_expr] sum [sub_tree] Array ( [0] Array ( [expr_type] colref [base_expr] c2 [sub_tree] [position] 533 ) ) [position] 529 ) [1] Array ( [expr_type] operator [base_expr] [sub_tree] [position] 537 ) [2] Array ( [expr_type] const [base_expr] 1 [sub_tree] [position] 539 ) ) [ORDER] Array ( [0] Array ( [expr_type] pos [base_expr] 2 [direction] ASC [position] 550 ) [1] Array ( [expr_type] alias [base_expr] c1 [direction] DESC [position] 553 ) ) [LIMIT] Array ( [offset] 0 [rowcount] 10 ) )说明SELECT[1]别名节点中的base_expr as \c2在 Wiki 源码中因换行被拆成两行展示实际语义为别名子句as c2 的原始文本。五、解析树的顶层骨架11 个关键分区从上面的完整输出可以看到php-sql-parser 把整条 SELECT 语句切分成顶层分区键associative array 的 key官方 Parser-Manual.md 称之为 sections本例中一共出现 11 个顶层键含义本例内容SELECT查询列表达式列表7 个元素算术表达式、聚合、CASE、列引用、子查询OPTIONS查询级修饰选项DISTINCT、FOR UPDATE、LOCK IN SHARE MODEINTO结果写入目标a1/a2/a3用户变量与outfile /xyzFROM表与连接4 张表t1/t2/t3/t4及各自 JOIN 条件WHERE过滤条件12 个线性节点比较、IN、EXISTS、括号表达式GROUP分组2 个位置参数1,2HAVING分组后过滤sum(c2) 1ORDER排序位置参数2 ASC与别名c1 DESCLIMIT分页offset 0, rowcount 10每个分区下的条目item是解析树的基本单元它们共享一套公共字段expr_type条目类型本示例中出现了expression表达式、const常量、operator操作符、colref列引用、aggregate_function聚合函数、reserved保留字如 CASE/WHEN/THEN/ELSE/END/EXISTS、in-listIN 列表、bracket_expression括号表达式、subquery子查询、table表、pos位置参数、alias别名引用共 12 种base_expr该条目在 SQL 中的原始文本切片sub_tree子节点数组叶子节点为null/空用于承载嵌套表达式positionbase_expr在原始 SQL 字符串中的起始字符偏移量仅在$calcPositions true时出现alias别名信息可能是空、字符串或数组部分类型有专属字段例如table类型还有table/join_type/ref_type/ref_clauseLIMIT分区有offset/rowcount。六、SELECT 分区深读七种列表达式的解析形态SELECT分区最能体现解析器的表达式子树设计本例 7 个元素逐一说明1. 算术表达式 隐式别名12 c1[expr_type] expression [base_expr] 12 [sub_tree] [const:1] [operator:] [const:2]12是一个二元运算表达式sub_tree按中缀顺序线性展开为常量 → 操作符 → 常量三个叶子节点。alias节点记录了省略AS的隐式别名c1[as]为空表示没有AS关键字。2. 反引号别名1 2 as \c2第二个元素与第一个几乎一致但alias节点的[as] 1表示显式书写了AS关键字且别名c2使用了 MySQL 反引号。值得注意的是输出中base_expr as \c2连在一起——这是 Wiki 源码换行造成的展示效果实际取值是as c2。反引号别名是 MySQL 方言的标志性语法官方测试 [backtickTest.php](https://link.gitcode.com/i/51b056b20a08b2c77debcdcb48909187) 专门覆盖了c1.some column as an alias、GROUP BY an alias 等场景验证别名与带空格列名的解析。3. 聚合函数sum(c2)sum(c2)被识别为aggregate_functionbase_expr只保留函数名sum真正的实参c2放在sub_tree里类型为colref。sum(c3) as sum_c3则在aggregate_function节点上附加了带AS的alias。这说明解析器把函数调用与调用实参分层建模——后续HAVING中的sum(c2)也是同样的结构。4. CASE WHEN 表达式Status CASE ... END case_statement这是 SELECT 分区里最复杂的单个节点。外层是一个expression左侧是常量Status注意Status被解析为const而非列名这是 MySQL 默认把双引号当字符串常量处理的结果如需把双引号当标识符可开启ansi_quotes选项见 Options.php中间是操作符右侧则是 CASE 表达式。CASE 表达式的sub_tree是一串11 个线性节点reserved(CASE) → reserved(WHEN) → colref(quantity) → operator() → const(0) → reserved(THEN) → const(in stock) → reserved(ELSE) → const(out of stock) → reserved(END)。解析器没有为 CASE 发明专门类型而是用reserved保留字把CASE/WHEN/THEN/ELSE/END的骨架标记出来条件分支和取值分支则作为普通表达式节点穿插其中——这正体现了该解析器非校验型、语法面完整的设计取向它忠实记录 token 顺序与角色而不做语义校验。5. 限定列引用t4.c1t4.c1是最简单的colref叶子节点base_expr保留完整限定名没有sub_treeposition为 181。6. 标量子查询(select c1c2 from t1 inner_t1 limit 1) as subqueryexpr_type为expression其sub_tree内嵌一个subquery节点而subquery的sub_tree又是一棵完整的迷你解析树拥有自己的SELECT、FROM、LIMIT分区[sub_tree] Array ( [SELECT] [expression: c1c2] // 同样展开为 colref operator colref [FROM] [table: t1, alias: inner_t1, join_type: JOIN] [LIMIT] [offset , rowcount 1] )子查询是递归解析的DefaultProcessor会对括号包裹的语句再次走完整解析流程因此在读取解析树时需要递归遍历sub_tree才能还原完整结构。WHERE分区里的EXISTS子查询也是同样的递归形态。七、FROM / WHERE / 分组排序分区深读FROM 分区四种连接的统一建模FROM分区包含 4 个table节点官方手册特别提示Every table item is a join, but it may not have any join criteria——即每个表条目本质上都是一个 join区别只在于有无连接条件。本例恰好展示了四种组合表join_typeref_typeref_clause说明t1 the_t1JOIN空空首表无连接条件alias为隐式别名the_t1t2 using(c1,c2)LEFTUSING[colref:c1, colref:c2]LEFT OUTER JOIN简写为LEFTref_clause是列引用数组t3 as tX ON tX.c1 the_t1.c1JOINON[colref, operator, colref]ON 条件复用 SELECT 的表达式节点模型t4 t4_x using(x)JOINUSING[colref:x]隐式别名 USING 单列要点join_type只记录连接类型的核心词JOIN/LEFTLEFT OUTER JOIN中的OUTER被归一化掉ref_type二选一USING值是一组 colref或ON值是一个完整表达式子树表别名与列别名共用同一个alias数组结构as/name/base_expr/position。WHERE 分区12 个线性节点组成的条件链WHERE 是一个扁平数组4 组条件用and操作符串起来c1 1→colref(c1) → operator() → const(1)c2 in (1,2,3, apple)→colref(c2) → operator(in) → in-list(...)。in-list的sub_tree是 4 个const叶子数字与字符串混排base_expr保留了完整的(1,2,3, apple)原文exists ( select 1 from some_other_table another_table where x 1)→reserved(exists) → subquery(...)。EXISTS被当作保留字标记其后紧跟递归解析的子查询树内含 SELECT/FROM/WHERE 三个分区(zebra orange or 1 1)→ 单个bracket_expression节点sub_tree内是 7 个线性子节点const → operator → const → operator(or) → const → operator → const。这种整条 WHERE 平铺成线性节点数组的设计意味着解析结果不区分优先级、不建二叉语法树括号用bracket_expression单独打包。消费方如果要做优先级推导需要自行处理官方定位本来就是记录而非校验。GROUP / HAVING / ORDER / LIMITGROUPgroup by 1, 2里的1、2是按输出列位置分组的语法解析器专门用expr_type pospositional reference位置引用来标记base_expr为数字本身HAVINGsum(c2) 1与 SELECT 中聚合函数的建模完全一致aggregate_function(sum(c2)) → operator() → const(1)说明同一套表达式节点模型在多个子句间复用ORDERORDER BY 2, c1 DESC的第一个元素是expr_type pos位置参数2默认方向ASC第二个是expr_type alias按别名c1排序方向DESC——排序依据的类型列/别名/位置被显式区分每个节点还带direction字段LIMIT没有走通用节点模型而是扁平化为offset 0与rowcount 10两个字段子查询中的limit 1同样如此offset为空。OPTIONS 与 INTO 分区OPTIONS收集查询级修饰符本例把DISTINCT、FOR UPDATE、LOCK IN SHARE MODE三个词按出现顺序放入一个纯字符串数组。官方 README 的入门示例里STRAIGHT_JOIN也出现在该分区INTO同样是一个扁平数组into a1, a2, a3 into outfile /xyz被拆成a1、a2、a3、outfile、/xyz五项——用户变量名与outfile关键字、文件路径字符串处于同一层级进一步印证忠实记录、不做深度归并的解析风格。八、position 字段定位原始 SQL 的利器由于调用时传入了$calcPositions true输出中每个节点都带有position。对照原始 SQL 可以验证其语义select DISTINCT 12中1位于第 16 个字符0 起始所以expression(12)的position为 16、const(1)为 16、operator()为 17、const(2)为 18c1别名位置 22、c2位置 44 等全部与 SQL 字符串逐字符对应。该字段由 PositionCalculator.php 在解析后统一回填setPositionsWithinSQL($sql, $queries)对把解析结果映射回原始 SQL 做高亮/改写这类需求至关重要但官方明确说明位置计算有额外开销纯做结构分析时建议传false关闭。九、在 ShowDoc 中的真实落地CREATE TABLE → Markdown 数据库文档回到本文开头的应用场景ShowDoc 的数据库文档功能依赖同一个解析器。在 server/app/Common/Helper/Convert.php 的convertSqlToArray()方法中项目正是基于本文剖析的解析树结构来提取建表信息的$parser new PHPSQLParser(); $parsed $parser-parse($sql); // 1. 通过顶层 CREATE 分区判断是否为建表语句 if (!isset($parsed[CREATE]) || ($parsed[CREATE][expr_type] ?? ) ! table) { return $result; } // 2. 从 TABLE 分区的 create-def 子树逐列读取字段 $fields $tableNode[create-def][sub_tree]; foreach ($fields as $field) { // 跳过 PRIMARY KEY / UNIQUE 等 constraint 行 if (($field[sub_tree][0][expr_type] ?? ) constraint) { continue; } // 在列定义中寻找 contenteditable="false">【免费下载链接】showdocShowDoc is a tool greatly applicable for an IT team to share documents online一个非常适合IT团队的在线API文档、技术文档工具项目地址: https://gitcode.com/gh_mirrors/sh/showdoc创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考