13.2.15 WITH（公用表表达式）

MySQL 8.0 参考手册 / 第 13 章 SQL 语句 / 13.2 数据操作语句 / 13.2.15 WITH（公用表表达式）

13.2.15 WITH（公用表表达式）

公用表表达式 (CTE) 是一个命名的临时结果集，它存在于单个语句的范围内，稍后可以在该语句中引用，可能多次引用。以下讨论描述了如何编写使用 CTE 的语句。

有关 CTE 优化的信息，请参阅第 8.2.2.4 节，“使用合并或实现优化派生表、视图引用和公用表表达式”。

其他资源

这些文章包含有关在 MySQL 中使用 CTE 的其他信息，包括许多示例：

公用表表达式

要指定公用表表达式，请使用 WITH具有一个或多个逗号分隔子句的子句。每个子句都提供一个生成结果集的子查询，并将名称与子查询相关联。以下示例在子句中定义了名为 cte1and的 CTE，并cte2在子句 WITH后的顶层引用它们SELECT：WITH

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

在包含该 WITH子句的语句中，可以引用每个 CTE 名称来访问相应的 CTE 结果集。

一个CTE名称可以在其他CTE中被引用，从而实现基于其他CTE定义CTE。

CTE 可以引用自身来定义递归 CTE。递归 CTE 的常见应用包括序列生成和层次结构或树结构数据的遍历。

公用表表达式是 DML 语句语法的可选部分。它们是使用 WITH子句定义的：

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

cte_name命名单个公用表表达式，并可用作包含该WITH子句的语句中的表引用。

的subquery部分称为 “ CTE 的子查询”，是生成 CTE 结果集的部分。后面的括号是必需的。 AS (subquery)AS

如果公用表表达式的子查询引用它自己的名称，则该公用表表达式是递归的。如果子句中的任何 CTE 是递归的RECURSIVE，则必须包含关键字。WITH有关详细信息，请参阅递归公用表表达式。

给定 CTE 的列名称的确定如下：

如果带括号的名称列表跟在 CTE 名称之后，则这些名称是列名称：
```
WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;
```
列表中的名称数必须与结果集中的列数相同。
否则，列名来自SELECT该部分中第一个的选择列表： AS (subquery)
```
WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;
```

在这些上下文中允许使用WITH子句：

在SELECT、 UPDATE和 DELETE语句的开头。

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

在子查询（包括派生表子查询）的开头：

SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...

紧接SELECT 在包含 SELECT语句的 for 语句之前：

INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

WITH同一级别只允许有一个子句。不允许在同一级别 WITH后跟，因此这是非法的：WITH

WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...

要使声明合法，请使用单个 WITH子句，用逗号分隔子句：

WITH cte1 AS (...), cte2 AS (...) SELECT ...

WITH但是，如果语句出现在不同级别，则语句可以包含多个子句：

WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

一个WITH子句可以定义一个或多个公用表表达式，但每个 CTE 名称对于该子句必须是唯一的。这是非法的：

WITH cte1 AS (...), cte1 AS (...) SELECT ...

要使声明合法，请使用唯一名称定义 CTE：

WITH cte1 AS (...), cte2 AS (...) SELECT ...

CTE 可以引用自身或其他 CTE：

自引用 CTE 是递归的。
CTE 可以引用同一 WITH条款中前面定义的 CTE，但不能引用后面定义的 CTE。
此约束排除了相互递归的 CTE，其中 cte1引用cte2 和cte2引用 cte1。其中一个引用必须指向稍后定义的 CTE，这是不允许的。
给定查询块中的 CTE 可以引用更外层查询块中定义的 CTE，但不能引用更内层查询块中定义的 CTE。

为了解析对同名对象的引用，派生表隐藏了 CTE；和 CTE 隐藏基表、 TEMPORARY表和视图。通过在同一查询块中搜索对象来进行名称解析，然后在没有找到具有该名称的对象的情况下依次进行到外部块。

与派生表一样，CTE 不能包含 MySQL 8.0.14 之前的外部引用。这是 MySQL 8.0.14 中解除的 MySQL 限制，不是 SQL 标准的限制。有关特定于递归 CTE 的其他语法注意事项，请参阅递归公用表表达式。

递归公用表表达式

递归公用表表达式是一个具有引用其自身名称的子查询的表达式。例如：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

执行时，该语句会产生此结果，一个包含简单线性序列的列：

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

递归 CTE 具有以下结构：

如果子句中的任何 CTE 引用其自身，则该WITH子句必须以开头。（如果没有 CTE 引用自身，则允许但不是必需的。） WITH RECURSIVEWITHRECURSIVE
如果您忘记RECURSIVE了递归 CTE，则可能会出现此错误：
```
ERROR 1146 (42S02): Table 'cte_name' doesn't exist
```
递归 CTE 子查询有两部分，由 UNION ALL or分隔UNION [DISTINCT]：
```
SELECT ...      -- return initial row set
UNION ALL
SELECT ...      -- return additional row sets
```
第一个SELECT生成 CTE 的初始行，但不引用 CTE 名称。第二个通过在其子句SELECT 中引用 CTE 名称来生成额外的行和递归。FROM当此部分不产生新行时，递归结束。因此，递归 CTE 由非递归 SELECT部分和递归SELECT部分组成。
每个SELECT部分本身可以是多个SELECT 语句的联合。
CTE 结果列的类型仅从非递归部分的列类型中推断出来 SELECT，并且这些列都是可以为空的。对于类型确定，递归SELECT部分被忽略。
如果非递归和递归部分用分隔 UNION DISTINCT，则消除重复行。这对于执行传递闭包的查询很有用，可以避免无限循环。
递归部分的每次迭代仅对前一次迭代生成的行进行操作。如果递归部分有多个查询块，则每个查询块的迭代按未指定的顺序安排，并且每个查询块对由其前一次迭代或自上一次迭代结束后其他查询块生成的行进行操作。

前面显示的递归 CTE 子查询有这个非递归部分，它检索单行以生成初始行集：

SELECT 1

CTE 子查询也有这个递归部分：

SELECT n + 1 FROM cte WHERE n < 5

在每次迭代中，都会SELECT 生成一个新值比 n前一个行集的值大 1 的行。第一次迭代对初始行集 ( 1) 进行操作并生成1+1=2; 第二次迭代对第一次迭代的行集 ( 2) 进行操作并生成2+1=3; 等等。这一直持续到递归结束，当 n不再小于 5 时发生。

如果 CTE 的递归部分为列生成比非递归部分更宽的值，则可能需要加宽非递归部分中的列以避免数据截断。考虑这个声明：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

在非严格 SQL 模式下，该语句产生以下输出：

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

列str值都是 'abc'因为非递归 SELECT决定了列宽。str因此，递归产生的更宽的值SELECT 被截断了。

在严格 SQL 模式下，该语句会产生错误：

ERROR 1406 (22001): Data too long for column 'str' at row 1

要解决此问题，以便语句不会产生截断或错误，请在非递归中使用CAST() 以SELECT使str列更宽：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

现在语句产生这个结果，没有截断：

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

列是按名称而不是位置访问的，这意味着递归部分中的列可以访问非递归部分中具有不同位置的列，如该 CTE 所示：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 1 AS p, -1 AS q
  UNION ALL
  SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;

因为p一行是 q从前一行派生的，反之亦然，正值和负值在输出的每个连续行中交换位置：

+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+

一些语法约束适用于递归 CTE 子查询：

递归SELECT部分不得包含这些结构：
- 聚合函数，例如SUM()
- 窗口函数
- GROUP BY
- ORDER BY
- DISTINCT
在 MySQL 8.0.19 之前， SELECT递归 CTE 的递归部分也不能使用LIMIT子句。这个限制在 MySQL 8.0.19 中被解除， LIMIT现在在这种情况下被支持，还有一个可选的OFFSET子句。LIMIT对结果集的影响与在最外层使用时相同 SELECT，但也更有效，因为将它与递归一起使用会SELECT在生成请求的行数后立即停止行的生成。
这些约束不适用于 SELECT递归 CTE 的非递归部分。该禁令DISTINCT仅适用于UNION会员； UNION DISTINCT被允许。
递归部分必须仅在其子句SELECT中引用 CTE 一次，而不是在任何子查询中。FROM它可以引用 CTE 以外的表并将它们与 CTE 连接。如果在这样的连接中使用，CTE 不得位于LEFT JOIN.

这些约束来自 SQL 标准，而不是 MySQL 特定的排除项ORDER BY（ LIMITMySQL 8.0.18 及更早版本）和 DISTINCT.

对于递归 CTE，EXPLAIN 递归部分的输出行SELECT 显示Recursive在 Extra列中。

显示的成本估算 EXPLAIN代表每次迭代的成本，这可能与总成本有很大差异。优化器无法预测迭代次数，因为它无法预测WHERE子句在什么时候变为假。

CTE 实际成本也可能受结果集大小的影响。生成许多行的 CTE 可能需要一个足够大的内部临时表，以便从内存格式转换为磁盘格式，并且可能会降低性能。如果是这样，增加允许的内存中临时表大小可能会提高性能；参见第 8.4.4 节，“MySQL 中内部临时表的使用”。

限制公用表表达式递归

对于递归 CTE，递归 SELECT部分包含终止递归的条件很重要。作为一种防止失控的递归 CTE 的开发技术，您可以通过限制执行时间来强制终止：

cte_max_recursion_depth 系统变量强制限制 CTE 的递归级别数。服务器终止任何递归级别超过此变量值的 CTE 的执行。
系统变量强制执行在当前会话中执行的语句的max_execution_time 执行超时。SELECT
优化器提示对其出现的语句强制MAX_EXECUTION_TIME 执行每个查询的执行超时。SELECT

假设错误地写了一个没有递归执行终止条件的递归CTE：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT * FROM cte;

默认情况下， cte_max_recursion_depth值为 1000，导致 CTE 在递归超过 1000 级时终止。应用程序可以更改会话值以适应它们的要求：

SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

您还可以设置全局 cte_max_recursion_depth值以影响随后开始的所有会话。

对于执行并因此递归缓慢的查询，或者在有理由将 cte_max_recursion_depth值设置得非常高的上下文中，另一种防止深度递归的方法是设置每个会话超时。为此，请在执行 CTE 语句之前执行如下语句：

SET max_execution_time = 1000; -- impose one second timeout

或者，在 CTE 语句本身中包含优化器提示：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

从 MySQL 8.0.19 开始，你还可以 LIMIT在递归查询中使用内部限制返回到最外层的行数 SELECT，例如：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;

除了或代替设置时间限制，您还可以执行此操作。因此，以下 CTE 在返回一万行或运行一秒（1000 毫秒）后终止，以先发生者为准：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

如果没有执行时间限制的递归查询进入无限循环，您可以使用 KILL QUERY. 在会话本身中，用于运行查询的客户端程序可能会提供一种终止查询的方法。例如，在 mysql中，键入Control+C 会中断当前语句。

递归公用表表达式示例

如前所述，递归公用表表达式 (CTE) 经常用于序列生成和遍历分层或树结构数据。本节展示了这些技术的一些简单示例。

斐波那契数列生成

斐波那契数列以两个数字 0 和 1（或 1 和 1）开头，之后的每个数字都是前两个数字的总和。如果递归生成的每一行都 SELECT可以访问系列中的前两个数字，则递归公用表表达式可以生成斐波那契系列。以下 CTE 使用 0 和 1 作为前两个数字生成一个 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;

CTE 产生以下结果：

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+

CTE 的工作原理：

n是一个显示列，指示该行包含第n- 个斐波那契数。例如，第 8 个斐波那契数是 13。
该fib_n列显示 Fibonacci 数n。
该next_fib_n列显示 number 之后的下一个 Fibonacci 数n。此列将下一个系列值提供给下一行，以便该行可以生成其fib_n列中前两个系列值的总和。
当达到 10 时递归结束n。这是一个任意选择，用于将输出限制为一小组行。

前面的输出显示了整个 CTE 结果。要仅选择其中的一部分，请将适当的WHERE子句添加到顶级SELECT. 例如，要选择第 8 个斐波那契数，请执行以下操作：

mysql> WITH RECURSIVE fibonacci ...
       ...
       SELECT fib_n FROM fibonacci WHERE n = 8;
+-------+
| fib_n |
+-------+
|    13 |
+-------+

日期序列生成

公共表表达式可以生成一系列连续的日期，这对于生成包含系列中所有日期的行的摘要很有用，包括未在摘要数据中表示的日期。

假设销售数字表包含以下行：

mysql> SELECT * FROM sales ORDER BY date, price;
+------------+--------+
| date       | price  |
+------------+--------+
| 2017-01-03 | 100.00 |
| 2017-01-03 | 200.00 |
| 2017-01-06 |  50.00 |
| 2017-01-08 |  10.00 |
| 2017-01-08 |  20.00 |
| 2017-01-08 | 150.00 |
| 2017-01-10 |   5.00 |
+------------+--------+

此查询汇总了每天的销售额：

mysql> SELECT date, SUM(price) AS sum_price
       FROM sales
       GROUP BY date
       ORDER BY date;
+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-06 |     50.00 |
| 2017-01-08 |    180.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+

但是，该结果包含未在表格跨越的日期范围内表示的日期的“漏洞” 。可以使用递归 CTE 生成表示该范围内所有日期的结果，以生成该日期集，并与 LEFT JOIN销售数据连接。

这是生成日期范围系列的 CTE：

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT * FROM dates;

CTE 产生以下结果：

+------------+
| date       |
+------------+
| 2017-01-03 |
| 2017-01-04 |
| 2017-01-05 |
| 2017-01-06 |
| 2017-01-07 |
| 2017-01-08 |
| 2017-01-09 |
| 2017-01-10 |
+------------+

CTE 的工作原理：

非递归SELECT 生成表跨越的日期范围内的最低日期 sales。
递归生成的每一行 SELECT都会在前一行生成的日期上增加一天。
在日期达到表跨越的日期范围内的最高日期后，递归结束sales。

将 CTE 与表连接起来LEFT JOIN生成 sales销售摘要，其中包含范围内每个日期的一行：

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
FROM dates LEFT JOIN sales ON dates.date = sales.date
GROUP BY dates.date
ORDER BY dates.date;

输出如下所示：

+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-04 |      0.00 |
| 2017-01-05 |      0.00 |
| 2017-01-06 |     50.00 |
| 2017-01-07 |      0.00 |
| 2017-01-08 |    180.00 |
| 2017-01-09 |      0.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+

需要注意的几点：

查询是否效率低下，尤其是 MAX()对递归中的每一行执行子查询的查询 SELECT？ EXPLAIN显示包含的子查询MAX()仅被评估一次并且结果被缓存。
使用COALESCE()可避免在表中没有销售数据的日期显示NULL在 sum_price列sales中。

分层数据遍历

递归公用表表达式对于遍历形成层次结构的数据很有用。考虑创建一个小型数据集的这些语句，该数据集显示了公司中每个员工的员工姓名和 ID 号，以及员工经理的 ID。顶级员工（CEO）的经理 ID 为NULL（无经理）。

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);

生成的数据集如下所示：

mysql> SELECT * FROM employees ORDER BY id;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  123 | Adil    |        692 |
|  198 | John    |        333 |
|  333 | Yasmina |       NULL |
|  692 | Tarek   |        333 |
| 4610 | Sarah   |         29 |
+------+---------+------------+

要生成包含每个员工管理链的组织结构图（即从 CEO 到员工的路径），请使用递归 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;

CTE 产生以下输出：

+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+

CTE 的工作原理：

非递归SELECT 生成 CEO 的行（具有 NULL经理 ID 的行）。
该path列被加宽到 CHAR(200)以确保path递归生成的更长值有空间SELECT。
递归生成的每一行 SELECT查找直接向前一行生成的员工报告的所有员工。对于每个这样的员工，该行包括员工 ID 和姓名，以及员工管理链。链条是经理的链条，在末尾添加了员工ID。
当员工没有其他人向他们报告时，递归结束。

要查找一个或多个特定员工的路径，请将一个 WHERE子句添加到顶级 SELECT. 例如，要显示 Tarek 和 Sarah 的结果，请 SELECT像这样修改：

mysql> WITH RECURSIVE ...
       ...
       SELECT * FROM employees_extended
       WHERE id IN (692, 4610)
       ORDER BY path;
+------+-------+-----------------+
| id   | name  | path            |
+------+-------+-----------------+
| 4610 | Sarah | 333,198,29,4610 |
|  692 | Tarek | 333,692         |
+------+-------+-----------------+

公用表表达式与类似结构的比较

公用表表达式 (CTE) 在某些方面类似于派生表：

两个构造都被命名。
两种构造都存在于单个语句的范围内。

由于这些相似性，CTE 和派生表通常可以互换使用。作为一个简单的例子，这些语句是等价的：

WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;

但是，CTE 比派生表有一些优势：

派生表只能在查询中引用一次。一个 CTE 可以被多次引用。要使用派生表结果的多个实例，您必须多次派生结果。
CTE 可以是自引用的（递归的）。
一个 CTE 可以引用另一个。
当 CTE 的定义出现在语句的开头而不是嵌入其中时，CTE 可能更容易阅读。

CTE 类似于创建的表， CREATE [TEMPORARY] TABLE但不需要显式定义或删除。对于 CTE，您无需任何权限即可创建表。