关系型数据库在处理二维表格数据时游刃有余，但当业务模型本身呈现出树状或图状结构时，传统的查询方式便会暴露出明显的局限性。以组织架构、地区层级、物料清单（BOM）等典型场景为例，表中的行与行之间不再是简单的平等并列关系，而是存在着明确的父子链接。在这种层级结构下，如果想要获取某个特定节点的所有子孙节点，或者从某个叶子节点一路回溯到根节点，使用传统的自连接查询或多次发起数据库查询会面临一个致命弱点：查询的层数被固定死，无法灵活扩展。为了解决这一痛点，SQL标准引入了递归公共表表达式（Recursive Common Table Expression），也就是我们常说的 WITH RECURSIVE。它赋予了数据库引擎在一条SQL语句内完成迭代式数据遍历的能力，极大地简化了复杂层级数据的查询逻辑。
递归CTE的语法结构与执行原理
要理解递归CTE的运作机制，首先需要剖析其语法结构。从整体上看，WITH RECURSIVE 关键字出现在标准的 SELECT 语句之前，其后紧跟着CTE的名称、列名列表，以及一个由 UNION ALL 集合操作符连接的查询块。这个 UNION ALL 将整个递归CTE划分为两个核心部分：一部分称为锚成员，另一部分称为递归成员。锚成员是整个递归过程的起点，它通常是一次性的非递归查询，用于找出初始的行集。递归成员则引用CTE自身的名称，在每次执行时，它会以锚成员或上一轮迭代产出的结果作为输入，计算出新的行，并将这些新行再次纳入CTE自身的引用范围。数据库引擎会反复执行这个递归成员，直到某一次迭代返回空集为止，整个迭代过程由引擎自动完成，对开发者透明。
我们可以通过一个生成连续数字序列的经典例子来说明其执行原理。假设我们需要让数据库生成1到10的数字序列，锚成员可以写成 SELECT 1 AS n，递归成员则写成 SELECT n+1 FROM cte WHERE n < 10。在执行阶段，引擎首先计算锚成员得到初始值1，随后将这个1送入递归成员，计算得到2；接着把2作为下一轮迭代的输入，产生3，如此周而复始。当n的值等于10时，递归成员由于 WHERE n < 10 条件的限制会停止产出新行，最终的结果集便完整收录了1到10的序列。这里需要特别强调的一个关键点是：递归成员中引用的CTE名称，代表的始终是上一轮递归产出的全部行，而不是整个累积的结果集。
此外，开发者需要特别注意递归深度的限制问题。默认情况下，不同的数据库管理系统对递归迭代的次数有着不同的限制策略。例如，PostgreSQL默认允许递归迭代的最大次数为100次，而MySQL则通过 cte_max_recursion_depth 系统变量来进行限制。这种设计机制有助于防止因意外编写出无限循环的递归查询而吞光服务器资源，但在实际应用中，如果确认需要更深的递归，应当手动调整这些系统参数。
WITH RECURSIVE numbers(n) AS (
SELECT 1 -- 锚成员：定义递归的起点
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10 -- 递归成员：基于上一轮结果迭代
)
SELECT * FROM numbers; -- 输出1到10的序列
利用递归查询处理树形结构数据
在实际的企业级应用中，递归查询最常见的应用场景莫过于树形数据的管理。假设我们有一张名为 departments 的部门表，包含 id、name 和 parent_id 三个核心字段，其中 parent_id 指向该部门的上级部门ID，而根部门的 parent_id 为 NULL。如果业务需求是要找出某个特定部门及其下属的所有层级的下级部门，使用递归CTE可以轻松实现。此时，锚成员直接查找目标部门本身的记录，递归成员则从CTE当前的结果行出发，通过连接 departments 表找出 parent_id 等于当前 id 的记录。这样一层一层地向下延伸，最终能够获取到完整的子树结构。
与向下遍历相对应的另一个常见操作是向上回溯。例如，从某个员工所在的底层部门出发，逐级向上查询直到根部门，以获取完整的层级路径。在这种场景下，锚成员定位到该员工对应的部门记录，递归成员则以 cte.parent_id = d.id 的方式连接 departments 表，一直往上追溯，直到 parent_id 为 NULL 时终止。这种向上回溯的查询在实际开发中经常被用于生成面包屑导航，或者用于分析某一具体节点在整个组织层级中的定位。
针对更为复杂的业务需求，我们还可以在递归查询的过程中携带并构建额外的信息。例如，除了获取部门ID，我们可能还需要记录从根节点到当前节点的完整路径字符串，这对于展示缩进层次或生成物料编码非常有帮助。在示例中，路径可以通过字符串拼接操作符逐步构建：在锚成员阶段，路径即为部门名称本身；在递归成员阶段，将父级的路径与当前节点的名称用特定的分隔符连接起来。这样，最终结果集中的每一行都会包含从根节点到该节点的完整层级路径。
WITH RECURSIVE dept_tree(id, name, parent_id, path) AS (
-- 锚成员：找出根节点，路径初始化为部门名称
SELECT id, name, parent_id, name AS path
FROM departments
WHERE parent_id IS NULL
UNION ALL
-- 递归成员：连接基础表，向下查找子节点并拼接路径
SELECT d.id, d.name, d.parent_id, dt.path || ' > ' || d.name
FROM departments d
INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree ORDER BY path;
递归查询的常见陷阱与优化策略
尽管递归CTE的功能十分强大，但如果使用不当，也极易引发性能问题甚至导致数据库陷入死循环。首先需要高度警惕的是数据中“环”的出现。在某些数据模型中，如果父子关系由于数据录入错误意外形成了闭环——比如部门A的上级是B，而B的上级又被错误设置成了A——那么递归查询将永远无法自动终止，最终因超出数据库的递归深度限制而报错。为了应对这一问题，许多数据库提供了循环检测机制。例如，PostgreSQL支持使用 CYCLE 子句来检测循环；而MySQL则需要在递归成员中手动维护一个已访问路径的字符串，并使用 FIND_IN_SET 之类的函数来排除已经访问过的节点，从而强行打断死循环。
另一个常见的性能陷阱是索引的缺失。递归成员在每次迭代时，都需要根据CTE的当前结果去关联基础表，如果没有合适的索引支持，检索每一层数据的效率会极其低下。对于向下遍历的查询，通常需要在 parent_id 列上建立索引；而对于向上遍历的查询，则应当在主键及被关联的外键列上建好索引。此外，递归深度本身也会直接影响执行时间，如果一棵树的层级非常深，比如达到几百层的物料BOM结构，迭代次数会变得非常大，此时应当考虑结合缓存策略或物化路径等其他方案来减轻数据库的运算负担。
最后需要留意的是语法层面的限制。在递归CTE的内部，通常不能直接使用聚合函数或 ORDER BY、LIMIT 等限定单个集合的操作。如果需要对最终结果进行去重或者排序，必须在外部的最终 SELECT 语句中进行处理。同时，不同数据库对递归的支持程度也存在差异，例如SQLite需要启用特定的编译选项，而MySQL在低版本中则完全不具备递归CTE的能力，此时可能不得不借助存储过程或在应用程序层手动实现递归。在日常的架构设计中，如果预计层次结构会被非常频繁地查询，也可以考虑预先计算并维护一张闭包表，以空间换时间，从而获取极致的查询性能。
-- PostgreSQL 使用 CYCLE 子句避免循环死锁
WITH RECURSIVE dept_tree(id, name, parent_id) AS (
SELECT id, name, parent_id
FROM departments
WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.name, d.parent_id
FROM departments d
INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
-- 设置循环检测，当id重复出现时标记并终止该分支
CYCLE id SET is_cycle TO true DEFAULT false USING path
SELECT * FROM dept_tree WHERE NOT is_cycle;
综上所述，WITH RECURSIVE 递归查询为关系型数据库处理树形和图状数据提供了强大的内建支持。通过理解锚成员与递归成员的协作机制，开发者能够用极其优雅的代码完成复杂的层级遍历。然而，要真正在生产环境中发挥其价值，还必须对数据循环检测、索引优化以及各数据库的语法限制有充分的认知。在遇到极端深度的层级结构时，结合闭包表或物化路径等反范式设计，往往能获得更稳定的系统表现。合理运用递归CTE，不仅能够提升开发效率，更能让数据查询逻辑保持清晰与可维护。
SQL递归查询WITH_RECURSIVE递归CTE修改时间：2026-08-12 12:06:57