快捷導(dǎo)航

深入理解MySQL公共表表達(dá)式

更新時(shí)間：2024年09月03日 10:55:21 作者：V1ncent Chen

公用表達(dá)式是MySQL8.0的新特性,它是一個(gè)命名的臨時(shí)結(jié)果集,作用范圍是當(dāng)前語句,本文主要介紹了MySQL公共表表達(dá)式使用,感興趣的可以了解一下

有時(shí)我們需要在一個(gè)SQL中重復(fù)執(zhí)行同一個(gè)子查詢，而每次子查詢都會(huì)重新計(jì)算結(jié)果，帶來性能的浪費(fèi)。而采用CTE可以在查詢的一開始就定義好子查詢的結(jié)果集，MySQL只會(huì)計(jì)算一次結(jié)果，然后在查詢中使用CTE的名稱可以反復(fù)引用。

一、CTE定義及分類

CTE的定義方式是在with子句后跟一個(gè)子查詢，如果一個(gè)SQL中需要定義多個(gè)CTE，則用逗號(hào)分隔即可。

定義語法：

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

CTE分為兩種：

普通CTE：定義一個(gè)簡(jiǎn)單子查詢
遞歸CTE：定義時(shí)可以引用自己，產(chǎn)生一個(gè)遞歸的結(jié)果集

普通CTE和遞歸CTE的區(qū)別在于，遞歸CTE多了一個(gè)recursive關(guān)鍵字，且需要引用自己。

二、普通CTE

2.1 普通CTE示例

以下的演示SQL可以在在MySQL的官方示例數(shù)據(jù)庫中執(zhí)行：

with
cte1 as (select emp_no,first_name,last_name from employees where emp_no=10012), -- 定義cte1
cte2 as (select emp_no,dept_no from dept_emp)    -- 定義cte2
select cte1.emp_no,cte2.dept_no,cte1.first_name
from cte1
join cte2 on cte1.emp_no=cte2.emp_no;

示例中在select子句前定義了cte1和cte2（以逗號(hào)分隔），隨后在select子句中可以直接引用cte1和cte2的名稱進(jìn)行查詢。

cte定義時(shí)也可以引用其他cte，例如在上面的定義中，cte2的定義可以引用cte1:

with
cte1 as (select emp_no,first_name,last_name from employees where emp_no=10012),
cte2 as (select emp_no,last_name from cte1)    -- cte2的定義引用了cte1
select cte1.emp_no,cte2.last_name
from cte1
join cte2 on cte1.emp_no=cte2.emp_no;

注意之只有后定義的cte可以引用前面的定義的cte，如果把cte2定義位置調(diào)到前面，則會(huì)報(bào)錯(cuò)：cte1不存在.

cte定義的名稱后面可以添加括號(hào)，顯式定義cte的列名，但要和后面子查詢返還的列數(shù)量相同：

with
cte1(col1, col2, col3) as (select emp_no,first_name,last_name from employees where emp_no=10012)
select col1, col2, col3    -- 引用定義的列名
from cte1;

此時(shí)后續(xù)cte則必須通過顯示定義的列名來引用（col1, col2, col3），定義中子查詢的列名不能再引用了。

2.2 CTE的使用場(chǎng)景

cte的定義不僅僅用在select中，也可以用在update/delete語句前，子查詢中，以及其他可以嵌套select語句的地方（例如 insert …select）：

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE …
SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

三、遞歸CTE

3.1 遞歸CTE示例

如果一個(gè)cte定義過程中引用了自己，則是遞歸cte，此時(shí)需要with recursive子句定義，其中recursive關(guān)鍵字是必須的。

遞歸cte包含2個(gè)部分，使用union all 或 union [distinct]連接：

with recursive
cte(n) as (
select 1
union all
select n+1 from cte where n<5)
select * from cte;

上述cte定義中第1部分生成了一條初始數(shù)據(jù)，union all后面的第二部分引用了cte自己，且遞歸執(zhí)行，直到不再滿足條件(n<5)。

1個(gè)遞歸cte其實(shí)包含了非遞歸部分和遞歸部分，遞歸的第二部分每次都以上一次產(chǎn)生的結(jié)果集為基礎(chǔ)計(jì)算數(shù)據(jù)。但是大小是以非遞歸部分為準(zhǔn)，如果遞歸產(chǎn)生列越來越長(zhǎng)，可能會(huì)發(fā)生錯(cuò)誤。

例如下面的遞歸拼接：

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;

如上圖所示，在strict SQL模式下，因?yàn)榈诙幸苑沁f歸部分的長(zhǎng)度為準(zhǔn)，遞歸后長(zhǎng)度列的長(zhǎng)度變長(zhǎng)導(dǎo)致SQL直接報(bào)錯(cuò)。

而在非strict SQL模式下，以上SQL可以執(zhí)行成功，但是第二列都被按非遞歸部分截?cái)嗔?，如下所示?/p>

在遇到此類cte定義時(shí)，將非遞歸部分的列定義大一些，例如下面將'abc'的非遞歸部分加長(zhǎng)，即可顯示正確的遞歸結(jié)果：

with recursive
cte as (
select 1 as n, cast('abc' as char(20)) as str    -- 定義長(zhǎng)度
union all
select n+1,concat(str,str) from cte where n<3)
select * from cte;

另外，對(duì)于遞歸cte的遞歸部分（即union后的SQL）還有部分使用限制：

遞歸部分不能包含聚合函數(shù)、窗口函數(shù)、group by、order by、distinct
遞歸部分引用自身只能引用一次且必須在from子句中，不能在子查詢中。

3.2 限制無限遞歸

對(duì)于遞歸cte，如果沒有加限制遞歸的條件，在邏輯上是可以無限遞歸的（死循環(huán)）。為了限制這種情況，MySQL有4種解決方式：

使用參數(shù)cte_max_recursion_depth來限制最大遞歸的次數(shù)，超過遞歸深度強(qiáng)制終止。
使用參數(shù)max_execution_time來限制最大的執(zhí)行時(shí)間。
使用優(yōu)化器提示 MAX_EXECUTION_TIME來限制最大執(zhí)行時(shí)間。
MySQL 8.0.19后，可以用limit子句限制最大返還行數(shù)。

示例：通過cte_max_recursion_depth限制遞歸次數(shù)，超過10次遞歸終止

set session cte_max_recursion_depth=10;  -- 全局默認(rèn)值是1000，我們這里修改會(huì)話級(jí)為10次
with recursive
cte(n) as (
select 1
union all
select n+1 from cte)
select * from cte;

示例：超過10毫秒終止遞歸

set session cte_max_recursion_depth=100000;   -- 將遞歸次數(shù)增大，防止先觸發(fā)
set session max_execution_time=10;    -- 將最大遞歸執(zhí)行時(shí)長(zhǎng)修改為10毫秒
with recursive
cte(n) as (
select 1
union all
select n+1 from cte)
select * from cte;

示例：使用優(yōu)化器提示限制遞歸執(zhí)行時(shí)間

with recursive
cte(n) as (
select 1
union all
select n+1 from cte)
select /*+ MAX_EXECUTION_TIME(10) */ * from cte;    -- 使用提示語法限制執(zhí)行時(shí)間

四、一個(gè)遞歸CTE應(yīng)用示例

假設(shè)我們有一張訂單表，

create table orders (dt date,price decimal(10,2));
insert into orders values
('2022-01-01',100),
('2022-01-01',200),
('2022-01-03',200),
('2022-01-03',200),
('2022-01-05',300),
('2022-01-07',200);

現(xiàn)在要統(tǒng)計(jì)截止'2022-01-07'日的營(yíng)業(yè)額，正常我們使用group by按日期匯集訂單金額即可：

select dt, sum(price) sales from orders group by dt;

但是注意到由于2號(hào)/4號(hào)/6號(hào)沒有訂單，所以查詢出來的結(jié)果中不包含這些日期，而通過遞歸cte我們可以先按日期遞歸，將這些日期列出來然后與orders連接：

with recursive cte(dt) as (
select min(dt) from orders
union all
select dt + interval 1 day from cte where dt <(select max(dt) from orders))
select e.dt,ifnull(sum(o.price),0) turnover
from cte e
left join orders o on o.dt=e.dt
group by e.dt
order by e.dt;