MySQL存储函数实现树形结构递归查询

核心逻辑准备

在构建树形结构数据表时，一般包含三个关键字段：自增主键 i_id、父节点ID p_id 以及名称等属性。为了验证后续脚本，我们需要一张具备层级关系的数据表及示例数据。

表结构定义如下：

CREATE TABLE `city` (
`i_id` INT(11) NOT NULL AUTO_INCREMENT ,
`p_id` INT(11) NULL DEFAULT NULL ,
`c_name` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询的核心依赖于两个MySQL内置字符串处理函数：

1. GROUP_CONCAT(i_id)：将多行记录的某一列合并为以逗号分隔的单行字符串。例如查询父ID为2的所有直接子节点ID：

SELECT GROUP_CONCAT(i_id) FROM city WHERE p_id='2';

2. FIND_IN_SET(p_id, 'id_list')：判断字段值是否存在于指定的逗号分隔字符串列表中，返回位置索引（大于0表示存在）。这是实现层级递归的关键：

SELECT * FROM city WHERE FIND_IN_SET(p_id, '1,4');

递归查找存储函数的实现

为了实现从根节点到叶子节点的完整递归，我们将分步构建并演示存储函数的写法。

第1步：只查第一级下级

这一步是基础查询，用于测试数据关联是否正确。仅找出直接下属节点。

SELECT GROUP_CONCAT(i_id) AS ids FROM city WHERE p_id=1;

第2步：创建查询单级子节点的存储函数

创建一个名为 getSubNodes 的函数，输入一个节点ID，输出该节点下所有子节点的ID字符串（仅限下一层，但为了方便演示递归逻辑可复用此模式）。

CREATE DEFINER = `root`@`localhost` FUNCTION `getSubNodes`(param_i_id INT)
RETURNS VARCHAR(100)
BEGIN
    DECLARE strSubIds VARCHAR(100);
    DECLARE strPid VARCHAR(100);
    
    -- 初始化变量
    SET strSubIds = '$';
    SET strPid = CAST(param_i_id AS CHAR);
    
    -- 查询当前节点的一级子节点
    SELECT GROUP_CONCAT(i_id) INTO strSubIds FROM city WHERE p_id=strPid;
    
    RETURN strSubIds;
END;

调用该函数即可得到指定节点的一级子节点集合：

第3步：创建查询所有下级节点的完整递归函数

这是最终方案。逻辑为：先获取一级子节点存入临时表或变量，然后检查这些子节点是否还有子节点（即查询它们的 p_id 是否在上一级的结果集中），如果有，则合并结果继续循环，直到没有新的子节点为止。

CREATE DEFINER = `root`@`localhost` FUNCTION `getAllSubNodes`(param_i_id INT)
RETURNS VARCHAR(100)
BEGIN
    DECLARE strAllSubIds VARCHAR(100);
    DECLARE strTempPid VARCHAR(100);
    
    -- 1. 获取第一级子节点
    SET strAllSubIds = getSubNodes(param_i_id);
    
    -- 2. 初始临时变量记录当前层级找到的节点
    SET strTempPid = strAllSubIds;
    
    -- 3. 循环查找更深层级的子节点
    WHILE strTempPid IS NOT NULL DO
        -- 在上一步找到的所有节点中，继续找它们的子节点
        SELECT GROUP_CONCAT(i_id) INTO strTempPid 
        FROM city 
        WHERE FIND_IN_SET(p_id, strTempPid) > 0;
        
        -- 如果找到了更深一层的子节点，将其追加到总结果中
        IF (strTempPid IS NOT NULL AND strTempPid != '') THEN
            SET strAllSubIds = CONCAT(strAllSubIds, ',', strTempPid);
        END IF;
    END WHILE;
    
    RETURN strAllSubIds;
END;

执行完毕后，该函数返回以逗号分隔的该节点下所有子孙节点的 ID 列表：

使用场景与说明

在业务代码或复杂视图查询中，只需像调用普通函数一样传入根节点ID：
SELECT getAllSubNodes(1);
即可获得完整的树形展开路径。相比应用层多次发起SQL查询，数据库层面的批量字符串处理具有更低的网络开销。

以上就是MySQL递归查找树形结构（存储函数法）的详细内容，更多关于MySQL存储过程优化递归操作的资料请关注本站其它相关文章！