mysql8 实现connect by语法

都2019年了，在网上搜索mysql实现connect by却依然还是满篇的mysql5的老方法，还全都是抄的，还没抄全。。。
实在看不下去写下此文。

对于其他数据库（比如oracle）中的connect by语法（即层次查询），mysql8中已经几乎实现了它的全部功能，只是mysql跳过了connect by这种语法，直接跳到了cte这个更高级的语法。
在oracle中，有一个与它极其相似的语法，在那边叫做递归查询。既然是递归，那自然是要包含层次查询子集关系的语法的。

本文默认读者精通oracle（或者其他有connect语法的数据库语言）中的connect语法

基本用法

建立测试数据

CREATE DATABASE IF NOT EXISTS blog_temp;

CREATE TABLE blog_temp.etc_test (
  id        int,
  parent_id int
);

select * from blog_temp.etc_test;

delete from blog_temp.etc_test;

INSERT INTO blog_temp.etc_test VALUES (1, NULL);
INSERT INTO blog_temp.etc_test VALUES (2, 1);
INSERT INTO blog_temp.etc_test VALUES (3, 2);
INSERT INTO blog_temp.etc_test VALUES (4, 2);
INSERT INTO blog_temp.etc_test VALUES (5, 4);
INSERT INTO blog_temp.etc_test VALUES (6, 4);
INSERT INTO blog_temp.etc_test VALUES (7, 1);
INSERT INTO blog_temp.etc_test VALUES (8, 7);
INSERT INTO blog_temp.etc_test VALUES (9, 1);
INSERT INTO blog_temp.etc_test VALUES (10, 9);
INSERT INTO blog_temp.etc_test VALUES (11, 10);
INSERT INTO blog_temp.etc_test VALUES (12, 9);

这是一个很明显的有父子关系的表，下面用cte语法对其进行遍历

with recursive t1(id, parent_id) as (
select*from blog_temp.etc_test t0 where t0.parent_id is null  -- Anchor member.
 union all
select t2.id, t2.parent_id from blog_temp.etc_test t2, t1  -- Recursive member.
 where t2.parent_id = t1.id
)
select*from t1;

简单说明一下。
cte语法基本是由两个查询语句用union all连接起来的。Anchor member是union all中的前一句，这是一个锚点，或者说是根节点。它的结果集会以with recursive t1中的t1这个别名传递到Recursive member这个递归语句中。

需要注意的是，就像一般的递归一样，cte也需要递归条件，也要注意死循环的问题。而且mysql因为某种考虑，对递归的深度进行了限制。可以通过这个语句来修改SET SESSION cte_max_recursion_depth = 1000000;。（默认值是1000）
这个连接中有更详细的介绍
https://dev.mysql.com/doc/refman/8.0/en/with.html

总之，cte的基本用法与oracle的实在是太相似了。我把我之前写的oracle的这篇文章
https://blog.csdn.net/nayi_224/article/details/82147786
中的语句直接粘到mysql中，除了少许的语法差异外，可以直接运行。

更详细的讲解我建议直接看这篇oracle的文章。我暂时还没找到更好的讲解递归with的文章
https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2

特殊语法的实现

level

with recursive tab1(id, parent_id, lv) as (
select t0.*, 1 lv from blog_temp.etc_test t0 where t0.parent_id is null  
 union all
select t2.id, t2.parent_id, t1.lv + 1 from blog_temp.etc_test t2, tab1 t1  
 where t2.parent_id = t1.id
)
select t1.*,
	   concat(lpad('-', (lv - 1) * 2, '-'), t1.id) str
  from tab1 t1;

注:lpad单纯是为了增加可读性

按深度排序

很明显，上面的查询都是按照广度排序的。很多时候这并不是我们想要的。
改为深度排序需要加一些额外的辅助参数

with recursive tab1(id, parent_id, lv, rownum, order_str) as (
select t0.*, 1 lv, @rownum := 1 rn, cast(@rownum as char) sdfsf from blog_temp.etc_test t0 where t0.parent_id is null  
 union all
select t2.id, t2.parent_id, t1.lv + 1, @rownum := @rownum + 1 rn, concat(t1.order_str, '-', @rownum) st 
  from blog_temp.etc_test t2, tab1 t1  
 where t2.parent_id = t1.id
)
select t1.*,
	   concat(lpad('-', (lv - 1) * 2, '-'), t1.id) tree
  from tab1 t1
 order by t1.order_str
;

sys_connect_by_path

见上一节的order_str列

查询接下来12个月的日期

with recursive tab1(dt, num) as (
select date_format(now(), '%Y-%m-%d'), @num := 1
union all
select date_format(date_add(now(), interval num month), '%Y-%m-%d'), @num := @num + 1 from tab1 where num < 12
)
select dt from tab1
;

参考资料

https://forums.mysql.com/read.php?20,622725,623607#msg-623607
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2
https://blog.csdn.net/nayi_224/article/details/82147786