Mysql5.* Mysql 8.0* 一条SQL实现树形递归查询

7个月前 教程分享 264°C

这篇文章最后更新于07-07 09:14前,您需要注意相关的内容是否还可用,如有疑问请联系作者!

用户表 user

idusernamepid
1张三0
2李四1
3王五2
4赵六3
5孙七4

拿到id为5的用户,查他的所有上级,如何用一条SQL实现树形的递归查询呢

Mysql5.* @变量迭代递归查询
老版本的Mysql(5.6)通过使用的@变量

SELECT T2.id, T2.`username` FROM (
 SELECT @r AS _id,(
    SELECT @r := pid FROM user WHERE id = _id
 ) AS parent_id,@l := @l + 1 AS lvl
 FROM
  ( SELECT @r := 5, @l := 0 ) vars,user h
 WHERE @r <> 0 )
 T1
 JOIN user T2 ON T1._id = T2.id
ORDER BY
 T1.lvl DESC

Mysql8. 中不能这么用会报警告⚠*
Warning: #1287 Setting user variables within expressions is deprecated and will be removed in a future release. Please set variables in separate statements instead.

Mysql8.* CTE递归查询
Mysql8版本的支持了CTE递归查询的语法

 WITH RECURSIVE parent_cte AS (
    SELECT * FROM user WHERE id=91
        UNION ALL
        SELECT u.* FROM user u INNER JOIN parent_cte parent_cte2 ON u.id = parent_cte2.pid
 ) SELECT id,username FROM parent_cte ORDER BY id ASC

本地Mysql8.* ,线上服务器Mysql5.6,在不改动数据库版本的情况下,使用用户自定义函数Function 实现树形递归

Mysql Function函数
// 在Mysql中创建getParentList函数

CREATE DEFINER=`root`@`%` FUNCTION `getParentList`(rootId varchar(100)) RETURNS varchar(1000) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
BEGIN
    DECLARE fid varchar(100) default '';
    DECLARE str varchar(1000) default rootId;
    WHILE rootId is not null  do
     SET fid =(SELECT PID FROM user WHERE ID = rootId);
     IF fid is not null THEN
         SET str = concat(str, ',', fid);
         SET rootId = fid;
     ELSE
         SET rootId = fid; 
     END IF;
END WHILE;
return str;
END
// 业务中直接调用函数
SELECT * from user where FIND_IN_SET(id,getParentList(5));
   
CTE
CTE,全名 Common Table Expressions

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

cte1, cte2 为我们定义的CTE,可以在当前查询中引用

递归查询
先来看下递归查询的语法

WITH RECURSIVE cte_name AS
(
    SELECT ...      -- return initial row set
    UNION ALL / UNION DISTINCT
    SELECT ...      -- return additional row sets
)
SELECT * FROM cte;

定义一个CTE,这个CTE 最终的结果集就是我们想要的 ”递归得到的树结构",RECURSIVE 代表当前 CTE 是递归的
第一个SELECT 为 “初始结果集”
第二个SELECT 为递归部分,利用 "初始结果集/上一次递归返回的结果集" 进行查询得到 “新的结果集”
直到递归部分结果集返回为null,查询结束
最终UNION ALL 会将上述步骤中的所有结果集合并(UNION DISTINCT 会进行去重),再通过 SELECT * FROM cte; 拿到所有的结果集
可以参考下MySQL开发文档:
https://dev.mysql.com/doc/refman/8.0/en/with.html
https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-examples
文章来源于:https://blog.tius.cn/archives/260

版权说明:本站所提供的文章、图片等内容均为用户发布或互联网整理而来,仅供学习参考,如有侵犯您的版权,请联系我们客服人员删除。
本文链接:https://huue.cc/index.php/archives/920.html
最后编辑: 21年07月07日

网友评论

4天前

大佬啥时候参与max/routing的开发啊,把功能加到routing中 https://github.com/topyao/max-routing

6天前

11111

1个月前

1111

1个月前

1

2个月前

e21e12e12

2个月前

菊花残,满腚伤。ଲ