公用表达式是MySQL8.0的新特性,它是一个命名的临时结果集,作用范围是当前语句。
可以理解成为当前sql语句定义了一个视图,sql语句的任何地方都可以使用这个视图,如果被多次使用就体现出了公用表达式的特点公用。
依据语法结构和执行方式不同,公用表达式可以分为普通公用表达式和递归公用表达式。
#公共表表达式定义语法
WITH [RECURSIVE]
cte_name [(col_name [, col_name] ...)] AS (subquery)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
非递归公用表达式案例
提取公共表达式
select u.* from a u join b o on u.USER_ID_=o.USER_ID_ and o.XXXXX='1001'
where u.SEX_='male'
UNION
select u.* from a u join b o on u.USER_ID_=o.USER_ID_ and o.XXXXX='1001'
where u.SEX_='female'
#提取公用部分组成一个公用表达式
# select u.* from a u join b o on u.USER_ID_=o.USER_ID_ and o.XXXXX='1001'
with m as (
##将公共查询部分提取出来作为一个临时表,后面的查询直接使用临时表即可
select u.* from a u join b o on u.USER_ID_=o.USER_ID_ and o.XXXXX='1001'
)
select * from m
where SEX_='female'
UNION
select * from m
where SEX_='male'
其中为构建的公用表起名为 m。写法的优势:
1、便于我们阅读sql语句。
2、也有提升sql性能
递归公用表达式讲解
生成一周的日期,从周一到周日
with RECURSIVE m as (
select DATE('2024-03-18') t #初始数据我们很容易通过代码给出
UNION all
select DATE_ADD(t, INTERVAL 1 DAY) from m limit 7 #获取指定日期及其后面的日期,总数为7条
)
select * from m
运行结果为:
递归公用表表达式通用模板:
WITH RECURSIVE cte (n) AS
(
SELECT ... -- 通过第一条select的到初始数据集,第一个执行,且只执行一次。
UNION [ALL]
SELECT ... -- 递归select语句,不断的通过递归数据集得到递归结果集。在将此轮得到的递归结果集作为下一轮递归数据集。并且将此轮的递归结果集加入到最终的数据集中。这里将会因为union后面有没有all为导致不同。
)
SELECT * FROM cte;
#比如:
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
图解递归运行原理
第一次递归的递归数据集来自于第一条select语句产生的数据(初始数据集)。后期每一次的递归结果集作为下一轮的递归的递归数据集(也就是只是对增量数据进行递归)。
递归停止的条件如下:
1、当输入递归数据为空时表示递归结束,数据构造完成。
2、当明确指明了需要数据条数limit。若先触发了条数满足的情况也会递归结束。
对于上图的讲解:
1、通过第一条初始select语句得到初始数据集。将初始数据集放入最终结果集中,也将初始数据集作为第一轮递归的递归数据集。
(若初始数据集为空,则触发了递归停止的第一个条件。不进行递归)。
2、递归数据集通过递归select语句(递归函数)得到递归结果集。此时需要判断UNION有没有ALL.
2.1、如果没有ALL; 那么将会把此轮的递归结果集去重在和最终数据结果集去重。
比如当前递归结果集为1、1、2、3。而最终结果集为2,5,6,9。那么自身去重以及和最终结果集去重后得到的结果为1、3.那么将1、3作为本轮的递归结果集。
2.2、如果有ALL就啥也不做(不需要去重)。
3、如果递归结果集有数据,则将递归结果集的数据放入最终结果集中,此时如果有限制递归数据的条数(limit),
且达到了条数则递归退出,否则将此轮的递归结果集作为下一轮的递归数据集(输入参数)准备进入下一轮的递归。 如果递归结果集为空,则退出递归。最终结果集就是我们要的公用表数据集了。
以下语句展示了有all和没有all的区别。
---以下语句有all,所以递归结果集一定有值,只能通过limit限制条数才能退出递归
with RECURSIVE m(n) as (
select 1
UNION all
select if(n=3,1,3) from m limit 200
)
select * from m ;
---以下语句没有all,所以第二次递归产生的1将会被过滤掉,导致第二次递归结果为空值,第二次就导致递归退出
with RECURSIVE m(n) as (
select 1
UNION
select if(n=3,1,3) from m limit 200
)
select * from m ;
公用表表达式注意事项
公用表表达式通过非递归列进行表结构的定义(列的类型定义) 后续递归生成的数据列需要满足非递归列定义的结构
比如
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;
###如上类似于建立了如下一张临时表:
# create table cte (
# int n ,
# char(3) str
# )
#后续递归不断进行前面的字符串叠加。 abcabc 第一次递归就导致str长度为6. 不满足上面的cte定义
###显示类型转换
WITH RECURSIVE cte AS
(
SELECT 1 AS n, CAST('abc' AS char(12) ) as str
UNION ALL
SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;
# create table cte (
# int n ,
# char(12) str
# )