SQL WITH RECURSIVE 递归查询如何避免死循环？深度限制与循环检测实战

用一句递归SQL查出整棵部门树、追溯一个用户的所有上级、展开一条评论的所有回复楼层——WITH RECURSIVE 几乎是处理层级数据最优雅的武器。但它有个致命的软肋：如果数据里存在环，比如A的上级是B，B的上级又是A，递归就永远不会停止。很多生产事故正是这么产生的，一条看似无害的查询跑了几十分钟，最终把数据库拖垮。这篇文章就来彻底讲清楚递归CTE的运作机制，以及如何用深度限制和循环检测把递归查询关进笼子里。

递归CTE的执行原理：为什么会死循环

一个标准的递归CTE由两部分组成：锚部分（anchor）和递归部分（recursive），两者用UNION ALL连接。数据库先执行锚部分得到初始结果集，然后把这个结果集作为输入，反复执行递归部分，把新产生的行继续喂回去，直到某一次迭代不再产生新行为止。听起来很安全——迭代总会收敛吧？可惜不一定。

问题出在终止条件上。递归CTE停止的唯一条件是递归部分返回空结果集。如果数据中存在环，比如员工表里id为1的员工上级是2，而2的上级又是1，那么每一次迭代都会重新产生对方这行数据，结果集永远不为空，递归就永远停不下来。MySQL默认给了一次递归的迭代上限（cte_max_recursion_depth默认1000），超过会直接报错；PostgreSQL则没有这么仁慈，它会一直跑下去直到你手动kill掉会话。

先建一张带环的测试表，后面的示例都基于它：

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employee VALUES
(1, '张三', 2),
(2, '李四', 1),   -- 与张三互为上下级，构成环
(3, '王五', 2),
(4, '赵六', 3);

如果直接写一个查所有下级的递归查询去跑这张表，就能直观感受到死循环的威力。所以在写任何递归查询之前，都必须想清楚两个问题：数据有没有可能出现环？即使没有环，结果集会不会大到失控？下面三个方法分别应对这两种风险。

方法一：用深度计数器限制递归层级

最简单粗暴也最常用的防护手段，是在递归过程中携带一个深度字段，每次迭代加一，并在递归部分的WHERE条件里限制最大深度。这相当于给递归装了一个安全阀：即使数据有环，迭代到设定层数后就不会再展开，查询能正常返回结果而不是挂死。

WITH RECURSIVE sub_tree AS (
    -- 锚部分：深度为0
    SELECT id, name, manager_id, 0 AS depth,
           CAST(CONCAT('/', id) AS CHAR(1000)) AS path
    FROM employee
    WHERE id = 2
    UNION ALL
    -- 递归部分：深度加一，超过5层就不再展开
    SELECT e.id, e.name, e.manager_id, t.depth + 1,
           CONCAT(t.path, '/', e.id)
    FROM employee e
    JOIN sub_tree t ON e.manager_id = t.id
    WHERE t.depth < 5
)
SELECT * FROM sub_tree;

这个写法在MySQL 8.0和PostgreSQL上都能直接运行。深度字段的初始值定为0，锚部分查出的起始行深度为0，每往下一层加1，WHERE t.depth < 5保证了最多递归到第5层。对于组织架构这类现实业务，层级通常不会超过十层，把上限设得比业务最大层级略高即可。

它的优点是实现简单、性能开销几乎为零，而且顺便提供了一个有用的副产品——depth列可以直接用来在应用层做层级缩进展示。缺点也很明显：它只能保证不死循环，不能保证结果的正确性。如果有环，前几层的重复行依然会被查出来，比如上面的查询中1和2会交替出现好几轮。所以深度限制适合作为兜底保险，而不是唯一的环处理手段。

方法二：用路径数组实现真正的循环检测

要精确地发现环，需要记录从起点到当前节点走过的完整路径，每次迭代前检查下一跳是否已经在路径里。如果已经在，说明绕回了走过的节点，直接跳过。PostgreSQL原生支持数组类型，这个方案写起来最顺手：

WITH RECURSIVE sub_tree AS (
    SELECT id, name, manager_id,
           ARRAY[id] AS path
    FROM employee
    WHERE id = 2
    UNION ALL
    SELECT e.id, e.name, e.manager_id,
           t.path || e.id
    FROM employee e
    JOIN sub_tree t ON e.manager_id = t.id
    WHERE NOT e.id = ANY(t.path)   -- 关键：新节点不能出现在已有路径中
)
SELECT * FROM sub_tree;

NOT e.id = ANY(t.path)这一行就是循环检测的核心。它检查即将加入的新节点是否已经在从起点到当前节点的路径中出现过，出现过就说明这条分支形成了环，直接剪掉。这样递归保证了一定会收敛：每个可能的路径组合都是有限的，环被排除后不可能无限延伸。

MySQL没有数组类型，但可以用字符串拼接模拟：把路径存成"2,1,3"这样的逗号分隔串，配合FIND_IN_SET函数做成员判断：

WITH RECURSIVE sub_tree AS (
    SELECT id, name, manager_id,
           CAST(id AS CHAR(1000)) AS path
    FROM employee
    WHERE id = 2
    UNION ALL
    SELECT e.id, e.name, e.manager_id,
           CONCAT(t.path, ',', e.id)
    FROM employee e
    JOIN sub_tree t ON e.manager_id = t.id
    WHERE FIND_IN_SET(e.id, t.path) = 0
)
SELECT * FROM sub_tree;

需要注意两点：一是路径字符串要预设足够的长度，MySQL会根据初始行的长度推断字段类型，初始太短后面拼接会报截断错误，所以锚部分用CAST指定了1000字符；二是FIND_IN_SET对超长路径的性能一般，如果层级很深，最好还是配合方法一的深度限制一起用，双保险。

路径检测的精度是三种方案里最高的，它不仅能防死循环，还能顺便用path列还原出完整的层级链路，排查脏数据时特别有用——你一眼就能看出环是由哪几个节点构成的。代价是每次迭代都要做一次路径扫描，节点度数高、路径长时开销会明显上升。

方法三：数据库层面的兜底配置与事后排查

除了在SQL里做防护，数据库本身也提供了一些兜底机制。MySQL可以通过系统变量调整递归深度上限，PostgreSQL则可以用会话级的statement_timeout给所有查询加一把时间锁：

-- MySQL：把递归深度上限调整为100
SET SESSION cte_max_recursion_depth = 100;

-- PostgreSQL：语句超过30秒自动终止
SET statement_timeout = '30s';

这些配置的意义在于为线上环境提供最后一道防线。应用代码写得再周全，也架不住有人直接在客户端手写一条递归SQL去查库。把cte_max_recursion_depth设成一个贴合业务实际的值（比如组织架构最多15层，就设50），statement_timeout控制在可接受的范围内，即使出了问题也能快速失败而不是拖垮整个实例。

事后排查同样重要。如果怀疑数据里存在环，可以用一条独立的检测查询把所有构成环的节点直接揪出来。思路是：对每个节点做一次带路径检测的递归，如果能在路径中再次遇到起点本身，就说明这个节点参与了环：

WITH RECURSIVE walk AS (
    SELECT id, manager_id, ARRAY[id] AS path, id AS origin
    FROM employee
    UNION ALL
    SELECT e.id, e.manager_id, w.path || e.id, w.origin
    FROM employee e
    JOIN walk w ON e.manager_id = w.id
    WHERE NOT e.id = ANY(w.path)
)
SELECT DISTINCT origin, path
FROM walk
WHERE id = origin AND array_length(path, 1) > 1;

这条查询会把所有环的完整路径列出来，比如示例数据中的环2-1-2。拿到结果后，就该考虑是修数据（更新manager_id打破环），还是在业务层加上写入校验，比如保存上下级关系前先反查一遍是否会成环。源头治理永远比查询时补救更可靠。

方案对比与生产实践建议

把三种方案放到一起比较，各自适用场景就很清晰了：

生产环境中有几条经验值得坚持。第一，凡是写递归CTE，深度限制无条件加上，成本忽略不计却杜绝了最坏情况；第二，数据源不受控（比如来自用户输入或外部同步）时，必须叠加路径检测；第三，写操作层面建立环校验，别让脏数据有机会进表；第四，上线前用EXPLAIN观察递归的执行计划，评估锚部分的选择性——如果锚部分本身就返回海量行，递归的总开销会被成倍放大，这时该考虑的是加索引或改用应用层分批处理，而不是死磕SQL写法。

递归CTE是一把锋利但需要小心使用的工具。理解了它的收敛条件，掌握了深度限制与循环检测这两手防护，你就可以放心地用它去处理各种层级和图状数据，而不必每次执行前都提心吊胆了。

WITH RECURSIVE递归CTE循环检测修改时间：2026-09-04 08:14:54