什么是SQL的递归查询？WITHRECURSIVE的实现方法

SQL递归查询通过WITH RECURSIVE实现，由锚成员和递归成员组成，前者定义初始数据集，后者引用自身逐步遍历层级，UNION ALL合并结果，适用于组织架构、BOM等不定深度关系处理，需注意索引优化、循环检测与深度限制以提升性能。

SQL的递归查询，在我看来，是处理那些层层嵌套、枝蔓缠绕的数据结构时，数据库赋予我们的一把利器。它允许我们编写的查询能够“自我引用”，从而一步步地探索和构建出完整的层级关系或路径。

WITH RECURSIVE

解决方案

要实现SQL的递归查询，核心在于理解

WITH RECURSIVE

UNION ALL

UNION

1. 锚成员（Anchor Member）：这是递归的起点，它定义了查询的初始数据集。你可以把它想象成树的根节点，或者递归函数的基础情况。这个部分是一个普通的SELECT语句，不引用CTE（Common Table Expression）自身。
2. 递归成员（Recursive Member）：这是递归的核心，它会引用CTE自身，并基于前一次迭代的结果生成新的行。每次迭代，它都会从上一次迭代产生的行中获取数据，然后生成新的、更深层次的行。这个过程会一直重复，直到递归成员不再产生新的行，或者达到某个预设的终止条件。

UNION ALL

我们来看一个经典的例子，比如员工层级结构：

-- 假设我们有一个员工表
-- CREATE TABLE employees (
--     employee_id INT PRIMARY KEY,
--     employee_name VARCHAR(100),
--     manager_id INT -- NULL for top-level manager
-- );

-- INSERT INTO employees VALUES
-- (1, 'Alice', NULL),
-- (2, 'Bob', 1),
-- (3, 'Charlie', 1),
-- (4, 'David', 2),
-- (5, 'Eve', 2),
-- (6, 'Frank', 3);

WITH RECURSIVE EmployeeHierarchy AS (
    -- 锚成员：找到顶层经理（没有经理的员工）
    SELECT
        employee_id,
        employee_name,
        manager_id,
        1 AS level, -- 初始层级为1
        CAST(employee_name AS VARCHAR(MAX)) AS path -- 记录路径
    FROM
        employees
    WHERE
        manager_id IS NULL

    UNION ALL

    -- 递归成员：找到每个经理的下属
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1 AS level, -- 层级加1
        CAST(eh.path || ' -> ' || e.employee_name AS VARCHAR(MAX)) AS path -- 更新路径
    FROM
        employees e
    INNER JOIN
        EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT
    employee_id,
    employee_name,
    manager_id,
    level,
    path
FROM
    EmployeeHierarchy
ORDER BY
    path;

这个例子清晰地展示了如何从顶层员工开始，逐步向下遍历整个组织结构，并且还额外计算了每个员工的层级以及他们到顶层的管理路径。这在传统的不定层级自连接中，几乎是不可能优雅实现的。

为什么我们需要SQL递归查询？它解决了哪些传统查询难以应对的问题？

坦白说，当我第一次接触到那些需要处理无限层级关系的需求时，比如“找出某个部门所有下属部门，无论嵌套多深”，或者“计算某个产品所有子组件的成本，包括子组件的子组件”，我首先想到的是一堆复杂的自连接。但很快就会发现，如果层级是固定的，比如只有两三层，自连接还能勉强应付。可一旦层级是动态的、不确定的，甚至可能达到几十上百层，传统的自连接就会变成一场噩梦——你需要写无数个JOIN，代码冗长、难以维护，而且性能也极差。

这就是SQL递归查询真正发光的地方。它以一种优雅且高效的方式，解决了传统查询在处理以下问题时的痛点：

蓝心千询

蓝心千询是vivo推出的一个多功能AI智能助手

34

查看详情

• 不定深度层级遍历：这是最核心的优势。无论是组织架构图、文件系统目录树、社交网络的好友关系链，还是复杂的物料清单（BOM），它们共同的特点是层级深度不确定。递归查询能够从一个或多个起点出发，自动探索所有可达的节点，直到没有新的节点可以访问。
• 路径追踪与聚合：在遍历层级时，我们往往不仅需要知道最终的节点，还需要知道到达该节点的完整路径，或者沿途的某些聚合信息。例如，找出从A到B的所有可能路径，或者计算从根节点到某个叶子节点的所有中间节点的总和。递归查询通过在每次迭代中累积这些信息（如上面例子中的

  path

  登录后复制
  字段），能够轻松实现。
• 图遍历问题：虽然SQL不是专业的图数据库，但对于一些简单的图结构（如无向图或有向无环图），递归查询可以模拟图的遍历算法，找出连通分量、最短路径（如果结合其他逻辑）等。
• 避免冗余代码和性能瓶颈：相比于手动编写多个自连接来模拟层级遍历，递归查询的代码更简洁，可读性更强。数据库引擎对递归CTE有专门的优化，通常比多层自连接更高效，尤其是在处理深层级时。

说白了，它提供了一种声明式的方式来描述“从这里开始，然后重复这个过程直到没有新的发现”，这比我们用程序语言去写循环遍历要高效和直观得多。

WITH RECURSIVE 的语法结构是怎样的？有哪些关键组成部分和注意事项？

WITH RECURSIVE

WITH RECURSIVE cte_name AS (
    -- 锚成员 (Anchor Member)
    SELECT ...
    FROM base_table
    WHERE initial_condition

    UNION ALL -- 或 UNION

    -- 递归成员 (Recursive Member)
    SELECT ...
    FROM another_table AS t
    JOIN cte_name AS r ON t.join_column = r.join_column
    WHERE recursive_condition
)
SELECT ... FROM cte_name;

这里面有几个关键的组成部分和需要注意的地方：

1. cte_name

   登录后复制
   ：这是你定义的公共表表达式的名称，你可以在递归成员中引用它。
2. 锚成员：
   • 这是一个标准的

     SELECT

     登录后复制
     语句，它定义了递归的起始数据集。
   • 不允许引用

     cte_name

     登录后复制
     自身。它必须是独立的，就像你扔出的第一块石头。
   • 它的列列表（包括数据类型）必须与递归成员的列列表完全匹配，因为

     UNION ALL

     登录后复制
     要求两边的结构一致。
3. 递归成员：
   • 这是一个

     SELECT

     登录后复制
     语句，它必须引用

     cte_name

     登录后复制
     自身。这是它“递归”的本质所在。
   • 它通常会通过

     JOIN

     登录后复制
     操作将

     cte_name

     登录后复制
     的结果与另一个表（或自身）连接起来，从而找到下一层级的数据。
   • 终止条件至关重要：递归成员的

     WHERE

     登录后复制
     子句是控制递归停止的关键。如果这个条件设计不当，或者没有有效地限制每次迭代产生的新行，那么查询就可能陷入无限循环，最终导致数据库资源耗尽或报错。例如，在员工层级查询中，当

     e.manager_id

     登录后复制
     不再能找到对应的

     eh.employee_id

     登录后复制
     时，递归就自然停止了。

4. UNION ALL

   登录后复制
   vs.

   UNION

   登录后复制
   ：

   • UNION ALL

     登录后复制
     会保留所有行，包括重复的。在大多数递归场景中，我们希望看到所有路径和节点，所以

     UNION ALL

     登录后复制
     是更常见的选择。

   • UNION

     登录后复制
     会去除重复的行。如果你需要确保结果集中没有重复的节点或路径，可以使用

     UNION

     登录后复制
     ，但它通常会带来额外的性能开销，因为数据库需要进行去重操作。
5. 数据类型匹配：

   UNION ALL

   登录后复制
   要求左右两边的查询结果集列的数量和对应列的数据类型必须兼容。如果类型不匹配，数据库会尝试隐式转换，如果无法转换则会报错。
6. 路径追踪与深度限制：
   • 为了防止循环引用（比如在社交网络中A关注B，B关注A），或者为了限制递归的深度，我们常常会在CTE中加入额外的列来记录当前的路径或深度。
   • 例如，可以添加一个

     level

     登录后复制
     列来记录当前递归的深度，并在递归成员的

     WHERE

     登录后复制
     子句中添加

     AND eh.level < max_depth

     登录后复制
     来限制深度。
   • 对于循环检测，一些数据库（如PostgreSQL）支持

     CYCLE

     登录后复制
     子句，可以更优雅地处理循环图。如果没有，你可能需要手动在路径中记录已访问的节点，并在递归成员中检查新节点是否已在当前路径中。

理解这些细节，能够帮助我们不仅写出能运行的递归查询，更能写出健壮、高效且能够处理复杂业务场景的查询。

在实际应用中，如何优化SQL递归查询的性能并避免常见陷阱？

实践中，SQL递归查询虽然强大，但并非没有性能陷阱。我个人在处理一些大型数据集的递归查询时，就踩过不少坑，也总结了一些经验。优化和避免陷阱的关键在于理解其工作机制，并针对性地进行设计和调优。

1. 索引是生命线：
   • 递归查询的性能瓶颈往往出现在

     JOIN

     登录后复制
     操作上。确保锚成员和递归成员中用于连接的列（例如

     manager_id

     登录后复制
     和

     employee_id

     登录后复制
     ）以及

     WHERE

     登录后复制
     子句中使用的列都建立了合适的索引。
   • 例如，在员工层级例子中，

     employees.manager_id

     登录后复制
     和

     employees.employee_id

     登录后复制
     上都应该有索引。这能极大加速每次迭代的查找过程。
2. 限制初始数据集（锚成员）：
   • 如果业务允许，尽量缩小锚成员的范围。从一个更小的起点开始递归，比从一个巨大的起点开始要高效得多。例如，如果你只需要某个特定部门的层级结构，就不要从整个公司的顶层经理开始。
3. 避免不必要的列：
   • 在

     SELECT

     登录后复制
     列表中只选择你真正需要的列。递归过程中传递的数据量越小，性能开销越低。尤其是在路径追踪时，如果路径字符串过长，会增加内存和CPU的负担。
4. 注意

   UNION ALL

   登录后复制
   与

   UNION

   登录后复制
   的选择：
   • 正如前面提到的，

     UNION

     登录后复制
     会进行去重操作，这通常比

     UNION ALL

     登录后复制
     有更高的性能开销。如果你的业务逻辑允许重复（或者你知道不会产生重复），优先使用

     UNION ALL

     登录后复制
     。
5. 循环检测与深度限制：
   • 无限循环：这是递归查询最常见的陷阱。如果你的数据中存在循环引用（例如员工A是B的经理，B又是A的经理），或者递归成员的终止条件有误，查询就会陷入无限循环。
     • 解决方案：
       • 路径跟踪：在CTE中增加一个列来记录已经访问过的节点路径（如

         path

         登录后复制
         或

         visited_nodes

         登录后复制
         ）。在递归成员中，检查当前节点是否已存在于路径中，如果存在，则终止该分支的递归。
       • 深度限制：增加一个

         level

         登录后复制
         或

         depth

         登录后复制
         列，并在递归成员的

         WHERE

         登录后复制
         子句中添加

         AND level < max_allowed_depth

         登录后复制
         来强制终止。这对于防止无限循环和限制查询范围都很有用。

       • CYCLE

         登录后复制
         子句：某些数据库（如PostgreSQL 9.1+）提供了

         CYCLE

         登录后复制
         子句，可以更方便地检测和处理循环。
   • 性能下降：当递归深度非常大，或者每次迭代产生大量新行时，性能会急剧下降。
     • 解决方案：
       • 分批处理/分页：如果可能，尝试将大问题分解为小问题，或者对结果进行分页处理。
       • 考虑其他方案：对于极其庞大且深度不定的图结构，或者对实时性要求极高的场景，可能需要考虑使用专门的图数据库（如Neo4j）或者预先计算并存储路径/层级信息（如使用

         Materialized Path

         登录后复制
         或

         Nested Set

         登录后复制
         模型）。
6. 数据库特定优化：
   • 不同的数据库管理系统（DBMS）对

     WITH RECURSIVE

     登录后复制
     的实现和优化策略可能有所不同。例如，SQL Server的CTE可能在某些情况下表现不同于PostgreSQL或Oracle。查阅你所用数据库的官方文档，了解其特定的性能提示和限制。
7. 测试与监控：
   • 在实际部署前，务必在接近生产环境的数据量和结构上进行充分的测试，并使用数据库的性能监控工具（如

     EXPLAIN ANALYZE

     登录后复制
     或执行计划）来分析查询的瓶颈。这能帮助你识别哪些步骤消耗了最多的资源，从而进行针对性优化。

总的来说，

WITH RECURSIVE

以上就是什么是SQL的递归查询？WITHRECURSIVE的实现方法的详细内容，更多请关注php中文网其它相关文章！