公共表表達式（Common Table Expressions, CTE）是MySQL在單一語句中執(zhí)行過程中，預先定義的臨時結果集。

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

一、CTE定義及分類

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

定義語法：

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

CTE分為兩種：

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

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

二、普通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（以逗號分隔），隨后在select子句中可以直接引用cte1和cte2的名稱進行查詢。

cte定義時也可以引用其他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)到前面，則會報錯：cte1不存在.

cte定義的名稱后面可以添加括號，顯式定義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;

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

2.2 CTE的使用場景

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示例

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

遞歸cte包含2個部分，使用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個遞歸cte其實包含了非遞歸部分和遞歸部分，遞歸的第二部分每次都以上一次產(chǎn)生的結果集為基礎計算數(shù)據(jù)。但是大小是以非遞歸部分為準，如果遞歸產(chǎn)生列越來越長，可能會發(fā)生錯誤。

例如下面的遞歸拼接：

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模式下，因為第二列以非遞歸部分的長度為準，遞歸后長度列的長度變長導致SQL直接報錯。

而在非strict SQL模式下，以上SQL可以執(zhí)行成功，但是第二列都被按非遞歸部分截斷了，如下所示：

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

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

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

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

3.2 限制無限遞歸

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

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

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

set session cte_max_recursion_depth=10;  -- 全局默認值是1000，我們這里修改會話級為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í)行時長修改為10毫秒
with recursive
cte(n) as (
select 1
union all
select n+1 from cte)
select * from cte;

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

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

四、一個遞歸CTE應用示例

假設我們有一張訂單表，

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)計截止'2022-01-07'日的營業(yè)額，正常我們使用group by按日期匯集訂單金額即可：

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

但是注意到由于2號/4號/6號沒有訂單，所以查詢出來的結果中不包含這些日期，而通過遞歸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;

可以看到?jīng)]有訂單的日期也顯示出來了，營業(yè)額顯示為0，這個技巧在做報表類數(shù)據(jù)時很有用。

到此這篇關于深入理解MySQL公共表表達式的文章就介紹到這了,更多相關MySQL公共表表達式內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論