都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