MySQL 8.0递归查询WITH RECURSIVE用法详解

MySQL 8.0+ 不支持在存储函数或过程中使用 WITH RECURSIVE，因其依赖动态迭代上下文，与函数的确定性、单次执行语义冲突；递归CTE必须作为顶层查询使用，且受深度限制、禁止聚合/排序等硬约束。

这里有个核心结论：在MySQL 8.0+中，想实现递归树状查询，必须依赖 WITH RECURSIVE 这个语法结构。它不是一个可以封装进 FUNCTION 或 PROCEDURE 里复用的函数，这是设计上的根本区别。

为什么不能写成存储函数？

如果你试图在存储函数或过程的SQL体内嵌入 WITH RECURSIVE，MySQL解析器会毫不留情地抛出错误，比如常见的 ERROR 1356 (HY000): View 'xxx' references invalid table(s) or column(s)。这背后是理念的冲突：递归CTE依赖执行时的临时作用域和动态迭代上下文，而存储函数则要求确定性、无副作用和单次执行的语义，两者无法兼容。

• 因此，WITH RECURSIVE 必须作为顶层查询的一部分，想给它“套个壳”放进函数里是行不通的。
• 甚至试图用 PREPARE 和 EXECUTE 在存储过程中动态拼接递归SQL也不行，因为MySQL同样禁止在预处理语句中使用递归CTE。
• 这样一来，替代方案就非常明确了：要么每次查询都手写完整的CTE语句，要么将递归逻辑封装在应用层（比如用Python或Ja va），由程序来拼接和执行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 性能更好。话说回来，在规范的树形结构里，本就不该出现重复记录，用 UNION 去重反而可能掩盖了数据本身的问题。
• 强烈建议在递归步中加上深度控制条件，例如 WHERE dt.depth < 10。否则，一旦数据中存在意外的循环引用，很容易触发默认的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在前，本人在后）。如果省略排序，结果集默认会是“从叶到根”。
• 需要警惕的是，如果表结构允许“矩阵汇报”（即一个员工有多个上级），这个查询会返回多条路径。而且，由于MySQL递归CTE内部不支持 DISTINCT 或 GROUP BY，你无法在递归过程中直接去重。

容易被忽略的三个硬限制

这些不是可商量的最佳实践，而是MySQL运行时强制执行的硬性规定，一旦触发直接报错：

• 递归深度限制：超过默认的1000次迭代会报错 ERROR 3636 (HY000): Recursive query aborted after 1001 iterations。解决方案是调大会话级变量：SET SESSION cte_max_recursion_depth = 3000;（注意，这通常是SESSION级别而非GLOBAL级别的设置）。
• 递归部分禁止聚合与排序：在递归CTE的递归部分（即UNION ALL之后的部分），禁止出现 ORDER BY、LIMIT、GROUP BY、窗口函数或聚合函数。即使你把这些子句写在子查询里试图绕过，解析器也会拦截。
• 缺乏内置的环检测机制：MySQL的递归CTE本身无法自动检测数据中的循环引用（例如A→B→A这样的死循环）。防范措施必须前置：要么依靠业务逻辑约束，要么在表上建立唯一索引（如(id, parent_id)），或者在递归查询的WHERE条件中手动加入类似 WHERE parent_id != id 的过滤，排除自引用。