当前位置：首页 > news >正文

Mysql 递归查询所有子节点，hutool树形结构封装

news 2025/8/3 8:29:38

工作中经常会有像目录，部门的多级结构，记录一下查询自己点的方式，留着复制粘贴

方式1：

SELECT* 
FROMcus_department 
WHEREFIND_IN_SET( id, @pid ) > 0;UNIONSELECTcd.* 
FROM( SELECT * FROM cus_department WHERE pid IS NOT NULL AND deleted = 0 ) cd,( SELECT @pid := '1742370890433187841' ) pd 
WHEREFIND_IN_SET( pid, @pid ) > 0 AND @pid := concat( @pid, ',', id )

UNION 上方包括了自己，只查下级可去掉
需要替换的有
表名：cus_department
查询条件：deleted = 0
父id字段名称：pid
主键名称：id

方式2：

SELECT* 
FROM(SELECTc1.*,IF( FIND_IN_SET( pid, @parent_ids ) > 0, @parent_ids := CONCAT( @parent_ids, ',', id ), '0' ) AS ischild FROM( SELECT * FROM cus_department AS cd WHERE cd.deleted = 0  ORDER BY cd.id ASC ) c1,( SELECT @parent_ids := '1742370890433187841' ) c2 ) c3 
WHEREischild != '0'

此方式只查下级

需要替换的有
表名：cus_department
查询条件：deleted = 0
父id字段名称：pid
主键名称：id

方式3：

	SELECTrd.* 
FROM( SELECT * FROM cus_department WHERE pid IS NOT NULL AND deleted = 0 ) rd,( SELECT @p_id := '1742370890433187841' ) pd 
WHEREFIND_IN_SET( pid, @p_id ) > 0 AND @p_id := concat( @p_id, ',', id )

此方式只查下级
需要替换的有：
表名：cus_department
查询条件：deleted = 0
父id字段名称：pid
主键名称：id

使用hutool快速构建树形结构

引入依赖

<dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.7.17</version></dependency>

代码

      //配置TreeNodeConfig treeNodeConfig = new TreeNodeConfig();treeNodeConfig.setIdKey("id");//设置父类idtreeNodeConfig.setParentIdKey("pid");//设置排序字段treeNodeConfig.setWeightKey("orderr");//设置树形结构子类treeNodeConfig.setChildrenKey("children");List<Tree<String>> treesNodes = TreeUtil.build(cusDepartments, "0", treeNodeConfig,(result, tree) -> {//设置idtree.setId(String.valueOf(result.getId()));tree.setParentId(String.valueOf(result.getPid()));tree.setName(result.getName());//设置其他字段tree.putExtra("level", result.getLevel());tree.putExtra("createTime",DateUtil.format(result.getCreateTime(), "yyyy-MM-dd"));tree.putExtra("orderr", result.getOrderr());});