mysql8 实现Oracle的level connect by

mysql8 (8.0.22) 支持采用嵌套查询获取自增序列 

具体写法: 

with recursive tab1(pid) as 
(
select @num := 1
union all 
select @num := @num + 1 from tab1 where pid < 100
)
select pid from tab1;

有个前提: mysql 默认只能嵌套查询到1000层 ,超过1000层会有如下错误

Error Code: 3636. Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value

需要调整会话级别的控制参数 来实现更大的层级嵌套

set cte_max_recursion_depth = 65535;

然后再次执行;