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

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;

 这个存储过程实现了递归调用，其中level字段是层级字段，没有的话可以删除，参数也一并删除即可，region_name对应的还是级别名称，替换为自己的表级别名称字段

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（）

执行语句如下：

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有关的字段，参数，游标变量等都 删除，重新执行存储过程即可，数据只能生成一次，重复生成报错。