MySQL8.0新特性—CTE
MySQL 8支持公用表表达式，包括非递归和递归两种。
公用表表达式允许使用命名的临时结果集，这是通过允许在SELECT语句和某些其他语句前面使用WITH子句来实现的。
不能在同一查询中两次引用派生表（子查询），因为那样的话，查询会根据派生表的引用次数计算两次或多次，这会引发严重的性能问题。使用CTE后，子查询只会计算一次。
非递归CTE
1. 派生表（子查询）
select …… from (subquery) as derived, table_name ……; 2. CTE
with derived as ( subquery ) select …… from derived, table_name ……; CTE可能在SELECT/UPDATE/DELETE之前，包括with derived as (subquery)的子查询，例如：
with derived as ( subquery ) delete from table_name where table_name.col_name in ( select col_name from derived ); - CTE可以引用其他CTE
with derived_one as ( subquery ), derived_two as ( select …… from derived_one ) select …… from derived_one, derived_two ……; 递归CTE
1. 语法解释
递归CTE是一种特殊的CTE，其子查询会引用自己的名字。WITH子句必须以WITH RECURSIVE开头。递归CTE子查询包括两部分：seed查询和recursive查询，由UNION[ALL]或UNION DISTINCT分隔。
seed select被执行一次以创建初始数据子集；recursive select被重复执行以返回数据的子集，直到获得完整的结果集。
当迭代不会生成任何新行时，递归会停止。这对挖掘层次结构（父/子或部分/子部分）非常有用。
with recursive {temp_table_name}(n) as ( select …… from table_name /* seed select */ union all select …… from {temp_table_name}, table_name /* recursive select */ ) select …… from derived ……; 假设要打印从1到5的所有数字：
mysql> with recursive derived(n) as ( -> select 1 /* seed select */ -> union all -> select n + 1 from derived where n < 5 /* recursive select */ -> ) -> select * from derived; +------+ | n | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec) 在每次迭代中，SELECT都会生成一个带有新值的行，比前一行的值n多1。第一次迭代在初始行集合（1）上运行并生成值为1 + 1 = 2的行；第二次迭代对第一次迭代的行集合（2）进行操作并生成值为2 + 1 = 3的行。以此类推，一直持续到n不再小于5时，递归结束。
2. 案例
假设要执行分层数据遍历，以便为每个员工生成一个组织结构图（即从CEO到每个员工的路径），也可以使用递归CTE。
1. 创建带有manager_id的测试表
mysql> create table employees.employees_mgr ( -> id int primary key not null, -> name varchar(100) not null, -> manager_id int null, -> index (manager_id), -> foreign key (manager_id) references employees_mgr (id) -> ); Query OK, 0 rows affected (0.03 sec) 2. 插入示例数据
mysql> insert into employees.employees_mgr values -> (333, Yasmina, null), /* Yasmina is the CEO (manager_id is null) */ -> (198, John, 333), /* John has id 198 and reports to 333 (Yasmina) */ -> (692, Tarek, 333), -> (29, Pedro , 198), -> (4610, Sarah, 29), -> (72, Pierre, 29), -> (123, Adil, 692); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select id, name, manager_id from employees.employees_mgr; +------+---------+------------+ | id | name | manager_id | +------+---------+------------+ | 29 | Pedro | 198 | | 72 | Pierre | 29 | | 123 | Adil | 692 | | 198 | John | 333 | | 333 | Yasmina | NULL | | 692 | Tarek | 333 | | 4610 | Sarah | 29 | +------+---------+------------+ 7 rows in set (0.00 sec) 3. 执行递归CTE
mysql> with recursive employee_paths (id, name, path) as ( -> select id, name, cast(id as char(200)) -> from employees.employees_mgr -> where manager_id is null -> union all -> select e.id, e.name, concat(ep.path, '->', e.id) -> from employee_paths as ep -> join employees.employees_mgr as e -> on ep.id = e.manager_id -> ) -> select * from employee_paths; +------+---------+--------------------+ | id | name | path | +------+---------+--------------------+ | 333 | Yasmina | 333 | | 198 | John | 333->198 | | 692 | Tarek | 333->692 | | 29 | Pedro | 333->198->29 | | 123 | Adil | 333->692->123 | | 72 | Pierre | 333->198->29->72 | | 4610 | Sarah | 333->198->29->4610 | +------+---------+--------------------+ 7 rows in set (0.00 sec) with recursive employee_paths (id, name, path) as是CTE的名称，列是(id, name, path)。
select id, name, cast(id as char(200)) from employees.employees_mgr where manager_id is null是查询CEO的seed查询（没有在CEO之上的管理者）。
select e.id, e.name, concat(ep.path, '->', e.id) from employee_paths as ep join employees.employees_mgr as e on ep.id = e.manager_id是递归查询。
递归查询生成的每一行，会查找直接向前一行生成的员工做汇报的所有员工。对于每个员工，该行的信息包括员工ID、姓名和员工管理链，该链是在最后添加了员工ID的管理链。