A Definitive Guide To MySQL Recursive CTE

摘要：在本教程中，您将了解 MySQL 递归 CTE 以及如何使用它来遍历分层数据。

MySQL递归CTE简介

递归公用表表达式(CTE) 是具有引用 CTE 名称本身的子查询的 CTE。下面说明了递归 CTE 的语法

WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;
Code language: SQL (Structured Query Language) (sql)

递归 CTE 由三个主要部分组成：

• 形成 CTE 结构的基本结果集的初始查询。初始查询部分称为锚成员。
• 递归查询部分是引用CTE名称的查询，因此称为递归成员。递归成员通过UNION ALL或UNION DISTINCT运算符与锚成员连接。
• 确保当递归成员没有返回行时递归停止的终止条件。

递归CTE的执行顺序如下：

1. 首先，将成员分为两部分：锚成员和递归成员。
2. 接下来，执行锚成员以形成基本结果集（ R0 ）并使用该基本结果集进行下一次迭代。
3. 然后，以Ri结果集作为输入执行递归成员，并以Ri+1作为输出。
4. 之后，重复第三步，直到递归成员返回空结果集，即满足终止条件。
5. 最后，使用UNION ALL运算符合并从 R0 到 Rn 的结果集。

递归成员限制

递归成员不得包含以下结构：

• 聚合函数，例如MAX 、 MIN 、 SUM 、 AVG 、 COUNT等。
• GROUP BY子句
• ORDER BY子句
• LIMIT条款
• 清楚的

请注意，上述约束不适用于锚固构件。此外，对DISTINCT禁止仅在使用UNION运算符时适用。如果您使用UNION DISTINCT运算符，则允许使用DISTINCT 。

此外，递归成员只能在其FROM子句中引用 CTE 名称一次，而不能在任何子查询中引用 CTE 名称。

简单的 MySQL 递归 CTE 示例

请参阅以下简单的递归 CTE 示例：

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;
Code language: SQL (Structured Query Language) (sql)

在此示例中，有以下查询：

SELECT 1
Code language: SQL (Structured Query Language) (sql)

是返回 1 作为基本结果集的锚成员。

以下查询

SELECT n + 1
FROM cte_count 
WHERE n < 3
Code language: SQL (Structured Query Language) (sql)

是递归成员，因为它引用了 CTE 的名称，即cte_count 。

递归成员中的表达式n < 3是终止条件。一旦 n 等于 3，递归成员将返回一个空集，该空集将停止递归。

下图说明了上面CTE的元素：

递归 CTE 返回以下输出：

递归CTE的执行步骤如下：

1. 首先，将锚点成员和递归成员分开。
2. 接下来，锚成员形成初始行 ( SELECT 1 )，因此第一次迭代产生 1 + 1 = 2，其中 n = 1。
3. 然后，第二次迭代对第一次迭代 (2) 的输出进行运算，并生成 2 + 1 = 3，其中 n = 2。
4. 之后，在第三次操作（n = 3）之前，满足终止条件（ n < 3 ），因此查询停止。
5. 最后，使用UNION ALL运算符合并所有结果集 1、2 和 3

使用MySQL递归CTE遍历分层数据

我们将使用classicmodels示例数据库中的employees表进行演示。

employees表具有引用employeeNumber列的reportsTo列。 reportsTo列存储经理的 ID。最高管理者不向公司组织结构中的任何人汇报，因此reportsTo列中的值为NULL 。

您可以应用递归CTE以自上而下的方式查询整个组织结构，如下所示：

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
           officeCode, 
           1 lvl
   FROM employees
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
            e.officeCode,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
       city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;Code language: SQL (Structured Query Language) (sql)

让我们将查询分成更小的部分，以便更容易理解。

首先，使用以下查询形成锚成员：

SELECT 
    employeeNumber, 
    reportsTo managerNumber, 
    officeCode
FROM
    employees
WHERE
    reportsTo IS NULL
Code language: SQL (Structured Query Language) (sql)

此查询（锚成员）返回reportsTo为NULL的最高管理者。

其次，通过引用 CTE 名称来创建递归成员，在本例中为employee_paths ：

SELECT 
    e.employeeNumber, 
    e.reportsTo, 
    e.officeCode
FROM
    employees e
INNER JOIN employee_paths ep 
    ON ep.employeeNumber = e.reportsTo
Code language: SQL (Structured Query Language) (sql)

此查询（递归成员）返回经理的所有直接下属，直到不再有直接下属。如果递归成员没有返回直接报告，则递归停止。

第三，使用employee_paths CTE 的查询将CTE 返回的结果集与offices表连接起来以形成最终结果集。

以下是查询的输出：

在本教程中，您了解了 MySQL 递归 CTE 以及如何使用它来遍历分层数据。