MySQL recursive CTE怎样限制递归查询深度

我在整理部门树形组织数据时，最容易踩的坑不是 WITH RECURSIVE 本身，而是把“业务允许走几层”和“数据库最多递归几层”混成一件事。MySQL 的稳妥做法是三层控制：递归成员先写业务停止条件，当前连接再设置 cte_max_recursion_depth，最后按需要增加结果行数和执行时间兜底。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

先把业务停止条件写进递归成员

如果查询的是组织、目录或评论树，真正的终止理由通常来自数据：已经到达允许的层级，或者已经找到目标节点。把条件写在递归成员里，查询即使换到另一台服务器，也不会因为服务器默认值不同而改变业务结果。

-- 先用业务层级限制树的展开范围
WITH RECURSIVE org AS (
    -- 锚点：从指定员工开始，根节点深度为 0
    SELECT id, parent_id, name, 0 AS depth
    FROM employee
    WHERE id = 1001

    UNION ALL

    -- 递归成员：只继续展开未达到边界的节点
    SELECT e.id, e.parent_id, e.name, org.depth + 1
    FROM employee AS e
    JOIN org ON e.parent_id = org.id
    WHERE org.depth 

这里的 20 是业务选择，不是 MySQL 的默认值。若数据可能存在环，还要额外设计路径去重或访问标记；单纯把深度调大，只会让异常数据消耗更多资源。

cte_max_recursion_depth 控制的是递归层数

MySQL 提供 cte_max_recursion_depth 作为服务器护栏。MySQL 8.4 手册记录它的作用域为 Global、Session，默认值为 1000，允许值从 0 到 4294967295。日常排查先改当前连接，不要为了单条查询直接改全局配置：

-- 会话级设置只影响当前连接，适合单次验证
SET SESSION cte_max_recursion_depth = 100;

-- 读取当前连接真正生效的值
SELECT @@session.cte_max_recursion_depth AS max_depth;

-- 查看全局默认值，供新连接使用
SELECT @@global.cte_max_recursion_depth AS global_max_depth;

这个变量限制的是递归层数，不等于最终返回行数。一个层级很浅但每层分支很多的树，仍然可能产生大量结果；反过来，线性序列的行数也不应直接当成递归深度。生产环境我更愿意让 SQL 自己带着 depth 边界，再把变量当作失控保护。

递归深度、返回行数和执行时间要分开设防

MySQL 8.0.19 起，递归查询的递归成员可以使用 LIMIT 限制返回给最外层查询的行数；对执行缓慢的查询，还可以用会话级 max_execution_time 或 MAX_EXECUTION_TIME 做时间上限。一个用于开发排查的示例是：

-- 递归成员限制最多产生 1000 行
WITH RECURSIVE numbers (n) AS (
    -- 锚点行
    SELECT 1
    UNION ALL
    -- 递归成员：LIMIT 是行数护栏，不是深度条件
    SELECT n + 1 FROM numbers
    LIMIT 1000
)
-- 超过 1 秒就中止当前 SELECT
SELECT /*+ MAX_EXECUTION_TIME(1000) */ n
FROM numbers;

上线前用一组边界样本确认限制真的生效

我通常会准备三组数据：刚好达到允许深度、比允许深度多一层、以及单层分支很多的树。第一组验证业务结果，第二组验证停止条件，第三组观察“层数没超但行数暴涨”的情况。修改会话变量后，确认连接池不会把这个临时值带给下一次业务请求。

• 先确认递归成员存在可证明的终止条件，而不是只依赖默认 1000。
• 需要临时放宽限制时使用 SET SESSION，任务结束后让连接回到连接池清理策略。
• 如果查询已经失控，优先中止当前语句；不要无条件把全局深度改成很大的数。

常见问题

把 cte_max_recursion_depth 设为 0 就是不限制吗？

不要把它当作“无限制”开关使用。官方手册给出了最小值 0，但具体语义仍应以目标版本的行为和测试结果为准；业务停止条件和执行时间兜底不能因此删除。

为什么深度限制没超，查询还是很慢？

深度只描述递归轮数，树的分支数、连接条件、临时结果和返回行数都可能让查询变慢。此时应分别检查递归成员的连接条件、行数上限和执行时间上限。

调大 Session 值后，其他连接也会跟着变化吗？

不会。Session 值只影响当前连接；Global 值会影响之后建立的连接。连接池场景要把临时设置和连接归还策略一起考虑。

最终可以记成一句话：用递归成员表达“应该走到哪里”，用 cte_max_recursion_depth 防止“层数失控”，再用行数和时间限制保护资源。这样既不会把服务器参数当业务逻辑，也能更快定位递归 CTE 的异常。