MYSQL递归查询,根据子类ID查询所有父类(宇宙第一详细教程)

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MYSQL递归查询,根据子类ID查询所有父类(宇宙第一详细教程)

前言


工作中业务需求碰到了MYSQL递归这个点,网上资料大都抄来抄去,虽然代码的确能运行。但是萌新是真的看不懂这SQL写的什么玩意啊,看底下评论很多人提问皆是未回复,自己狠下心花了一上午时间,把这个点总结整理了一下,希望能给以后工作学习中遇到MYSQL递归一头雾水像我一样的萌新们一点小小的帮助。


数据表结构


id    name  parent_id


1        A        0

2        B        1

3        C        1

4        D        2

5        E        4

6        F        1

7        G        1


注:parent_id 为0表示顶级父节点,即A是顶级父辈,没有父节点


转储SQL表结构及数据


SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------

-- Table structure for demo

-- ----------------------------

DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `parent_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of demo

-- ----------------------------

INSERT INTO `demo` VALUES ('1', 'A', '0');
INSERT INTO `demo` VALUES ('2', 'B', '1');
INSERT INTO `demo` VALUES ('3', 'C', '1');
INSERT INTO `demo` VALUES ('4', 'D', '2');
INSERT INTO `demo` VALUES ('5', 'E', '4');
INSERT INTO `demo` VALUES ('6', 'F', '1');
INSERT INTO `demo` VALUES ('7', 'G', '1');

需求:根据一个子ID,查询所有父类


SQL如下:


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

结果如下


id      name

1         A

2         B

4         D

5         E


分析过程(答疑过程)


我使用将SQL语句拆分的方式,并展示每句SQL运行结果,让各位可以看的更明白,以便于根据你自己的业务需求进行更改


1、先不管T2,先把T1的SQL抽出来看


SELECT 
        @r AS _id, 
        (SELECT @r := parent_id FROM demo WHERE id = _id) AS parent_id, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := 5, @l := 0) vars, 
        demo h 
    WHERE @r <> 0

运行结果



从这里可以看出,其实T1已经找出ID为5节点的所有父节点了,和T2(即demo表)进行左连接,只不过是为了根据Id获取Name而已。而且还可以看出@l其实在整个SQL中并没有什么作用,只是用来标识节点的等级,底级子节点的lvl为1,父节点lvl值越大表示越靠近顶级父节点,想象一下树结构,你就明白了


2、搞明白@r := 5


SELECT @r := 5, @l := 0

运行结果



从这里看出,其实@r,@l 就是一个变量而已,


作为变量自然你可以随便起名,当然也可以随便赋值,改成@a,@b也都是可以的


那这句SQL的意思就出来了,它表示给变量@r赋值,值为5,给@l赋值,值为0


因此,其实整个SQL的意思也明了了就是根据子ID5,查询所有父类


3、搞懂变量@r 值如何变化


@r AS _id,
(SELECT @r := parent_id FROM demo WHERE id = _id) AS parent_id


根据MYSQL执行顺序(在文章末尾处),SQL语句在执行时会先执行From,即会先执行上一步的赋值操作,因此这里的_id值为5,所以上面的SQL等同于如下SQL


SELECT @r := parent_id FROM demo WHERE id = 5

运行结果



这句SQL肯定没人不会,但是有一个点很重要,它是整个执行过程的核心,就是@r := parent_id,它在查询Id为5的节点的父Id时,把这个父Id同时赋给了变量@r。因此@r值改变了,它从5变为了4。


这个时候思路就很清晰了,只要我们设置限制条件,让SQL在@r为0的时候结束循环就OK了


4、<>符号的含义


WHERE @r <> 0

这个就没什么好说的了,<>符号在MYSQL中表示不等于,这就是我们在第四步中所说的限制条件,它限制了@r这个变量不能等于0。所以当@r不等于0时,SQL语句会根据子ID向上查询父ID,又把父ID当做子ID赋值给@r,再次向上查询,直至@r变量的值为0为止。


补充:如果业务不需要,可以完全可以去掉@l变量


SELECT ,
        @a AS _id, 
        (SELECT @a := parent_id FROM demo WHERE id = _id) AS parent_id
    FROM 
        (SELECT @a := 5) vars, 
        demo h 
    WHERE @a <> 0

运行结果



可以看出@l其实在整个SQL中并没有什么作用,只是用来标识节点的等级,这也证实了我们上述第一步的关于@l的解释


因为这个过程很类似于JAVA通过构造树结构向上递归查询的方式,所以这个SQL形式被很多人叫做MYSQL递归查询


文章结尾


关于MYSQL的查询顺序


FROM
ON
JOIN
WHERE
GROUP By
CUBE|ROllUP
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT

最先执行的是FROM操作,最后执行的是LIMIT操作。每个操作都会产生一个虚拟表,该虚拟表作为一个处理的输入


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
SQL 关系型数据库 MySQL
MySQL查询语句优化的十个小技巧!
查询语句的优化是SQL效率优化的一个方式,可以通过优化sql语句来尽量使用已有的索引,避免全表扫描,从而提高查询效率。最近在对项目中的一些sql进行优化,总结整理了一些方法。
9 1
|
4天前
|
数据可视化 关系型数据库 MySQL
百度搜索:蓝易云【MySQL Workbench的使用教程】
总的来说,MySQL Workbench是一款功能强大的MySQL数据库管理工具,适用于数据库开发人员和管理员,能够提高数据库开发和管理的效率。使用时,可以根据具体需求和操作步骤,结合MySQL Workbench的可视化界面进行操作。 买CN2云服务器,免备案服务器,高防服务器,就选蓝易云。百度搜索:蓝易云
24 0
|
20天前
|
存储 关系型数据库 MySQL
如何在MySQL现有表中添加自增ID?
如何在MySQL现有表中添加自增ID?
37 5
如何在MySQL现有表中添加自增ID?
|
24天前
|
SQL 缓存 关系型数据库
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
39 0
|
26天前
|
SQL 关系型数据库 MySQL
(B站动力节点老杜MySQL教程)MySQL课堂笔记-day01.txt
(B站动力节点老杜MySQL教程)MySQL课堂笔记-day01.txt
|
1天前
|
SQL 存储 关系型数据库
MySQL表的增删改查(基础且保姆级的教程)
MySQL表的增删改查(基础且保姆级的教程)
|
1天前
|
关系型数据库 MySQL
Mysql基础第二十天,组合查询
Mysql基础第二十天,组合查询
12 0
Mysql基础第二十天,组合查询
|
1天前
|
存储 SQL 关系型数据库
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
35 0
|
4天前
|
SQL 存储 关系型数据库
Mysql内核查询成本计算
Mysql内核查询成本计算
|
6天前
|
SQL 消息中间件 关系型数据库
Flink查询问题之每秒入库到mysql数量很少如何解决
Apache Flink是由Apache软件基金会开发的开源流处理框架,其核心是用Java和Scala编写的分布式流数据流引擎。本合集提供有关Apache Flink相关技术、使用技巧和最佳实践的资源。