探索 MySQL 递归查询，优雅的给树结构分页

最新推荐文章于 2024-05-20 14:31:07 发布
最新推荐文章于 2024-05-20 14:31:07 发布
阅读量5 收藏
点赞数
文章标签： mysql 数据库
原文链接：https://mp.weixin.qq.com/s?__biz=MzUxOTc4NjEyMw==&mid=2247578010&idx=2&sn=8ab8d490149319c5e84b78ab46c37c11&chksm=f8782442826c1c375eb27ff7b0f54d106a7a8691061f1a17482d398434329d976b63a49aaaaa&scene=126&sessionid=0
版权

👉 这是一个或许对你有用的社群

🐱 一对一交流/面试小册/简历优化/求职解惑，欢迎加入「芋道快速开发平台」知识星球。下面是星球提供的部分资料： 

fcc5b24e64e362fb516f1ef288ff9e68.gif

👉这是一个或许对你有用的开源项目

国产 Star 破 10w+ 的开源项目，前端包括管理后台 + 微信小程序，后端支持单体和微服务架构。

功能涵盖 RBAC 权限、SaaS 多租户、数据权限、商城、支付、工作流、大屏报表、微信公众号等等功能：

  • Boot 仓库：https://gitee.com/zhijiantianya/ruoyi-vue-pro

  • Cloud 仓库：https://gitee.com/zhijiantianya/yudao-cloud

  • 视频教程：https://doc.iocoder.cn

【国内首批】支持 JDK 21 + SpringBoot 3.2.2、JDK 8 + Spring Boot 2.7.18 双版本 

来源：juejin.cn/post/
7300174977168441378

一、概述

递归查询是一种在数据库中处理具有层级结构数据的技术。它通过在查询语句中嵌套引用自身，以实现对嵌套数据的查询。递归查询在处理树状结构、父子关系或层级关系的数据时非常有用。

在MySQL中，递归查询可以使用WITH RECURSIVE语句来实现。该语句允许我们定义一个递归查询，并在查询中引用自身。

递归查询通常包含两个部分：基础查询 和递归查询 。

7828f6df68505dc9857f2df8442da6e5.png

  • 基础查询是指查询的起始点，它返回递归查询中的初始结果集。

  • 递归查询部分定义了如何从基础查询的结果集中继续查询下一层的数据，直到满足终止条件为止。

“

注意：MySQL是在8.0 才引入的窗口函数功能；属于MySQL8的新特性

基于 Spring Boot + MyBatis Plus + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

  • 项目地址：https://github.com/YunaiV/ruoyi-vue-pro

  • 视频教程：https://doc.iocoder.cn/video/

二、结构

“

递归查询通常包含以下几个关键元素：

814e4975d331565acc0db013aa3fcb18.png

  • 初始查询 （Anchor Query）：这是递归查询的起点，返回初始结果集。它是递归查询的第一步。

  • 递归查询 （Recursive Query）：这是递归查询的核心部分，它引用自身并定义了如何从上一层的结果集中继续查询下一层的数据。递归查询通常包含一个递归关系，通过引用父节点与子节点之间的关联来构建数据的层级结构。

  • 终止条件 （Termination Condition）：这是递归查询的结束条件，用于指定何时停止递归查询。终止条件通常是基于已查询的数据的某种条件或限制。

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

  • 项目地址：https://github.com/YunaiV/yudao-cloud

  • 视频教程：https://doc.iocoder.cn/video/

三、递归查询的执行过程

递归查询的执行过程如下：

1552474477d4a8e1237c25be84e143bb.png

  1. 执行初始查询，获取初始结果集。

  2. 将初始结果集作为递归查询的输入，执行递归查询，并将结果集与初始结果集合并。

  3. 重复执行递归查询，直到满足终止条件为止。

四、递归查询的应用场景

递归查询在许多应用场景中都是非常有用的。以下是一些常见的递归查询的应用场景：

f28bfd8955a742b75080b7884547526d.png

“

注意：以上内容只是递归查询的一些常见应用场景，实际上，递归查询可以适用于任何具有层级或递归结构的数据。通过合理地设计和应用递归查询，可以更轻松地处理复杂的数据关系和层次结构，提供更高效和灵活的数据访问和分析能力。

五、一个案例演示递归查询

为了更好的认识递归查询，这里使用一个简单的组织架构来演示一下递归查询是怎么实现的。

5.1 创建一个组织架构表

CREATE TABLE `organization` (
  `org_id` int NOT NULL COMMENT '主键',
  `org_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '组织名称',
  `parent_id` int DEFAULT NULL COMMENT '父组织id',
  `org_level` int DEFAULT NULL COMMENT '组织级别',
  PRIMARY KEY (`org_id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `organization_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `organization` (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='公司组织架构';

5.2 在这个组织架构表里面插入一些数据

INSERT INTO `organization`(`org_id`, `org_name`, `parent_id`, `org_level`) VALUES (1, '集团总部', NULL, 1);
INSERT INTO `organization`(`org_id`, `org_name`, `parent_id`, `org_level`) VALUES (2, '华北分公司', 1, 2);
INSERT INTO `organization`(`org_id`, `org_name`, `parent_id`, `org_level`) VALUES (3, '华南分公司', 1, 2);
INSERT INTO `organization`(`org_id`, `org_name`, `parent_id`, `org_level`) VALUES (4, '华北-北京公司', 2, 3);
INSERT INTO `organization`(`org_id`, `org_name`, `parent_id`, `org_level`) VALUES (5, '华北-内蒙公司', 2, 3);
INSERT INTO `organization`(`org_id`, `org_name`, `parent_id`, `org_level`) VALUES (6, '华南-广州公司', 3, 3);
INSERT INTO `organization`(`org_id`, `org_name`, `parent_id`, `org_level`) VALUES (7, '华南-深圳公司', 3, 3);

5.3 使用递归查询分页查看我们的组织架构

WITH RECURSIVE RecursiveOrganization AS (
  SELECT org_id, org_name, parent_id, org_level
  FROM organization
  WHERE parent_id IS NULL  -- 查找根节点
  UNION ALL
  SELECT o.org_id, o.org_name, o.parent_id, o.org_level
  FROM organization o
  INNER JOIN RecursiveOrganization ro ON ro.org_id = o.parent_id
)
SELECT org_id, org_name, parent_id, org_level
FROM RecursiveOrganization
ORDER BY org_id
LIMIT 2 OFFSET 0;  -- 设置每页的条目数量和偏移量

解析一下这个SQL：

  • 首先，使用WITH RECURSIVE子句创建了一个名为RecursiveOrganization的递归查询视图。在初始查询部分，通过WHERE parent_id IS NULL条件查找根节点，选择了根节点的组织信息（org_id, org_name, parent_id, org_level）。

  • 然后，使用UNION ALL和INNER JOIN将递归查询与organization表连接起来，逐级递归获取下级组织的信息。通过SELECT o.org_id, o.org_name, o.parent_id, o.org_level选择下级组织的信息，并使用ON ro.org_id = o.parent_id指定连接条件。

  • 最后，从RecursiveOrganization视图中选择所需的组织架构数据，并使用ORDER BY对结果按org_id进行排序。通过LIMIT和OFFSET可以设置每页的条目数量和偏移量，实现分页查询。

六、总结

递归查询在处理父子结构、树状结构或层级关系的数据时非常有用。它允许我们轻松地查询所有层级的数据，无论层级有多深。递归查询还可以用于处理分页查询、路径查询、层级计算等各种复杂的查询需求。

需要注意的是，递归查询可能会占用较多的系统资源，并且在处理大型数据集时可能会导致性能问题。因此，在使用递归查询时，需要谨慎设计和优化查询，以确保查询的效率和性能。

欢迎加入我的知识星球，全面提升技术能力。

👉 加入方式，“长按”或“扫描”下方二维码噢：

fba58c79b7a089885cba4dbe9da5be69.png

星球的内容包括：项目实战、面试招聘、源码解析、学习路线。

3d16b457b35fafeeb03fe9cade6dc870.png

ef163a1765ef47f035ff5ffcf35b4516.pngfed4afeeef2186dc0e07e2a76421da05.png41fe7560b1d41e498de8159fa20a3a9b.png1560dec7ae00f48866ea6f885470b154.png

文章有帮助的话，在看，转发吧。
谢谢支持哟 (*^__^*）
Java基基
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
使用递归删除树形结构的所有子节点(java和mysql实现)
08-29
下面小编就为大家带来一篇使用递归删除树形结构的所有子节点(java和mysql实现)。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧
利用java+mysql递归实现拼接树形JSON列表的方法示例
10-19
主要给大家介绍了关于利用java+mysql递归实现拼接树形JSON列表的方法，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面跟着小编来一起看看吧。
Oracle递归树形结构查询功能
09-09
oracle树状结构查询即层次递归查询，是sql语句经常用到的，在实际开发中组织结构实现及其层次化实现功能也是经常遇到的。这篇文章给大家介绍了Oracle递归树形结构查询功能，需要的朋友参考下
Mysql树形递归查询的实现方法
12-16
前言 对于数据库中的树形结构数据，如部门表，有时候，我们需要知道某部门的所有下属部分或者某部分的所有上级部门，这时候就需要用到mysql的递归查询 最近在做项目迁移，Oracle版本的迁到Mysql版本，遇到有些oracle的函数，mysql并没有，所以就只好想自定义函数或者找到替换函数的方法进行改造。 Oracle递归查询 oracle实现递归查询的话，就可以使用start with … connect by connect by递归查询基本语法是： select 1 from 表格 start with … connect by prior id = pId start with
PostgreSQL树形结构的递归查询示例
09-09
主要给大家介绍了关于PostgreSQL树形结构的递归查询的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用PostgreSQL具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧
mysql 递归查询树结构_Mysql递归查询树型结构实现代码
weixin_42425947的博客
01-27 154
本节内容：mysql递归查询表结构为：分类Id SortId所属分类Id ParentID分类名称 SortName分类描述 SortDesc测试数据：1000 0 A类 A类1001 1000 A类_1 A类_11002 1000 A类_1 A类_11003 1001 A类_1_1 A类_1_12000 0 B类 B类2001 2000 B类_1 B类_12002 2001 B类_1_...
mysql树型结构查询父类函数，mysql递归查询父类函数
蕃薯耀的博客
10-10 995
。一、MySQL 中创建函数时出现这种错误的解决方法。二、创建函数查询父节点。mysql树型结构查询父类函数
探索 MySQL 递归查询，优雅的给树结构分页!
03-13 1285
探索 MySQL 递归查询，优雅的给树结构分页!
mysql树形结构分页_非递归法实现论坛树型结构及分页！！（心血结晶啊，呵呵）...
weixin_39710251的博客
01-20 651
简介：这是非递归法实现论坛树型结构及分页！！(心血结晶啊，呵呵)的详细页面，介绍了和php,有关的知识、技巧、经验，和一些php源码等。 class='pingjiaF' frameborder='0' src='http://biancheng.dnbcw.info/pingjia.php?id=327695' scrolling='no'>现将本人的实践结果show给大家，不足之处就是分...
【MySQL精通之路】管理和实用程序
Anakki的博客
05-19 179
8 mysql_migrate_keyring——keyring密钥迁移实用程序。2 innocchecksum——离线InnoDB文件校验和实用程序。7 mysql_config_editor——mysql配置工具。9 mysqlbinlog——用于处理二进制日志文件的实用程序。6 myisampack——生成压缩的只读MyISAM表。4 myisamchk——MyISAM表格维护实用程序。5 myisamlog——显示MyISAM日志文件内容。3 myisam_ftdump——显示全文索引信息。
MySQL--增、删、改、查，
weixin_65476290的博客
05-15 1007
数据库的概述、发展、现状、历史、分类MySQL关系型数据库、架构（C/S）window系统安装MySQL数据库Linux系统【选学】数据库对象——数据库（database）show、create、drop命令数据库对象——表（table）数据类型数据库表的约束表结构的调整（alter）数据库授权和权限管理（grant）数据库的编码情况【编码一致，建议统一采用utf8mb4】
mysql高版本导入低版本Unknown collation: utf8mb4_0900_ai_ci
susu1083018911的博客
05-15 180
mysql高版本导入低版本Unknown collation: utf8mb4_0900_ai_ci
js遇到需要正则匹配来修改img标签+清除行内样式
阿乐的博客
05-14 331
遇到特殊的情况就是自带样式，那么方法一就不能用了，因为只会算是从原有字符串上面多加字符串。这个正则表达式就是匹配所有的。//踩坑完毕，可以直接使用。第二行代码按自己需要改改。
一条sql语句在mysql中是怎么执行的
m0_59925573的博客
05-17 663
连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。
【Golang】 MySQL 在 Go 语言驱动程序中常用参数
tr6666的博客
05-15 322
在 Go 语言中，我们使用数据库驱动程序来连接和操作数据库。对于 MySQL，我们通常使用 go-sql-driver/mysql 驱动程序。在使用这个驱动程序时，我们需要提供一个数据源名称（DSN），它是一个包含数据库连接信息的字符串。在这个字符串中，我们可以设置很多参数来控制驱动程序的行为。本文将介绍一些常用的参数。在 MySQL 的 Go 语言驱动程序中，我们可以通过设置 DSN 中的参数来控制驱动程序的行为。这些参数可以影响到连接的建立，数据的读写，以及数据的解析等各个方面。
Mysql利用AI生成问题完成目标
最新发布
kkaagf的博客
05-20 273
帮我创建2个表student与score表，要求student表有id,createDate,userName,phone,age,sex,introduce，帮我创建一个修改score表scoreName的触发器，当修改scoreName的时候触发，判断修改的scoreName是否是数学，如果是数学就改成(天书)。帮我根据两个DDL创建一个插入信息的存储过程，先插入10条学生表信息，再根据插入的学生表信息插入100条对应studentId的score成绩，所有信息随机生成，学生表的自增序号从6开始。
Mysql-事务
2302_77099705的博客
05-17 331
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。例如银行转账。默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。
MySQL-InnoDB数据存储结构
我姓谭却弹不死你的博客
05-13 878
MySQL-InnoDB数据存储结构
数据库基础MySql
2301_78085386的博客
05-14 763
●数据(结构)定义语言DDL(Data Definition Language)，是用于创 建和修改数据库表结构的语言。●常用的语句：create ,alter,drop,rename。
mysql递归查询树结构
02-28
MySQL递归查询树结构是指在MySQL数据库中，通过使用递归查询语句来查询树形结构的数据。树形结构是一种常见的数据结构，例如组织架构、分类目录等都可以表示为树形结构。 在MySQL中，可以使用递归查询语句来查询树形结构的数据。递归查询语句使用了WITH RECURSIVE关键字来定义递归查询，并通过递归调用自身来实现对树形结构的遍历。 下面是一个示例，假设有一个名为categories的表，其中包含了id、name和parent_id字段，用于表示分类目录的树形结构： ``` CREATE TABLE categories ( id INT PRIMARY KEY, name VARCHAR(50), parent_id INT ); INSERT INTO categories (id, name, parent_id) VALUES (1, '电子产品', NULL), (2, '手机', 1), (3, '电脑', 1), (4, '苹果手机', 2), (5, '小米手机', 2), (6, '华为手机', 2), (7, '苹果电脑', 3), (8, '联想电脑', 3); ``` 要查询整个分类目录的树形结构，可以使用以下递归查询语句： ``` WITH RECURSIVE category_tree AS ( SELECT id, name, parent_id, 0 AS level FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id, ct.level + 1 FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id ) SELECT * FROM category_tree; ``` 这个查询语句中，首先定义了一个递归查询表category_tree，初始查询是根节点（parent_id为NULL的节点），然后通过UNION ALL和JOIN操作递归查询子节点，直到查询完整个树形结构。 通过执行以上查询语句，可以得到如下结果： ``` id | name | parent_id | level --+--+--+------ 1 | 电子产品 | NULL | 0 2 | 手机 | 1 | 1 3 | 电脑 | 1 | 1 4 | 苹果手机 | 2 | 2 5 | 小米手机 | 2 | 2 6 | 华为手机 | 2 | 2 7 | 苹果电脑 | 3 | 2 8 | 联想电脑 | 3 | 2 ``` 这样就可以通过递归查询语句来获取整个树形结构的数据。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值