with recursive 是 mysql 8.0+ 唯一原生递归机制，用于安全高效处理树形结构；必须确保锚点与递归部分列数类型一致、用 union all、禁用聚合与排序，并通过 where 隐式终止。
WITH RECURSIVE 是 MySQL 8.0+ 唯一原生支持递归查询的机制，不依赖存储过程或应用层拼接。它不是“可选技巧”，而是处理树形结构（如部门、分类、员工汇报线）的刚需能力——如果你还在用自连接写三层嵌套或 find_in_set() 模拟，大概率已经踩坑了。
为什么不能用普通 JOIN 实现无限层级？
普通 JOIN 只能固定层数：两层查子节点要 JOIN 一次，三层就得再 JOIN 一次。一旦层级动态变化（比如某部门有 5 级，另一部门只有 2 级），SQL 就得重写。而 WITH RECURSIVE 自动迭代，直到没有新行产生为止。
常见错误现象：ERROR 3636 (HY000): Recursive query aborted after 1001 iterations —— 这不是语法错，是隐含循环（比如父子 ID 写反、parent_id 指向自身、缺失终止条件）触发了 MySQL 默认 1000 次迭代保护。
- 必须确保锚点（initial query）和递归部分（recursive query）的列数、类型、顺序严格一致
-
UNION ALL是强制要求（UNION会去重但性能差，且可能意外截断合法重复数据） - 递归部分中不能出现
GROUP BY、LIMIT、ORDER BY、聚合函数 - 终止条件靠
WHERE子句中的逻辑隐式实现，例如WHERE d.parent_id = dt.id无匹配时自然停止
怎么写一个安全的子节点递归查询？
以查部门 id = 1 的所有下级为例，核心是锚点取根，递归查“当前结果的子节点”：
WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id, 0 AS level FROM department WHERE id = 1 UNION ALL SELECT d.id, d.name, d.parent_id, dt.level + 1 FROM department d INNER JOIN dept_tree dt ON d.parent_id = dt.id ) SELECT * FROM dept_tree ORDER BY level, id;
关键点：
- 锚点语句里
WHERE id = 1必须命中真实存在的记录，否则整个 CTE 返回空 —— 不报错，但结果不符合预期 -
INNER JOIN是推荐写法；若用LEFT JOIN，会引入NULL行并破坏递归链 -
level字段建议加上，既便于排序，也方便后续加WHERE level 控制深度防爆栈 - 字段别名（如
0 AS level）必须显式声明，否则递归部分无法对齐列类型
如何生成带路径的面包屑（如 “技术部 > 后端组 > Java组”）？
路径拼接依赖字符串累积，需注意初始值类型与长度限制：
WITH RECURSIVE category_path AS ( SELECT id, name, parent_id, CAST(name AS CHAR(1000)) AS path FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id, CONCAT(cp.path, ' > ', c.name) FROM categories c INNER JOIN category_path cp ON c.parent_id = cp.id ) SELECT id, name, path FROM category_path;
容易被忽略的细节：
-
CAST(name AS CHAR(1000))不可省略：MySQL 默认CONCAT返回VARCHAR(255)，超长会被静默截断 - 锚点里
parent_id IS NULL是典型根节点判断，若业务中根节点用parent_id = 0，这里必须同步改成= 0 -
CONCAT(cp.path, ' > ', c.name)中，cp.path在递归中始终是非 NULL 的（因为锚点已初始化），不用担心空值污染 - 如果路径过长（比如超过 1000 字符），建议在最终
SELECT加SUBSTRING(path, 1, 500)防止前端渲染异常
查父节点链（向上追溯 CEO）和查子节点有什么本质区别？
方向相反，但写法几乎对称：锚点仍是目标节点，递归部分改为“找当前节点的父节点”：
WITH RECURSIVE manager_chain AS ( SELECT id, name, parent_id, 0 AS depth FROM employees WHERE id = 1093 -- 目标员工 UNION ALL SELECT e.id, e.name, e.parent_id, mc.depth + 1 FROM employees e INNER JOIN manager_chain mc ON e.id = mc.parent_id ) SELECT * FROM manager_chain ORDER BY depth DESC;
这里最易错的是 ON e.id = mc.parent_id —— 很多人习惯性写成 ON mc.id = e.parent_id（那是查子节点的逻辑），方向反了就会查不出任何结果。
另外，向上查通常层级有限（CEO 就是终点），但要注意 parent_id 为 NULL 或 0 时是否代表终止。若表设计中 CEO 的 parent_id 是 0，递归部分的 WHERE 或 ON 条件必须兼容，否则最后一级就断了。
真正麻烦的从来不是语法，而是数据质量：环状引用（A→B→C→A）、parent_id 指向不存在的 id、混合使用 NULL 和 0 表示根节点——这些都会让递归中途失败或无限循环。上线前务必用 SELECT COUNT(*) 对比手工展开的层级结果，而不是只看“没报错”。
