IT博客汇 | MySQL 8.0 中的 CTE 功能

MySQL 8.0 中的 CTE 功能

admin发表于 2024-12-27 10:57:09

CTE 的全称是 Common Table Expressions，有时候也缩写为 CTEs，在 SQL 标准中使用 WITH clause表达^[1]。在 ANSI standard 标准的 SQL:1999 中定义，参考：ISO/IEC 9075-2:1999^[7][8]。该特性支持在 MySQL 8.0 中正式被引入^[9]。

通常，如果不需要编写特别复杂的 SQL ，更为具体的，如果无需编写带有递归功能的 SQL 时，CTE 通常都不是必须的。所以，该语法功能，对于分析型的场景或者数据开发等场景更为常用。

这里概述 CTE 的两个常用功能：简化子查询结构、实现递归执行。其中，实现递归执行，是 CTE 独特的能力，是对 SQL 能力的一种补充。

简化子查询结构

CTE 功能提供了一种新的子查询或临时表的写法。可以把后续 SQL 中需要使用的临时表或子查询独立的放在WITH子句中，使得结构看起来更为清晰一些。

例如，如下两个写法在语义上是等价的：

WITH 
  t_mid as (
    SELECT * FROM t_1 JOIN t_2
  )
SELECT * FROM t_mid;

SELECT * 
FROM 
  (
    SELECT * FROM t_1 JOIN t_2
  ) t_mid;

递归执行

该功能是 CTE 的核心能力。例如，在表中查询好友的好友、查询主管的主管、递归数据生成等功能。

递归执行方式概述

在各个数据库的关于 CTE 介绍都会较为详细的描述，这里对该执行过程做一个简单的描述。

对于递归 CTE 对应的 SQL 总是有类似结构：

WITH RECURSIVE table_cte (col_01 ... ) AS (
  SELECT ...                 -- return initial row set
  UNION ALL
  SELECT ... FROM  table_cte -- return additional row sets
)

在该结构中：

第一个SELECT为数据初始化
第二个SELECT则为递归执行的部分，该部分在引用表table_cte时，总是使用该表的最后一条生成记录

斐波那契数列的生成

这是一个MySQL文档中的示例^[10]。

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
...
|   10 |    34 |         55 |
+------+-------+------------+

根据前面小结“递归执行方式概述”中所描述，在每次执行上述的第二个 SELECT，即SELECT n + 1, next_fib_n, fib_n + next_fib_n FROM fibonacci时，总是使用 CTE 表fibonacci最后生成的记录。所以，该SQL中引用的列n 、fib_n和next_fib_n均为表fibonacci最后生成的记录，即上一条记录。

所以，该 SQL 第一列返回的是一个自增的序列；第二列，即为斐波那契数列；第三列是用于计算的中间列。

广度优先还是深度优先

在 MySQL 的递归遍历中，并没有像 PostgreSQL 那样可以通过 SQL 关键字去控制。在文档中，也没有描述是广度优先还是深度优先。这里通过一些 SQL 变量来看看，到底是广度优先还是深度优先。

先说结论吧：MySQL 使用的是广度优先遍历。关注验证过程的，可以继续阅读这一段的示例和解读。

这里使用 MySQL 官方文档中另一个示例。该示例包含一个企业人员组织架构的关系：

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

我们可以使用如下的递归 CTE 语句进行组织架构遍历：

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;

我们对这条 SQL 语句进行如下修改，以观察是广度优先还是深度优先：

WITH RECURSIVE employee_paths (id, name, path,seq) AS
(
  SELECT id, name, CAST(id AS CHAR(200)),@seq:=1
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id),@seq:=@seq+1
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY seq;

+------+---------+-----------------+------+
| id   | name    | path            | seq  |
+------+---------+-----------------+------+
|  333 | Yasmina | 333             |    1 |
|  198 | John    | 333,198         |    2 |
|  692 | Tarek   | 333,692         |    3 |
|   29 | Pedro   | 333,198,29      |    4 |
|  123 | Adil    | 333,692,123     |    5 |
|   72 | Pierre  | 333,198,29,72   |    6 |
| 4610 | Sarah   | 333,198,29,4610 |    7 |
+------+---------+-----------------+------+

该结果的最后一列 seq，反映了输出的先后顺序。可以看到，这里使用的广度优先遍历。