mysql巧妙化解递归查询树形数据 | 纯sql

Mysql 查询子节点所有的父节点(用法+详细解释) 子节点向上查询 阅读详情

本文正在参加「技术专题19期 漫谈数据库技术」活动

前言

  • 开发中树形结构应该是很常见的一种数据结构了。而在数据库方面往往也都伴随相应的树形设计。在 mysql 中通过 parent_id 来绑定其上游,从而达到树形结构的存储,但是在查询的过程中就需要我们将 List 列表转成我们理想中的 Tree 树。

构建树

List<Location>locations = this.baseMapper.selectList(queryWrapper);
Map<String, List<Location>> collect = locations.stream().collect(Collectors.groupingBy(Location::getId));
List<QueryLocationDto> resultList = new ArrayList<>();
List<Location> parentLocation = getParentLocation(1, collect, id);
if (CollectionUtils.isNotEmpty(parentLocation)) {
    for (Location location : parentLocation) {
        QueryLocationDto dto = new QueryLocationDto();
        BeanUtils.copyProperties(location, dto);
        resultList.add(dto);
    }
}
复制代码
private List<Location> getParentLocation(int level, Map<String, List<Location>> collect, String id) {
        List<Location>locationList = new ArrayList<>();
        if (collect.containsKey(id)) {
            Location location = collect.get(id).get(0);
            locationList.add(location);
            String superid = location.getSuperid();
            locationList.addAll(getParentLocation(level + 1, collect, superid));
        }
        return locationList;
    }
复制代码
  • 相信大部分我们 都是通过 Java 来处理的。 其中 getParentLocation 就是用递归不断的去构建上下级关系。这种方式也是我比较推荐的,因为这样就把职责分的很清楚 Java 负责处理业务 ,数据库 就仅仅用来做数据的持久化,这也方便我们对数据库切换与升级。否则在更换其他数据库时还需要考虑是否支持递归属性。
public <T> List<OperatorTree> getTree(List<T> parentList , Map<?, List<T>> collectMap, OperatorTreeInterface operatorTreeInterface) throws InvocationTargetException, IllegalAccessException, NoSuchMethodException {
        if (org.apache.commons.collections4.CollectionUtils.isEmpty(parentList)) {
            return new ArrayList<>();
        }
        List<OperatorTree> list = new ArrayList<>();
        OperatorMapping operatorMapping = operatorTreeInterface.operatorMapping();
        for (T t : parentList) {
            OperatorTree tree = new OperatorTree();
            tree.setId(BeanUtils.getProperty(t, operatorMapping.getId()));
            tree.setLabel(BeanUtils.getProperty(t, operatorMapping.getLabel()));
            tree.setRealData(t);
            if (collectMap.containsKey(tree.getId())) {
                List<T> ts = collectMap.getOrDefault((tree.getId()), new ArrayList<>());
                tree.setChildren(getTree(ts, collectMap, operatorTreeInterface));
            } else {
                try {
                    List<T> ts = collectMap.getOrDefault(Long.valueOf(tree.getId()), new ArrayList<>());
                    tree.setChildren(getTree(ts, collectMap, operatorTreeInterface));
                } catch (Exception e) {
                    tree.setChildren(getTree(new ArrayList<>(), collectMap, operatorTreeInterface));
                }
            }
            list.add(tree);
        }
        return list;
    }
复制代码
  • 有的时候为了能够量产化,这里我也针对项目的场景利用泛型进行一次封装。有兴趣可以自己针对自己的项目场景进行封装。这样树形与列表的转换就无须每次冗余了。然而今天我们的重点是如何利用 mysql 来实现递归查询, 虽然这种方式个人不推荐使用,但我们还是需要了解 mysql 的特性的。

image-20221111151153226.png

  • 如图我们先在数据库中创建一张 test 表, 表里构建一棵树出来。 祖父→父亲→孙子 。

请根据选中节点查询其上游关系

  • 啥意思呢?就是根据你我想找到你家血脉关系。

image-20221111152248057.png

  • 这个业务本身没有难点,我们只需要一步一步的去寻找即可。但是从技术层面上看就有点搞头了。首先我们不确定是多少层,这就无法通过代码堆砌的方法进行实现了。只能动态的去递归查询。
SELECT t2.id, t2.`name`,t1.lvl
FROM
    (
        SELECT 
        @r AS _id,
        (SELECT @r := parent_id FROM test WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
        FROM
        (SELECT @r := 3, @l := 0) vars, test AS h
        WHERE @r <> 0 
    ) t1
JOIN test t2
ON t1._id = t2.Id
order by t1.lvl
复制代码

image-20221111152741090.png

  • 通过上述代码我们即可完成家族血脉的查询。能够清楚的查出家族的具体支脉。

解析

  • 上面的 sql 虽然能够实现递归,但是好像和我们平时接触的 sql 不大一样。这里我们简单分析下
  • 首先 mysql 是支持我们用户定义对象的。比如下面这个 sql
set @name = '123';
select @name from dual;
复制代码
  • 执行上面的sql 你会发现查出来的就是 123 。 这就是使用到 mysql 中变量定义功能。 通过 @ 来进行对象的定义以及赋值。

image-20221111153933916.png

  • 首先 mysql的执行顺序是由里及外 。 就是说越在内部越先执行。所以针对上面的 SQL 我们将它整理下顺序能够得出。

image-20221111154225881.png

  • 注意 (SELECT @r := parent_id FROM test WHERE id = _id) AS parent_id, 这段sql 实际上归属于第二层。图示中并没有列出,太占地方了。

第三层

  • 有了上面的两个知识点铺垫,现在我们直接看 SELECT @r := 3, @l := 0 是不是就很容易明白了呢? r、l 就是 mysql 中定义出来的两个变量。分别是3,0 。和我们上面演示案列中 select @name from dual 是一个意思。
  • 至于 (SELECT @r := 3, @l := 0) vars, test AS h这段就更好理解了。test本身就是一张物理表 , 我们定义出来的两个变量也可以理解成一张表。在mysql中查询两种表是为了区分开来正常都是需要各自取别名的。所以我们定义的变量就是 vars 别名 。 test 别名 h。

第二层

SELECT 
        @r AS _id,
        (SELECT @r := parent_id FROM test WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
        FROM
        (SELECT @r := 3, @l := 0) vars, test AS h
        WHERE @r <> 0 
复制代码
  • 第二层可以说是递归的核心 。这里我们需要明白 上述代码为什么会产生递归的效果呢?首先我们知道 mysql 存在 左外连接、右外连接、内连接 。除了这三种还有一种就是 笛卡尔积连接。什么意思呢?

image-20221111160800485.png

  • 也就是说上面我们的sql FROM (SELECT @r := 3, @l := 0) vars, test AS h 就是为了给我们构建出一个笛卡尔积 ,而我们定义的变量其实就是一张表里的一条数据,所以这里就是将 test 表所有记录都提取出来。

image-20221111161500413.png

image-20221111160800485.png

  • 也就是说我们第二层的sql 就是将 test表全部查出来,然后将其字段进行扩展。说白了这种方式并不是一种真正的递归。但是因为引入了一个变量,所以在扩充的时候和递归一个效果。
  • 但是笛卡尔积查询出来的数据是无法保证两张表的关联性的,所以我们并没有将 h 表相关字段查出来,因为那根本没用。剩下的就是我们一开始说的一步一步的查询扩充字段了

第一层

  • 第一层就简单很多了 ,因为第二层就已经查询出来相关的树形数据了,但是因为第二层使用的笛卡尔积 h 表信息没法使用,仅仅使用了其数量。那么我们的名称这个时候还没有,第一层的作用就是将节点名称扩充出来。

总结

  • 最后我们简单总结下,mysql 的查询递归正常使用存储过程来实现。但是上面提到的方法巧妙的实现了递归的效果。理论上上述方法和存储过程相比存在一个优点就是不会死循环。

本文正在参加「技术专题19期 漫谈数据库技术」活动

来源:https://juejin.cn/post/7165684702903271437
【PADS-开发指南】 馒头破戒大师 阅读详情

相关推荐

五分钟搞懂游戏开发中的抗锯齿算法

常见抗锯齿算法总结锯齿由来抗锯齿算法SSAAMSAACSAA 锯齿由来 场景的定义在三维空间中是连续的,而最终显示的像素则是一个离散的二维数组,这是计算机屏幕产生锯齿的原因。在计算机处理图形的过程中(渲染管线了解一下),有一个非常重要的阶段,就是光栅化,光栅化主要的作用是将顶点数据的不连续性通过插值计算,将两个顶点之间不存在的点进行弥补,然后实现到屏幕像素点上的一一映射。如图,现在要在屏幕上绘制一...

绿洲守望者 1万+

2020-11-27

MySQL中进行树状所有子节点的查询 转发:https://blog.youkuaiyun.com/ACMAIN_CHM/article/details/4142971 在Oracle 中我们知道有一个 Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但很遗憾,在MySQL的目前版本中还没有对应的功能。 在MySQL中如果是有限的层次,比如我们事先如果可以确定这个树的最大深度是4, 那么所有节点为根的树的深度均不会超过4,则我们可以直接..

sssssscccccc的博客 367

从理论到实践:利用FilterSolutions高效设计7阶椭圆低通滤波器

本文详细介绍了如何利用FilterSolutions软件高效设计7阶椭圆低通滤波器。文章从理论入手,对比了巴特沃斯、切比雪夫等经典滤波器特性,阐述了在要求高衰减陡度(如62dB抑制)和严格通带波纹(0.2dB)时,椭圆滤波器是最优选择。通过分步实战教程,指导读者完成从软件参数设置、性能验证到实际电路布局与调试的全过程,助力射频与高速数字信号处理工程师将理论转化为可靠设计。

jupi8的博客 564

超赞,老外的一种避免递归查询所有子部门的树数据表设计与实现!

文章来源:https://sourl.cn/aCCTwr|目录问题来了查出所有子孙部门查询子孙部门总数判断是否叶子节点要不试试这个方法?查出所有子孙部门查询子孙部门总数判断是否叶子节点其他基本操作完结通常树形结构的存储,是在子节点上存储父节点的编号来确定各节点的父子关系,例如这样的组织结构:与之对应的表数据(department):部门表结构(department)id...

石杉的架构笔记 772

验证码接收平台搭建教程(含源代码)

在很多注册流程中,我们常常需要通过短信验证码验证用户的身份。本教程将手把手教你搭建一个验证码接收平台,用于自动接收并展示来自短信网关或短信转发器的验证码。⚠️ 本教程仅供学习使用,请勿用于任何非法用途!本教程展示了一个简单的验证码接收平台的搭建流程,从后端到前端完整实现。你可以将其用于学习 Web 开发、数据库操作以及 Flask 项目部署。访问我的优快云主页。

weixin_52548902的博客 4087

别再纠结高侧低侧了!用INA199/INA219这类集成电流检测芯片,5分钟搞定精准电流测量

本文详细解析了使用INA199/INA219等集成电流检测芯片实现精准电流测量的高效方案。通过对比传统分立元件方案,突出集成芯片在精度、温度稳定性和设计简化方面的优势,并提供硬件设计避坑指南和软件实现最佳实践,帮助开发者快速掌握电流检测技术。

weixin_42685438的博客 473

MySQL树形结构的数据库表设计和查询

1、邻接表(Adjacency List) 实例:现在有一个要存储一下公司的人员结构,大致层次结构如下: 那么怎么存储这个结构?并且要获取以下信息: 1.查询小天的直接上司。 2.查询老宋管理下的直属员工。 3.查询小天的所有上司。 4.查询老王管理的所有员工。 方案一、(Adjacency List)只存储当前节点的父节点信息。 -- 2018-8-11 MySQL树结构 -...

X-Dragon的博客 3万+

simulink求解器选择的小tip

给出了simulink选择求解器的一种tip,与积分初值有关。

高能阿博特的博客 2057

mysql 树形汇总_SQL中的树状数据整理(Mysql)

I have two tables in my databaseTable A with columns user_id, free_data, used_dataTable B with columns donor_id, receptor_id, share_dataBasically, a user (lets call x) has some data in his account whi...

weixin_29292535的博客 702

Deepseek+Word插件:一键搞定论文翻译与润色的学术写作新革命

本文介绍了Deepseek Word插件如何革新学术写作流程,实现论文翻译与润色的一键操作。该插件深度集成于Word环境,能智能完成中英互译、语法修正、术语统一及逻辑优化,显著提升写作效率与文本质量,是研究者的高效AI助手。

weixin_29252171的博客 518

基于Pascal VOC数据集的YOLOv8目标检测系统实现:人、鸟、猫、牛、电视、车等20类目标识别与UI界面部署全流程详解

VOC 2007、VOC 2012 两个主要版本;每个图像提供 bounding box(目标框)+ 目标类别;共计 20 类,所有图像手工标注,质量高。完整复现 Pascal VOC 20类检测任务成功训练 YOLOv8 模型实现可视化 UI + 实时检测系统支持多平台部署和演示。

YOLO 773

从二叉树到B+树:核心树结构演进、对比与实战选型指南

数据结构是计算机科学的基础,其中树形结构因其高效的查找、插入和删除能力,在数据组织与管理中扮演着核心角色。其核心原理在于通过特定的节点组织规则(如排序、平衡)来减少数据访问的路径长度,从而提升操作效率。这一技术价值在需要快速检索和动态维护有序数据的场景中尤为突出,例如数据库索引、文件系统、内存中的映射容器等。从基础的二叉树、二叉搜索树,到严格平衡的AVL树,再到工程实践中广泛采用的红黑树,其演进逻辑始终围绕着在查询性能与维护成本之间寻求最佳平衡。当数据规模超出内存容量时,为适配磁盘I/O特性而设计的B树与B

weixin_30621959的博客 725

数据预处理

数据预处理 数据预处理(数据清洗)是数据挖掘过程中的重要一步,它主要包括数据清洗,数据集成,数据变换和数据规约。 在我们进行数据挖掘的过程中,大量的原始数据中存在着大量有问题的数据(脏数据)比如有缺失值的,不一致的,有异常的,大量重复的数据等。这些脏数据会严重影响到数据挖掘建模过程的执行效率,也可能会使挖掘结果产生偏差。通过数据预处理(数据清洗)可以提高数据的质量,更好的适应特定的挖掘技术及工具。...

CJW的博客 9931

mysql 树形结构表设计与优化

前言 在诸多的管理类,办公类等系统中,树形结构展示随处可见,以“部门”或"机构"来说,接触过的同学应该都知道,最终展示到页面的效果就是层级结构的那种,下图随机列举了一个部门的树型结构展示图 设计考虑因素 1、表结构设计 稍稍有点开发和表结构设计经验的同学,设计出这样一张表,应该很容易,只需要在depart表中,添加一个pid/字段即可满足要求,参考下表: CREATE TABLE `depart` ( `depart_id` varchar(32) NOT NULL COMMENT '部门ID',

congge 1万+

复分析 阿尔福斯(Ahlfors) 第三版答案 英文

答案分为十章,有部分习题没有答案,全英文复分析阿尔福斯(Ahlfors) 第三版答案

精雕软件(JDpaint) 5.5 绿色版

精雕软件(JDpaint) 5.5 绿色版精雕软件(JDpaint) 5.5 绿色版精雕软件(JDpaint) 5.5 绿色版

MySQL-树型结构数据查询

MySQL-树型结构数据查询

hw2544622045的博客 5961

数学建模模型总结

这是本人在两年内准备参加数学建模竞赛搜集到的模型资料,已经经过了本人的筛选和整理,希望能够对准备参加数学建模竞赛的学弟学妹们提供一些帮助!

mysql 存储过程 树结构_mysql 树形结构查询(存储过程)

就用数据数据库表地址数据(中国地区)来说吧(用Windows 请使用 gbk !!)可直接运行(去除注解)存储过程:DELIMITER//dropprocedureifexistsfindLChild///*iid递归父节点,layer允许递归深度*/CREATEPROCEDUREfindLChild(iidbigint(20),layerbigint(20))BEGIN/*创建接受查询...

weixin_35045323的博客 476

SAP B1中文培訓教材_SDK

公司内部培训资料,对开发工具 ,DIAPI, UIAPI,Add-ON有详细解说

mysql如何实现树状结构_实现树状结构的两种方法

实现树状结构的两种方法实现树状结构的两种方法1。递归法递归是指在函数中显式的调用它自身。利用递归法实现树状结构的特点是写入数据速度较快,显示速度较慢(在树的分支/层次较多的情况下尤其明显)。适用与写入数据量大,树的结构复杂的情况下。数据结构(以mysql为例)代码:-------------------------------------------------------------------...

weixin_34358799的博客 3189

还在用递归查询 MySQL 的树形结构吗?教你一种更好的解决方案!

点击关注公众号,实用技术文章及时了解来源:juejin.cn/post/7076079848824766494通常树形结构的存储,是在子节点上存储父节点的编号来确定各节点的父子关系,例如这样的组织结构:与之对应的表数据(department):部门表结构(department)id部门编号 name部门名称 level所在...

Java知音 2914

mysql数据库递归查询树形结构(适用场景:菜单多级分类,多级关联评论查询),用strea流把list转成树的方法详解

mysql数据库递归查询树形结构(适用场景:菜单多级分类,多级关联评论查询),用strea流把list转成树的方法详解

qq_26112725的博客 9198

MySQL 递归查找树形结构,这个方法太实用了!

点击关注公众号,利用碎片时间学习这两天,遇到了子节点的需求。这里简单做个总结。1、数据库中的树形结构数据库中存贮的数据,以ID和P_ID(父id),来存贮树形结构这样如果需要查找某个节点的子节点,就可以寻找P_ID。如果要查找所有子节点,就需要遍历所有的子节点的子节点。如果要判断是否为同级的节点,就可以查找是否有相同的父节点。2、MySQL中如何查找相应的数据这里,我采用...

Java笔记虾 9716

mysql获取树形结构表全路径表达式

1、表结构如下: CREATE TABLE `t_sys_department` ( `sf_department_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '部门ID', `sf_parent_id` bigint(20) NOT NULL DEFAULT -1 COMMENT '上级部门ID', `sf_department_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_genera

全栈攻城狮JSON的博客 2121

MySQL实现递归查找树形结构

这两天,遇到了子节点的需求.这里简单做个总结. 1.数据库中的树形结构 数据库中存贮的数据,以ID和P_ID(父id),来存贮树形结构 这样如果需要查找某个节点的子节点,就可以寻找P_ID.如果要查找所有子节点,就需要遍历所有的子节点的子节点. 如果要判断是否为同级的节点,就可以查找是否有相同的父节点.   2.MySQL中如何查找相应的数据 这里,我采用的是写一个存贮函数.在查询时...

学习真香 4万+

MYSQL的汇总求和问题

MYSQL的汇总求和问题

wctt的专栏 548

MySQL 常用树形结构设计总结

开发中,经常会遇到树形结构的设计,所谓的树形结构,其实就是数据与关系的设计,本文用来总结常用的树形结构设计 1. 递归表 id pid node_name 1 0 父1 2 0 父2 3 1 子1 4 2 子2 注意: 在数据量少的情况下,一次加载所有节点,无大影响;数据量大的情况,建议懒加载(逐层加载) 2.路径枚举 id pid node_name pids 1 0 父1 ...

lvhui321的博客 2681
上一篇: Java实现异步编程的8种方式
下一篇: static、final、abstract 、interface 、字段、属性、方法、构造器 应用场景分析
码趣资源网
博客等级 码龄4年 269粉丝 518原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值