MySQL8新特性：公用表表达式_mysql 公共子表达式

一、概述

1. 公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions）。
2. CTE是一个命名的临时结果集，作用范围是当前语句。
3. CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。所以，可以考虑代替子查询。
4. 依据语法结构和执行方式的不同，公用表表达式分为普通公用表表达式和递归公用表表达式 2 种。

二、普通公用表表达式

1、语法结构

普通公用表表达式的语法结构是：

WITH CTE名称 
AS （子查询）
SELECT|DELETE|UPDATE 语句;
1
2
3

普通公用表表达式类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表表达式所引用。

2、案例

举例：查询员工所在的部门的详细信息。

mysql> SELECT * FROM departments
    -> WHERE department_id IN (
    ->                  SELECT DISTINCT department_id
    ->                  FROM employees
    ->                  );
+---------------+------------------+------------+-------------+
| department_id | department_name  | manager_id | location_id |
+---------------+------------------+------------+-------------+
|            10 | Administration   |        200 |        1700 |
|            20 | Marketing        |        201 |        1800 |
|            30 | Purchasing       |        114 |        1700 |
|            40 | Human Resources  |        203 |        2400 |
|            50 | Shipping         |        121 |        1500 |
|            60 | IT               |        103 |        1400 |
|            70 | Public Relations |        204 |        2700 |
|            80 | Sales            |        145 |        2500 |
|            90 | Executive        |        100 |        1700 |
|           100 | Finance          |        108 |        1700 |
|           110 | Accounting       |        205 |        1700 |
+---------------+------------------+------------+-------------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

这个查询也可以用普通公用表表达式的方式完成：

mysql> WITH emp_dept_id
    -> AS (SELECT DISTINCT department_id FROM employees)
    -> SELECT *
    -> FROM departments d JOIN emp_dept_id e
    -> ON d.department_id = e.department_id;
+---------------+------------------+------------+-------------+---------------+
| department_id | department_name  | manager_id | location_id | department_id |
+---------------+------------------+------------+-------------+---------------+
|            90 | Executive        |        100 |        1700 |            90 |
|            60 | IT               |        103 |        1400 |            60 |
|           100 | Finance          |        108 |        1700 |           100 |
|            30 | Purchasing       |        114 |        1700 |            30 |
|            50 | Shipping         |        121 |        1500 |            50 |
|            80 | Sales            |        145 |        2500 |            80 |
|            10 | Administration   |        200 |        1700 |            10 |
|            20 | Marketing        |        201 |        1800 |            20 |
|            40 | Human Resources  |        203 |        2400 |            40 |
|            70 | Public Relations |        204 |        2700 |            70 |
|           110 | Accounting       |        205 |        1700 |           110 |
+---------------+------------------+------------+-------------+---------------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

1. 例子说明，公用表表达式可以起到子查询的作用。
2. 以后如果遇到需要使用子查询的场景，你可以在查询之前，先定义公用表表达式，然后在查询中用它来代替子查询。
3. 而且，跟子查询相比，公用表表达式有一个优点，就是定义过公用表表达式之后的查询，可以像一个表一样多次引用公用表表达式，而子查询则不能。

三、递归公用表表达式

1、语法结构

递归公用表表达式也是一种公用表表达式，只不过，除了普通公用表表达式的特点以外，它还有自己的特点，就是可以调用自己。它的语法结构是：

WITH RECURSIVE
CTE名称 AS （子查询）
SELECT|DELETE|UPDATE 语句;
1
2
3

1. 递归公用表表达式由 2 部分组成，分别是种子查询和递归查询，中间通过关键字 UNION [ALL]进行连接。
2. 这里的种子查询，意思就是获得递归的初始值。这个查询只会运行一次，以创建初始数据集，之后递归查询会一直执行，直到没有任何新的查询数据产生，递归返回。

2、案例

案例：针对于我们常用的employees表，包含employee_id，last_name和manager_id三个字段。如果a是b的管理者，那么，我们可以把b叫做a的下属，如果同时b又是c的管理者，那么c就是b的下属，是a的下下属。
下面我们尝试用查询语句列出所有具有下下属身份的人员信息。

如果用我们之前学过的知识来解决，会比较复杂，至少要进行 4 次查询才能搞定：

• 第一步，先找出初代管理者，就是不以任何别人为管理者的人，把结果存入临时表；

• 第二步，找出所有以初代管理者为管理者的人，得到一个下属集，把结果存入临时表；

• 第三步，找出所有以下属为管理者的人，得到一个下下属集，把结果存入临时表。

• 第四步，找出所有以下下属为管理者的人，得到一个结果集。

• 如果第四步的结果集为空，则计算结束，第三步的结果集就是我们需要的下下属集了，否则就必须继续进行第四步，一直到结果集为空为止。比如上面的这个数据表，就需要到第五步，才能得到空结果集。而且，最后还要进行第六步：把第三步和第四步的结果集合并，这样才能最终获得我们需要的结果集。

如果用递归公用表表达式，就非常简单了。我介绍下具体的思路。

• 用递归公用表表达式中的种子查询，找出初代管理者。字段 n 表示代次，初始值为 1，表示是第一代管理者。
• 用递归公用表表达式中的递归查询，查出以这个递归公用表表达式中的人为管理者的人，并且代次的值加 1。直到没有人以这个递归公用表表达式中的人为管理者了，递归返回。
• 在最后的查询中，选出所有代次大于等于 3 的人，他们肯定是第三代及以上代次的下属了，也就是下下属了。这样就得到了我们需要的结果集。
  这里看似也是 3 步，实际上是一个查询的 3 个部分，只需要执行一次就可以了。而且也不需要用临时表保存中间结果，比刚刚的方法简单多了。

代码实现：

WITH RECURSIVE cte 
AS 
(
SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100 -- 种子查询，找到第一代领导
UNION ALL
SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte
ON (a.manager_id = cte.employee_id) -- 递归查询，找出以递归公用表表达式的人为领导的人
)
SELECT employee_id,last_name FROM cte WHERE n >= 3; 
1
2
3
4
5
6
7
8
9

总之，递归公用表表达式对于查询一个有共同的根节点的树形结构数据，非常有用。它可以不受层级的限制，轻松查出所有节点的数据。如果用其他的查询方式，就比较复杂了。

四、注意事项

公用表表达式的作用是可以替代子查询，而且可以被多次引用。递归公用表表达式对查询有一个共同根节点的树形结构数据非常高效，可以轻松搞定其他查询方式难以处理的查询。但在使用时需要时刻注意以下事项：

1. 版本要求：
   在开始使用公用表表达式之前，确保您使用的是 MySQL 8 或更高版本。公用表表达式是 MySQL 8 中引入的新功能，因此在旧版本的 MySQL 中无法使用。如果您的数据库还在旧版本上运行，请考虑升级到 MySQL 8。

2. 语法规则：
   公用表表达式的语法类似于子查询，但使用 WITH 关键字进行定义。了解并熟悉公用表表达式的语法结构，包括 WITH 子句、CTE 名称、列名和实际查询部分。确保您正确地编写公用表表达式，并在查询中使用它们。

3. CTE 的作用范围：
   公用表表达式的作用范围仅限于定义它们的查询。这意味着在同一查询中，您可以在多个地方引用相同的 CTE，但在其他查询中无法使用相同的 CTE。在编写查询时，请确保将 CTE 的使用限制在合适的范围内，以避免引起错误。

4. 递归查询的使用：
   公用表表达式还可以用于执行递归查询，即查询中的表达式引用自身的情况。递归查询在处理层次结构的数据时非常有用，例如组织结构或评论回复。然而，递归查询需要谨慎使用，因为错误的查询可能导致无限循环。确保您了解递归查询的工作原理，并遵循最佳实践，例如设置递归终止条件和使用适当的索引。

5. 性能优化：
   虽然公用表表达式提供了更简洁的查询语法，但在处理大型数据集时，性能可能成为一个关键问题。CTE 的执行可能涉及到临时表的创建和数据复制，这可能导致额外的开销。在编写复杂的查询时，请评估性能影响并进行必要的优化，例如适当的索引和查询重写。使用 EXPLAIN 语句来分析查询计划，并确保查询的执行效率得到优化。

6. 数据库兼容性：
   请注意，公用表表达式是 MySQL 8 特有的功能，并不一定在其他数据库管理系统（DBMS）中可用。如果您计划将查询迁移到其他 DBMS，您可能需要调整查询语句以适应该系统的特定语法和功能。在跨数据库平台的项目中，请确保了解目标 DBMS 支持的功能和语法。

总结：

MySQL 8 的公用表表达式（CTE）是一个强大的功能，可以提高查询的可读性和编写复杂查询的灵活性。在使用公用表表达式时，请注意版本要求、语法规则、作用范围、递归查询的使用、性能优化以及数据库兼容性等重要事项。通过充分了解这些注意事项，您将能够更好地应用 MySQL 8 的公用表表达式功能，并获得更好的查询性能和结果。