深入理解MySQL公共表表达式_Mysql

公共表表达式（common table expressions, cte）是mysql在单一语句中执行过程中，预先定义的临时结果集。

有时我们需要在一个sql中重复执行同一个子查询，而每次子查询都会重新计算结果，带来性能的浪费。而采用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：定义时可以引用自己，产生一个递归的结果集

普通cte和递归cte的区别在于，递归cte多了一个recursive关键字，且需要引用自己。

二、普通cte

2.1 普通cte示例

以下的演示sql可以在在mysql的官方示例数据库中执行：

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定义位置调到前面，则会报错：cte1不存在.

cte定义的名称后面可以添加括号，显式定义cte的列名，但要和后面子查询返还的列数量相同：

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;

此时后续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部分生成了一条初始数据，union all后面的第二部分引用了cte自己，且递归执行，直到不再满足条件(n<5)。

1个递归cte其实包含了非递归部分和递归部分，递归的第二部分每次都以上一次产生的结果集为基础计算数据。但是大小是以非递归部分为准，如果递归产生列越来越长，可能会发生错误。

例如下面的递归拼接：

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可以执行成功，但是第二列都被按非递归部分截断了，如下所示：

在遇到此类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）还有部分使用限制：

递归部分不能包含聚合函数、窗口函数、group by、order by、distinct
递归部分引用自身只能引用一次且必须在from子句中，不能在子查询中。

3.2 限制无限递归

对于递归cte，如果没有加限制递归的条件，在逻辑上是可以无限递归的（死循环）。为了限制这种情况，mysql有4种解决方式：

使用参数cte_max_recursion_depth来限制最大递归的次数，超过递归深度强制终止。
使用参数max_execution_time来限制最大的执行时间。
使用优化器提示 max_execution_time来限制最大执行时间。
mysql 8.0.19后，可以用limit子句限制最大返还行数。

示例：通过cte_max_recursion_depth限制递归次数，超过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;   -- 将递归次数增大，防止先触发
set session max_execution_time=10;    -- 将最大递归执行时长修改为10毫秒
with recursive
cte(n) as (
select 1
union all
select n+1 from cte)
select * from cte;

示例：使用优化器提示限制递归执行时间

with recursive
cte(n) as (
select 1
union all
select n+1 from cte)
select /*+ max_execution_time(10) */ * from cte;    -- 使用提示语法限制执行时间

四、一个递归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);

现在要统计截止'2022-01-07'日的营业额，正常我们使用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;