mysql8 实现connect by语法

迈不过友情╰ 2021-09-30 09:36 618阅读 0赞

都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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70]

简单说明一下。  
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][https_dev.mysql.com_doc_refman_8.0_en_with.html]

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

更详细的讲解我建议直接看这篇oracle的文章。我暂时还没找到更好的讲解递归with的文章  
[https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2][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单纯是为了增加可读性**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70 1]

#### 按深度排序 ####

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

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
    ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70 2]

#### 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
    ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70 3]

### 参考资料 ###

[https://forums.mysql.com/read.php?20,622725,623607\#msg-623607][https_forums.mysql.com_read.php_20_622725_623607_msg-623607]  
[http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html][http_www.artfulsoftware.com_mysqlbook_sampler_mysqled1ch20.html]  
[https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2][https_oracle-base.com_articles_11g_recursive-subquery-factoring-11gr2]  
[https://blog.csdn.net/nayi\_224/article/details/82147786][https_blog.csdn.net_nayi_224_article_details_82147786]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70]: /images/20210923/9b555ad5992947058fec1a9761fc7eb3.png
[https_dev.mysql.com_doc_refman_8.0_en_with.html]: https://dev.mysql.com/doc/refman/8.0/en/with.html
[https_blog.csdn.net_nayi_224_article_details_82147786]: https://blog.csdn.net/nayi_224/article/details/82147786
[https_oracle-base.com_articles_11g_recursive-subquery-factoring-11gr2]: https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70 1]: /images/20210923/947d2b0dec394a368cec0f4702158bb5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70 2]: /images/20210923/acc1187469694dd3bc384cc9e374352f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25heWlfMjI0_size_16_color_FFFFFF_t_70 3]: /images/20210923/54f5426fabfd4f269d0690ecd6cc2ed4.png
[https_forums.mysql.com_read.php_20_622725_623607_msg-623607]: https://forums.mysql.com/read.php?20,622725,623607#msg-623607
[http_www.artfulsoftware.com_mysqlbook_sampler_mysqled1ch20.html]: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html