MySQL的SQL语句 - 数据操作语句（17）- WITH 语句（2）

2023-08-06 23:21:16

递归公共表表达式

递归公共表表达式是具有引用其自身名称的子查询的表达式。例如：


1. WITH RECURSIVE cte (n) AS
2. (
3.   SELECT 1
4.   UNION ALL
5.   SELECT n + 1 FROM cte WHERE n < 5
6. )
7. SELECT * FROM cte;

执行时，语句将生成以下结果，即一个包含简单线性序列的列：


1. +------+
2. | n    |
3. +------+
4. |    1 |
5. |    2 |
6. |    3 |
7. |    4 |
8. |    5 |
9. +------+

递归 CTE 具有以下结构：

● 如果 WITH 子句中的任何 CTE 引用自身，则 WITH 子句必须以 WITH RECURSIVE 开头。（如果没有 CTE 引用自身，也允许使用 RECURSIVE，但不是必须的）

如果忘记给递归 CTE 使用 RECURSIVE 关键字，则可能会出现以下错误：

1. ERROR 1146 (42S02): Table 'cte_name' doesn't exist

● 递归 CTE 子查询有两部分，由 UNION [ALL] 或 UNION DISTINCT 分隔：

1. SELECT ...      -- return initial row set
2. UNION ALL
3. SELECT ...      -- return additional row sets

第一个 SELECT 生成 CTE 的初始行，但不引用 CTE 名称。第二个 SELECT 生成额外的行，并通过引用 FROM 子句中的 CTE 名称来递归调用。当此部分不生成新的行时递归结束。因此，递归 CTE 由一个非递归的 SELECT 部分和一个递归的 SELECT 部分组成。

每个 SELECT 部分本身可以是多个 SELECT 语句的联合。

● CTE 结果列的类型仅从非递归 SELECT 部分的列类型中推断出来，并且这些列都可以为空。对于如何确定类型，会忽略递归 SELECT 部分的语句。

● 如果非递归部分和递归部分由 UNION DISTINCT 分隔，则消除重复行。这对于执行传递闭包的查询非常有用，可以避免无限循环。

● 递归部分的每次迭代只对上一次迭代产生的行进行操作。如果递归部分有多个查询块，则每个查询块的迭代将按未指定的顺序进行调度，并且每个查询块将对其上一次迭代或自上次迭代结束后由其他查询块生成的行进行操作。

前面显示的递归 CTE 子查询具有以下非递归部分，它检索一行以生成初始行集：

1.SELECT 1

CTE 子查询还有以下递归部分：


1.SELECT n + 1 FROM cte WHERE n < 5

每次迭代时，该 SELECT 将生成一行，它的新值大于之前行集中 n 的值。第一次迭代对初始行集 (1) 进行操作，生成 1+1=2；第二次迭代对第一次迭代的行集 (2) 进行操作，生成 2+1=3；依此类推。以上过程将持续进行，直到 n 不再小于5时，递归结束。

如果 CTE 的递归部分生成的列值比非递归部分的值更宽，则可能需要加宽非递归部分中的列以避免数据截断。考虑以下语句：

1. WITH RECURSIVE cte AS
2. (
3.   SELECT 1 AS n, 'abc' AS str
4.   UNION ALL
5.   SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
6. )
7. SELECT * FROM cte;

在非严格 SQL 模式下，语句生成以下输出：

1. +------+------+
2. | n    | str  |
3. +------+------+
4. |    1 | abc  |
5. |    2 | abc  |
6. |    3 | abc  |
7. +------+------+

str 列值都是 'abc'，因为非递归 SELECT 决定了列宽。因此，递归 SELECT 产生的更宽的 str 值被截断。

在严格 SQL 模式下，该语句将引发错误：


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

要解决此问题，以便语句不产生截断或错误，请在非递归 SELECT 中使用 CAST() 使 str 列变宽：

1. WITH RECURSIVE cte AS
2. (
3.   SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
4.   UNION ALL
5.   SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
6. )
7. SELECT * FROM cte;

现在，语句将生成以下结果，而不进行截断：

1. +------+--------------+
2. | n    | str          |
3. +------+--------------+
4. |    1 | abc          |
5. |    2 | abcabc       |
6. |    3 | abcabcabcabc |
7. +------+--------------+

通过名称而不是位置访问列，这意味着递归部分中的列可以访问非递归部分中不同位置的列，如本 CTE 所示：

1. WITH RECURSIVE cte AS
2. (
3.   SELECT 1 AS n, 1 AS p, -1 AS q
4.   UNION ALL
5.   SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
6. )
7. SELECT * FROM cte;

因为一行中的 p 是从前一行的 q 派生出来的，反之亦然，在连续输出的每一行中正负值交换位置：

1. +------+------+------+
2. | n    | p    | q    |
3. +------+------+------+
4. |    1 |    1 |   -1 |
5. |    2 |   -2 |    2 |
6. |    3 |    4 |   -4 |
7. |    4 |   -8 |    8 |
8. |    5 |   16 |  -16 |
9. +------+------+------+

某些语法限制在递归 CTE 子查询中使用：

● 递归 SELECT 部分不能包含以下结构：

■ 聚合函数，如 SUM()

■ 窗口函数

■ GROUP BY

■ ORDER BY

■ DISTINCT

在 MySQL 8.0.19 之前，递归 CTE 的递归 SELECT 部分也不能使用 LIMIT 子句。这个限制在 MySQL 8.0.19 中被取消了，现在在这种情况下支持 LIMIT，同时还支持可选的 OFFSET 子句。对结果集的影响与在最外层的 SELECT 中使用 LIMIT 时的效果相同，但效率也更高，因为在递归 SELECT 中使用它时，一旦生成所请求的行数，就会停止继续生成这些行。

这些约束不适用于递归 CTE 的非递归 SELECT 部分。对 DISTINCT 的禁止只适用于 UNION 成员；但是允许 UNION DISTINCT。

● 递归 SELECT 部分只能引用 CTE 一次，并且只能在其 FROM 子句中引用，而不能在任何子查询中引用。它可以引用 CTE 以外的表，并将它们与 CTE 连接起来。在这种情况下，CTE 一定不能在 LEFT JOIN 的右边。

这些约束来自 SQL 标准，而不是特定于 MySQL 的对 ORDER BY、LIMIT（MySQL 8.0.18及更早版本）和 DISTINCT 的排除。

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

EXPLAIN 显示的成本估算代表每次迭代的成本，这可能与总成本相差很大。优化器无法预测迭代次数，因为它无法预测 WHERE 子句条件何时不满足。

CTE 的实际成本也可能受到结果集大小的影响。产生许多行的 CTE 可能需要一个足够大的内部临时表来从内存转换为磁盘格式，并且可能会导致性能损失。如果是这样，增加允许的内存临时表大小可以提高性能。

官方网址：
https://dev.mysql.com/doc/refman/8.0/en/with.html

码农公寓

相关文章