递归CTE必须包含锚点、递归成员和显式终止控制三要素;缺一不可,否则无法正确展开层级结构或导致无限循环。

当递归逻辑需要多层嵌套、反复引用同一中间结果,或必须遍历树形/层级结构时,就应该用递归CTE替代传统递归子查询。
递归子查询在哪些场景下会失控
典型表现是:写一个“查某部门所有下属部门”的查询,发现不得不写三层 UNION ALL 手动拼接,或者用临时表 + 循环(WHILE)+ INSERT INTO @t,逻辑散落在多处,改一处要同步改五处。
- 子查询无法自我引用,所以“找子节点的子节点”只能靠硬编码层级,最多撑到 3~4 层就难维护
- 每次嵌套都重新执行全表扫描,没有复用中间结果,性能随层级指数级下降
- SQL Server 或 MySQL 8.0+ 中,
WITH后紧跟递归定义能被优化器识别为单一执行计划;而手写多层SELECT ... FROM (SELECT ... FROM (...))很可能触发多次独立编译
递归CTE的最小可用结构必须包含什么
缺一不可:锚点(anchor) + 递归成员(recursive member) + 显式终止控制。比如查组织架构:
WITH OrgTree AS ( -- 锚点:顶层节点 SELECT Id, Name, ParentId, 0 AS Level FROM Department WHERE ParentId = 0 <p>UNION ALL</p><p>-- 递归成员:关联自身 SELECT d.Id, d.Name, d.ParentId, ot.Level + 1 FROM Department d INNER JOIN OrgTree ot ON d.ParentId = ot.Id ) SELECT * FROM OrgTree;</p>
- 必须用
UNION ALL(不能用UNION),否则递归会被去重中断 - 递归成员的
FROM子句里必须直接引用 CTE 名(OrgTree),不能包在子查询里 - MySQL 默认递归深度上限是 1000,超限会报错
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations,需提前设SET SESSION cte_max_recursion_depth = 2000;
为什么不能只靠索引解决递归性能问题
即使 ParentId 加了索引,传统循环方式仍要反复查表;而递归CTE允许优化器将整个展开过程当作一次连接操作来规划。
- 复合索引
(ParentId, Id)对递归CTE 的INNER JOIN阶段特别有效,能避免回表 - 但若锚点结果集过大(比如根节点有上万直属子节点),CTE 仍可能内存溢出;此时应在锚点加过滤条件,而不是依赖后续
WHERE - SQL Server 中可加
OPTION (MAXRECURSION 50)强制截断,防止意外死循环;MySQL 没这个语法,只能靠cte_max_recursion_depth全局控制
真正容易被忽略的是:递归CTE 的生命周期仅限于紧随其后的那一条语句。写完 WITH ... SELECT 之后如果插了一行 PRINT 'done',再想 SELECT * FROM OrgTree 就会报错 Invalid object name 'OrgTree' —— 它不是临时表,也不是变量,只是语法作用域内的命名结果集。










