MySQL8.0之CTE(公用表表达式)

2022-05-05 03:42:57

在之前的文章中介绍了关于窗口函数的一些知识，在本文中来看一下在MySQL8.0中另一个重要的特性--CTE(公用表表达式)。咱们来看下什么是CTE（公共表表达式）？

一、CTE简介(公用表表达式)

1.1 什么是CTE(公用表表达式)

CTE(公用表表达式)是一个命名的临时结果集，仅在单个SQL语句的执行范围内存在。与派生表类似，CTE不作为对象存储，仅在查询执行期间持续。与派生表不同，CTE可以是自引用。此外，与派生表相比，CTE提供了更好的可读性和性能。CTE的结构包括:名称，可选列列表和定义CTE的查询。定义CTE后，可以像SELECT，INSERT，UPDATE，DELETE或视图一样使用。

1.2 CTE(公用表表达式)功能

CTE有两种用法，非递归的CTE和递归的CTE。非递归的CTE可以用来增加代码的可读性，增加逻辑的结构化表达。递归的CTE，应用的场景也比较多，比如查询某结构下的子结构，每个子结构下面的子结构等等，就需要使用递归的方式。递归的CTE当然递归不会无限下去，不同的数据库有不同的递归限制，MySQL8.0中默认限制的最大递归次数是1000。超过最大低估次数会报错：Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value，由参数cte_max_recursion_depth决定。

二、CTE(公用表表达式)语法及特点

2.1 CTE(公用表表达式)语法

在包含WITH子句的语句中，可以引用每个CTE名称以访问相应的CTE结果集。可以在其他CTE中引用CTE名称，从而可以基于其他CTE定义CTE。CTE可以引用自身来定义递归CTE，递归CTE的常见应用包括序列生成和遍历分层或树状数据。公用表表达式使用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部分称为“CTE的子查询”，是产生CTE结果集的部分。如果公用表表达式的子查询引用其自己的名称，则该表表达式是递归的，RECURSIVE关键字必须被包含。

要指定公用表表达式，需使用WITH具有一个或多个逗号分隔子句的子句。每个子句都提供一个子查询，该子查询产生一个结果集，并将一个名称与该子查询相关联。下面的示例定义名为cte1和cte2中WITH子句，并且是指在它们的顶层SELECT下面的WITH子句：

WITH
  cte1 AS (SELECT id, amount FROM t1),
  cte2 AS (SELECT id, amount FROM t2)
SELECT cte1.amount, cte2.amount FROM cte1 JOIN cte2
WHERE cte1.id = cte2.id;
#列表中的名称数必须与结果集中的列数相同

2.2 CTE(公用表表达式)特点

WITH在以下情况下允许使用子句：

在开始时SELECT，UPDATE和DELETE语句。

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

在子查询（包括派生表子查询）的开头：

SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...

SELECT 对于包含以下SELECT语句的语句，紧接在前面：

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;

为了使语句合法，需使用唯一的名称定义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表和视图。通过在同一查询块中搜索对象来进行名称解析，然后在未找到具有该名称的对象的情况下依次进入外部块。

2.3 公用表表达式与类似构造的比较

公用表表达式（CTE）在某些方面类似于派生表：

两种结构都被命名。
两种结构都存在于单个语句的范围内。

由于这些相似之处，CTE和派生表通常可以互换使用。作一个简单的例子，这些语句是等效的：

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

但是，CTE与派生表相比具有一些优势：

在查询中只能一次引用派生表。可以多次引用CTE。要使用派生表结果的多个实例，您必须多次派生结果。
CTE可以是自引用的（递归的）。
一个CTE可以引用另一个。
当CTE的定义出现在语句的开始而不是嵌入在语句的开头时，它可能更易于阅读。

CTE与使用创建的表相似，CREATE [TEMPORARY] TABLE但无需显式定义或删除。对于CTE，不需要创建表的权限。

三、递归查询

3.1 递归查询介绍

递归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部分组成。
每个SELECT部分本身可以是多个SELECT 语句的并集。
CTE结果列的类型SELECT只能从非递归部分的列类型中推断出来，并且这些列都是可空的。对于类型确定，将SELECT忽略递归部分。
如果非递归和递归部分之间用分隔UNION DISTINCT，则将消除重复的行。递归部分的每次迭代仅对前一次迭代产生的行进行操作。如果递归部分具有多个查询块，则每个查询块的迭代将以未指定的顺序进行调度，并且每个查询块将对从上一次迭代结束后由其上一次迭代或其他查询块生成的行进行操作。

3.2 递归查询示例

MySQL [test]> WITH RECURSIVE cte (n) AS (SELECT 1   UNION ALL   SELECT n + 1 FROM cte WHERE n < 10 ) SELECT * FROM cte;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)

MySQL [test]> WITH RECURSIVE cte (n) AS (   SELECT 1   UNION ALL   SELECT n + 1 FROM cte WHERE n < 100000 ) SELECT * FROM cte;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

3.2 递归查询特点

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

聚合函数，如SUM()
窗口函数
GROUP BY
ORDER BY
LIMIT
DISTINCT
此约束不适用于SELECT递归CTE的非递归部分。递归SELECT部分必须仅在其FROM子句中引用一次CTE，而不能在任何子查询中引用。它可以引用CTE以外的表，并将它们与CTE联接在一起。如果在这样的联接中使用，则CTE不得位于的右侧LEFT JOIN。这些约束来自于SQL标准，比其他的MySQL特定的排除ORDER BY，LIMIT和DISTINCT。

递归的CTE，EXPLAIN递归输出行SELECT部分显示Recursive在Extra列中。
EXPLAIN代表每次迭代的成本，可能与总成本有很大不同。优化器无法预测迭代次数。
CTE实际成本也可能会受到结果集大小的影响。产生许多行的CTE可能需要一个内部临时表，该表必须足够大才能从内存格式转换为磁盘格式，并且可能会降低性能。如果是这样，则增加允许的内存中临时表大小可能会提高性能。
对于递归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 /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

如果没有执行时间限制的递归查询进入无限循环，则可以使用终止另一个会话的查询KILL QUERY。

四、总结

窗口函数和CTE(公用表表达式)的增加，简化了SQL代码的编写和逻辑的实现，新特性的增加，可以用更优雅和可读性的方式来写SQL。不过这都是在MySQL8.0中实现的新功能，在MySQL8.0之前，只能按照较为复杂的方式实现。

码农公寓