要指定公用表表达式，请使用一个带有一个或多个以逗号分隔的子句的 WITH 子句。每个子句提供一个生成结果集的子查询，并将一个名称与子查询关联起来。以下示例在 WITH 子句中定义了名为 cte1 和 cte2 的 CTE，并在后面的顶层 SELECT 中引用它们，该 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 部分是 AS (subquery)，被称为 “CTE 的子查询”，它生成 CTE 结果集。 AS 后面的括号是必需的。

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

确定给定 CTE 的列名按以下步骤进行。

在以下上下文中允许使用 WITH 子句。

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

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

要使语句合法，请使用单个 WITH 子句，用逗号分隔子句。

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

但是，如果语句在不同级别出现多个 WITH 子句，则该语句可以包含多个 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 隐藏基本表、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 具有以下结构。

前面显示的递归 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 决定了列宽度。因此，递归 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 子查询中应用某些语法约束。

这些约束来自 SQL 标准，除了前面提到的 MySQL 特定的排除之外。

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

由 EXPLAIN 显示的成本估算代表每次迭代的成本，这可能与总成本大不相同。优化器无法预测迭代次数，因为它无法预测 WHERE 子句何时变为假。

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

对于递归 CTE 而言，递归的 SELECT 部分必须包含一个终止递归的条件。作为一种防止递归 CTE 失控的开发技巧，您可以通过限制执行时间来强制终止。

假设递归 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 的工作原理

前面的输出显示了整个 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 |
+------------+-----------+

需要注意的一些要点。

分层数据遍历

递归公共表表达式对于遍历构成层次结构的数据非常有用。考虑以下语句，这些语句创建了一个小型数据集，该数据集显示了公司中每位员工的员工姓名和 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 类似于使用 CREATE [TEMPORARY] TABLE 创建的表，但不需要显式定义或删除。对于 CTE，您不需要创建表的权限。