MySQL 8.3 Release Notes
公共表表达式（CTE）是一个命名的临时结果集，它存在于单个语句的作用域内，并且可以在该语句中多次引用。以下讨论描述了如何编写使用 CTE 的语句。
有关 CTE 优化的信息，请参阅 第 10.2.2.4 节，“使用合并或物化优化派生表、视图引用和公共表表达式”。
要指定公共表表达式，请使用带有一个或多个逗号分隔的子句的 WITH 子句。每个子句提供一个生成结果集的子查询，并将名称与子查询关联。以下示例在 WITH 子句中定义了名为 cte1 和 cte2 的 CTE，并在随后的 SELECT 中引用它们：
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 部分的 AS ( 称为 CTE 的“子查询”，它生成 CTE 结果集。subquery)AS 后面的括号是必需的。
公共表表达式是递归的，如果其子查询引用自己的名称。必须在 WITH 子句中包含 RECURSIVE 关键字，如果任何 CTE 是递归的。有关更多信息，请参阅 递归公共表表达式。
确定给定 CTE 的列名的过程如下：
-
如果 CTE 名称后跟随括号中的名称列表，那么这些名称就是列名：
WITH cte (col1, col2) AS ( SELECT 1, 2 UNION ALL SELECT 3, 4 ) SELECT col1, col2 FROM cte;名称列表中的名称数量必须与结果集中的列数量相同。
-
否则，列名来自
AS (部分中的第一个subquery)SELECT的选择列表：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语句的语句：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 ...要使语句合法，定义 CTEs 以唯一的名称：
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 的附加语法考虑，请参阅 递归公共表表达式。
一个递归公共表表达式是一个具有对自身名称的子查询的表达式。例如：
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 具有以下结构：
-
如果
WITH子句中的任何 CTE 引用自身，则必须以WITH RECURSIVE开头。（如果没有 CTE 引用自身，RECURSIVE是可选的。）如果您忘记了
RECURSIVE对于递归 CTE，这可能会导致以下错误：ERROR 1146 (42S02): Table 'cte_name' doesn't exist -
递归 CTE 子查询由两个部分组成，使用
UNION ALL或UNION [DISTINCT]分隔：SELECT ... -- return initial row set UNION ALL SELECT ... -- return additional row sets第一个
SELECT产生初始行或行集，并不引用 CTE 名称。第二个SELECT产生附加行，并递归地引用 CTE 名称。递归结束时，第二部分不再产生新行。因此，递归 CTE 由非递归SELECT部分和递归SELECT部分组成。 -
CTE 结果列的类型是从非递归
SELECT部分的列类型推断出来的，只有这些列都是可空的。对于类型确定，递归SELECT部分被忽略。 -
如果非递归和递归部分用
UNION DISTINCT分隔，重复行将被消除。这对于执行传递闭包的查询非常有用，以避免无限循环。 -
每次递归部分的迭代都只操作前一次迭代产生的行。如果递归部分有多个查询块，各个查询块的迭代将以未指定的顺序进行，每个查询块都操作前一次迭代或其他查询块自上一次迭代结束以来产生的行。
前面显示的递归 CTE 子查询具有以下非递归部分，以检索单行以生成初始行集：
SELECT 1该 CTE 子查询还具有以下递归部分：
SELECT n + 1 FROM cte WHERE n < 5在每次迭代中，该 SELECT 产生一个新值，高于前一行集中的 n 值。第一次迭代操作初始行集 (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 确定列宽度。因此，递归 SELECT 产生的更宽的 str 值被截断。
在严格 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
递归
SELECT部分也可以使用LIMIT子句，伴随可选的OFFSET子句。该结果集的效果与在最外层SELECT中使用LIMIT相同，但更高效，因为使用它可以在生成所需的行数后停止生成行。对
DISTINCT的禁令仅适用于UNION成员；UNION DISTINCT是允许的。 -
-
递归
SELECT部分必须仅引用 CTE 一次，并且仅在其FROM子句中，而不是在任何子查询中。如果在连接中使用它，例如与其他表连接 CTE，CTE 不能在LEFT JOIN的右侧。
这些约束来自 SQL 标准，除了前面提到的 MySQL 特定排除项。
对于递归CTE， EXPLAIN 输出行对于递归 SELECT 部分显示 Recursive 在 Extra 列中。
由 EXPLAIN 显示的成本估算表示每次迭代的成本，可能与总成本有很大差异。优化器无法预测迭代次数，因为它无法预测 WHERE 子句何时变为 false。
CTE 的实际成本也可能受到结果集大小的影响。生成许多行的 CTE 可能需要一个足够大的内部临时表，以便从内存转换为磁盘格式，并可能遭受性能损失。如果是这样，增加允许的内存临时表大小可能会改善性能；见 第 10.4.4 节，“MySQL 中的内部临时表使用”。
对于递归 CTE，递归 SELECT 部分包括终止递归的条件非常重要。作为开发技术，以防止递归 CTE 运行不受控制，可以强制终止执行时间：
-
系统变量
cte_max_recursion_depth强制 CTE 的递归级别限制。服务器将终止任何超过该变量值的 CTE 的执行。 -
系统变量
max_execution_time强制当前会话中的SELECT语句的执行超时。 -
优化器提示
MAX_EXECUTION_TIME强制每个查询的执行超时。
假设递归 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 值非常高的查询，可以使用 per-session 超时来防止深递归。为此，请在执行 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;您也可以在递归查询中使用 LIMIT 限制返回的最大行数，例如：
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;您可以这样做，以便在返回十万行或运行一秒（1000 毫秒）时终止，ichever occurs first：
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生成了一个10个数字的系列，使用0和1作为第一个两个数字：
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显示第n个斐波那契数字。 -
列
next_fib_n显示下一个斐波那契数字，位于数字n之后。该列提供了下一个系列值给下一行，以便下一行可以在其fib_n列中产生两个前一个系列值的和。 -
递归结束时
n达到10。这是一个任意的选择，以限制输出到一个小的行集。
前面的输出显示了整个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的工作原理：
使用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 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()子查询的那个？EXPLAIN显示，包含MAX()的子查询只执行一次，并将结果缓存。 -
使用
COALESCE()避免在sum_price列中显示NULL，在销售表中没有销售数据的日期。
分层数据遍历
递归公共表表达式对于遍历形成层次结构的数据非常有用。考虑以下语句，它们创建了一个小型数据集，显示公司中每个员工的员工姓名和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 的工作原理：
要找到特定员工或员工的路径，请添加 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 |
+------+-------+-----------------+公共表表达式（CTEs）在某些方面类似于派生表：
-
两者都是命名的。
-
两者都存在于单个语句的范围内。
由于这些相似之处，CTEs 和派生表通常可以互换使用。例如，这些语句是等效的：
WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;然而，CTEs 比派生表具有以下优势：
-
派生表只能在查询中引用一次。CTE 可以多次引用。要使用派生表结果的多个实例，必须多次派生结果。
-
CTE 可以自我引用（递归）。
-
一个 CTE 可以引用另一个。
-
CTE 可能更易于阅读，因为其定义出现在语句的开头，而不是嵌入其中。
CTEs 类似于使用 CREATE [TEMPORARY] TABLE 创建的表，但不需要明确定义或删除。对于 CTE，您不需要创建表的权限。