MySQL CTE 递归查询怎么限制层数避免无限展开

层级菜单、组织架构、商品分类都可能用到 MySQL 递归 CTE。要限制递归查询的层数，最稳妥的做法不是单独调大或调小某个系统变量，而是把业务停止条件写进递归成员，再用会话级递归上限和必要的行数、耗时限制兜底。这样即使数据出现环，也不会让查询无限展开。

实际写法通常是三层保护：递归 SQL 用 depth 控制业务深度，当前会话用 cte_max_recursion_depth 防止递归失控，调试或高风险查询再加递归 LIMIT 与 MAX_EXECUTION_TIME。

先把“层数限制”和“防失控”分成两道门

递归 CTE 由锚点查询和递归成员组成。锚点先产生根节点，递归成员拿上一轮结果继续找子节点；当递归成员不再产生新行时，查询自然结束。开发时最容易犯的错，是只依赖 MySQL 抛出的递归深度错误，却没有定义业务上允许的最大层数。

几个限制参数解决的不是同一个问题：

用一个层级树把终止条件写进递归成员

下面假设 category_node 保存分类树，parent_id 指向父节点。示例同时维护 depth 和 path：前者限制最大层数，后者阻止脏数据中的父子环重新访问同一节点。

WITH RECURSIVE node_tree (id, parent_id, name, depth, path) AS (
  -- 锚点：从根节点开始，根层记为 0
  SELECT id, parent_id, name, 0,
         CAST(id AS CHAR(200))
  FROM category_node
  WHERE parent_id IS NULL

  UNION ALL

  -- 递归成员：只接收未超深、未回到旧节点的子节点
  SELECT child.id,
         child.parent_id,
         child.name,
         parent.depth + 1,
         CONCAT(parent.path, '/', child.id)
  FROM node_tree AS parent
  JOIN category_node AS child
    ON child.parent_id = parent.id
  WHERE parent.depth 

这里的 parent.depth 是关键。根节点为 0，根的子节点为 1，因此结果最多包含 0 到 4 层。若需求是“只展示四层”，可以把上限写成 3；不要把返回行数误当成层数，因为一层可能有很多兄弟节点。

CAST(id AS CHAR(200)) 也不是装饰。递归 CTE 的列类型以非递归部分为准，路径在递归中不断变长，先预留宽度可以避免路径被截断。

cte_max_recursion_depth 只能做兜底，别拿它代替 WHERE

开发或排查时，可以先把当前会话的递归上限设得保守一些。它只影响当前连接，不会把业务查询自动改造成“最多四层”。如果把值调得很大，错误的连接条件或环形数据仍然会持续生成中间结果。

-- 只在当前会话设置保护值，避免影响新连接
SET SESSION cte_max_recursion_depth = 64;

-- 调试时先限制递归成员产生的总行数
WITH RECURSIVE probe (n) AS (
  -- 产生一个起点
  SELECT 1
  UNION ALL
  -- 递归成员故意保留简单条件，LIMIT 负责试跑边界
  SELECT n + 1 FROM probe
  LIMIT 20
)
SELECT n FROM probe;

-- 高风险 SELECT 可增加单条语句的毫秒级时间上限
SELECT /*+ MAX_EXECUTION_TIME(1000) */ id, name
FROM category_node;

递归成员的 LIMIT 控制的是 CTE 产生的行数，不是树的层数；MAX_EXECUTION_TIME 控制的是查询时间。它们适合试跑和最后一道保险，不能替代 WHERE 中对深度和环的明确判断。

看到 3636 错误时按三列数据复查

如果出现“Recursive query aborted”一类错误，先别急着把 cte_max_recursion_depth 调大。把递归结果临时投影为 id、depth、path，再按下面顺序检查：

1. 递归成员是否真的让 depth 逐轮增加，终止条件是否写在递归成员内部。
2. 连接方向是否正确，是否存在某条记录让子节点重新指回祖先。
3. 路径字段是否足够长，是否因为截断导致已访问判断失效。
4. 确认业务深度后，再按需要调整会话递归上限，而不是直接修改全局值。

相关问题

递归 CTE 的最大层数应该写多少？

按业务模型决定。菜单、组织架构通常可以设置一个明确的产品上限；未知数据先用较小值试跑，再根据实际最大深度调整。

只在外层 SELECT 写 LIMIT 可以防止无限递归吗？

不一定。递归成员里的 LIMIT 能尽早停止生成，外层 LIMIT 主要限制最终读取的结果，排查失控查询时优先把限制放进递归部分。

为什么设置了递归上限仍然报错？

因为上限是保护阈值，不是修复条件。检查 WHERE、连接关系和环检测；如果确实需要更深层级，再在当前会话提高上限并配合耗时保护。

把“业务什么时候停止”和“服务器最多允许跑多深”分开，MySQL 递归 CTE 才容易解释、调试和上线。层级上限负责正确性，递归次数、行数和耗时限制负责把错误控制在可恢复范围内。