WITH RECURSIVE 是处理树形结构的唯一可靠方式，因其能动态遍历未知深度的父子关系，避免硬编码层级、应用层迭代或字符串拼接等不可靠方案；它强制要求非递归种子和递归成员两部分按序定义，中间用 UNION ALL 连接，并依赖 JOIN 或 WHERE 隐含终止条件以防止无限循环。
为什么 WITH RECURSIVE 是处理树形结构的唯一可靠方式
因为标准 SQL 的 JOIN 无法动态展开未知深度的层级关系，而树（如部门、分类、评论回复链）天然具有可变嵌套深度。只有支持递归 CTE 的数据库（PostgreSQL、SQL Server、SQLite 3.8.3+、MySQL 8.0+）能用声明式语法安全表达父子遍历——不写循环、不拼字符串、不依赖应用层迭代。
常见错误是试图用多层 LEFT JOIN 硬编码 3–5 级，结果一加新层级就漏数据；或用存储过程拼接 ID 字符串，导致索引失效、难以调试。
WITH RECURSIVE 必须包含的两个部分及其顺序不能颠倒
递归 CTE 语法强制分两块：非递归种子（anchor） + 递归成员（recursive term），中间用 UNION ALL 连接。顺序错、用 UNION（去重）或漏 ALL 都会报错或死循环。
- 种子部分必须能独立执行，通常查根节点（例如
parent_id IS NULL或level = 0） - 递归部分的
FROM必须引用 CTE 自身名，且关联字段需有索引（如parent_id） - 必须有终止条件隐含在
JOIN或WHERE中（例如子节点parent_id指向父节点id），否则触发最大递归限制错误ERROR: recursive query terminated due to stack overflow
WITH RECURSIVE tree AS ( SELECT id, name, parent_id, 1 AS level FROM categories WHERE parent_id IS NULL -- 种子：根分类 UNION ALL SELECT c.id, c.name, c.parent_id, t.level + 1 FROM categories c INNER JOIN tree t ON c.parent_id = t.id -- 递归：子连父 ) SELECT * FROM tree ORDER BY level, id;
MySQL 8.0 和 PostgreSQL 在递归控制上的关键差异
两者语法一致，但默认行为和调优点不同。MySQL 不限制递归深度，默认 cte_max_recursion_depth = 1000，超限报错 ERROR 3636 (HY000): Recursive query aborted after 1000 iterations；PostgreSQL 默认无硬限制，靠 statement_timeout 或手动加 WHERE level 防失控。
- MySQL 中修改深度需设会话变量：
SET SESSION cte_max_recursion_depth = 500; - PostgreSQL 中推荐显式加深度保护，避免意外卡住连接：
WHERE t.level - 路径构建（如生成
/root/child/grandchild）时，MySQL 的字符串函数（CONCAT）和 PostgreSQL 的（||或array_to_string(ARRAY[...], '/')）写法不同，但逻辑一致
性能陷阱：为什么加了索引还是慢？
递归查询慢，往往不是因为没索引，而是索引没用对。只给 id 加主键不够，必须确保递归 JOIN 的驱动字段（通常是 parent_id）也有索引。更隐蔽的问题是：若种子返回 100 个根节点，每层又展开 10 个子节点，第 5 层就会扫描 10⁵ 行——指数级膨胀。
- 先确认
EXPLAIN（MySQL）或EXPLAIN ANALYZE（PostgreSQL）中递归步骤是否走了索引扫描（Index Scan using idx_parent_id on categories） - 避免在递归分支里做复杂计算或子查询，它们会在每一层重复执行
- 如果只需某一层（如“查所有三级子类”），用
WHERE level = 3提前过滤，别等全展开再LIMIT - 超大数据量（百万级节点）时，考虑物化路径（
path字段存祖先 ID 链表）或闭包表替代
递归查询的边界感很关键：它解决的是“从某点出发能到达哪些节点”，而不是“任意两点间最短路径”。后者得用图算法，SQL 不适合。
