在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;

写入数据：

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, '网络和信息安全管理部');

树状结构：

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

实现

查询

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;  

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

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

语句解析

• 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|网络和信息安全管理部 |

• t2：该子查询初始化一个用户定义变量@pids，并为其赋予一个名为groupId

pids|
----+
   2|

• 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        |

• 使用where子句过滤结果，只包括那些ischild不等于0的行

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

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;

• 使用了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|

代码递归

    @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;
    }
    
    //方式二，使用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;
}
    

[{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=网络和信息安全管理部}]