MySQL CTE 递归查询为什么会超过默认深度

执行 WITH RECURSIVE 查询时，如果递归层数超过 MySQL 的默认限制，常见结果是语句被终止，而不是“数据库坏了”。先看递归项能否在有限层内产生空结果，再看数据是否形成环；只有业务确实允许超过 1000 层时，才考虑提高 cte_max_recursion_depth，并同时保留时间或行数保护。

为什么会超过默认深度：递归终止由两道边界共同决定

递归 CTE 由锚点查询和递归查询组成。锚点产生初始行，递归 SELECT 只处理上一轮结果；当递归 SELECT 产生不出新行时自然停止。如果 WHERE 始终为真，或者关联关系把数据重新连回已经访问过的节点，就会继续生成下一轮。

MySQL 还会用 cte_max_recursion_depth 兜底。8.4 手册给出的默认值是 1000，超过该深度后服务器终止 CTE。因此“默认深度报错”只能说明 SQL 触碰了保护边界，不能单独证明需要把数值调大。

先检查锚点、递归项和循环数据

值班时先复制原 SQL 到低风险会话，不要立即改全局变量。重点检查三件事：锚点是否只选根节点；递归项是否沿正确的父子方向连接；终止条件是否随着层级变化而收紧。下面这个例子给组织架构增加显式层数，便于观察是哪一层开始异常：

-- 先限制演练范围，并让层数成为可观察字段
WITH RECURSIVE org (employee_id, manager_id, depth) AS (
    -- 锚点：只从没有上级的根节点开始
    SELECT employee_id, manager_id, 0
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 递归项：沿 manager_id 找直接下属
    SELECT e.employee_id, e.manager_id, o.depth + 1
    FROM employees AS e
    JOIN org AS o ON e.manager_id = o.employee_id
    WHERE o.depth 

若根节点本身不止一个、manager_id 允许自指，或一组员工互相指向，递归项就可能重复走回旧节点。层数上限能止损，但不能替代数据修复；还要把异常行单独查出来，确认是否存在 employee_id = manager_id 或闭环关系。

先修终止条件，再调整递归上限

业务层级确实可能超过 1000 层时，可以在当前会话临时调整变量，避免影响新连接。先读取现值，测试完恢复：

-- 记录当前会话值，只影响本连接
SELECT @@SESSION.cte_max_recursion_depth;
SET SESSION cte_max_recursion_depth = 2000;

-- 额外增加 2 秒语句保护，防止慢查询长期占用资源
SELECT /*+ MAX_EXECUTION_TIME(2000) */ employee_id, manager_id
FROM org;

-- 排查结束后恢复常见默认值，避免污染后续测试
SET SESSION cte_max_recursion_depth = DEFAULT;

MAX_EXECUTION_TIME 控制的是 SELECT 的执行时间，LIMIT 控制递归查询产生的行数；两者和深度上限解决的不是同一个问题。真实层级查询可在递归 SELECT 中加入合理的 LIMIT，但上线前仍要修好终止条件与环检测逻辑。

回滚路径、告警确认与复盘清单

临时调大上限只在会话内做，确认结果后执行 SET SESSION cte_max_recursion_depth = DEFAULT。如果查询已在生产连接中持续运行，可由另一会话使用 KILL QUERY 终止，不要直接重启实例。告警确认至少包含错误时间、连接账号、SQL 摘要、递归深度配置和扫描到的异常层级。

复盘时保留原 SQL、修订后的终止谓词、会话变量前后值和一组最小复现数据。这样下一次出现“CTE 超过默认深度”时，能先判断是合法深树还是不收敛，而不是把全局阈值越改越大。

相关问题

cte_max_recursion_depth 调大后还会超时吗？

会。它只限制递归层数，不保证查询在可接受时间内完成，仍应按查询设置执行时间上限。

把 UNION ALL 改成 UNION DISTINCT 能解决环吗？

它可以消除重复行，适合部分传递闭包场景，但不能替代明确的终止条件，也不能修复错误的层级数据。

为什么只在大数据量环境出现？

小数据可能恰好在保护边界前结束；数据量增大后，分支数、路径长度或闭环更容易把递归推过深度和时间限制。

事实依据：MySQL 8.4 WITH（Common Table Expressions） 与 MySQL 8.4 Server System Variables。