mysql 维护层级树字段 hierarchy (修改挂载的父节点使用代码维护hierarchy字段)

「爱情、让人受尽委屈。」 2023-06-16 04:56 37阅读 0赞

### 一 表结构 ###

CREATE TABLE `sys_user` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `parent_id` bigint(255) NOT NULL COMMENT '父节点id',
      `hierarchy` varchar(255) DEFAULT NULL COMMENT '层级关系',
      `name` varchar(255) NOT NULL COMMENT '用户名',
      `password` varchar(255) NOT NULL COMMENT '用户密码',
      `enabled` tinyint(255) NOT NULL DEFAULT '1' COMMENT '是否可用(0:停用 1:可用)',
      `phone` bigint(255) NOT NULL DEFAULT '0' COMMENT '手机号',
      `store_id` bigint(20) DEFAULT NULL COMMENT '门店id',
      `create_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
      `deleted` tinyint(2) NOT NULL DEFAULT '0' COMMENT '逻辑删(0:未删,1已删)',
      `user_type_enums` int(255) DEFAULT NULL COMMENT '角色(1:学员 2:教师 4:班主任)',
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE KEY `un_password` (`password`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;
    
    CREATE DEFINER=`root`@`%` TRIGGER `sys_user_hierarchy_triger` BEFORE INSERT ON `sys_user` FOR EACH ROW BEGIN
    
        DECLARE _hierarchy VARCHAR(255);
    
        IF (new.parent_id is not NULL) THEN
    
            SET _hierarchy = (
                SELECT hierarchy
                FROM sys_user
                WHERE id = new.parent_id
            );
    
            SET new.hierarchy = CONCAT(_hierarchy, new.parent_id, '/');
    
        ELSE
    
            SET new.hierarchy = '/';
    
        END
            IF;
    
    
    END;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjUwMTIy_size_16_color_FFFFFF_t_70]  
查询子节点的时候

//查询子节点需要加根节点的id 使用likeRight可以利用到索引
    IPage<SysUser> page = this.sysUserService.page(new Page<>(pagingVO.getPage(), pagingVO.getLimit()), new LambdaQueryWrapper<SysUser>()
     .likeRight(StringUtils.isNotEmpty(vo.getHierarchy()), SysUser::getHierarchy, vo.getHierarchy() + vo.getId())

新增时利用触发器自动维护hierarchy层级字段  
触发器为  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjUwMTIy_size_16_color_FFFFFF_t_70 1]  
触发器维护hierarchy

BEGIN
    
        DECLARE _hierarchy VARCHAR(255);
    
        IF (new.parent_id is not NULL) THEN
    
            SET _hierarchy = (
                SELECT hierarchy
                FROM sys_user
                WHERE id = new.parent_id
            );
    
            SET new.hierarchy = CONCAT(_hierarchy, new.parent_id, '/');
    
        ELSE
    
            SET new.hierarchy = '/';
    
        END
            IF;
    
    
    END

修改是代码维护改层级hierarchy字段 自己写的模板代码生成

##定义Dao名
    #set($daoName = $!tool.append($!tool.firstLowerCase($!tableInfo.name), "Dao"))
    
    
        /** * 改变了父节点 所有子节点的层级字段需要维护 * * @author $!author * @Date $!time.currTime() * @param id 节点自己 * @param newParentId 新的父节点 * @return java.lang.Boolean 是否成功 **/
        @Override
        @Transactional(rollbackFor = Exception.class)
        public Boolean updateHierarchy(Long id, Long newParentId) { 
            //父节点data
            $!{ tableInfo.name}Entity byId = $tool.append("this.",$daoName,".selectById(id)");
            if (Objects.isNull(byId)) { 
                throw new SZException("修改的数据不存在!");
            }
            //校验, 修改的父节点不能挂到自己本身
            if (byId.getId().equals(newParentId)) { 
                throw new SZException("不能挂载到本身或本身的子节点!");
            }
            //查询需要维护的子节点
            List<$!{ tableInfo.name}Entity> entitys = $tool.append("this.",$daoName,".selectList(new LambdaQueryWrapper<",$!{ tableInfo.name},"Entity>()")
                    .likeRight($!{ tableInfo.name}Entity::getHierarchy, byId.getHierarchy() + byId.getId()));
            //校验 , 修改的父节点不能是自己的子节点
            List<Long> collect = entitys.stream()
                    .map($!{ tableInfo.name}Entity::getId)
                    .collect(Collectors.toList());
            if (collect.contains(newParentId)) { 
               throw new SZException("不能挂载到本身的子节点!");
            }
            //旧的父节点
            $!{ tableInfo.name}Entity oldParent = $tool.append("this.",$daoName,".selectById(byId.getParentId());")
            //新的父节点
            $!{ tableInfo.name}Entity newParent = $tool.append("this.",$daoName,".selectById(newParentId);")
            //0是根节点是存在的
            if (0 != newParentId) { 
                if (Objects.isNull(newParent)) { 
                    throw new SZException("挂载的父节点不存在");
                }
            }
            //维护父节点parentId
            $!{ tableInfo.name}Entity updateParent = new $!{ tableInfo.name}Entity();
            updateParent.setId(byId.getId());
            updateParent.setHierarchy(byId.getHierarchy());
            updateParent.setParentId(newParentId);
            //维护节点的parentId
            $tool.append("this.",$daoName,".update(updateParent, new LambdaUpdateWrapper<",$!{ tableInfo.name},"Entity>()")
                    .eq($!{ tableInfo.name}Entity::getId, byId.getId()));
            //根节点加入维护hierarchy
            entitys.add(updateParent);
            //维护节点的所有子节点的hierarchy
            entitys.forEach(entity -> { 
                $!{ tableInfo.name}Entity updataEntity = new $!{ tableInfo.name}Entity();
                String olds ="";
                if (!Objects.isNull(oldParent) || byId.getParentId() == 0) { 
                    if (byId.getParentId() == 0) { 
                        olds ="/";
                    } else { 
                        olds = oldParent.getHierarchy() + oldParent.getId() + "/";
                    }
                }
                //旧的Hierarchy去掉父节点以上层级
                String s = null;
                if (StringUtils.startsWith(entity.getHierarchy(),olds)) { 
                    s = entity.getHierarchy().substring(olds.length());
                }
                //说明是根节点
                if (null == s || "/".equals(s)) { 
                    s = "";
                }
                //根节点没有id = 0的节点
                if (Objects.isNull(newParent)) { 
                    updataEntity.setHierarchy("/" + s);
                } else { 
                    updataEntity.setHierarchy(newParent.getHierarchy() + newParent.getId() + "/" + s);
                }
                //更新需要维护的hierarchy
                $tool.append("this.",$daoName,".update(updataEntity, new LambdaUpdateWrapper<",$!{ tableInfo.name},"Entity>()")
                        .eq($!{ tableInfo.name}Entity::getId, entity.getId()));
            });
            return true;
        }
    
        /** * 获取该节点的所有父节点 * @author $!author * @Date $!time.currTime() * @return java.util.List<java.lang.Long> 父节点集合 **/
         List<Long> getParentIds(String hierarchy) { 
            return Arrays.stream(hierarchy.split("/"))
                    .filter(StringUtils::isNotBlank)
                    .map(Long::parseLong)
                    .collect(Collectors.toList());
        }
        
        /** 限制层级最多为10 int hierarchyDeep = getHierarchyDeep(byId.getHierarchy()); if (hierarchyDeep == 10) { return Result.error("最多挂载10层,无法继续挂载子节点"); } **/
        /** * 根据树路径获取层级 * @author $!author * @Date $!time.currTime() * @return 层级 */
        private int getHierarchyDeep(String hierarchy) { 
            return (int) (Arrays.stream(hierarchy.split("/"))
                    .filter(StringUtils::isNotBlank).count() + 1);
        }

在sql里面获取一些层级关系

# 获取id=5的层级关系 result:/1/2/3/4/ 图一
    select hierarchy from sys_user where id = 5;
    
    # 转换获取层级 result:,1,2,3,4, 图二
    select replace((select hierarchy from sys_user where id = 5), '/', ',');
    
    # 切割首尾逗号 result:1,2,3,4 图三
    SELECT trim(BOTH ',' FROM (select replace((select hierarchy from sys_user where id = 5), '/', ','))) as str;
    
    # 利用mysql自带help_topic进行字符串转行结构 图四
    SELECT DISTINCT substring_index(substring_index(a.str, ',', b.help_topic_id + 1), ',', - 1)
    FROM (SELECT trim(BOTH ',' FROM (select replace((select hierarchy from sys_user where id = 5), '/', ','))) as str) a
             INNER JOIN mysql.help_topic b
                        ON b.help_topic_id < (length(a.str) - length(replace(a.str, ',', '')) + 1);
    
    # 获取所有父节点数据 图五
    select *
    from sys_user
    where id in (
        SELECT DISTINCT substring_index(substring_index(a.str, ',', b.help_topic_id + 1), ',', - 1)
        FROM (SELECT trim(BOTH ',' FROM (select replace((select hierarchy from sys_user where id = 5), '/', ','))) as str) a
                 INNER JOIN mysql.help_topic b
                            ON b.help_topic_id < (length(a.str) - length(replace(a.str, ',', '')) + 1)
    )

下面的sql 是看到做的笔记, 和维护层级树不相关(不相关的sql , GROUP\_CONCAT收集结果集)

# xxx:yjx2,xxx11:yjx3,xxx3:yjx4,xxx4:yjx5,xxx5:yjx6,xxx6:yjx7
    select GROUP_CONCAT(name, ':', password)
    from sys_user
    where name like 'xx%';
    
    #获取ids 2,3,4,5,6,7
    select GROUP_CONCAT(id) as str
    from sys_user
    where name like 'xx%';
    
    # 使用获取的ids获取行
    select *
    from sys_user
    where id in (
        SELECT DISTINCT substring_index(substring_index(a.str, ',', b.help_topic_id + 1), ',', - 1)
        FROM (select GROUP_CONCAT(id) as str from sys_user where name like 'xx%') a
                 INNER JOIN mysql.help_topic b
                            ON b.help_topic_id < (length(a.str) - length(replace(a.str, ',', '')) + 1));

图一 : 获取id=5的层级关系 result:/1/2/3/4/  
![在这里插入图片描述][2019112417110418.png]  
图二 : 转换获取层级 result:,1,2,3,4,  
![在这里插入图片描述][20191124171043959.png]  
图三 : 切割首尾逗号 result:1,2,3,4  
![在这里插入图片描述][20191124171031378.png]  
图四 : 利用mysql自带help\_topic进行字符串转行结构  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjUwMTIy_size_16_color_FFFFFF_t_70 2]  
图五 : 获取所有父节点数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjUwMTIy_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjUwMTIy_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191124173259468.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjUwMTIy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjUwMTIy_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2019112417390214.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjUwMTIy,size_16,color_FFFFFF,t_70
[2019112417110418.png]: https://img-blog.csdnimg.cn/2019112417110418.png
[20191124171043959.png]: https://img-blog.csdnimg.cn/20191124171043959.png
[20191124171031378.png]: https://img-blog.csdnimg.cn/20191124171031378.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjUwMTIy_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191124171015616.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjUwMTIy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjUwMTIy_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191124173337332.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjUwMTIy,size_16,color_FFFFFF,t_70