限制公共表表达式递归
对于递归 CTE 来说，递归 SELECT 部分包含终止递归的条件是很重要的。作为一种防止递归 CTE 失控的开发技术，可以通过限制执行时间来强制终止递归：
● cte_max_recursion_depth 系统变量对 CTE 的递归层次数强制限制。服务器终止任何递归层次超过此变量值的 CTE 的执行。
● max_execution_time 系统变量为当前会话中执行的 SELECT 语句设置强制执行超时时间。
● MAX_EXECUTION_TIME 优化器提示对出现在 SELECT 语句中的每个查询设置强制执行超时时间。
假设在没有递归执行终止条件的情况下，编写了递归 CTE：
1. WITH RECURSIVE cte (n) AS
2. (
3. SELECT 1
4. UNION ALL
5. SELECT n + 1 FROM cte
6. )
7. SELECT * FROM cte;
默认情况下，cte_max_recursion_depth 的值为 1000，当它递归超过1000次时，会导致 cte 终止。应用程序可以更改会话值以适应其要求：
1. SET SESSION cte_max_recursion_depth = 10; -- permit only shallow recursion
2. SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion
也可以设置全局 cte_max_recursion_depth 值来影响随后开始的所有会话。
对于执行并因此递归缓慢的查询，或者在有理由将 cte_max_recursion_depth 值设置得非常高的上下文中，防止深度递归的另一种方法是设置基于会话的超时。要实现此效果，请在执行 CTE 语句之前执行如下语句：
1. SET max_execution_time = 1000; -- impose one second timeout
或者，在 CTE 语句本身中包含一个优化器提示：
1. WITH RECURSIVE cte (n) AS
2. (
3. SELECT 1
4. UNION ALL
5. SELECT n + 1 FROM cte
6. )
7. SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;
8.
9. WITH RECURSIVE cte (n) AS
10. (
11. SELECT 1
12. UNION ALL
13. SELECT n + 1 FROM cte
14. )
15. SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;
从 MySQL 8.0.19 开始，还可以在递归查询中使用 LIMIT 来限制返回给最外层 SELECT 的最大行数，例如：
1. WITH RECURSIVE cte (n) AS
2. (
3. SELECT 1
4. UNION ALL
5. SELECT n + 1 FROM cte LIMIT 10000
6. )
7. SELECT * FROM cte;
可以在设置时间限制之外执行此操作，也可以不设置时间限制。以下 CTE 在返回一万行或运行一千秒后终止，以先发生的为准：
1. WITH RECURSIVE cte (n) AS
2. (
3. SELECT 1
4. UNION ALL
5. SELECT n + 1 FROM cte LIMIT 10000
6. )
7. SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;
如果没有执行时间限制的递归查询进入无限循环，则可以使用 KILL QUERY 命令从另一个会话终止它。在会话本身内，用于运行查询的客户端程序也可能提供终止查询的方法。例如，在 mysql 中，键入 Control+C 会中断当前语句。
递归公共表表达式示例
如前所述，递归公共表表达式（CTE）经常用于序列生成和遍历层次或树结构数据。本节将展示一些简单示例。
斐波纳契数列生成
斐波纳契数列从两个数字0和1（或1和1）开始，之后的每个数字都是前两个数字的和。如果递归 SELECT 生成的每一行都可以访问序列中的前两个数字，则递归公共表表达式可以生成一个 Fibonacci 数列。以下 CTE 使用0和1作为前两个数字生成10个数字系列：
1. WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
2. (
3. SELECT 1, 0, 1
4. UNION ALL
5. SELECT n + 1, next_fib_n, fib_n + next_fib_n
6. FROM fibonacci WHERE n < 10
7. )
8. SELECT * FROM fibonacci;
CTE 产生以下结果：
1. +------+-------+------------+
2. | n | fib_n | next_fib_n |
3. +------+-------+------------+
4. | 1 | 0 | 1 |
5. | 2 | 1 | 1 |
6. | 3 | 1 | 2 |
7. | 4 | 2 | 3 |
8. | 5 | 3 | 5 |
9. | 6 | 5 | 8 |
10. | 7 | 8 | 13 |
11. | 8 | 13 | 21 |
12. | 9 | 21 | 34 |
13. | 10 | 34 | 55 |
14. +------+-------+------------+
CTE 的工作原理：
● n 是一个显示列，指示该行包含第 n 个 Fibonacci 数。例如，第8个斐波纳契数是13。
● fib_n 列显示 Fibonacci 数 n。
● next_fib_n 列显示数字 n 后面的下一个 Fibonacci 数。此列给下一行提供下一个数列值，这样该行就可以在 fib_n 列中生成前两个数列值的和。
● 当 n 达到10时递归结束。这是一个任意的选择，将输出限制为一个小的行集合。
前面的输出显示了整个 CTE 结果。要只选择其中的一部分，请在顶层 SELECT 中添加适当的 WHERE 子句。例如，要选择第8个斐波纳契数，请执行以下操作：
1. mysql> WITH RECURSIVE fibonacci ...
2. ...
3. SELECT fib_n FROM fibonacci WHERE n = 8;
4. +-------+
5. | fib_n |
6. +-------+
7. | 13 |
8. +-------+
日期序列生成
公共表表达式可以生成一系列连续的日期，这对于生成摘要非常有用，这些摘要包含一列表示数列中的所有日期，包括摘要数据中未展示的日期。
假设销售数据表包含以下行：
1. mysql> SELECT * FROM sales ORDER BY date, price;
2. +------------+--------+
3. | date | price |
4. +------------+--------+
5. | 2017-01-03 | 100.00 |
6. | 2017-01-03 | 200.00 |
7. | 2017-01-06 | 50.00 |
8. | 2017-01-08 | 10.00 |
9. | 2017-01-08 | 20.00 |
10. | 2017-01-08 | 150.00 |
11.| 2017-01-10 | 5.00 |
12. +------------+--------+
此查询汇总每天的销售额：
1. mysql> SELECT date, SUM(price) AS sum_price
2. FROM sales
3. GROUP BY date
4. ORDER BY date;
5. +------------+-----------+
6. | date | sum_price |
7. +------------+-----------+
8. | 2017-01-03 | 300.00 |
9. | 2017-01-06 | 50.00 |
10. | 2017-01-08 | 180.00 |
11. | 2017-01-10 | 5.00 |
12. +------------+-----------+
但是，该结果缺失表日期范围中未表示的日期。可以使用递归 CTE 生成表示范围内所有日期的结果，生成的日期集与销售数据用 LEFT JOIN 连接。
下面是生成日期范围系列的 CTE：
1. WITH RECURSIVE dates (date) AS
2. (
3. SELECT MIN(date) FROM sales
4. UNION ALL
5. SELECT date + INTERVAL 1 DAY FROM dates
6. WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
7. )
8. SELECT * FROM dates;
CTE 产生以下结果：
1. +------------+
2. | date |
3. +------------+
4. | 2017-01-03 |
5. | 2017-01-04 |
6. | 2017-01-05 |
7. | 2017-01-06 |
8. | 2017-01-07 |
9. | 2017-01-08 |
10. | 2017-01-09 |
11. | 2017-01-10 |
12. +------------+
CTE的工作原理：
● 非递归 SELECT 生成销售表日期范围中的最小日期。
● 递归 SELECT 生成的每一行将在前一行生成的日期上增加一天。
● 递归在日期到达销售表日期范围中的最大日期之后结束。
用 LEFT JOIN 将 CTE 和销售表连接，生成销售摘要，其中包含范围内每个日期行:
1. WITH RECURSIVE dates (date) AS
2. (
3. SELECT MIN(date) FROM sales
4. UNION ALL
5. SELECT date + INTERVAL 1 DAY FROM dates
6. WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
7. )
8. SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
9. FROM dates LEFT JOIN sales ON dates.date = sales.date
10. GROUP BY dates.date
11. ORDER BY dates.date;
输出如下：
1. +------------+-----------+
2. | date | sum_price |
3. +------------+-----------+
4. | 2017-01-03 | 300.00 |
5. | 2017-01-04 | 0.00 |
6. | 2017-01-05 | 0.00 |
7. | 2017-01-06 | 50.00 |
8. | 2017-01-07 | 0.00 |
9. | 2017-01-08 | 180.00 |
10. | 2017-01-09 | 0.00 |
11. | 2017-01-10 | 5.00 |
12. +------------+-----------+
注意事项：
● 查询是否效率低下，尤其是递归 SELECT 中每行都执行 MAX() 子查询的查询？EXPLAIN 显示包含 MAX() 的子查询只计算一次，结果被缓存。
● 使用 COALESCE() 可以避免在销售表中没有销售数据的日子在 sum_price 列中显示 NULL。
分层数据遍历
递归公共表表达式对于遍历形成层次结构的数据非常有用。考虑这些语句，创建一个小数据集，该数据集显示公司中每个员工的员工姓名和 ID 号，以及员工经理的 ID。顶级员工（CEO）的经理 ID 为 NULL（无经理）。
1. CREATE TABLE employees (
2. id INT PRIMARY KEY NOT NULL,
3. name VARCHAR(100) NOT NULL,
4. manager_id INT NULL,
5. INDEX (manager_id),
6. FOREIGN KEY (manager_id) REFERENCES employees (id)
7. );
8. INSERT INTO employees VALUES
9. (333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL)
10. (198, "John", 333), # John has ID 198 and reports to 333 (Yasmina)
11. (692, "Tarek", 333),
12. (29, "Pedro", 198),
13. (4610, "Sarah", 29),
14. (72, "Pierre", 29),
15. (123, "Adil", 692);
结果数据集如下所示：
1. mysql> SELECT * FROM employees ORDER BY id;
2. +------+---------+------------+
3. | id | name | manager_id |
4. +------+---------+------------+
5. | 29 | Pedro | 198 |
6. | 72 | Pierre | 29 |
7. | 123 | Adil | 692 |
8. | 198 | John | 333 |
9. | 333 | Yasmina | NULL |
10. | 692 | Tarek | 333 |
11. | 4610 | Sarah | 29 |
12. +------+---------+------------+
要生成包含每个员工管理链的组织结构图（即从CEO到员工的路径），请使用递归 CTE：
1. WITH RECURSIVE employee_paths (id, name, path) AS
2. (
3. SELECT id, name, CAST(id AS CHAR(200))
4. FROM employees
5. WHERE manager_id IS NULL
6. UNION ALL
7. SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
8. FROM employee_paths AS ep JOIN employees AS e
9. ON ep.id = e.manager_id
10. )
11. SELECT * FROM employee_paths ORDER BY path;
CTE 产生以下输出：
1. +------+---------+-----------------+
2. | id | name | path |
3. +------+---------+-----------------+
4. | 333 | Yasmina | 333 |
5. | 198 | John | 333,198 |
6. | 29 | Pedro | 333,198,29 |
7. | 4610 | Sarah | 333,198,29,4610 |
8. | 72 | Pierre | 333,198,29,72 |
9. | 692 | Tarek | 333,692 |
10. | 123 | Adil | 333,692,123 |
11. +------+---------+-----------------+
CTE 的工作原理：
● 非递归 SELECT 为 CEO 生成行（经理 ID 为 NULL 的行）。
path 列被加宽为 CHAR(200)，以确保有空间容纳递归 SELECT 生成的较长路径值。
● 递归 SELECT 生成的每一行都会查找直接向前一行生成的员工报告的所有员工。对于每个这样的员工，该行包括员工 ID 和姓名，以及员工管理链。管理链是经理的管理链，末尾添加了员工 ID。
● 当员工没有其他人向他们报告时，递归结束。
若要查找特定员工的路径，请在顶层 SELECT 中添加 WHERE 子句。例如，要显示 Tarek 和 Sarah 的结果，请按如下方式修改 SELECT 语句：
1. mysql> WITH RECURSIVE ...
2. ...
3. SELECT * FROM employees_extended
4. WHERE id IN (692, 4610)
5. ORDER BY path;
6. +------+-------+-----------------+
7. | id | name | path |
8. +------+-------+-----------------+
9. | 4610 | Sarah | 333,198,29,4610 |
10. | 692 | Tarek | 333,692 |
11. +------+-------+-----------------+
公共表表达式与类似结构的比较
公共表表达式（CTE）在某些方面与派生表相似：
● 两个结构都需要命名。
● 这两个结构都存在于单个语句的范围内。
由于这些相似性，CTE 和派生表通常可以互换使用。下面的一个简单例子中，这些语句是等价的：
1. WITH cte AS (SELECT 1) SELECT * FROM cte;
2. SELECT * FROM (SELECT 1) AS dt;
但是，与派生表相比，CTE 有一些优势：
● 派生表只能在查询中被引用一次。一个 CTE 可以被多次引用。要使用派生表结果的多个实例，必须多次派生结果。
● CTE 可以是自引用（递归的）。
● 一个 CTE 可以引用另一个 CTE。
● 当 CTE 的定义出现在语句的开头而不是嵌入其中时，它可能更易于阅读。
CTE 类似于用 CREATE [TEMPORARY] TABLE 创建的表，但不需要显式定义或删除。对于 CTE，不需要创建表的权限。
官方网址： https://dev.mysql.com/doc/refman/8.0/en/with.html
