mysql 8.0+ 不支持在存储函数或过程中使用 with recursive，因其依赖动态迭代上下文，与函数的确定性、单次执行语义冲突；递归cte必须作为顶层查询使用，且受深度限制、禁止聚合/排序等硬约束。
MySQL 8.0+ 不提供递归函数（比如自定义函数里调用自身），必须用 WITH RECURSIVE CTE 实现树状查询；它不是函数，是语法结构，不能封装进 FUNCTION 或 PROCEDURE 中复用。
为什么不能写成存储函数？
MySQL 明确禁止在 FUNCTION 或 PROCEDURE 的 SQL 体内使用 WITH RECURSIVE —— 解析器会直接报错 ERROR 1356 (HY000): View 'xxx' references invalid table(s) or column(s) 或类似语法错误。根本原因是：递归 CTE 依赖执行时的临时作用域和迭代上下文，而函数体要求确定性、无副作用、单次执行语义，二者冲突。
-
WITH RECURSIVE必须作为顶层查询的一部分，不能嵌套在函数/过程的SELECT语句中（哪怕只是包装一层） - 试图用
PREPARE + EXECUTE在存储过程中动态拼接也不行：MySQL 不允许在预处理语句中使用递归 CTE - 替代思路只有两种：每次查询手写完整 CTE；或用应用层（如 Python/Java）封装逻辑并拼 SQL
向下查子树（找所有后代）怎么写？
典型场景：给定部门 ID = 2，查它和所有下级部门。关键在递归步的连接方向 —— 子表的 parent_id 要等于上一轮结果的 id。
WITH RECURSIVE dept_tree AS ( SELECT id, name, parent_id, 1 AS depth FROM departments WHERE id = 2 -- 锚点：从技术部开始 UNION ALL SELECT d.id, d.name, d.parent_id, dt.depth + 1 FROM departments d INNER JOIN dept_tree dt ON d.parent_id = dt.id -- 注意这里是 d.parent_id = dt.id ) SELECT * FROM dept_tree ORDER BY depth, id;
- 若误写成
dt.parent_id = d.id，就变成向上查父节点，结果完全相反 -
UNION ALL比UNION快，除非你明确需要去重（但树结构通常应避免环，去重反而掩盖数据问题） - 务必加
WHERE控制深度，比如WHERE dt.depth ，否则可能触发默认 1000 层限制或死循环
向上查父路径（找所有祖先）怎么写？
典型场景：给员工 ID = 123，查他直属领导、总监、CEO 这条链路。锚点是叶子节点，递归步反向追踪 parent_id。
WITH RECURSIVE path AS ( SELECT id, name, parent_id, 0 AS depth FROM employees WHERE id = 123 UNION ALL SELECT e.id, e.name, e.parent_id, p.depth + 1 FROM employees e INNER JOIN path p ON e.id = p.parent_id -- 关键：用上一轮的 parent_id 去匹配下一轮的 id ) SELECT * FROM path ORDER BY depth DESC;
- 这里
e.id = p.parent_id是向上走的核心逻辑，和向下查的连接条件正好对调 -
ORDER BY depth DESC才能得到“根→叶”顺序（CEO 在前，本人在后）；不加则本人在第一行 - 如果表里存在多父引用（如矩阵汇报），这个查询会返回多条路径，且无法靠
DISTINCT去重 —— 因为 MySQL 递归 CTE 内部不支持DISTINCT或GROUP BY
容易被忽略的三个硬限制
这些不是警告，是运行时强制拦截：
- 递归深度超限会直接报错
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations，需调大全局变量：SET SESSION cte_max_recursion_depth = 3000;（注意是 SESSION 级，非 GLOBAL） - 递归部分禁止出现
ORDER BY、LIMIT、GROUP BY、窗口函数、聚合函数 —— 即使只写在子查询里也报错 - 无法在递归 CTE 中检测环（比如 A→B→A），必须靠业务约束（如加唯一索引
(id, parent_id)）或提前用WHERE parent_id != id过滤自引用
