mysql5.0自定义存储过程实现递归

一、mysql自带递归函数

mysql8.0以上才有查询树形结构数据的递归函数RECURSIVE，比如需求是查询一张树形结构表的级别名称上下层级关系路径：

WITH RECURSIVE subordinates AS (
  SELECT id, region_name, parent_id, CAST(region_name AS CHAR(200)) AS path
  FROM mr_industry_region
  WHERE parent_id  = -1 and id = 67652
    UNION all 
  SELECT e.id, e.region_name, e.parent_id, CONCAT(s.path, '/', e.region_name)
  FROM mr_industry_region e
  JOIN subordinates s ON e.parent_id = s.id
)
SELECT id, region_name, path
FROM subordinates;

图中红色的部分替换为需要查询的表即可，字段也根据需要替换

核心字段是id，parent_id

region_name则是级别名称字段

subordinates是递归中用到的临时表的别名，可随意替换

以上sql查询的结果如下

可以看到mysql8.0以上借助函数RECURSIVE实现递归查询还是比较简单的

二、自定义存储过程

8.0以下要想完全通过sql实现递归查询，则需要通过存储过程来实现，使用游标实现递归向下查询

实现递归的存储过程如下

CREATE PROCEDURE get_tree_recursive(IN current_id INT, IN inputLevel INT,IN path VARCHAR(255))
BEGIN
    -- 自定义结束标志
    DECLARE done BOOLEAN  DEFAULT FALSE;
    -- 自定义游标赋值变量
    DECLARE currentId INT;
    DECLARE regionName varchar(255);
    DECLARE parentId INT;
    DECLARE curLevel INT;
    -- 自定义游标
    DECLARE cur CURSOR FOR
        SELECT id, region_name, parent_id,level
        FROM mr_industry_region
        WHERE parent_id = current_id and level = inputLevel;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

        -- 创建临时表
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_mr_industry_region
    (
        id        INT PRIMARY KEY,
        region_name   VARCHAR(255),
        parent_id INT,
                path   VARCHAR(255),
        level     INT
    );

    OPEN cur;

    read_loop:
    LOOP
        FETCH cur INTO currentId, regionName, parentId, curLevel;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO temp_mr_industry_region (id, region_name, parent_id, path,level)
        VALUES (currentId, regionName, parentId,concat(path,'/',regionName), curLevel);
        CALL get_tree_recursive(currentId, inputLevel + 1,concat(path,'/',regionName));
    END LOOP;

    CLOSE cur;

END;

这个存储过程get_tree_recursive用于递归地获取一个层次结构的数据，例如树形结构。以下是存储过程的详细步骤：
 

1.声明变量：
done：布尔变量，作为循环结束的标志，默认为FALSE。
currentId，regionName，parentId，curLevel：用于存储游标取出的当前记录的值。

2.定义游标：
cur：对mr_industry_region表中parent_id等于current_id且level等于inputLevel的记录进行遍历。

3.设置异常处理：
当游标没有更多记录时（NOT FOUND），将done设置为TRUE，退出循环。

4.创建临时表：
temp_mr_industry_region：如果不存在，则创建，用于存储递归过程中获取的树节点信息。

5.打开游标：
OPEN cur：开始遍历游标。

6.读取循环：
read_loop：在循环中，从游标中获取数据并插入到临时表中，同时递归调用自身，将当前节点的子节点添加到路径中。

7.递归调用：
对每个找到的节点，调用get_tree_recursive，传入当前节点的ID，增加的层级（inputLevel + 1）以及更新后的路径（当前路径加上当前节点的名称）。

8.关闭游标：
CLOSE cur：完成循环后关闭游标。

通过这个存储过程，可以构建一个以current_id为根节点，inputLevel指定层级的树结构，并存储在临时表temp_mr_industry_region中。

CREATE PROCEDURE get_tree()
BEGIN
    -- 自定义结束标志
    DECLARE done BOOLEAN DEFAULT FALSE;
    -- 自定义游标赋值变量
    DECLARE currentId INT;
    DECLARE regionName varchar(255);
    DECLARE parentId INT;
    DECLARE curLevel INT;
    -- 自定义游标,查询所有首级进行游标循环
    DECLARE cur CURSOR FOR
        SELECT id, region_name, parent_id, level
        FROM mr_industry_region
        WHERE parent_id = -1;-- 自行修改条件


    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
        

    OPEN cur;
    read_loop:
    LOOP
        FETCH cur INTO currentId, regionName, parentId, curLevel;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO temp_mr_industry_region (id, region_name, parent_id,path, level)
        VALUES (currentId, regionName, parentId,regionName, curLevel);
        CALL get_tree_recursive(currentId, curLevel + 1,regionName);
    END LOOP;
    CLOSE cur;
END;

这个存储过程get_tree与之前的get_tree_recursive有所不同，它首先获取所有顶级节点（即parent_id = -1的节点）并开始递归构建树。以下是其详细步骤：

1.声明变量：
同get_tree_recursive，声明了结束标志done和游标变量currentId，regionName，parentId，curLevel。

2.定义游标：
cur：遍历mr_industry_region表中parent_id为-1的所有记录，这些记录代表树的第一层级。

3.设置异常处理：
当游标没有更多记录时，将done设置为TRUE，退出循环。

4.打开游标：
OPEN cur：开始遍历游标。

5.读取循环：
read_loop：在循环中，从游标中获取数据并插入到临时表temp_mr_industry_region中，同时调用get_tree_recursive进行递归。

6.递归调用：
对每个顶级节点，调用get_tree_recursive，传入当前节点的ID，增加的层级（curLevel + 1）以及节点名称（作为路径的初始部分）。

7.关闭游标：
CLOSE cur：完成循环后关闭游标。

这个存储过程首先获取所有顶级节点，然后对每个顶级节点调用递归存储过程get_tree_recursive，从而构建整个树结构。与get_tree_recursive相比，它不需要传入初始层级inputLevel，因为它是从顶层开始的。

接着执行如下语句：

set SESSION max_sp_recursion_depth=20;

call get_tree();

第一句是mysql要求的递归设置层级深度，session是只在当前sql会话窗口有效，全局的则替换为global，同时账号也需要最高权限，set SESSION max_sp_recursion_depth=20; 我这里设置的是20，不设置默认是0，存储过程无法执行。

第二句则是开始调用存储过程

执行完毕后查看临时表temp_mr_industry_region

注意：level字段如果没有，则把两个存储过程和level有关的字段，参数，游标变量等都 删除，重新执行存储过程即可，数据只能生成一次，重复生成报错。