MySQL 8.0 WithRecursive语法如何实现递归查询？

本文共计1343个文字，预计阅读时间需要6分钟。

markdownWITH 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 <= 5 控制深度防爆栈
• 字段别名（如 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(*) 对比手工展开的层级结果，而不是只看“没报错”。