WITH(公用表表达式)
公用表表达式(CTE)是一个命名的临时结果集,它存在于单个语句的范围内,以后可以在该语句中引用,可能多次。以下讨论描述了如何编写使用CTE的语句。
- 常用表表达式
- 递归公用表表达式
- 限制公用表表达式递归
- 递归公用表表达式示例
- 公用表表达式与类似构造的比较
有关CTE优化的信息,请参见“通过合并或实现来优化派生表,视图引用和公用表表达式”。
其他资源
这些文章包含有关在MySQL中使用CTE的其他信息,包括许多示例:
- MySQL 8.0实验:[递归] MySQL(CTE)中的通用表表达式
- MySQL 8.0实验:[递归] MySQL公用表表达式(CTE),第二部分-如何生成序列
- MySQL 8.0实验:[递归] MySQL(CTE)中的通用表表达式,第三部分–层次结构
- MySQL 8.0.1:MySQL(CTE)中的[递归]公用表表达式,第四部分–深度优先或广度优先遍历,传递闭包,循环避免
常用表表达式
要指定公用表表达式,请使用WITH
具有一个或多个逗号分隔子句的子句。每个子节都提供一个子查询,该子查询产生一个结果集,并将一个名称与该子查询相关联。下面的示例定义名为的CTE cte1
和cte2
中WITH
子句,并且是指在它们的顶层SELECT
下面的WITH
子句:
WITH cte1AS (SELECT a, bFROM table1), cte2AS (SELECT c, dFROM table2)SELECT b, dFROM cte1JOIN cte2WHERE 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
部分称为“ CTE的子查询”,是产生CTE结果集的部分。需要以下括号。AS(subquery)
AS
如果公共表表达式的子查询引用了自己的名称,则该表表达式是递归的。该RECURSIVE
关键字必须被包含,如果在任何CTE WITH
条款是递归的。有关更多信息,请参见递归公用表表达式。
确定给定CTE的列名的过程如下:
如果带括号的名称列表位于CTE名称之后,则这些名称为列名称:
WITH cte (col1, col2)AS (SELECT 1, 2UNION ALL SELECT 3, 4 )SELECT col1, col2FROM cte;列表中的名称数必须与结果集中的列数相同。
否则,列名来自首的选择列表中
SELECT
的内部分:AS(subquery)
WITH cteAS (SELECT 1AS col1, 2AS col2UNION ALL SELECT 3, 4 )SELECT col1, col2FROM cte;
WITH
在以下情况下允许使用子句:
在开始时
SELECT
,UPDATE
和DELETE
语句。WITH ...SELECT ...WITH ...UPDATE ...WITH ...DELETE ...在子查询(包括派生表子查询)的开头:
SELECT ...WHERE idIN (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 cte1AS (...)WITH cte2AS (...)SELECT ...
为了使该语句合法,请使用单个WITH
子句以逗号分隔各子句:
WITH cte1AS (...), cte2AS (...)SELECT ...
但是,如果一条语句WITH
出现在不同的级别,则它可以包含多个子句:
WITH cte1AS (SELECT 1)SELECT *FROM (WITH cte2AS (SELECT 2)SELECT *FROM cte2JOIN cte1)AS dt;
一个WITH
子句可以定义一个或多个公用表表达式,但每个CTE名称必须是唯一的条款。这是非法的:
WITH cte1AS (...), cte1AS (...)SELECT ...
为了使该语句合法,请使用唯一的名称定义CTE:
WITH cte1AS (...), cte2AS (...)SELECT ...
CTE可以引用自身或其他CTE:
- 自引用CTE是递归的。
CTE可以引用先前在同一
WITH
子句中定义的CTE ,但不能引用稍后定义的CTE 。此约束排除了相互递归的CTE,其中
cte1
引用cte2
和cte2
引用cte1
。这些引用之一必须是稍后定义的CTE,这是不允许的。- 给定查询块中的CTE可以引用在更外部级别的查询块中定义的CTE,但不能引用在更内部级别的查询块中定义的CTE。
为了解析对具有相同名称的对象的引用,派生表会隐藏CTE。CTE隐藏基本表,TEMPORARY
表和视图。通过在同一查询块中搜索对象来进行名称解析,然后依次搜索外部块,而找不到具有该名称的对象。
像派生表一样,CTE不能包含MySQL 8.0.14之前的外部引用。这是MySQL 8.0.14中取消的MySQL限制,而不是SQL标准的限制。有关特定于递归CTE的其他语法注意事项,请参阅递归公用表表达式。
递归公用表表达式
递归公用表表达式是具有引用其自身名称的子查询的表达式。例如:
WITH RECURSIVE cte (n)AS (SELECT 1UNION ALL SELECT n + 1FROM cteWHERE n < 5 )SELECT *FROM cte;
执行后,该语句将产生以下结果,即包含简单线性序列的单列:
+------+ | n | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+
递归CTE具有以下结构:
如果该
WITH
子句中的WITH RECURSIVE
任何CTEWITH
指向其自身,则该子句必须以其开头。(如果没有CTE引用自己,RECURSIVE
则允许,但不是必需的。)如果您忘记
RECURSIVE
了递归CTE,则可能会出现以下错误:ERROR 1146 (42S02): Table 'cte_name' doesn't exist
递归CTE子查询分为两部分,用
UNION[ALL]
或分隔UNION DISTINCT
:SELECT ... -- return initial row setUNION ALL SELECT ... -- return additional row sets第一个
SELECT
生成CTE的初始行或多个行,并且不引用CTE名称。第二个SELECT
通过引用其FROM
子句中的CTE名称产生其他行并递归。当此部分不产生新行时,递归结束。因此,递归CTE由一个非递归SELECT
部分和一个递归SELECT
部分组成。每个
SELECT
部分本身可以是多个SELECT
语句的并集。- CTE结果列的类型
SELECT
只能从非递归部分的列类型中推断出来,并且这些列都是可空的。对于类型确定,将SELECT
忽略递归部分。 - 如果非递归和递归部分之间用分隔
UNION DISTINCT
,则将消除重复的行。这对于执行传递闭包的查询很有用,以避免无限循环。 - 递归部分的每次迭代仅对先前迭代产生的行进行操作。如果递归部分具有多个查询块,则每个查询块的迭代将以未指定的顺序进行调度,并且每个查询块将对从上一次迭代结束后由其上一次迭代或其他查询块生成的行进行操作。
前面显示的递归CTE子查询具有以下非递归部分,该部分检索单个行以产生初始行集:
SELECT 1
CTE子查询还具有以下递归部分:
SELECT n + 1FROM cteWHERE n < 5
在每次迭代中,这将SELECT
产生一行新值,该行的新值比n
上一行中的值大一个。第一次迭代在初始行集(1
)上操作并产生1+1=2
;第二次迭代在第一个迭代的行集(2
)上操作并产生2+1=3
;等等。这一直持续到递归结束为止,递归n
不再小于5。
如果CTE的递归部分为列生成的值比非递归部分宽,则可能有必要加宽非递归部分中的列以避免数据被截断。考虑以下语句:
WITH RECURSIVE cteAS (SELECT 1AS n, 'abc'AS strUNION ALL SELECT n + 1, CONCAT(str, str)FROM cteWHERE n < 3 )SELECT *FROM cte;
在非严格SQL模式下,该语句产生以下输出:
+------+------+ | n | str | +------+------+ | 1 | abc | | 2 | abc | | 3 | abc | +------+------+
str
列值都是'abc'
因为非递归SELECT
确定列宽。因此,str
递归产生的较宽的值将SELECT
被截断。
在严格的SQL模式下,该语句产生错误:
ERROR 1406 (22001): Data too long for column 'str' at row 1
要解决此问题,以使语句不会产生截断或错误,请CAST()
在非递归中使用SELECT
以使str
列变宽:
WITH RECURSIVE cteAS (SELECT 1AS n, CAST('abc'AS CHAR(20))AS strUNION ALL SELECT n + 1, CONCAT(str, str)FROM cteWHERE n < 3 )SELECT *FROM cte;
现在,该语句产生以下结果,而不会截断:
+------+--------------+ | n | str | +------+--------------+ | 1 | abc | | 2 | abcabc | | 3 | abcabcabcabc | +------+--------------+
列是通过名称而不是位置来访问的,这意味着递归部分中的列可以访问非递归部分中具有不同位置的列,如本CTE所示:
WITH RECURSIVE cteAS (SELECT 1AS n, 1AS p, -1AS qUNION ALL SELECT n + 1, q * 2, p * 2FROM cteWHERE 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子查询:
递归
SELECT
部分不得包含以下构造:- 汇总功能,例如
SUM()
- 视窗功能
GROUP BY
ORDER BY
DISTINCT
在MySQL 8.0.19之前,
SELECT
递归CTE 的递归部分也不能使用LIMIT
子句。MySQL 8.0.19中取消了此限制,并且LIMIT
在这种情况下,现在还支持此限制以及可选OFFSET
子句。对结果集的影响与LIMIT
在最外层使用时相同SELECT
,但效率更高,因为与递归结合使用时,SELECT
一旦产生了所需数量的行,行就会停止生成。这些约束不适用于
SELECT
递归CTE 的非递归部分。禁止DISTINCT
仅适用于UNION
会员;UNION DISTINCT
被允许。- 汇总功能,例如
- 递归
SELECT
部分必须仅在其FROM
子句中引用一次CTE ,而不能在任何子查询中引用。它可以引用CTE以外的表,并将它们与CTE联接在一起。如果在这样的联接中使用,则CTE不得位于的右侧LEFT JOIN
。
这些约束来自于SQL标准,比的MySQL特定排除其他ORDER BY
,LIMIT
(MySQL的8.0.18和更早的版本),和DISTINCT
。
对于递归CTE,在列中显示EXPLAIN
递归SELECT
零件的输出行。Recursive
Extra
显示的成本估算值EXPLAIN
代表每次迭代的成本,可能与总成本有很大不同。优化器无法预测迭代次数,因为它无法预测该WHERE
子句何时变为假。
CTE实际成本也可能会受到结果集大小的影响。产生许多行的CTE可能需要一个足够大的内部临时表,以将其从内存中转换为磁盘上的格式,并且可能会降低性能。如果是这样,则增加允许的内存中临时表大小可能会提高性能;请参见“ MySQL中的内部临时表使用”。
限制公用表表达式递归
对于递归CTE,重要的是递归SELECT
部分包括终止递归的条件。作为一种防止递归CTE失控的开发技术,您可以通过限制执行时间来强制终止:
- 该
cte_max_recursion_depth
系统变量强制执行递归级别的热膨胀系数为数量限制。服务器终止任何递归级别高于此变量值的CTE。 - 所述
max_execution_time
系统变量强制用于执行超时SELECT
在当前会话中执行的语句。 - 该
MAX_EXECUTION_TIME
优化器提示强制为每个查询执行超时SELECT
在它出现的语句。
假设在没有递归执行终止条件的情况下错误地编写了递归CTE:
WITH RECURSIVE cte (n)AS (SELECT 1UNION ALL SELECT n + 1FROM cte )SELECT *FROM cte;
默认情况下,cte_max_recursion_depth
值为1000,导致CTE递归超过1000级时终止。应用程序可以更改会话值以适应其要求:
SET SESSION cte_max_recursion_depth = 10; -- permit only shallow recursionSET 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 1UNION ALL SELECT n + 1FROM cte )SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ *FROM cte;WITH RECURSIVE cte (n)AS (SELECT 1UNION ALL SELECT n + 1FROM cte )SELECT /*+ MAX_EXECUTION_TIME(1000) */ *FROM cte;
从MySQL 8.0.19开始,您还可以LIMIT
在递归查询中使用,以将最大数目的行强加给最外层SELECT
,例如:
WITH RECURSIVE cte (n)AS (SELECT 1UNION ALL SELECT n + 1FROM cteLIMIT 10000 )SELECT *FROM cte;
除了设置时间限制以外,还可以设置时间限制。因此,以下CTE在返回一万行或运行一千秒后(以先到者为准)终止:
WITH RECURSIVE cte (n)AS (SELECT 1UNION ALL SELECT n + 1FROM cteLIMIT 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, 1UNION ALL SELECT n + 1, next_fib_n, fib_n + next_fib_nFROM fibonacciWHERE 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的工作方式:
n
是显示列,指示该行包含n
-th斐波那契数。例如,第8个斐波那契数是13。- 该
fib_n
列显示斐波纳契数n
。 - 该
next_fib_n
列显示number之后的下一个斐波那契数n
。该列为下一行提供了下一个序列值,因此该行可以在其fib_n
列中产生前两个序列值的总和。 - 递归在
n
到达10 时结束。这是一个任意选择,可以将输出限制为一小组行。
前面的输出显示了整个CTE结果。要仅选择其中一部分,请WHERE
在顶层添加一个适当的子句SELECT
。例如,要选择第8个斐波那契数,请执行以下操作:
mysql>WITH RECURSIVE fibonacci ... ...SELECT fib_nFROM fibonacciWHERE n = 8; +------- + | fib_n | +------- + | 13 | +------- +
日期系列生成
公用表表达式可以生成一系列连续的日期,这对于生成摘要(包括该系列中所有日期的一行,包括未在汇总数据中表示的日期)非常有用。
假设销售数字表包含以下行:
mysql>SELECT *FROM salesORDER 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_priceFROM salesGROUP BY dateORDER 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 salesUNION ALL SELECT date +INTERVAL 1 DAYFROM datesWHERE 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的工作方式:
- 非递归
SELECT
在该sales
表跨越的日期范围内产生最低日期。 - 递归产生的每一行都
SELECT
将前一天产生的日期增加一天。 - 当日期达到该
sales
表跨越的日期范围内的最高日期后,递归结束。
用加入CTE LEFT JOIN
对sales
表产生与一排范围内的每个日期的销售汇总:
WITH RECURSIVE dates (date)AS (SELECT MIN(date)FROM salesUNION ALL SELECT date +INTERVAL 1 DAYFROM datesWHERE date +INTERVAL 1 DAY <= (SELECT MAX(date)FROM sales) )SELECT dates.date, COALESCE(SUM(price), 0)AS sum_priceFROM datesLEFT JOIN salesON dates.date = sales.dateGROUP BY dates.dateORDER 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 | +------------+-----------+
需要注意的几点:
- 查询是否效率低下,尤其是
MAX()
对于递归的每一行执行子查询的查询SELECT
?进行检查,EXPLAIN
表明子查询已针对效率进行了优化。 - 使用的
COALESCE()
显示避免NULL
在sum_price
天列发生在没有销售数据sales
表。
分层数据遍历
递归公用表表达式对于遍历形成层次结构的数据很有用。考虑以下这些语句,这些语句创建了一个小的数据集,该数据集为公司中的每个员工显示了员工姓名和ID号以及员工经理的ID。顶层员工(CEO)的经理ID为NULL
(无经理)。
CREATE TABLE employees ( id INTPRIMARY KEY NOT NULL,name VARCHAR(100) NOT NULL, manager_id INT NULL,INDEX (manager_id),FOREIGN KEY (manager_id)REFERENCES employees (id) );INSERT INTO employeesVALUES (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 employeesORDER 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(idAS CHAR(200))FROM employeesWHERE manager_id IS NULLUNION ALL SELECT e.id, e.name , CONCAT(ep.path , ',', e.id)FROM employee_pathsAS epJOIN employeesAS eON ep.id = e.manager_id )SELECT *FROM employee_pathsORDER 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
产生CEO行(具有NULL
经理ID 的行)。所述
path
柱被加宽,以CHAR(200)
确保有足够的空间对于较长path
通过递归产生的值SELECT
。- 递归产生的每一行都会
SELECT
找到直接向上一行产生的雇员报告的所有雇员。对于每个这样的员工,该行包括员工ID和姓名以及员工管理链。该链是经理的链,最后添加了员工ID。 - 当员工没有其他人向其报告时,递归结束。
要查找特定雇员或多名雇员的路径,请WHERE
在顶层添加一个子句SELECT
。例如,要显示Tarek和Sarah的结果,请进行如下修改SELECT
:
mysql>WITH RECURSIVE ... ...SELECT *FROM employees_extendedWHERE idIN (692, 4610)ORDER BY path ; +------ +------- +----------------- + | id | name | path | +------ +------- +----------------- + | 4610 | Sarah | 333,198,29,4610 | | 692 | Tarek | 333,692 | +------ +------- +----------------- +
公用表表达式与类似构造的比较
公用表表达式(CTE)在某些方面类似于派生表:
- 两种结构都被命名。
- 两种构造都存在于单个语句的范围内。
由于这些相似之处,CTE和派生表通常可以互换使用。作为一个简单的例子,这些语句是等效的:
WITH cteAS (SELECT 1)SELECT *FROM cte;SELECT *FROM (SELECT 1)AS dt;
但是,CTE与派生表相比具有一些优势:
- 派生表只能在查询中一次引用。可以多次引用CTE。要使用派生表结果的多个实例,您必须多次派生结果。
- CTE可以是自引用的(递归的)。
- 一个CTE可以引用另一个。
- 当CTE的定义出现在语句的开始而不是嵌入在语句的开头时,可能更易于阅读。
CTE与使用创建的表相似,CREATE[TEMPORARY] TABLE
但无需明确定义或删除。对于CTE,您不需要创建表的特权。