MySQL 8.4 递归 CTE 怎么防止无限展开：深度上限、环检测与路径验收

部门树、目录树、评论回复场景里，经常要一次性查出指定节点下的所有后代。MySQL 8.4 提供的递归 CTE 可以把这类层级查询合并成单条 SQL，但如果底层数据存在自引用或者环状指向，查询就会不停生成重复的递归层级。稳妥的实现方案是同时配置深度上限、维护访问路径做环检测，最后通过结果集校验确认停止条件生效。

先确定层级查询的验收口径

假设 org_node(id,parent_id,name) 存储部门关联关系，输入查询根节点 10。我们希望返回节点 10 及其所有后代，每行数据同步返回当前层级和完整访问路径；如果某条分支里出现节点指向已经访问过的旧节点，就标记为循环并终止这条路径的继续扩展。

CREATE TABLE org_node (
  id BIGINT PRIMARY KEY,
  parent_id BIGINT NULL,
  name VARCHAR(80) NOT NULL
);

INSERT INTO org_node VALUES
  (10,NULL,'总部'), (20,10,'研发'), (30,20,'平台'), (40,30,'数据');

这里的核心要求不是返回多少行数据，而是每条记录都能明确追溯到父节点来源、标识当前递归深度。缺少这两个字段，递归生成的重复结果很难和业务允许的多分支合法结果区分开。

用锚点和递归成员表达父子关系

锚点部分先返回指定的根节点数据，递归成员部分再根据 parent_id 关联查找下一层节点。depth 从 0 开始计数，方便直接把业务要求的最大层数作为边界判断条件。

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 0 AS depth,
         CAST(CONCAT('/', id, '/') AS CHAR(1000)) AS path
  FROM org_node
  WHERE id = 10
  UNION ALL
  SELECT child.id, child.parent_id, child.name,
         tree.depth + 1,
         CONCAT(tree.path, child.id, '/')
  FROM tree
  JOIN org_node AS child ON child.parent_id = tree.id
  WHERE tree.depth 

path NOT LIKE 是这类示例里常用的环检测逻辑：如果下一个待扩展节点的ID已经存在于已访问路径中，就不再把它加入递归结果。生产环境中如果节点ID不是纯数字类型，要提前配置路径分隔符规则，避免字符匹配逻辑误判。

深度上限和环检测解决的不是同一件事

单纯调大 cte_max_recursion_depth 的值，只是允许递归执行更多层，完全不能证明当前数据集不存在环。反过来，环检测只能阻止某条路径回退到旧节点，不合法的导入数据也可能生成层级达数千层的深树。所以查询语句内部要写死业务允许的最大深度条件，数据库连接会话也要配置合理的系统保护阈值。

SET SESSION cte_max_recursion_depth = 100;

WITH RECURSIVE tree AS (
  SELECT id, parent_id, 0 AS depth,
         CAST(CONCAT('/', id, '/') AS CHAR(1000)) AS path
  FROM org_node WHERE id = 10
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1,
         CONCAT(t.path, n.id, '/')
  FROM tree AS t
  JOIN org_node AS n ON n.parent_id = t.id
  WHERE t.depth 

建议把会话级递归阈值配置在受控的批处理连接里，不要为了适配某个异常请求直接修改全局参数值。递归查询触发报错时，优先记录输入根节点、当前配置和对应数据快照，再排查是合法超深树还是数据存在循环关联。

把循环数据做成可定位的结果

直接过滤掉重复节点，接口调用方可能只会看到返回结果数量变少，完全感知不到底层数据已经存在环。更稳妥的处理方式是先用一条诊断查询保留所有候选子节点，新增 cycle_hit 标记字段；正式业务导出场景下直接停止扩展，诊断得到的异常数据写入专门的异常表，进入后续数据修复流程。

结果验收至少覆盖三个校验点：根节点在结果集中仅出现一次；每行的 depth 值等于路径里的节点总数减一；同一条访问路径上不存在重复ID。面向跨租户的树状查询，还要把租户过滤条件同时写进锚点和递归关联逻辑里，不能只在最外层加过滤。

常见问题

cte_max_recursion_depth 能自动发现循环吗？

不能。它的作用只是限制递归的总层数；环检测需要开发者在递归成员逻辑里手动记录访问路径，或者维护等价的已访问节点集合判断重复。

为什么查询结果里根节点会重复？

最常见的原因是锚点的查询范围写得太宽，或者递归成员的关联条件又把根节点重新接回结果集里。先确认锚点逻辑只会返回指定的单个根节点，再逐层检查递归部分的关联条件即可。

递归 CTE 适合无限深度的目录吗？

不能把“无限深度”当成正常的业务输入。必须提前设置业务允许的最大层级、数据库保护阈值和异常记录逻辑，一旦超过边界就直接转入数据修复或者分页处理流程。

总结

递归 CTE 的安全边界由三层逻辑组成：锚点限定查询起点，路径检测阻止回环，深度阈值限制异常层级的无限制增长。把这三层逻辑和根节点唯一性、最大深度校验、路径重复检查三项验收规则结合起来，层级查询才不会从普通的报表需求演变成数据库层面的线上故障。