MySQL 8.4 发行说明
公用表表达式 (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 子句中的任何 CTE 都是递归的，则必须包含 RECURSIVE 关键字。有关更多信息，请参阅 递归公用表表达式。
对于给定 CTE 的列名称的确定，过程如下：
如果 CTE 名称后面紧跟括号括起来的名称列表，则这些名称即为列名称。
WITH cte (col1, col2) AS ( SELECT 1, 2 UNION ALL SELECT 3, 4 ) SELECT col1, col2 FROM cte;列表中的名称数量必须与结果集中的列数相同。
否则，列名称来自
AS (部分中第一个subquery)SELECT的 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 ...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 定义 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 的其他语法注意事项，请参阅 递归公用表表达式。
递归公用表表达式是指其子查询引用其自身名称的公用表表达式。例如：
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子句必须以WITH RECURSIVE开头。（如果没有任何 CTE 引用自身，则允许使用RECURSIVE，但不是必需的。）如果您忘记了递归 CTE 的
RECURSIVE，则可能会导致以下错误：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 的初始行或行，并且不引用 CTE 名称。第二个SELECT用于生成其他行，并通过在FROM子句中引用 CTE 名称来进行递归。当此部分不再生成新行时，递归结束。因此，递归 CTE 由一个非递归SELECT部分和一个递归SELECT部分组成。CTE 结果列的类型仅从非递归
SELECT部分的列类型推断出来，并且所有列都是可为空的。为了确定类型，会忽略递归SELECT部分。如果非递归部分和递归部分由
UNION DISTINCT分隔，则会消除重复行。这对于执行传递闭包的查询很有用，可以避免无限循环。递归部分的每次迭代仅对前一次迭代生成的行列进行操作。如果递归部分具有多个查询块，则每个查询块的迭代按未指定的顺序安排，并且每个查询块都会对自上一次迭代结束以来生成的行的操作，这些行可能是由其自身的先前迭代或其他查询块生成的。
前面显示的递归 CTE 子查询具有以下非递归部分，该部分检索单个行以生成初始行集：
SELECT 1CTE 子查询还具有以下递归部分：
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 |
+------+------+由于非递归 SELECT 确定了列宽，因此所有 str 列值都为 'abc'。因此，递归 SELECT 生成的更宽的 str 值会被截断。
在严格 SQL 模式下，该语句会生成错误：
ERROR 1406 (22001): Data too long for column 'str' at row 1要解决此问题，以便语句不会生成截断或错误，请在非递归 SELECT 中使用 CAST() 来加宽 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。
递归 CTE 的递归
SELECT部分还可以使用LIMIT子句，以及可选的OFFSET子句。对结果集的影响与在最外层的SELECT中使用LIMIT相同，但效率更高，因为在递归SELECT中使用它会停止生成行，只要生成所需数量的行即可。关于
DISTINCT的禁止仅适用于UNION成员；UNION DISTINCT是允许的。递归
SELECT部分只能在FROM子句中引用 CTE 一次，不能在任何子查询中引用 CTE。它可以引用除 CTE 之外的表，并将它们与 CTE 连接。如果在类似的连接中使用，则 CTE 不能位于LEFT JOIN的右侧。
这些约束来自 SQL 标准，除了之前提到的 MySQL 特定的排除项之外。
对于递归 CTE，EXPLAIN 输出行的递归 SELECT 部分在 Extra 列中显示 Recursive。
EXPLAIN 显示的成本估算代表每次迭代的成本，这可能与总成本有很大差异。优化器无法预测迭代次数，因为它无法预测 WHERE 子句何时变为假。
CTE 的实际成本也可能受到结果集大小的影响。生成大量行的 CTE 可能需要一个内部临时表，该表足够大，需要从内存转换为磁盘格式，并且可能会降低性能。如果是这种情况，增加允许的内存中临时表大小可能会提高性能；请参阅 第 10.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;您也可以在递归查询中使用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列显示第n个斐波那契数。next_fib_n列显示第n个斐波那契数之后的下一个斐波那契数。此列为下一行提供下一个序列值，以便该行可以在其fib_n列中生成前两个序列值的总和。当
n达到 10 时，递归结束。这是一个任意选择，用于将输出限制为一小部分行。
前面的输出显示了整个 CTE 结果。要选择其中的部分内容，请在顶层SELECT 中添加一个合适的WHERE 子句。例如，要选择第 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 的工作原理
将 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()可以避免在sales表中没有销售数据的日期上显示NULL值，从而避免在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 的工作原理
要查找特定员工或多个员工的路径，请在顶层SELECT 中添加一个WHERE 子句。例如，要显示 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，您无需创建表的权限。