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

MySQL 递归 CTE 的深度不能只靠一处配置解决。最稳妥的写法是：在递归成员里用业务字段明确停止条件，再用会话级 cte_max_recursion_depth 兜底；如果每层会产生很多分支，还要补充递归成员内的 LIMIT 或执行时间限制。

官方资料：https://dev.mysql.com/doc/refman/8.4/en/with.html

本文只讨论 MySQL 8.0+ 的递归 CTE。重点是“深度”与“行数”并不等价：一棵树即使只有 5 层，也可能展开出很多行。

先分清四种限制分别管什么

递归 CTE 由不递归的 anchor 查询和递归 member 查询组成。真正决定业务层级的通常是 member 里的 WHERE 条件，例如 depth 。它表达的是“最多展开到哪一层”，最接近业务规则。

cte_max_recursion_depth 是服务器对递归层数的保护线；MySQL 8.4 文档给出的默认值是 1000，可以按会话或全局调整。它不替代业务条件，也不等于结果行数上限。递归 member 内的 LIMIT 则限制 CTE 向外层产生的行数，分支较多时比只看深度更直接。max_execution_time 或 MAX_EXECUTION_TIME 负责时间边界。

用 depth 列限制组织树的业务层级

查询组织树、目录树或菜单树时，建议把深度作为 CTE 的显式列。这样读者能看见当前行来自哪一层，递归条件也不会依赖隐含的服务器默认值。

-- depth 从 0 开始，限制从根节点向下展开的业务层级
WITH RECURSIVE org_tree (id, parent_id, name, depth) AS (
    -- anchor：从指定根节点开始
    SELECT id, parent_id, name, 0
    FROM org_unit
    WHERE id = 100

    UNION ALL

    -- member：只继续展开尚未达到上限的父节点
    SELECT child.id, child.parent_id, child.name, parent.depth + 1
    FROM org_unit AS child
    JOIN org_tree AS parent ON child.parent_id = parent.id
    WHERE parent.depth 

这里的 depth 是业务层级条件：根节点为 0，下一层为 1，依此类推。它只控制这条查询想展开的范围，不会把服务器对其他递归 CTE 的保护线一起改掉。

用会话级配置建立递归兜底

当查询来自临时排查、报表任务或应用连接池时，优先使用会话级设置，避免把一个特殊查询的需求扩散到后来建立的所有连接。MySQL 文档说明，递归超过 cte_max_recursion_depth 后服务器会终止 CTE。

-- 只为当前连接设置较小的递归保护线
SET SESSION cte_max_recursion_depth = 50;

-- 用 SET_VAR 只影响这一条 SELECT，避免改变后续语句
WITH RECURSIVE numbers (n) AS (
    -- anchor：生成第一个数字
    SELECT 1
    UNION ALL
    -- member：条件负责正常结束，配置负责异常兜底
    SELECT n + 1 FROM numbers WHERE n 

如果使用连接池，要确认会话变量的生命周期：连接复用时，之前的 SET SESSION 可能仍然存在。更窄的查询级 Hint 适合只想保护某一条语句的场景；全局设置则需要谨慎评估对后续新会话的影响。

分支很多时补上行数和时间边界

只限制深度并不代表结果很小。树结构每层可能有多个子节点，因此还要看结果行数和单次执行耗时。MySQL 允许在递归查询的 member 中使用 LIMIT，这会在生成到指定行数后停止，通常比只在最外层 SELECT 截断更早。

-- 用递归 member 内的 LIMIT 限制最多生成的行数
WITH RECURSIVE seq (n) AS (
    -- anchor：从 1 开始
    SELECT 1
    UNION ALL
    -- member：仍保留正常终止条件，并限制总产出
    SELECT n + 1 FROM seq
    WHERE n 

这三个条件解决的是不同问题：WHERE n 是业务或算法边界，LIMIT 1000 是结果行数边界，MAX_EXECUTION_TIME(1000) 是时间边界。它们可以组合，但不能把超时当成正常的业务终止条件。

按现象排查限制是否放对位置

• 结果比预期多一层：先看 anchor 的初始 depth，以及 member 中是检查 parent.depth 还是递归后的新值。
• 出现递归深度超限：检查是否漏写 WITH RECURSIVE、终止条件是否永远为真，以及会话变量是否被连接池中的旧值覆盖。
• 层数不深但行数暴涨：增加递归 member 内的 LIMIT，同时检查父子连接是否产生重复分支。
• 查询占用时间过长：为当前语句加入 MAX_EXECUTION_TIME，并保留业务终止条件，必要时从另一会话终止查询。

最终可以按这张速查表决定措施：

常见问题

把最外层 SELECT 的 LIMIT 当成递归深度限制可以吗？

不建议。最外层限制只控制最终返回，递归过程可能已经生成了大量中间行。需要尽早止损时，把 LIMIT 放进递归 member，并保留正常的终止条件。

把 cte_max_recursion_depth 调得很大就安全吗？

不安全。它只是提高服务器允许的递归层数，不会解决错误连接、重复分支或缺少终止条件。更合理的组合是明确的 depth 条件，加上适合当前语句的会话或查询级保护。

为什么限制深度后结果仍然很多？

因为深度是树的层级，行数取决于每层的分支数量。层数、行数、时间要分别设边界，不能用一个参数替代全部控制。