mysql cte

【1】递归构造连续日期

该值默认最多只允许递归1000次

可以通过：set session cte_max_recursion_depth=1000000000   来配置最大递归数

with recursive  t1 as ( select 1 as id  union all select id+1 from t1 where id<100 ) 
SELECT date_format(date_add('20210101',interval t1.id day),'%Y%m%d') from t1;

 

 

【2】递归插入

insert into test.t1 with recursive q1 as (select 4 as id,4 as num union all select id+1,num+1 from q1 where id<=1000000)  select * from q1;