MySQL CTE 递归深度如何避免意外超限

MySQL 递归 CTE 偶尔报“递归深度超过限制”，最稳妥的处理不是直接把上限调得很大，而是把三层边界分开：递归成员里的业务停止条件负责让查询自然结束，cte_max_recursion_depth 负责拦截异常深度，时间和行数限制负责兜底。先写停止条件，再按查询设置护栏，最后用会话变量和返回结果复查，才能避免“这次不报错、下次又拖垮连接”的情况。

先区分业务终止条件与深度上限

递归 CTE 通常由一个非递归成员产生起点，再由递归成员根据上一轮结果产生下一轮。只要递归成员还能产生新行，查询就会继续。MySQL 8.4 文档中的 cte_max_recursion_depth 是 Global、Session 均可用的动态整数变量，默认值为 1000；服务器在递归层数超过这个值时终止 CTE。

因此它只能回答“最多允许深入多少层”，不能回答“业务应该在哪一天或哪一级结束”。例如组织树应由层级或节点访问规则结束，日期序列应由目标日期结束。把上限改成一个很大的数，只会把错误的停止条件推迟暴露。

在递归成员中写出可解释的停止条件

下面用日期序列说明边界。非递归成员先给出起始日期，递归成员每轮加一天；WHERE 让下一轮只在没有越过终点时产生新行：

WITH RECURSIVE calendar (day_no, day_value) AS (
  -- 非递归成员：明确序列起点，并把层数设为 1
  SELECT 1, CAST('2026-01-01' AS DATE)
  UNION ALL
  -- 递归成员：只生成终点以内的下一天
  SELECT day_no + 1, day_value + INTERVAL 1 DAY
  FROM calendar
  WHERE day_value 

这个写法的预期边界是 31 行，但不要只凭“应该是 31 行”判断成功。实际业务中还要考虑起点为空、终点早于起点、连接表产生一对多扩张，以及路径可能回到已经访问过的节点。对树遍历，要把“下一节点仍存在”与“不要重复访问”一起设计；对日期或数字序列，则应显式保留层数或范围字段。

用会话级 cte_max_recursion_depth 设置护栏

业务停止条件明确后，再按本次查询可能达到的合理层数设置会话值。会话设置只影响当前连接，适合报表、批处理或单个接口做局部保护：

-- 先读取当前连接的值，避免把别的连接配置当成当前配置
SHOW SESSION VARIABLES LIKE 'cte_max_recursion_depth';

-- 只给当前连接留出 64 层递归空间，阻止异常路径无限扩大
SET SESSION cte_max_recursion_depth = 64;

-- 业务查询放在同一连接中，执行后按需恢复到连接池约定值
WITH RECURSIVE tree (node_id, depth) AS (
  -- 起点：从指定根节点开始，深度为 0
  SELECT id, 0 FROM category WHERE id = 10
  UNION ALL
  -- 下一层：深度加一，并限制不超过会话护栏
  SELECT c.id, tree.depth + 1
  FROM category AS c
  JOIN tree ON c.parent_id = tree.node_id
  WHERE tree.depth 

这里的 63 是示例中的业务层数边界，和会话上限 64 形成一层余量；实际值应根据数据模型和调用契约决定。不要把 max_sp_recursion_depth 混进来，它针对存储过程递归，不是 CTE 的变量。若确需调整全局值，应明确它影响之后建立的会话，并评估所有调用方的资源预算。

叠加单条查询的时间与行数限制

当递归路径可能很慢，MySQL 官方还提供时间护栏。MAX_EXECUTION_TIME 作用于包含它的 SELECT；也可以先设置当前会话的 max_execution_time。对于 MySQL 8.0.19 及之后版本，递归成员还支持 LIMIT，它可以限制递归 CTE 向外层返回的行数：

WITH RECURSIVE numbers (n) AS (
  -- 起点行不参与递归计算
  SELECT 1
  UNION ALL
  -- LIMIT 是行数兜底，WHERE 仍应承担业务停止职责
  SELECT n + 1 FROM numbers
  WHERE n 

这个组合表示“到达业务终点、返回 10000 行或运行 1000 毫秒时停止”，具体先触发哪条边界取决于数据和执行情况。若你的版本或 SQL 形态不适合递归成员 LIMIT，至少保留业务条件、会话深度和时间限制三者中的前两层；不要用外层 LIMIT 误以为递归生成也会立即停止。

用变量、EXPLAIN 和结果边界复查

复查不要只看“查询没有报错”。可以按下面的顺序核对：

-- 用同一连接复查变量与递归计划；输出行数需结合真实数据判断
SHOW SESSION VARIABLES
WHERE Variable_name IN ('cte_max_recursion_depth', 'max_execution_time');

EXPLAIN
WITH RECURSIVE numbers (n) AS (
  -- 固定起点，便于对照返回边界
  SELECT 1
  UNION ALL
  -- 递归成员有明确上界，避免把计划检查变成无界查询
  SELECT n + 1 FROM numbers WHERE n 

如果频繁撞到深度上限，先回到递归成员检查条件是否真的会收敛；如果结果行数远超预期，再检查连接是否一对多扩张或是否缺少去重。只有确认业务确实需要更深层级时，才逐步提高会话值，并同步观察执行时间和临时结果集规模。

相关问题

把 cte_max_recursion_depth 调大就能解决报错吗？

只能在业务确实需要更多层、停止条件仍然可靠时解决“合法深度不够”的问题。若递归不收敛，调大只会延后终止。

为什么写了外层 LIMIT 仍然很慢？

外层限制的是最终读取，不等同于递归成员生成上限。需要时在递归成员中使用版本支持的 LIMIT，并叠加时间护栏。

会话设置会影响其他连接吗？

SET SESSION 只影响当前连接；连接池复用连接时，应在执行前明确设置或在执行后恢复约定值。

一句话收束：先让递归条件在业务上自然结束，再用会话深度、查询时间和递归行数做三道护栏，最后用变量、计划和结果边界复查。