MySQL 递归 CTE 控制最大层级的参数方案

如果递归 CTE 只是依赖 cte_max_recursion_depth 来“限制最大层级”，通常把两个问题混在了一起：业务上允许展开到哪一层，以及服务器最多允许递归多少层。更稳妥的做法是让查询条件负责正常结束，再用会话参数做兜底保护。

本文以 MySQL 8.4 为例。官方手册地址：https://dev.mysql.com/doc/refman/8.4/en/

推荐组合：递归 CTE 内维护 depth 并写出明确的终止条件；执行前用 SET SESSION cte_max_recursion_depth 设置略高于业务最大深度的安全上限。不要把参数调得很大来替代终止条件。

先记住三个结论：

• depth 决定这条查询何时自然停止，表达业务语义。
• cte_max_recursion_depth 限制递归层数，防止条件写错时无限展开。
• 单条慢查询还需要时间保护，必要时配合 max_execution_time。

为什么递归 CTE 要同时控制两种层级

递归 CTE 通常由一个非递归的初始查询和一个引用自身的递归查询组成。比如组织树从根节点开始，每次取下一层子节点。业务上可能只允许查看 50 层，但这不代表服务器应该把所有递归都放行到 50 层以上。

两类限制的职责不同：

如果只设置服务器参数，查询达到上限时会被终止并返回错误，但这个错误不是业务上的“正常结束”。如果只写业务条件，条件因为改动或脏数据失效时，又缺少最后一道保护。

给递归 CTE 加上业务终止条件

下面的例子用 depth 表示已经展开的层级。根节点是 0，递归部分只有在当前节点小于 50 时才继续产生下一层。这个条件是查询语义的一部分，不是参数调优。

WITH RECURSIVE org_tree AS (
    -- 初始行：从指定根节点开始，根层记为 0
    SELECT id, parent_id, name, 0 AS depth
    FROM organization
    WHERE id = 100

    UNION ALL

    -- 递归行：只有未到业务最大层级时才展开子节点
    SELECT child.id, child.parent_id, child.name, parent.depth + 1
    FROM organization AS child
    JOIN org_tree AS parent ON child.parent_id = parent.id
    WHERE parent.depth 

这里的 depth 表示允许生成深度 1 到 50 的子节点，结果中的最大 depth 是 50。若业务定义“最多 50 层”时把根节点算作第 1 层，就应把初始值和比较条件一起调整，不能只改数字。

还要确认递归连接本身能收敛。树数据中如果存在环，单纯的层级条件只能把输出截断，不能说明数据关系正确；需要额外记录路径或使用业务上的环检测策略。

用会话参数设置安全上限

MySQL 8.4 的 cte_max_recursion_depth 默认值为 1000。它限制 CTE 递归层数，超过上限时服务器终止执行。对单条任务，优先使用会话级设置：

-- 只为当前连接提高上限，不改变其他连接
SET SESSION cte_max_recursion_depth = 64;

-- 查询中的业务条件仍然负责正常结束
WITH RECURSIVE numbers AS (
    -- 初始值是第 0 层
    SELECT 0 AS n
    UNION ALL
    -- 业务边界限制为 50，服务器上限 64 只是安全余量
    SELECT n + 1 FROM numbers WHERE n 

参数值应覆盖业务查询可能需要的递归层数，并留出有限余量；不建议直接设置成极大的值。若把 cte_max_recursion_depth 调成 10，而业务条件需要展开到 50，查询会先触发服务器上限，得到递归深度不足的错误。

SET GLOBAL cte_max_recursion_depth = 64 的影响范围更大，主要用于调整后续新会话的默认值；它不应被当作单条 SQL 的临时开关。生产环境变更全局值前，还要考虑其他应用连接的递归查询预算。

深度上限还不够时，补一层执行时间保护

递归层数不是行数。一个节点可能产生很多子节点，即使层级不深，也可能产生很大的中间结果。因此，深度控制不能替代执行时间控制。

-- 当前连接内把 SELECT 的最长执行时间设为 1000 毫秒
SET SESSION max_execution_time = 1000;

-- 也可以只给这一条 SELECT 指定时间保护
WITH RECURSIVE numbers AS (
    -- 从 0 开始，递归条件仍然要明确
    SELECT 0 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n 

max_execution_time 适用于 SELECT 的执行时间保护，和递归层数是两个维度。它不能修复环、错误的 JOIN 或过宽的结果集，只是在查询耗时过长时尽早中止。

参数速查与常见误区

如果递归 CTE 在默认配置下报错，先检查三件事：业务终止条件是否存在；最深路径需要多少次递归；当前会话实际使用的 @@cte_max_recursion_depth 是多少。确认这三项后，再决定调小查询范围、提高 SESSION 值，还是修复数据关系。

相关问题

问：把 cte_max_recursion_depth 设为 0 就是不限制吗？
答：不要把它当作业务上的无限递归开关。即使放宽递归层数，也仍然需要明确的终止条件，并评估中间结果和执行时间。

问：递归 CTE 有 depth 条件，还需要设置参数吗？
答：建议保留。参数是条件失效、数据出现环或 JOIN 意外放大的安全兜底；它不替代业务条件。

问：调高参数后查询仍然很慢怎么办？
答：检查每层产生的子节点数量、连接条件和返回列，并用执行时间保护限制单次 SELECT；不要只继续提高层级上限。