MySQL 递归 CTE(公共表表达式)

原文地址 www.yiibai.com

最近在做项目管理的项目，其中有层级关联，多模块情况，感觉平时的树状数据库设计不太行，所以了解了一下，突然看到了关于MySQL在8.0上面迭代的更新，非常不错

在本教程中，您将了解 MySQL 递归 CTE(公共表表达式) 以及如何使用它来遍历分层数据。 自 MySQL 8.0 版以来简要介绍了公共表表达式或叫 CTE 的功能，因此需要 MySQL 8.0及以上版本

在本教程中，您将了解 MySQL 递归 CTE(公共表表达式) 以及如何使用它来遍历分层数据。

自 MySQL 8.0 版以来简要介绍了公共表表达式或叫 CTE 的功能，因此需要您在计算机上安装 MySQL 8.0，以便在本教程中练习本语句。

1. 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;

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

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

递归 CTE 的执行顺序如下：

1. 首先，将成员分为两个：锚点和递归成员。

2. 接下来，执行锚成员形成基本结果集 (R0)，并使用该基本结果集进行下一次迭代。

3. 然后，将Ri结果集作为输入执行递归成员，并将Ri+1作为输出。

4. 之后，重复第三步，直到递归成员返回一个空结果集，换句话说，满足终止条件。

5. 最后，使用UNION ALL运算符将结果集从R0到Rn组合。

6. 递归成员限制

---

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

• 聚合函数，如 MAX，MIN，SUM，AVG，COUNT 等
• GROUP BY 子句
• ORDER BY 子句
• LIMIT 子句
• DISTINCT

请注意，上述约束不适用于锚定成员。 另外，只有在使用UNION运算符时，要禁止DISTINCT才适用。 如果使用UNION DISTINCT运算符，则允许使用DISTINCT。

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

3. 简单的 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;

在此示例中，以下查询：

SELECT 1

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

以下查询 -

SELECT n + 1
FROM cte_count 
WHERE n < 3

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

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

下图显示了上述 CTE 的元素：

递归 CTE 返回以下输出：

递归 CTE 的执行步骤如下：

• 首先，分离锚和递归成员。
• 接下来，锚定成员形成初始行 (SELECT 1)，因此第一次迭代在n = 1时产生1 + 1 = 2。
• 然后，第二次迭代对第一次迭代的输出 (2) 进行操作，并且在n = 2时产生2 + 1 = 3。

1. 之后，在第三次操作 (n = 3) 之前，满足终止条件 (n <3)，因此查询停止。

2. 最后，使用UNION ALL运算符组合所有结果集1,2和3。

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

---

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

mysql> desc employees;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11)      | NO   | PRI | NULL    |       |
| lastName       | varchar(50)  | NO   |     | NULL    |       |
| firstName      | varchar(50)  | NO   |     | NULL    |       |
| extension      | varchar(10)  | NO   |     | NULL    |       |
| email          | varchar(100) | NO   |     | NULL    |       |
| officeCode     | varchar(10)  | NO   | MUL | NULL    |       |
| reportsTo      | int(11)      | YES  | MUL | NULL    |       |
| jobTitle       | varchar(50)  | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
8 rows in set

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;

让我们将查询分解成更小的部分，使其更容易理解。
首先，使用以下查询形成锚成员：

SELECT 
    employeeNumber, reportsTo managerNumber, officeCode
FROM
    employees
WHERE
    reportsTo IS NULL

此查询 (锚成员) 返回reportTo为NULL的总经理。

其次，通过引用 CTE 名称来执行递归成员，在这个示例中为 employee_paths：

SELECT 
    e.employeeNumber, e.reportsTo, e.officeCode
FROM
    employees e
        INNER JOIN
    employee_paths ep ON ep.employeeNumber = e.reportsTo

此查询 (递归成员) 返回经理的所有直接上级，直到没有更多的直接上级。 如果递归成员不返回直接上级，则递归停止。

第三，使用employee_paths的查询将 CTE 返回的结果集与offices表结合起来，以得到最终结果集合。

以下是查询的输出：

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