MySQL获取所有子节点

发表于2022-06-20更新于2022-06-20字数统计348阅读时长2分

背景说明

需求：MySQL树形结构，根据指定的节点，获取其下属的所有子节点（包含路径上的枝干节点和叶子节点）；

枝干节点：如果一个节点下还有子节点，则为枝干节点；

叶子节点：如果一个节点下不再有子节点，则为叶子节点。

问题分析

直接自定义MySQL函数getChildList，通过一层while循环，实现对指定节点的所有子节点进行查询。

功能实现

数据表结构

要保证我们的数据表中要有id和parent_id，即数据id和父节点id

字段

数据表

编写查询叶子节点函数 getChildList

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE DEFINER=`root`@`localhost` FUNCTION `getChildList`(`nodeId` int) RETURNS varchar(1000) CHARSET utf8
BEGIN
    DECLARE childList VARCHAR(1000);      # 返回叶子节点结果集
    DECLARE tempChild VARCHAR(1000);      # 临时存放子节点

    SET childList = '';
    SET tempChild = CAST(nodeId as CHAR); # 将int类型转换为String

    WHILE tempChild is not null DO        # 循环，用于查询节点下所有的子节点
        SET childList = CONCAT(childList, ',', tempChild);   # 存入到返回结果中
        SELECT GROUP_CONCAT(id) INTO tempChild FROM n_file_pattern where FIND_IN_SET(father_id, tempChild) > 0 AND delete_flag = 0;   # 查询节点下所有子节点
    END WHILE;
    RETURN SUBSTRING(childList, 2);       # 将返回结果处理，截取掉结果集前面的逗号
END

其中，用到了几个MySQL的系统函数，如：CAST, CONCAT, GROUP_CONCAT, FIND_IN_SET

调用函数

1
select getChildList(1) as childList

查询结果