MySQL8新特性-通用表达式(CTE)

MySQL8.0开始支持通用表表达式(CTE）(common table expression)，即WITH子句。

简单入门：

以下SQL就是一个简单的CTE表达式，类似于递归调用，这段SQL中，首先执行select 1 然后得到查询结果后把这个值n送入 union all下面的 select n+1 from cte where n <10,然后一直这样递归调用union all下面sql语句。

WITH recursive cte(n) as 
( select 1
  union ALL
  select n+1 from cte where n<10
)
select * from cte;

案例介绍：

一个staff表，里面有id，有name还有一个 m_id，这个是对应的上级id。数据如下：

如果我们想查询出每一个员工的上下级关系，可以使用以下方式

递归CTE：

with recursive staff_view(id,name,m_id) as
(select id ,name ,cast(id as char(200)) 
 from staff where m_id =0
 union ALL 
 select s2.id ,s2.name,concat(s1.m_id,'-',s2.id)
 from staff_view as s1 join  staff as s2
 on s1.id = s2.m_id
)
select * from staff_view order by id

使用通用表表达式的好处就是上下级层级就算有4，5，6甚至更多层，都可以帮助我们遍历出来，而老的方式的写法SQL语句就要调整。

总结：

通用表表达式与派生表类似，就像语句级别的临时表或视图。CTE可以在查询中多次引用，可以引用其他CTE，可以递归。CTE支持SELECT/INSERT/UPDATE/DELETE等语句。