递归 CTE 终止条件怎么配置或排查

MySQL 递归 CTE 的终止条件，首先要写在递归成员的查询里，让每一轮数据朝着明确的终点推进；外层 SELECT 的 WHERE 只能过滤最终结果，不能保证递归过程提前停止。排查“不退出”或“多一行”时，先看这个条件，再看服务端的递归深度和超时保护。

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

先确认递归成员真的在向终点前进

一个可控的递归 CTE 至少有三个要素：锚点行、递归成员和收敛条件。以生成日期为例，锚点从起始日期开始，递归成员每天加一天，dt 保证下一轮最终没有新行。这里使用小于号，是因为希望包含结束日期但不生成结束日期之后的日期。

WITH RECURSIVE day_list (dt) AS (
    -- 锚点只生成起始日期，决定 dt 的类型和初始值
    SELECT DATE('2026-01-01')
    UNION ALL
    -- 每轮向结束日期推进一天，不能让 dt 原地不变
    SELECT dt + INTERVAL 1 DAY
    FROM day_list
    -- 结束日期需要包含在结果中，所以这里限制“当前日期小于结束日期”
    WHERE dt 

最常见的两个错误正好相反：写成 dt 往往会多生成一行，写成固定不变的 SELECT dt 则永远不会收敛。层级表也一样，应该让 depth 增加，或让路径集合排除已经访问过的节点；不要只在外层写 WHERE depth ，那只是在结果阶段裁剪。

配置上限时，别把安全阀当业务条件

业务条件负责说明“什么时候应该结束”，服务端上限负责说明“异常时最多允许跑到哪里”。MySQL 8.4 手册列出的 cte_max_recursion_depth 默认值是 1000，作用域为 Global、Session；它超过阈值后终止 CTE，但不能修复一个逻辑上永远为真的 WHERE 条件。

-- 只限制当前连接，避免把临时排查参数扩散到新会话
SET SESSION cte_max_recursion_depth = 32;
-- 给本次连接中的 SELECT 设置毫秒级超时保护
SET SESSION max_execution_time = 1000;

WITH RECURSIVE seq (n) AS (
    -- 锚点从 1 开始，给 n 一个明确的整数类型
    SELECT 1
    UNION ALL
    -- 递归成员继续增长，但业务条件和 LIMIT 共同限制规模
    SELECT n + 1
    FROM seq
    WHERE n 

递归成员里的 LIMIT 是 MySQL 8.0.19 起支持的写法。它限制返回到外层的行数，适合给序列生成或可预估的遍历加硬上限；版本较老时不要照搬。若查询已经失控，可以从另一会话执行 KILL QUERY，但这只是中止手段，仍应回头修递归成员。

报错时沿着三个结果判断

不要看到“超过递归深度”就立刻把全局值调大。先用同一组输入做最小复现，并分别观察下面三类证据：

-- 先看当前会话的保护值，再解释查询结构
SHOW SESSION VARIABLES LIKE 'cte_max_recursion_depth';
EXPLAIN WITH RECURSIVE day_list (dt) AS (
    -- 保留与正式查询一致的锚点，便于比较边界
    SELECT DATE('2026-01-01')
    UNION ALL
    -- 用可证明会收敛的条件生成下一日期
    SELECT dt + INTERVAL 1 DAY FROM day_list
    WHERE dt 

EXPLAIN 只能帮助你看计划和递归成员，不能替代对实际边界的判断。若层级数据允许回指父节点，单纯增加深度上限只能把问题延后；应在递归列中携带路径并排除已访问节点，或改用 UNION DISTINCT 消除重复行（前提是去重语义确实符合业务）。

把终止条件变成上线前检查清单

• 递归成员每轮是否改变了日期、层级或游标列，并且变化方向明确。
• 结束值是否需要包含，锚点是否已经占用一层，结果是否允许多条起始记录。
• 连接条件是否可能重复扩张，循环数据是否有路径去重策略。
• 生产连接是否有会话级深度和时间保护，异常时是否有人能执行 KILL QUERY。
• 不要用调大全局 cte_max_recursion_depth 掩盖条件错误；先保留原始失败输入和递归边界。

实际配置时，我更愿意把业务终止条件写得足够小、足够可解释，再给单个会话设置略高于正常上限的保护值。这样即使脏数据触发循环，也会留下清晰的失败信号，而不是把数据库拖进无界递归。

相关问题

外层 SELECT 加 LIMIT 能停止递归吗？

不能把它当作递归成员的终止条件。需要控制递归生成时，应在递归 SELECT 中使用支持版本的 LIMIT，并保留逻辑 WHERE。

为什么已经写了 WHERE，仍然超过 1000 层？

可能是每轮数据没有朝终点变化，也可能存在循环或一对多扩张。先检查递归列的变化和连接结果，再决定是否调整会话深度。

cte_max_recursion_depth 应该直接改成很大吗？

不建议。它是防失控的上限，不是修复条件的方案；优先修正递归成员，再按真实业务最大层数设置会话值。