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

这篇具有很好参考价值的文章主要介绍了mysql数据库递归查询树形结构(适用场景:菜单多级分类,多级关联评论查询),用strea流把list转成树的方法详解。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1.创建表以及数据:

DROP TABLE IF EXISTS `dealer_permissions`;
CREATE TABLE `dealer_permissions`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '模块名称',
  `parent_Id` int(11) NULL DEFAULT NULL COMMENT '父id',
  `operation_value` int(1) NULL DEFAULT NULL COMMENT '操作值',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;



INSERT INTO `dealer_permissions` VALUES (1, '系统', NULL, NULL);
INSERT INTO `dealer_permissions` VALUES (2, '代理商', 1, NULL);
INSERT INTO `dealer_permissions` VALUES (3, '查询', 2, 100);
INSERT INTO `dealer_permissions` VALUES (4, '新增代理商', 2, 101);
INSERT INTO `dealer_permissions` VALUES (5, '密码还原', 2, 102);
INSERT INTO `dealer_permissions` VALUES (7, '修改', 2, 103);
INSERT INTO `dealer_permissions` VALUES (8, '代理商用户详情', 2, 104);
INSERT INTO `dealer_permissions` VALUES (9, '职员', 1, NULL);
INSERT INTO `dealer_permissions` VALUES (10, '查询', 9, 105);
INSERT INTO `dealer_permissions` VALUES (11, '新增', 9, 106);

2.数据以及层次关系:

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

层次关系:

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

现在的需求是把这个层级关系,在前端显示出来,后端的处理方法有两种:

1.直接把全部的数据从数据库中拿到,然后在java代码里面使用树形结构来进行解析,但是这种做法只能在数据量比较小的时候使用,然后数据量一大会造成内存溢出

2.在mysql中创建一个函数,传递一个根节点的id,把对应的所有叶子节点的数据查出来(如果对用上述的图,就是把“系统”的id传递进去,把代理商,职员,以及各自对应的下级找出来)

今天就介绍第二种,在此之前先来的学一下两个函数:

find_in_set 函数

 语法:find_in_set(str,strlist)

select * from dealer_permissions where FIND_IN_SET(id,'1,2,3');

 结果是返回id在表中的记录,即 id = '1' ,id = '2' ,id = '3' 三条记录。

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

可以使用函数:以向下递归查询所有子节点为例。只要找到一个包含当前节点和所有子节点的以逗号拼接的字符串 strlist,传进 find_in_set 函数。就可以查询出所有需要的递归数据了。

group_concat 函数(CONCAT_WS函数也是差不多的功能):

使用该函数,可以把表中的所有 id 以逗号拼接(该函数不只是这个作用,更多用处自行百度)

select GROUP_CONCAT(id) from dealer_permissions;

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

MySQL 自定义函数,实现递归查询

自定义一个函数,通过传入根节点id,找到它的所有子节点:

delimiter $$
drop function if exists get_child_list$$
create function get_child_list(in_id varchar(10)) returns varchar(1000)
begin
    declare ids varchar(1000) default '';
    declare tempids varchar(1000);

    set tempids = in_id;
    while tempids is not null do
            set ids = CONCAT_WS(',',ids,tempids);
            select GROUP_CONCAT(id) into tempids from dealer_permissions where FIND_IN_SET(parent_Id,tempids)>0;
        end while;
    return ids;
end
$$
delimiter ;

(1) delimiter         

        用于定义结束符。我们知道MySQL默认的结束符为分号,表明指令结束并执行。但是在函数体中,有时我们希望遇到分号不结束,因此需要暂时把结束符改为一个随意的其他值。我这里设置为$$ ,用于定义结束符。

(2)drop function if exists get_child_list         

        若函数getchildlist已经存在了,则先删除它。注意这里需要用当前自定义的结束符 来结束并执行语句。 因为,这里需要和下边的函数体单独区分开来。

(3)create function get_child_list

        创建函数。并且参数传入一个根节点的子节点id,需要注意一定要注明参数的类型和长度,如这里是 varchar(10)。然后returns varchar(1000) 用来定义返回值参数类型。

(4)begin 和 end

        中间包围的就是函数体。用来写具体的逻辑。

(5)declare

        用来声明变量,并且可以用 default 设置默认值。 这里定义的 ids 即作为整个函数的返回值,是用来拼接成最终我们需要的以逗号分隔的递归串的。 而 tempids 是为了记录下边 while 循环中临时生成的所有子节点以逗号拼接成的字符串。

(6) set

        用来给变量赋值。此处把传进来的根节点赋值给 tempids 。

(7) while do ... end

        while; 循环语句,循环逻辑包含在内。注意,end while 末尾需要加上分号。 循环体内,先用 CONCAT_WS 函数把最终结果 ids 和 临时生成的 tempids 用逗号拼接起来。 然后以 FIND_IN_SET(parent_Id,tempids)>0 为条件,遍历在 tempids 中的所有 parent_Id,寻找以此为父节点的所有子节点 id ,并且通过 GROUP_CONCAT(id) into tempids 把这些子节点 id 都用逗号拼接起来,并覆盖更新 tempids 。

         等下次循环进来时,就会再次拼接 ids ,并再次查找所有子节点的所有子节点。循环往复,一层一层的向下递归遍历子节点。直到判断 tempids 为空,说明所有子节点都已经遍历完了,就结束整个循环。

        这里,用 '1' 来举例,即是:(参看图1的表数据关系)

        第一次循环:   tempids=1    ids=1    tempids=2,9(1的所有子节点)

        第二次循环:   tempids=2,9     ids=1,2,9    

        tempids=3,4,5,6,7,8,10,11 (2和9 的所有子节点)

         第三次循环:   tempids=3,4,5,6,7,8,10,11    ids=1,2,9,3,4,5,6,7,8,10,11            tempids=3,4,5,6,7,8,10,11的所有子节点 因找不到任何一个子节点,所以                tempids=null,就结束循环。

(8)return ids;

        用于把 ids 作为函数返回值返回。

(9)函数体结束以后,记得用结束符 $$ 来结束整个逻辑,并执行。

(10)最后别忘了,把结束符重新设置为默认的结束符分号 。


 

执行代码:
 

select * from dealer_permissions where FIND_IN_SET(id,get_child_list('1'));

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

可以看到,已经把需要找的数据找到了,然后在java中用代码做相对应的处理即可

手动实现递归查询(向上递归)

相对于向下递归来说,向上递归比较简单。

因为向下递归时,每一层递归一个父节点都对应多个子节点。

而向上递归时,每一层递归一个子节点只对应一个父节点,关系比较单一。

同样的,我们可以定义一个函数 get_parent_list 来获取根节点的所有父节点。

delimiter $$
drop function if exists get_parent_list$$
create function get_parent_list(in_id varchar(10)) returns varchar(1000)
begin
    declare ids varchar(1000);
    declare tempid varchar(10);

    set tempid = in_id;
    while tempid is not null do
            set ids = CONCAT_WS(',',ids,tempid);
            select parent_Id into tempid from dealer_permissions where id=tempid;
    end while;
    return ids;
end
$$
delimiter ;

select * from dealer_permissions where FIND_IN_SET(id,get_parent_list('11'));

执行的结果:

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

注意事项:

我们用到了 group_concat 函数来拼接字符串。但是,需要注意它是有长度限制的,默认为 1024 字节。可以通过 show variables like "group_concat_max_len"; 来查看。

注意,单位是字节,不是字符。在 MySQL 中,单个字母占1个字节,而我们平时用的 utf-8下,一个汉字占3个字节。

这个对于递归查询还是非常致命的。因为一般递归的话,关系层级都比较深,很有可能超过最大长度。(尽管一般拼接的都是数字字符串,即单字节)

所以,我们有两种方法解决这个问题:

1. 修改 MySQL 配置文件 my.cnf ,增加 group_concat_max_len = 102400 #你要的最大长度.

2.执行以下任意一个语句。SET GLOBAL group_concat_max_len=102400; 
  或者
  SET SESSION group_concat_max_len=102400;

        他们的区别在于,global是全局的,任意打开一个新的会话都会生效,但是注意,已经打开的当前会话并不会生效。而 session 是只会在当前会话生效,其他会话不生效。

        共同点是,它们都会在 MySQL 重启之后失效,以配置文件中的配置为准。所以,建议直接修改配置文件。102400 的长度一般也够用了。假设一个id的长度为10个字节,也能拼上一万个id了。

除此之外,使用 group_concat 函数还有一个限制,就是不能同时使用 limit 。如,

本来只想查5条数据来拼接,现在不生效了。

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

不过,如果需要的话,可以通过子查询来实现,

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

 查看自定义函数:

        show function status;        

删除自定义函数:     

        drop function 函数名;

在mybatis-plus中获取结果:

        先把自定义的方法在mysql中执行先,然后我们在这个表对应的mapper文件中:

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

 使用@select注解,把要执行的语句添加进去
 

    /**
     * 获取id下的所有子节点
     */
    @Select("select * from dealer_permissions where FIND_IN_SET(id,get_child_list(#{id}));")
    List<DealerPermissionsModel> getAllByIdLower(@RequestParam(value = "id") Integer id);

通过代码把返回的结果转成树:

// 获取到了1节点下的所有数据(包括本节点)
List<DealerPermissionsModel> allByIdLower = permissionsMapper.getAllByIdLower(1);

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

allByIdLower集合中就包括了以上的数据,现在通过stream流的方式,把结果转成树


先定义一个方法:
 

/**
     * 链表转树
     * @return
     */
    public static List<DealerPermissionsModel> listToTree(DealerPermissionsModel root,List<DealerPermissionsModel> list)
    {
        return list.stream()
                .filter(a -> Objects.equals(a.getParentId(), root.getId()))
                .peek(m -> m.setModelList(listToTree(m, list)))
                .collect(Collectors.toList());
    }

调用方法:

        // 获取到了1节点下的所有数据(包括本节点)
        List<DealerPermissionsModel> allByIdLower = permissionsMapper.getAllByIdLower(1);

        // 排序成树的形式返回
        List<DealerPermissionsModel> collect = allByIdLower
                .stream()
                .filter(a -> a.getParentId() == null)
                .peek(m -> m.setModelList(listToTree(m, allByIdLower)))
                .collect(Collectors.toList());

输出collect:

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

方法的详解: 

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

首先通过:

List<DealerPermissionsModel> collect = allByIdLower
        .stream()
        .filter(a -> a.getParentId() == null)
        .peek(m -> m.setModelList(listToTree(m, allByIdLower)))
        .collect(Collectors.toList());

        上面这段代码会进入了listToTree方法,此时的m是父节点为null的元素,因为经过了

filter(a -> a.getParentId() == null) 筛选,所以第一次进入listToTree方法的肯定是最顶层的父元素(对应到数据中,就是名称为 "系统" 的 数据)

        然后就进入了listToTree,这个时候的listToTree方法的roo节点是名称为 "系统" 的数据,list是全部的数据,然后循环list集合通过filter来寻找list中有哪个元素的父节点等于root节点的id的数据(在这一步实际上就是循环找"系统"元素的一个子节点)循环到list的第二个元素的时候(第二个元素是名称为"代理商"的元素),因为“代理商”的父id是1,系统的id也是1,所以满足元素的父节点的id等于root的id条件,所以就会执行peek方法,然后把当前元素(名称为"代理商"的元素)以及list集合传递到listToTree方法

        再一次进入到listToTree后,此时的root节点就是(名称为"代理商"的元素),然后又进行filter方法来循环查询父节点是代理商的元素,经过循环会找到(id为3,名称为"查询")的元素,然后就会通过peek方法来查询该元素有那个子节点,如果找不到就返回,就这样子递归寻找下去...

以上就是全部内容,如果觉得对您有帮助,可以点个赞👍喔文章来源地址https://www.toymoban.com/news/detail-446497.html

到了这里,关于mysql数据库递归查询树形结构(适用场景:菜单多级分类,多级关联评论查询),用strea流把list转成树的方法详解的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【.net core】yisha框架 SQL SERVER数据库 反向递归查询部门(子查父)

     业务service.cs中ListFilter方法中内容 DepartmentService.cs中内容 mysql数据库中参考mysql 递归语法修改sql

    2024年02月07日
    浏览(46)
  • 数据库加密AES 适用 Mysql Oracle 以及java

    提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 备忘录:加密key为:vU4C!~0_HQtlU_p@ 可采用任意长度为16位的字符串 数据库数据需要对敏感数据进行加密,比如手机号,身份证号以及银行卡号等进行加密。需要一套通用的加密方法可以在java项目以及or

    2024年02月15日
    浏览(49)
  • MySQL数据库——MySQL WHERE:条件查询数据

    在 MySQL 中,如果需要有条件的从数据表中查询数据,可以使用 WHERE 来指定查询条件。 使用 WHERE 的语法格式如下: WHERE 查询条件 查询条件可以是: 带比较运算符和逻辑运算符的查询条件 带 BETWEEN AND 的查询条件 带 IS NULL 的查询条件 带 IN

    2024年02月03日
    浏览(64)
  • MySQL数据库,联合查询

    目录 1. 联合查询 1.1 内查询 1.2 外查询 1.3 自连接 1.4 子查询 1.5 合并查询 联合查询,简单的来讲就是多个表联合起来进行查询。这样的查询在我们实际的开发中会用到很多,因此会用 笛卡尔积 的概念。 啥是笛卡尔积?两张表经过笛卡尔积后得到的新表的列数是两表列数之和

    2023年04月23日
    浏览(76)
  • MySQL数据库,聚合查询

    目录 1、聚合查询 1.1聚合函数 1.1.1count函数 1.1.2sum函数 1.1.3avg函数 1.1.4max函数 1.1.5min函数 1.2group by子句 1.3having 聚合函数查询又叫函数查询,它是通过一些特定的函数根据需求来查询相关的信息,常见的聚合函数为: COUNT - 求总数,不包含某字段为null值 SUM - 求和,不包含某字

    2023年04月21日
    浏览(58)
  • MySQL数据库实验三 MySQL查询

    一、实验项目: MySQL查询。 二、实验目的 掌握MySQL的查询操作。 三、实验内容 (一): 1、查询lineitem表中商品编号(productid)和单价(unitprice),要求消除重复行。 2、计算lineitem表中每条记录的商品金额。 3、显示orders表单笔高于200元的客户号(userid)、成交金额(total

    2024年02月11日
    浏览(56)
  • MySQL数据库:数据库的约束以及数据的聚合、联合查询

    目录 一.关系模型的简要概述 二.数据库的约束  2.1约束类型         2.2NULL约束 2.3 UNIQUE:唯一约束 2.4 默认约束 2.5 PRIMARY KEY:主键约束 2.6 FOREIGN KEY:外键约束 2.7 CHECK约束 三.高效率查询 3.1高效率查询的分类 3.2聚合查询 3.2.1聚合函数 3.2.2 GROUP BY子句 3.2.3HAVING 3.3.联合查询

    2024年02月10日
    浏览(67)
  • 如何查看其他电脑的MYSQL数据库 mysql查询另一个ip数据库

    文章标签 如何查看其他电脑的MYSQL数据库mysql数据库服务器MySQL 文章分类 MySQL数据库 阅读数 143 1.如何通过IP访问MySQL数据库 1.1 改表法 1.2 授权法 2.MySQL数据库基本命令 2.1 基本命令 2.2 注释 2.3 2.4 实践操作 3 小结 1.1 改表法 如果不从远程登陆,可以用 localhost 。这个时候

    2024年01月21日
    浏览(71)
  • MySQL数据库高级查询语句

    基于这两个数据库表格来实现以下实验 concat(x,y)将提供的参数x和y拼接成一个字符串 trim()返回去除指定格式的值 GROUP BY 有一个原则,凡是在 GROUP BY 后面出现的字段,必须在 SELECT 后面出现; 凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段,必须出现在 GROUP BY 后

    2024年02月11日
    浏览(70)
  • MySQL数据库——高级查询语句

    数据库是用来存储数据,更新,查询数据的工具,而查询数据是一个数据库最为核心的功能,数据库是用来承载信息,而信息是用来分析和查看的。所以掌握更为精细化的查询方式是很有必要的。本文将围绕数据的高级查询语句展开。 1.指定指字段进行查询——SELECT 语法:

    2024年02月11日
    浏览(94)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包