在MySQL中，如何轻松找到所有子节点？

背景

项目中遇到一个需求，何轻要求查出菜单节点的松找所有节点，在网上查了一下，到所点大多数的有节方法用到了存储过程，由于线上环境不能随便添加存储过程。何轻

因此在这里采用类似递归的松找方法对组织下的所有子节点进行查询。

准备

创建组织表：

复制CREATE TABLE groups ( `group_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 组织ID,到所点 `parent_id` int(11) DEFAULT NULL COMMENT 父节点ID, `group_name` varchar(128) DEFAULT NULL COMMENT 组织名称, PRIMARY KEY (`group_id`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;1.2.3.4.5.6.

写入数据：

复制INSERT INTO groups VALUES (0, null, 系统管理组织); INSERT INTO groups VALUES (1, 0, 中国电信股份有限公司); INSERT INTO groups VALUES (2, 1, 万州分公司); INSERT INTO groups VALUES (3, 1, 涪陵分公司); INSERT INTO groups VALUES (4, 2, 龙都支局); INSERT INTO groups VALUES (5, 2, 新田支局); INSERT INTO groups VALUES (6, 3, 马武支局); INSERT INTO groups VALUES (7, 3, 南沱支局); INSERT INTO groups VALUES (8, 4, 党群工作部); INSERT INTO groups VALUES (9, 5, 客户服务部); INSERT INTO groups VALUES (10, 6, 采购和供应链管理事业部); INSERT INTO groups VALUES (11, 7, 网络和信息安全管理部);1.2.3.4.5.6.7.8.9.10.11.12.

树状结构：

复制- 系统管理组织 - 中国电信股份有限公司 - 万州分公司 - 龙都支局 - 党群工作部 - 新田支局 - 客户服务部 - 涪陵分公司 - 马武支局 - 采购和供应链管理事业部 - 南沱支局 - 网络和信息安全管理部1.2.3.4.5.6.7.8.9.10.11.12.

实现

查询

复制select group_id,group_name from ( select t1.group_id, t1.parent_id, t1.group_name, t2.pids, if(find_in_set(parent_id, @pids) > 0,@pids := concat(@pids, ,, group_id),0) as ischild from (select group_id,parent_id,group_name from `groups` ) t1, (select @pids := #{groupId} as pids) t2) t3 where ischild != 0;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.

比如，要查询的有节万州分公司下所有子节点,只需将#{groupId}变更为万州分公司的高防服务器组织ID即可：

复制group_id|group_name| --------+----------+ 4|龙都支局 | 5|新田支局 | 8|党群工作部 | 9|客户服务部 |1.2.3.4.5.6.

语句解析

t1：该子查询从groups表中选择group_id，parent_id，何轻group_name 复制group_id|parent_id|group_name | --------+---------+-----------+ 0| |系统管理组织 | 1| 0|中国电信股份有限公司 | 2| 1|万州分公司 | 3| 1|涪陵分公司 | 4| 2|龙都支局 | 5| 2|新田支局 | 6| 3|马武支局 | 7| 3|南沱支局 | 8| 4|党群工作部 | 9| 5|客户服务部 | 10| 6|采购和供应链管理事业部| 11| 7|网络和信息安全管理部 |1.2.3.4.5.6.7.8.9.10.11.12.13.14. t2：该子查询初始化一个用户定义变量@pids，松找并为其赋予一个名为groupId 复制pids| ----+ 2|1.2.3. if(find_in_set(parent_id,到所点 @pids) > 0,@pids := concat(@pids, ,, group_id),0)：这一部分使用find_in_set函数检查parent_id是否存在于@pids变量中。如果存在，有节则将当前group_id添加到@pids变量并返回；否则返回0 复制group_id|parent_id|group_name |pids|ischild | --------+---------+-----------+----+---------+ 0| |系统管理组织 | 2|0 | 1| 0|中国电信股份有限公司 | 2|0 | 2| 1|万州分公司 | 2|0 | 3| 1|涪陵分公司 | 2|0 | 4| 2|龙都支局 | 2|2,何轻4 | 5| 2|新田支局 | 2|2,4,5 | 6| 3|马武支局 | 2|0 | 7| 3|南沱支局 | 2|0 | 8| 4|党群工作部 | 2|2,4,5,8 | 9| 5|客户服务部 | 2|2,4,5,8,9| 10| 6|采购和供应链管理事业部| 2|0 | 11| 7|网络和信息安全管理部 | 2|0 |1.2.3.4.5.6.7.8.9.10.11.12.13.14. 使用where子句过滤结果，只包括那些ischild不等于0的松找行 复制group_id|group_name| --------+----------+ 4|龙都支局 | 5|新田支局 | 8|党群工作部 | 9|客户服务部 |1.2.3.4.5.6.

MySQL 8.0版本

引入了通用表表达式（CTE），可以使用CTE来进行递归查询

复制WITH RECURSIVE subordinates AS ( SELECT group_id,到所点 group_name, parent_id FROM groups WHERE parent_id = 2 -- 指定父节点ID UNION ALL SELECT g.group_id, g.group_name, g.parent_id FROM groups g INNER JOIN subordinates s ON s.group_id = g.parent_id ) SELECT * FROM subordinates;1.2.3.4.5.6.7.8.9.10.11.12. 使用了WITH RECURSIVE子句，它创建了一个名为subordinates的递归公共表达式（CTE）从groups表中选择group_id，服务器托管group_name和parent_id字段，其中parent_id = 2，也就是选择parent_id=2直接子组将groups表（别名为g）与subordinates（别名为s）进行内连接。连接条件是g的parent_id等于s的group_id。这意味着我们正在查找先前找到的每个子组的子组从subordinates中选择所有行 复制group_id|group_name|parent_id| --------+----------+---------+ 4|龙都支局 | 2| 5|新田支局 | 2| 8|党群工作部 | 4| 9|客户服务部 | 5|1.2.3.4.5.6.

代码递归

复制@Test public void test1() { List<Map<String, Object>> groupList = new ArrayList<>(); groupList = queryListParentId(2,groupList); System.out.println(groupList); groupList.clear(); System.out.println("====================="); List<String>list = new ArrayList<>(); list.add("3"); groupList = queryListParentId2(list,groupList); System.out.println(groupList); } //方式一，循环遍历查询 public List<Map<String, Object>> queryListParentId(Integer parentId,List<Map<String, Object>> groupList) { String sql = "select group_id,group_name from groups where parent_id = "+ parentId; List<Map<String, Object>> list = jdbcTemplate.queryForList(sql); if(!CollectionUtils.isEmpty(list)){ groupList.addAll(list); for (Map<String, Object> map : list){ queryListParentId((Integer) map.get("group_id"),groupList); } } return groupList; } //方式二，b2b信息网使用find_in_set函数 public List<Map<String, Object>> queryListParentId2(List<String> parentId,List<Map<String, Object>> groupList) { String join = String.join(",", parentId); String sql = "select group_id,group_name from groups where find_in_set(parent_id,"+ join+")"; List<Map<String, Object>> list = jdbcTemplate.queryForList(sql); if(!CollectionUtils.isEmpty(list)){ groupList.addAll(list); List<String> collect = list.stream().map(map -> map.get("group_id")+"").collect(Collectors.toList()); queryListParentId2(collect,groupList); } return groupList; }1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40. 复制[{group_id=4, group_name=龙都支局}, {group_id=5, group_name=新田支局}, {group_id=8, group_name=党群工作部}, {group_id=9, group_name=客户服务部}] ===================== [{group_id=6, group_name=马武支局}, {group_id=7, group_name=南沱支局}, {group_id=10, group_name=采购和供应链管理事业部}, {group_id=11, group_name=网络和信息安全管理部}]1.2.3.