To specify common table expressions, use a WITH clause that has one or more comma-separated subclauses. Each subclause provides a subquery that produces a result set, and associates a name with the subquery. The following example defines CTEs named cte1以及cte2 in the WITH clause, and refers to them in the top-level SELECT that follows the WITH clause:To specify common table expressions, use a WITH clause that has one or more comma-separated subclauses. Each subclause provides a subquery that produces a result set, and associates a name with the subquery. The following example defines CTEs named cte1 and cte2 in the WITH clause, and refers to them in the top-level SELECT that follows the WITH clause:

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 结果集。In the statement containing the WITH clause, each CTE name can be referenced to access the corresponding CTE result set.

一个 CTE 名称可以在其他 CTE 中引用，从而允许 CTE 基于其他 CTE 定义。A CTE name can be referenced in other CTEs, enabling CTEs to be defined based on other CTEs.

一个 CTE 可以引用自身以定义递归 CTE。递归 CTE 的常见应用包括序列生成和遍历分层或树状数据。A CTE can refer to itself to define a recursive CTE. Common applications of recursive CTEs include series generation and traversal of hierarchical or tree-structured data.

公用表表达式是 DML 语句语法的可选部分。它们使用WITH 子句定义：Common table expressions are an optional part of the syntax for DML statements. They are defined using a WITH clause:

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

cte_name 为单个公用表表达式命名，并可在包含WITH 子句的语句中作为表引用使用。cte_name names a single common table expression and can be used as a table reference in the statement containing the WITH clause.

subquery 部分在 AS (subquery) 中称为“CTE 的子查询”，它生成 CTE 结果集。AS 后的圆括号是必需的。The subquery part of AS (subquery) is called the “subquery of the CTE” and is what produces the CTE result set. The parentheses following AS are required.

如果 CTE 的子查询引用其自身名称，则该公用表表达式为递归 CTE。如果WITH 子句中的任何 CTE 是递归的，必须包含 RECURSIVE 关键字。更多信息，请参见 递归公用表表达式。A common table expression is recursive if its subquery refers to its own name. The RECURSIVE keyword must be included if any CTE in the WITH clause is recursive. For more information, see Recursive Common Table Expressions.

确定给定 CTE 的列名的方式如下：Determination of column names for a given CTE occurs as follows:

在以下上下文中允许使用WITH 子句：A WITH clause is permitted in these contexts:

同一级别仅允许一个 WITH 子句。WITH接 WITH 处于同一级别是不允许的，因此这是非法的：Only one WITH clause is permitted at the same level. WITH followed by WITH at the same level is not permitted, so this is illegal:

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

要使该语句合法化，只需使用一个 WITH用逗号分隔子句的子句：To make the statement legal, use a single WITH clause that separates the subclauses by a comma:

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

然而，一个语句可以包含多个 WITH 如果它们出现在不同层级However, a statement can contain multiple WITH clauses if they occur at different levels:

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

：A WITH clause 可以定义一个或多个常见表表达式，但每个 CTE 名称必须在该子句中独一无二。这是非法的：A WITH clause can define one or more common table expressions, but each CTE name must be unique to the clause. This is illegal:

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

为了使该声明合法，定义具有唯一名称的 CTE：To make the statement legal, define the CTEs with unique names:

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

CTE 可以指代自己或其他 CTE：A CTE can refer to itself or to other CTEs:

对于解析同名对象的引用，派生表隐藏 CTE;而 CTE 隐藏基表、TEMPORARY 表和视图。名称解析通过搜索同一查询块中的对象，然后在未找到该名称对象时依次访问外部块实现。For resolving references to objects with the same names, derived tables hide CTEs; and CTEs hide base tables, TEMPORARY tables, and views. Name resolution occurs by searching for objects in the same query block, then proceeding to outer blocks in turn while no object with the name is found.

关于递归 CTE 特有的更多语法考虑，请参见 递归公共表表达式。For additional syntax considerations specific to recursive CTEs, see Recursive Common Table Expressions.

具有指向自身名称的子查询。例如：A recursive common table expression is one having a subquery that refers to its own name. For example:

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

执行时，该语句产生一个包含简单线性序列的单列结果：When executed, the statement produces this result, a single column containing a simple linear sequence:

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

递归 CTE 具有以下结构：A recursive CTE has this structure:

递归 CTE 子查询中，已有此非递归部分，检索单行生成初始行集：The recursive CTE subquery shown earlier has this nonrecursive part that retrieves a single row to produce the initial row set:

SELECT 1

CTE 子查询也有此递归部分：The CTE subquery also has this recursive part:

SELECT n + 1 FROM cte WHERE n < 5

每次迭代中，SELECT生成一行，其值比前一行集的n大一倍。第一次迭代对初始行集（1）操作，生成 1+1=2;第二次迭代对第一个迭代的行集（2）操作，产生 2+1=3;依此类推。此过程持续至递归结束，当n 不再小于 5。At each iteration, that SELECT produces a row with a new value one greater than the value of n from the previous row set. The first iteration operates on the initial row set (1) and produces 1+1=2; the second iteration operates on the first iteration's row set (2) and produces 2+1=3; and so forth. This continues until recursion ends, which occurs when n is no longer less than 5.

如果 CTE 的递归部分为某列生成的值比非递归部分更宽，则可能有必要在非递归部分扩展该列，以避免数据截断。考虑以下语句：If the recursive part of a CTE produces wider values for a column than the nonrecursive part, it may be necessary to widen the column in the nonrecursive part to avoid data truncation. Consider this statement:

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 模式下，该语句产生如下输出：In nonstrict SQL mode, the statement produces this output:

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

str 列的值全是 'abc'，因为非递归 SELECT 决定了列的宽度。因此，递归 SELECT 生成的更宽 str 值会被截断。The str column values are all 'abc' because the nonrecursive SELECT determines the column widths. Consequently, the wider str values produced by the recursive SELECT are truncated.

在严格 SQL 模式下，该语句会产生错误：In strict SQL mode, the statement produces an error:

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

为了解决此问题，使语句不产生截断或错误，可在非递归 SELECT 中使用 CAST() 来扩展 str 列：To address this issue, so that the statement does not produce truncation or errors, use CAST() in the nonrecursive SELECT to make the str column wider:

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;

现在语句产生如下结果，不会截断：Now the statement produces this result, without truncation:

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

列是按名称访问的，而不是按位置访问，这意味着递归部分的列可以访问非递归部分中位置不同的列，如此 CTE 所示：Columns are accessed by name, not position, which means that columns in the recursive part can access columns in the nonrecursive part that have a different position, as this CTE illustrates:

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，反之亦然，输出的每一行中正值和负值的位置会互换：Because p in one row is derived from q in the previous row, and vice versa, the positive and negative values swap positions in each successive row of the output:

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

递归 CTE 子查询中适用某些语法约束：Some syntax constraints apply within recursive CTE subqueries:

这些约束源自 SQL 标准，除了前面提到的 MySQL 特有排除项外。These constraints come from the SQL standard, other than the MySQL-specific exclusions mentioned previously.

对于递归 CTE，EXPLAIN 递归输出行 SELECT parts 显示 Recursive，ExtraFor recursive CTEs, EXPLAIN output rows for recursive SELECT parts display Recursive in the Extra column.

成本估算由 EXPLAIN 表示每次迭代成本，可能与总成本有很大差异。优化器无法预测次数，因为无法预测WHERE变假。Cost estimates displayed by EXPLAIN represent cost per iteration, which might differ considerably from total cost. The optimizer cannot predict the number of iterations because it cannot predict at what point the WHERE clause becomes false.

CTE 的实际成本也可能受到结果集大小的影响。产生多行的 CTE 可能需要一个足够大的内部临时表，以便从内存转换为磁盘格式，可能会带来性能损失。如果是这样，增加允许的内存中临时表大小可能会提升性能;参见第 10.4.4 节，“MySQL 中的内部临时表使用”。CTE actual cost may also be affected by result set size. A CTE that produces many rows may require an internal temporary table large enough to be converted from in-memory to on-disk format and may suffer a performance penalty. If so, increasing the permitted in-memory temporary table size may improve performance; see Section 10.4.4, “Internal Temporary Table Use in MySQL”.

对于递归 CTE 来说非常重要，递归SELECT 部分包含终止递归的条件。作为防止递归失控 CTE 的开发技术，可以通过限制执行时间It is important for recursive CTEs that the recursive SELECT part include a condition to terminate recursion. As a development technique to guard against a runaway recursive CTE, you can force termination by placing a limit on execution time:

假设递归 CTE 错误地编写时没有递归执行终止条件：Suppose that a recursive CTE is mistakenly written with no recursion execution termination condition:

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

默认情况下，cte_max_recursion_depth 值为 1000，导致递归超过 1000 级时 CTE 终止。应用程序可以调整会话值以适应自身需求：By default, cte_max_recursion_depth has a value of 1000, causing the CTE to terminate when it recurses past 1000 levels. Applications can change the session value to adjust for their requirements:

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值，影响后续所有会话。You can also set the global cte_max_recursion_depth value to affect all sessions that begin subsequently.

对于执行且递归缓慢的查询，或在需要设置cte_max_recursion_depth值非常高的查询中，另一种防止深度递归的方法是设置每个会话的超时。为此，在执行 CTE 语句之前先执行类似的语句：For queries that execute and thus recurse slowly or in contexts for which there is reason to set the cte_max_recursion_depth value very high, another way to guard against deep recursion is to set a per-session timeout. To do so, execute a statement like this prior to executing the CTE statement:

SET max_execution_time = 1000; -- impose one second timeout

或者，在 CTE 语句中包含优化提示：Alternatively, include an optimizer hint within the CTE statement itself:

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，例如：You can also use LIMIT within the recursive query to impose a maximum number of rows to be returned to the outermost SELECT, for example:

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

你可以在设置时间限制的同时或替代此操作。因此，以下 CTE 在返回一万行或运行一秒（1000 毫秒）后终止，以先发生者为准：You can do this in addition to or instead of setting a time limit. Thus, the following CTE terminates after returning ten thousand rows or running for one second (1000 milliseconds), whichever 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 会中断当前语句。If a recursive query without an execution time limit enters an infinite loop, you can terminate it from another session using KILL QUERY. Within the session itself, the client program used to run the query might provide a way to kill the query. For example, in mysql, typing Control+C interrupts the current statement.

前所述，递归公共表表达式（CTE）常用于序列生成和遍历层级或树状结构数据。本节展示了这些技术的一些简单示例。As mentioned previously, recursive common table expressions (CTEs) are frequently used for series generation and traversing hierarchical or tree-structured data. This section shows some simple examples of these techniques.

斐波那契级数生成Fibonacci Series Generation

斐波那契数列以两个数字 0 和 1（或 1 和 1）开始，之后的每个数字都是前两个数字的和。如果递归的公共表表达式中的每一行都能访问数列中的前两个数字，就可以生成斐波那契数列。SELECT下面的 CTE 使用 0 和 1 作为前两个数字生成一个 10 个数字的数列：A Fibonacci series begins with the two numbers 0 and 1 (or 1 and 1) and each number after that is the sum of the previous two numbers. A recursive common table expression can generate a Fibonacci series if each row produced by the recursive SELECT has access to the two previous numbers from the series. The following CTE generates a 10-number series using 0 and 1 as the first two numbers:

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 产生如下结果：The CTE produces this result:

+------+-------+------------+
| 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 的工作原理：How the CTE works:

之前的输出显示了整个 CTE 结果。要选择其中一部分，可以在顶层添加相应的WHERE子句SELECT例如，要选择第八个斐波那契数，请这样做：The preceding output shows the entire CTE result. To select just part of it, add an appropriate WHERE clause to the top-level SELECT. For example, to select the 8th Fibonacci number, do this:

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

日期系列生成Date Series Generation

一个共同的表表达式可以生成一系列连续日期，这对于生成包含序列中所有日期行的摘要非常有用，包括汇总数据中未表示的日期。A common table expression can generate a series of successive dates, which is useful for generating summaries that include a row for all dates in the series, including dates not represented in the summarized data.

假设一个销售数字表包含这些行：Suppose that a table of sales numbers contains these rows:

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 |
+------------+--------+

此查询总结了每日销售额：This query summarizes the sales per day:

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连接。However, that result contains “holes” for dates not represented in the range of dates spanned by the table. A result that represents all dates in the range can be produced using a recursive CTE to generate that set of dates, joined with a LEFT JOIN to the sales data.

以下是生成日期范围系列的 CTE：Here is the CTE to generate the date range series:

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 产生如下结果：The CTE produces this result:

+------------+
| 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 的工作原理：How the CTE works:

用LEFT JOINsales，生成带有每个日期行的销售摘要：Joining the CTE with a LEFT JOIN against the sales table produces the sales summary with a row for each date in the range:

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;

输出如下：The output looks like this:

+------------+-----------+
| 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 |
+------------+-----------+

点：Some points to note:

层级数据遍历递Hierarchical Data Traversal

归常用表表达式适用于遍历形成层级的数据。考虑这些语句，创建了一个小型数据集，显示公司中每位员工的员工姓名、ID 编号以及该员工经理的 ID。顶层员工（CEO）的经理 ID 为NULL（无经理）。Recursive common table expressions are useful for traversing data that forms a hierarchy. Consider these statements that create a small data set that shows, for each employee in a company, the employee name and ID number, and the ID of the employee's manager. The top-level employee (the CEO), has a manager ID of NULL (no manager).

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

最终数据集如下：The resulting data set looks like this:

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：To produce the organizational chart with the management chain for each employee (that is, the path from CEO to employee), use a recursive 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 生成如下输出：The CTE produces this output:

+------+---------+-----------------+
| 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 的工作原理：How the CTE works:

要查找特定员工的路径，请在顶层 SELECT 中添加 WHERE 子句。例如，要显示 Tarek 和 Sarah 的结果，请将该 SELECT 修改如下：To find the path for a specific employee or employees, add a WHERE clause to the top-level SELECT. For example, to display the results for Tarek and Sarah, modify that SELECT like this:

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）在某些方面类似于派生表：Common table expressions (CTEs) are similar to derived tables in some ways:

由于这些相似之处，CTE 和派生表通常可以互换使用。举一个简单例子，这些语句是等价的：Because of these similarities, CTEs and derived tables often can be used interchangeably. As a trivial example, these statements are equivalent:

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

然而，CTE 相对于派生表也有一些优势：However, CTEs have some advantages over derived tables:

CTE 类似于使用 CREATE [TEMPORARY] TABLE 创建的表，但无需显式定义或删除。对于 CTE，不需要创建表的权限。CTEs are similar to tables created with CREATE [TEMPORARY] TABLE but need not be defined or dropped explicitly. For a CTE, you need no privileges to create tables.