SQL递归CTE是处理组织架构、分类树、路径查询等层级数据的常用工具，但实际使用中如果不做循环检测和深度限制，很容易出现查询卡死或者返回错误结果的问题，需要针对性处理这两个场景的异常情况。
递归CTE的基本结构回顾
递归CTE由初始成员和递归成员两部分组成，基本语法结构如下：
-- 基础递归CTE示例，查询部门层级
WITH RECURSIVE dept_cte AS (
-- 初始成员：查询根部门
SELECT
id,
parent_id,
dept_name,
1 AS depth,
CAST(dept_name AS CHAR(1000)) AS path
FROM department
WHERE parent_id IS NULL
UNION ALL
-- 递归成员：关联子部门，逐层向下查询
SELECT
d.id,
d.parent_id,
d.dept_name,
cte.depth + 1,
CONCAT(cte.path, '->', d.dept_name)
FROM department d
INNER JOIN dept_cte cte ON d.parent_id = cte.id
)
SELECT * FROM dept_cte;
循环检测（cycle detection）处理方法
当层级数据中存在循环引用时，比如部门A的父部门是部门B，部门B的父部门又指向部门A，递归CTE会无限执行直到报错。SQL标准提供了CYCLE子句来检测循环，不同数据库也支持对应的实现方式。
SQL标准CYCLE子句用法
SQL标准的递归CTE可以在定义末尾添加CYCLE子句，指定用于检测循环的列和标记列，示例如下：
WITH RECURSIVE dept_cte AS (
SELECT
id,
parent_id,
dept_name,
CAST(dept_name AS CHAR(1000)) AS path
FROM department
WHERE parent_id IS NULL
UNION ALL
SELECT
d.id,
d.parent_id,
d.dept_name,
CONCAT(cte.path, '->', d.dept_name)
FROM department d
INNER JOIN dept_cte cte ON d.parent_id = cte.id
)
CYCLE id SET is_cycle USING path -- 检测id列是否出现循环，用is_cycle标记，path记录路径
SELECT * FROM dept_cte WHERE is_cycle = FALSE; -- 过滤掉循环产生的记录
主流数据库的实现差异
- PostgreSQL：支持
CYCLE子句，也支持手动记录访问过的节点实现检测，示例如下：
WITH RECURSIVE dept_cte AS (
SELECT
id,
parent_id,
dept_name,
ARRAY[id] AS visited_ids -- 记录已访问的id数组
FROM department
WHERE parent_id IS NULL
UNION ALL
SELECT
d.id,
d.parent_id,
d.dept_name,
cte.visited_ids || d.id -- 追加当前节点id到已访问数组
FROM department d
INNER JOIN dept_cte cte ON d.parent_id = cte.id
WHERE NOT d.id = ANY(cte.visited_ids) -- 如果当前id已经在已访问数组里，停止递归
)
SELECT * FROM dept_cte;
- MySQL：不支持
CYCLE子句，需要通过记录路径或者已访问节点实现循环检测，示例如下：
WITH RECURSIVE dept_cte AS (
SELECT
id,
parent_id,
dept_name,
CAST(id AS CHAR(1000)) AS visited_ids -- 记录已访问的id字符串
FROM department
WHERE parent_id IS NULL
UNION ALL
SELECT
d.id,
d.parent_id,
d.dept_name,
CONCAT(cte.visited_ids, ',', d.id) -- 追加当前id到字符串
FROM department d
INNER JOIN dept_cte cte ON d.parent_id = cte.id
WHERE LOCATE(CONCAT(',', d.id, ','), CONCAT(',', cte.visited_ids, ',')) = 0 -- 检查当前id是否已被访问
)
SELECT * FROM dept_cte;
递归深度限制处理方法
即使没有循环引用，递归层级过深也会导致查询性能下降，甚至超出数据库的递归限制，需要手动控制递归的最大深度。
通用深度限制实现
可以在递归CTE中增加一个深度计数字段，递归时递增，当深度达到阈值时停止递归，示例如下：
WITH RECURSIVE dept_cte AS (
SELECT
id,
parent_id,
dept_name,
1 AS depth -- 初始深度为1
FROM department
WHERE parent_id IS NULL
UNION ALL
SELECT
d.id,
d.parent_id,
d.dept_name,
cte.depth + 1 -- 递归时深度加1
FROM department d
INNER JOIN dept_cte cte ON d.parent_id = cte.id
WHERE cte.depth < 10 -- 限制最大深度为10层，超过则停止递归
)
SELECT * FROM dept_cte;
不同数据库的内置限制参数
| 数据库 | 内置深度限制参数 | 说明 |
|---|---|---|
| MySQL | cte_max_recursion_depth | 默认值为1000，可全局或会话级修改，超过该值会报错 |
| PostgreSQL | 无内置参数，需手动加深度字段限制 | 递归没有默认深度限制，需要通过代码控制 |
| SQL Server | 默认递归100层，可通过OPTION (MAXRECURSION n)修改 | n为最大递归次数，设为0则无限制 |
以MySQL为例，修改会话级递归深度限制的代码如下：
-- 设置当前会话的递归CTE最大深度为500
SET SESSION cte_max_recursion_depth = 500;
-- 执行递归查询
WITH RECURSIVE dept_cte AS (
SELECT id, parent_id, dept_name, 1 AS depth
FROM department
WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.parent_id, d.dept_name, cte.depth + 1
FROM department d
INNER JOIN dept_cte cte ON d.parent_id = cte.id
)
SELECT * FROM dept_cte;
实践建议
在实际使用递归CTE时，建议同时添加循环检测和深度限制逻辑，避免查询出现异常。如果是处理可信的层级数据，没有循环引用的可能，可以只加深度限制；如果数据来源不可控，必须同时做两种处理，保证查询的稳定性和执行效率。
SQL递归CTEcycle_detection深度限制修改时间：2026-07-11 01:18:46