什么是SQL的递归查询？WITH RECURSIVE的用法与场景

苏承栈

2026-10-28 • 👁 0人浏览过

SQL递归查询通过WITH RECURSIVE实现，用于处理层级或图结构数据，如组织架构、物料清单等；它由锚点成员和递归成员组成，利用UNION ALL连接，逐层遍历直到无新数据生成，适用于深度不确定的父子关系查询，但需注意性能、索引、循环引用及数据库递归深度限制等问题。 SQL的递归查询，说白了，就是一种处理层级或图形结构数据的强大工具。它允许我们通过一个初始的“起点”（称为锚点成员）和一套“规则”（递归成员），不断地迭代查询，直到满足某个终止条件。

WITH RECURSIVE

是SQL标准中实现这种递归查询的方式，它特别擅长解决那些传统连接操作搞不定的，深度不确定的层级关系问题，比如组织架构、物料清单或者社交网络中的关系链。解决方案在我看来，理解

WITH RECURSIVE

的关键在于它模拟了一种“自我循环”的思考模式。我们不再需要写一堆层层嵌套的

JOIN

来找出所有下属，或者计算一个产品的所有子组件，而是定义一个基础集，然后定义一个如何从当前结果集生成下一个结果集的规则。这种方式，不仅代码更简洁，也更能清晰地表达业务逻辑。具体来说，一个

WITH RECURSIVE

CTE（Common Table Expression）通常包含两个主要部分：锚点成员（Anchor Member）：这是递归的起始点，它是一个非递归的

SELECT

语句，用于建立初始的结果集。可以把它想象成你寻宝的第一个线索。递归成员（Recursive Member）：这是一个

SELECT

语句，它会引用CTE本身，并与某个表进行连接，从而从上一步的结果中生成新的行。这个成员会反复执行，直到不再有新的行产生。这就好像你拿着上一个线索找到下一个线索，如此循环。这两个成员之间通常用

UNION ALL

（或

UNION

）连接起来。

UNION ALL

性能更好，因为它不检查重复项，但在某些情况下，如果你需要去重，

UNION

会是更好的选择。整个过程就像一个自动化的“深度优先”或“广度优先”遍历，直到所有相关的层级都被探索完毕。什么时候应该考虑使用SQL递归查询？在我个人的经验里，每当我遇到数据之间存在“父子关系”或“A依赖B，B又依赖C”这样的链式结构，并且这种链条的深度是不确定的，我就会立刻想到

WITH RECURSIVE

。最典型的应用场景包括：组织架构或员工层级：找出某个经理的所有下属，无论他们隔了多少层。这是我最常遇到的场景，传统方法需要写多个

LEFT JOIN

，既笨重又无法处理无限层级。物料清单（Bill of Materials, BOM）：一个产品由哪些组件构成，这些组件又由哪些子组件构成，直到最基本的原材料。这在制造业中非常常见。文件系统或目录结构：显示某个目录下所有的子目录和文件。社交网络中的关系链：比如查找“我的好友的好友”或者“与我相隔N度的人”。虽然对于大型社交网络，专门的图数据库可能更高效，但对于小规模分析，

WITH RECURSIVE

是个不错的选择。评论或论坛帖子：显示一个主帖下的所有回复及其子回复。说白了，只要你的数据能画成一棵树或者一个有向图，并且你需要遍历这棵树或图的所有节点，

WITH RECURSIVE

就是你的得力助手。它让原本复杂的、需要应用程序逻辑来处理的问题，变得可以在数据库层面优雅地解决。 WITH RECURSIVE 具体怎么写？一个实战案例解析我们用一个经典的员工层级结构来演示

WITH RECURSIVE

的写法。假设我们有一个

employees

表，包含

employee_id

、

employee_name

和

manager_id

，其中

manager_id

指向其上级员工的

employee_id

。目标：找出所有直接或间接向“Alice”（假设她的

employee_id

是101）汇报的员工。首先，我们模拟一下数据：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(101, 'Alice', NULL), -- CEO
(102, 'Bob', 101),
(103, 'Charlie', 101),
(104, 'David', 102),
(105, 'Eve', 102),
(106, 'Frank', 104),
(107, 'Grace', 103);

现在，我们来写

WITH RECURSIVE

查询： MySQL（Linux） MySQL 9.6.0是面向Linux平台的2026年创新版本，核心架构迎来重大革新。其将外键约束与级联操作从InnoDB引擎层上移至SQL层，确保所有数据变更均被完整记录至Binlog，彻底解决了CDC（变更数据捕获）与主从复制中的数据不一致难题。此外，该版本引入container_aware启动选项以原生适配容器环境，并对审计日志进行了组件化重构，为追求极致数据一致性与云原生体验的开发者提供了全新选择。下载

WITH RECURSIVE EmployeeHierarchy AS (
    -- 锚点成员 (Anchor Member): 从Alice开始，她是我们的起点
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        0 AS level -- 标记层级，Alice是第0层
    FROM
        employees e
    WHERE
        e.employee_id = 101 -- 指定起始员工ID

    UNION ALL

    -- 递归成员 (Recursive Member): 找到当前层级员工的所有下属
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1 AS level -- 下属的层级加1
    FROM
        employees e
    JOIN
        EmployeeHierarchy eh ON e.manager_id = eh.employee_id -- 关键：连接到CTE本身
)
SELECT
    employee_id,
    employee_name,
    manager_id,
    level
FROM
    EmployeeHierarchy
ORDER BY
    level, employee_id;

代码解析：

WITH RECURSIVE EmployeeHierarchy AS (...)

: 定义了一个名为

EmployeeHierarchy

的递归CTE。锚点成员：

SELECT e.employee_id, e.employee_name, e.manager_id, 0 AS level FROM employees e WHERE e.employee_id = 101

这部分选出了Alice（ID为101）作为起始点，并给她标记为

level 0

。这是递归的基石。

UNION ALL

: 将锚点成员和递归成员的结果合并。递归成员：

SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1 AS level FROM employees e JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id

这里是核心。它将

employees

表（别名

e

）与我们正在构建的

EmployeeHierarchy

CTE（别名

eh

）进行连接。连接条件

e.manager_id = eh.employee_id

的意思是：找出所有其

manager_id

等于

EmployeeHierarchy

中某个

employee_id

的员工。这实际上就是在找当前层级员工的直接下属。

eh.level + 1 AS level

：每找到一层下属，我们就把他们的层级加1，这样我们就能追踪到他们在组织架构中的深度。这个查询会首先把Alice放进

EmployeeHierarchy

。然后，在第一次递归中，它会找到所有

manager_id

是101的员工（Bob和Charlie），把他们加入

EmployeeHierarchy

，并标记为

level 1

。接着，在第二次递归中，它会找到所有

manager_id

是Bob或Charlie的员工（David, Eve, Grace），标记为

level 2

。这个过程会一直重复，直到再也找不到新的下属为止。最终，

EmployeeHierarchy

CTE会包含所有从Alice开始的层级结构。使用WITH RECURSIVE的性能考量与注意事项虽然

WITH RECURSIVE

非常强大，但它并非没有代价。在实际应用中，我发现有几个方面需要特别注意：性能问题：深度与广度：如果你的层级结构非常深或者每一层都有大量的节点（广度很大），递归查询的性能可能会急剧下降。每次递归都需要进行一次连接操作，数据量越大，开销越大。索引：确保用于连接的列（在这个例子中是

employee_id

和

manager_id

）有合适的索引。没有索引，数据库可能需要进行全表扫描，这会是灾难性的。数据量：对于非常庞大的数据集，或者需要频繁执行的复杂递归查询，可能需要考虑在应用层处理，或者使用专门的图数据库（如Neo4j）来获得更好的性能。无限循环（Cycles）：这是递归查询最常见的陷阱之一。如果你的数据中存在循环引用（比如A的经理是B，B的经理是C，而C的经理又是A），递归查询就会陷入无限循环，最终导致数据库报错（通常是“递归深度超出限制”）。如何避免：在设计数据模型时，应尽量避免循环引用。如果业务上允许，你可能需要在递归成员中加入额外的条件来检测和中断循环。一些数据库（如PostgreSQL）提供了

CYCLE

子句来帮助检测和处理循环。在我们的例子中，如果Bob的经理是Alice，而Alice的经理又是Bob，就会出现循环。一种手动处理方式是，在CTE中追踪已经访问过的路径，如果新节点已经在路径中，则停止。

UNION ALL

UNION

：大多数情况下，使用

UNION ALL

就足够了，因为它不进行去重，性能更好。但如果你的递归路径可能导致重复的节点（例如，一个节点可以通过多条路径到达），并且你只关心唯一的节点列表，那么

UNION

可能是必要的，尽管它会带来额外的去重开销。

MAXRECURSION

限制：一些数据库系统（比如SQL Server）默认对递归查询的深度有限制（通常是100层）。如果你的层级深度可能超过这个限制，你需要通过

OPTION (MAXRECURSION N)

来显式设置一个更高的值，或者设置为0表示无限制（但要小心无限循环）。PostgreSQL和MySQL通常没有这个默认限制。可读性与调试：虽然

WITH RECURSIVE

比多层

JOIN

更简洁，但对于初学者来说，理解其执行流程可能需要一点时间。在调试时，可以先单独运行锚点成员，再逐步理解递归成员如何工作。总的来说，

WITH RECURSIVE

是SQL工具箱里一把锋利的瑞士军刀，用好了能大大提高效率和代码的优雅度。但就像所有强大的工具一样，也需要我们理解其工作原理和潜在的风险，才能真正发挥它的价值。

相关文章