如何用递归 CTE 实现“路径去环”检测循环依赖

递归CTE中的循环依赖指数据存在闭环（如A→B→C→A），导致无限递归；PostgreSQL用ARRAY+@>检测路径重复节点，sql Server用带边界符的字符串+CHARINDEX查重，二者均需在WHERE中主动剪枝以避免报错。

什么是递归 CTE 中的循环依赖

递归 CTE 在查询层级关系（如组织架构、物料 bom、权限继承）时，如果数据中存在 A→B→C→A 这类闭环，WITH RECURSIVE 默认会无限展开，postgresql 报错 Error: infinite recursion detected，SQL Server 报 Maximum recursion exceeded，sqlite 则可能卡死或截断。这不是语法错，是数据逻辑冲突暴露出来的环。

PostgreSQL：用 Array + @> 检测路径重复节点

核心思路是把每层递归的路径存成 ARRAY，在递归成员中检查当前节点是否已出现在历史路径里。PostgreSQL 的 @>（包含操作符）比 UNNEST + EXISTS 更快且可索引。

• 起始查询必须包含 ARRAY[origin_id] 作为初始路径
• 递归部分用 path || child_id 追加，而非拼字符串（避免类型不一致或空值中断）
• WHERE NOT path @> ARRAY[child_id] 是关键守门条件，漏掉就进环
• 注意 child_id IS NOT NULL 要显式判断，否则 NULL 参与 @> 会返回 NULL，导致行被意外过滤

WITH RECURSIVE deps AS (   SELECT id AS origin_id, depends_on AS child_id, ARRAY[id] AS path   FROM modules WHERE depends_on IS NOT NULL   UNION ALL   SELECT d.origin_id, m.depends_on, d.path || m.id   FROM deps d   JOIN modules m ON d.child_id = m.id   WHERE m.depends_on IS NOT NULL     AND NOT d.path @> ARRAY[m.id] ) SELECT * FROM deps;

SQL Server：用 MAXRECURSION 配合 CHARINDEX 字符串路径

SQL Server 不支持数组类型，常用逗号分隔字符串记录路径，再用 CHARINDEX 查重。但要注意：直接用 ',' + path + ',' LIKE '%,' + child_id + ',%' 易误匹配（比如 ‘1’ 会被 ’11’ 匹中），必须前后加逗号并确保格式统一。

• 起始路径写成 ',' + CAST(id AS VARCHAR) + ','，不是 CAST(id AS VARCHAR)
• 递归中用 path + CAST(child_id AS VARCHAR) + ',' 会漏前导逗号，应统一为 path + CAST(child_id AS VARCHAR) + ',' 并在查重时补头尾
• OPTION (MAXRECURSION 100) 必须加，否则默认只跑 100 层，深层合法树也会被截断
• 若 child_id 是 NULL，CAST(NULL AS VARCHAR) 得到 NULL，整行消失，需提前 WHERE depends_on IS NOT NULL

WITH deps AS (   SELECT id AS origin_id, depends_on AS child_id,           ',' + CAST(id AS VARCHAR(10)) + ',' AS path   FROM modules WHERE depends_on IS NOT NULL   UNION ALL   SELECT d.origin_id, m.depends_on,          d.path + CAST(m.id AS VARCHAR(10)) + ','   FROM deps d   INNER JOIN modules m ON d.child_id = m.id   WHERE m.depends_on IS NOT NULL     AND CHARINDEX(',' + CAST(m.id AS VARCHAR(10)) + ',', d.path) = 0 ) SELECT * FROM deps OPTION (MAXRECURSION 0);

为什么不能只靠 MAXRECURSION 或超时机制

仅设 MAXRECURSION 0 或调大限制，只是让报错延后，并不解决环本身。真实场景中，一个环可能藏在某条分支下，其余分支正常；若不主动剪枝，整个 CTE 会因该分支失败而整体失败（SQL Server）或抛异常（PostgreSQL）。更危险的是，有些环只在特定参数组合下触发，上线后偶发崩溃，排查成本远高于写对路径检测逻辑。

真正要命的不是“有没有环”，而是“有没有在展开时立刻识别并跳过它”。数组查重和带边界符的字符串查重，都是为了在进入下一层前，把环扼杀在 WHERE 子句里——这步漏了，后面全是徒劳。