MySQL8.0 必须掌握的新特性--让你SQL性能与可读性都提高N倍。

一、公用表达式

公用表达式在8.0.19 版支持了limit子句

MySQL官方手册链接

1、让递归更简单

tree型结构查询父节点或子节点

-- 创建测试用例
CREATE TABLE `treetable` ( `id` int NOT NULL , `name` varchar(50) NOT NULL , `parid` int NOT NULL , PRIMARY KEY (`id`) ) ; 
insert into treetable 
select 1,'1',0 union all
select 2,'1-1',1 union all
select 3,'1-1-1',2 union all
select 4,'1-1-1-1',3  union all
select 5,'1-1-1-1-1',4;

-- 找出id = 4的所有父节点
with RECURSIVE  cte as
 ( select * from treetable where id = 4  
  union all   
  select a.* from treetable a inner join cte b on a.id = b.parid )
 select * from cte;

-- 找出id = 3的所有子节点
with RECURSIVE  cte as
 ( select * from treetable where id = 3  
  union all   
  select a.* from treetable a inner join cte b on a.parid = b.id )
 select * from cte;

语句执行结果

2、让代码可读性更高

求得5月iphone日销售曲线图

--  构造测试用例
create table salelist
(
  billid int, 
  phone varchar(20),
  saledate date,
  qty int
);

insert into salelist
select 1,'iphone','2022-05-01',1 union all
select 2,'iphone','2022-05-02',10 union all
select 3,'iphone','2022-05-03',20 union all
select 4,'iphone','2022-05-03',15 union all
select 5,'iphone','2022-05-04',20 union all
select 6,'xiaomi','2022-05-04',20 union all
select 7,'iphone','2022-05-30',30;

 -- 1、构造整个5月日期列表
with RECURSIVE cte1 as 
(
  select cast('2022-05-01' as date) as saledate
  union all
  SELECT saledate + INTERVAL 1 DAY FROM cte1  where saledate<'2022-05-31'
)
-- 2、iphone 按日汇总统计
,cte2 as  
(
  select  sum(qty) as qty,saledate from salelist 
  where phone='iphone'
  group by saledate
)
-- 3、关联取得结果
select cte1.saledate,ifnull(cte2.qty,0) as qty 
from cte1
left join cte2 on  cte1.saledate = cte2.saledate

执行结果

3、派生表复用更简单,性能也更好，只会生成一次临时表

在有些时候，我们在一个select语句中会多次使用到相同的派生表或子查询

 select * from t1 
 where  field1 in (select id from tt1 
                   inner join tt2 on tt1.xx= tt2.xx 
                   where ....)
 union all 
 select * from t2 
 where  field1 in (select id from tt1 
                   inner join tt2 on tt1.xx= tt2.xx 
                   where ....)
-- 使用公用表达式改写 
with cte as
(
 select id from tt1 
 inner join tt2 on tt1.xx= tt2.xx 
 where ....
)
select * from t1 where field1 in (select id from cte)
union all
select * from t2 where field1 in (select id from cte)

二、窗口函数

MySQL官方手册链接

1、简单实例

-- 按科目分别排名
 with cte_window as
 (
    select '张三' as stuName,'语文' as proName, 98 as score from dual
    union all
    select '张三' as stuName,'数学' as proName,88 as score from dual
    union all
    select '李四' as stuName,'语文' as proName, 99 as score from dual
    union all
    select '李四' as stuName,'数学' as proName,78 as score from dual    
    union all
    select '王五' as stuName,'语文' as proName, 94 as score from dual
    union all
    select '王五' as stuName,'数学' as proName,88 as score from dual      
 )
 
  select *,
  ROW_NUMBER() over w as score_rownumber
  from  cte_window
  window w as (partition by proName order by score desc)

执行结果

2、复杂案例

窗口函数本身非常复杂，可以实现很多复杂业务，上面举例的排名案例只是牛刀杀鸡

这个复杂案例也只是窗口函数部份功能的应用
掌握好窗口函数 让你SQL功力直接翻倍

复杂案例

3、窗口函数深入讲解

窗口函数因为有三部份子句，不同的窗口函数与聚合函数支持的子句又不同。所以很复杂。
我专门写了4篇文章对窗口函数做了深入的讲解

开窗函数讲解一
开窗函数讲解二
开窗函数讲解三
开窗函数讲解四

三、横向派生表

MySQL官方手册链接

1、横向派生表 让写在select 子句后的子查询变得更简单，性能也更优

-- 构造测试用例
create table vipinfo
( 
   vipid int not null comment '会员表主键ID' ,
   vipname varchar(30) comment '会员姓名',
   viptel  varchar(11) comment '会员电话',
   createdtime datetime comment'创建时间',
   primary key `pk_vipinfo_vipid`(vipid)
) engine = innodb comment = '会员表';
create table mobileinfo
( 
   id int not null comment '手机号对照表主键ID' ,
   postcode varchar(30) comment '邮编',
   province  varchar(30) comment '省',
   city  varchar(30) comment '市',
   primary key `pk_mobileinfo_id`(id)
) engine = innodb comment = '手机对照表';

-- 有如下需求:按会员创建时间取得每个会员所在的省，市，邮编

-- 8.x 版本以前写法 
select t.*,
(SELECT IFNULL(province, '') FROM mobileinfo WHERE mobilenum = SUBSTR(t.tel, 1, 7)) AS province,
(SELECT IFNULL(city, '') FROM mobileinfo WHERE mobilenum = SUBSTR(t.tel, 1, 7)) AS city,
(SELECT IFNULL(postcode, '') FROM mobileinfo WHERE mobilenum = SUBSTR(t.tel, 1, 7)) AS postcode
from vipinfo  as t
order by createdtime 
limit 0,50;

-- 8.0 以后写法 
select t.*,m.*
from  vipinfo,
LATERAL （select IFNULL(province, '') as  province ,IFNULL(city, '')  as city ,IFNULL(postcode, '') as postcode from  mobileinfo where mobilenum = SUBSTR(t.tel, 1, 7)） as m

2、搞个简单实例说两句

LATERAL 关键字，只能出现在from 子句中

完全兼容join 连接写法

from 子句后以,号分隔

with cte1 as
(
  select 1 as stuid ,'李四' as stuname union all
  select 2 as stuid ,'张三' as stuname  union all
  select 3 as stuid,'王五' as stuname
),
cte2 as
(
  select 1 as stuid,'语文' as proname, 80 as score union all
  select 1 as stuid,'数学' as proname,100 as score union all
  select 2 as stuid,'语文' as proname,99 as score 
),
cte3 as
(
select sum(score) as totalscore ,stuid from cte2 group by stuid 
)

select * 
from cte1,
 LATERAL ( select totalscore from cte3 where cte1.stuid = cte3.stuid) as t

执行结果

inner join 写法

with cte1 as
(
  select 1 as stuid ,'李四' as stuname union all
  select 2 as stuid ,'张三' as stuname  union all
  select 3 as stuid,'王五' as stuname
),
cte2 as
(
  select 1 as stuid,'语文' as proname, 80 as score union all
  select 1 as stuid,'数学' as proname,100 as score union all
  select 2 as stuid,'语文' as proname,99 as score 
),
cte3 as
(
select sum(score) as totalscore ,stuid from cte2 group by stuid 
)

select * 
from cte1
 inner join LATERAL ( select totalscore from cte3 where cte1.stuid = cte3.stuid) as t on 1=1

执行结果

left join 写法

with cte1 as
(
  select 1 as stuid ,'李四' as stuname union all
  select 2 as stuid ,'张三' as stuname  union all
  select 3 as stuid,'王五' as stuname
),
cte2 as
(
  select 1 as stuid,'语文' as proname, 80 as score union all
  select 1 as stuid,'数学' as proname,100 as score union all
  select 2 as stuid,'语文' as proname,99 as score 
),
cte3 as
(
select sum(score) as totalscore ,stuid from cte2 group by stuid 
)

select * 
from cte1
left join LATERAL ( select totalscore from cte3 where cte1.stuid = cte3.stuid) as t on 1=1

执行结果

欢迎大家关注我的微信公众号，分享MySQL相关知识