MySQL8.0新特性-通用表表达式（CTE）

select  * from (select 1) as cte1;
-- CTE 形式
with cte1 as (select 1)
select * from cte1;
-- CTE 更复杂的方式
with cte1(id) as (select 1)
cte2(id) as (select id+1 from cte1)
select * from cte1 join cte2;

with recursive cte(n) as
(
select 1 union all
select n+1 from cte where n<5
)
select * from cte;

-- 初始化
create table app_user
(
id int,
name varchar(10),
parent_id int
);
insert into app_user values(12,'jack',15),(32,'luc',15),(416,'halun',12),(15,'susan',61),(61,'mack',NULL);
select * from app_user order by id;
-- 管理关系
with recursive app_user_path(id,name,path) as
(
select id,name,cast(id as char(200)) from app_user
    where parent_id is null
    union all
    select u.id,u.name,concat(up.path,',',u.id)
    from app_user_path as up join app_user as u
    on up.id = u.parent_id
)
select * from app_user_path order by path;

-- 一个死循环的sql
with recursive cte(n) as
(
select 1 union all
select n+1 from cte
)
select * from cte;
-- ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
-- 系统默认限制为1000次
show variables like 'cte_max%';
-- 设置会话级别的参数
set session cte_max_recursion_depth=100;
-- 当前会生效，重启后失效
set global cte_max_recursion_depth=100;
-- 8.0之后，会把环境变量持久话，重启后不会失效
-- MySQL8.0的持久化操作实现很简单，会把当前持久化的边境变量写入/var/lib/mysql/mysqld-auto.cnf中。
set persist cte_max_recursion_depth=100;

with recursive cte(n,m) as
(
select 1,1 union all
select n+m,n from cte
    where m<20
)
select m from cte;