MySQL recursive CTE 怎样用 cte_max_recursion_depth 控制深度

我在把组织树、日期序列这类递归查询交给 MySQL 时，最容易混淆的是：cte_max_recursion_depth 不是“让递归自动停下来的业务条件”，而是一层服务器保护。真正决定结果边界的，仍然是递归 SELECT 里的 WHERE 条件；参数只负责给这次查询设一个最大递归预算。

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

实用做法是先写对递归终止条件，再用 SESSION 级 cte_max_recursion_depth 为本次查询设置足够但不过度的深度；如果递归可能很慢，再叠加 max_execution_time。

先看懂递归 CTE 的两条边界

一个递归 CTE 通常由初始成员和递归成员组成。初始成员给出起点，递归成员从 CTE 自身继续取下一层；例如生成 1 到 5 的序列：

WITH RECURSIVE numbers (n) AS (
    SELECT 1                          -- 初始成员：从 1 开始
    UNION ALL
    SELECT n + 1 FROM numbers
    WHERE n 

这里有两条不同的边界：n 决定结果应该在哪里结束，cte_max_recursion_depth 决定服务器最多允许递归多少层。前者缺失时，后者只能在失控后兜底；后者过小，则一个本来合法的树深度也可能被提前终止。

遇到深度错误时，先修终止条件

如果把 WHERE n 去掉，递归成员会持续生成新行。MySQL 8.4 手册说明，服务器会在 CTE 递归超过 cte_max_recursion_depth 允许的层数时终止执行，默认值是 1000。这个错误通常说明查询边界不完整，而不是应该直接把参数调到很大。

WITH RECURSIVE numbers (n) AS (
    SELECT 1                          -- 保留可追踪的起点
    UNION ALL
    SELECT n + 1 FROM numbers
    -- 这里故意缺少终止条件，生产查询不要照搬
)
SELECT n FROM numbers;

排查时我会按这个顺序看：递归列是否真的向目标靠近；WHERE 是否引用了递归列；日期、层级或父子键是否存在不会变化的情况；以及 UNION ALL 是否在业务上可能不断产生重复路径。只有确认终止条件合理后，才进入参数调整。

用 SESSION 级参数给一次查询设置预算

cte_max_recursion_depth 的作用域包含 Global 和 Session，并且可以动态设置。开发或接口请求中，优先使用 SESSION，避免把一个深度较大的报表需求变成所有新连接的默认行为。

SET SESSION cte_max_recursion_depth = 5000; -- 只提高当前连接的递归预算

WITH RECURSIVE org (id, parent_id, depth) AS (
    SELECT id, parent_id, 1                -- 初始成员：根节点深度从 1 计
    FROM department
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.parent_id, org.depth + 1
    FROM department AS d
    JOIN org ON d.parent_id = org.id
    WHERE org.depth 

参数值不是越大越好。值设为 0 会让允许的递归层数变成零，适合用来快速阻止此类查询，但不能替代权限、SQL 审核或资源治理。全局设置会影响之后建立的会话；已经存在的连接应以自己的 SESSION 值为准，生产变更前要确认连接池的会话初始化策略。

深度够了但仍然很慢，再加时间保护

深度限制和执行时间限制解决的是两种不同风险：前者限制递归层数，后者限制 SELECT 最长运行时间。MySQL 手册给出的做法是设置会话级 max_execution_time，单位是毫秒；也可以使用查询级优化器提示。

SET SESSION cte_max_recursion_depth = 5000; -- 给合法树深留出预算
SET SESSION max_execution_time = 1000;      -- 本次连接的 SELECT 最多运行 1 秒

WITH RECURSIVE dates (d) AS (
    SELECT DATE('2026-01-01')               -- 初始日期
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM dates
    WHERE d 

把错误响应归到正确原因

可以用一个小表快速判断：

总结一下：cte_max_recursion_depth 是递归层数的护栏，不是循环退出条件。文章里的示例只用于说明静态关系，正式接入时要把终止列、会话初始化和超时策略一起纳入 SQL 评审。

相关问题

把 cte_max_recursion_depth 调大能解决无限递归吗？

不能。它只会把错误推迟到更深层，甚至增加资源消耗；应先修复递归成员的终止条件。

应该改 SESSION 还是 GLOBAL？

单个报表或接口优先改 SESSION。只有确认所有新会话都需要同一预算，并且连接池初始化策略清楚时，才考虑 GLOBAL。