MySQL递归查找存储过程

最新推荐文章于 2022-06-17 09:51:52 发布

weixin_34232617

最新推荐文章于 2022-06-17 09:51:52 发布

阅读量72

点赞数

原文链接：https://my.oschina.net/u/1991646/blog/733531

版权

2019独角兽企业重金招聘Python工程师标准>>>

依然是SQL问题，是上篇博客（http://my.oschina.net/u/1991646/blog/731996）的升级版需求（上篇是查询3层，此次是查询所有）：无可避免的最终还是使用了MySQL存储过程。

先说明业务场景：查询出某个节点中的所有下属节点。

表结构和数据为：

id                        superior_id               
57adca6415a414043183326f                            
57adcbb915a4140431833277  57adca6415a414043183326f  
57aef2c115a48464e09f45cb  57adcbb915a4140431833277  
57af13a315a48464e09f45df  57aef2c115a48464e09f45cb  
57af541a15a44d6d4e5a54cf                            
57b014a515a44d6d4e5a54d1                            
57b13b2b15a4535f619d4a13                            
57b1f31f15a49fa67174a623                            
57b28e4e15a49fa67174a627                            
test2                     57adcbb915a4140431833277  
test3                     57adca6415a414043183326f

其中id为主键，superior_id为上级的id。

除去没用的数据，关系如下：

57adca6415a414043183326f
   |-57adcbb915a4140431833277
     |-57aef2c115a48464e09f45cb
        |-57af13a315a48464e09f45df
     |-test2
   |-test3

实现业务场景，和以前一样，也可以使用Java代码实现，一个考虑到性能，另一个考虑到应用服务器的压力，此次使用存储过程实现。

存储过程实现思路：

1. 需要存在一个循环递归条件（由于存在递归，这里通过返回结果来定义）

2. 递归时实时获取新id作为新查询条件（需要使用到游标）

3. 使用临时表存储数据，临时表通过存储过程删除和建立（需要存在两个存储过程，一个负责数据的递归，另一个负责临时表的建立和调用数据递归的存储过程）

思路搞定，开干！！

1. 定义数据递归存储过程

-- 定义数据递归存储过程：selectChildDisList
DELIMITER $$ -- MySQL分隔符定义，默认为; 如果默认为; 在创建存储过程的时候会出错，这里应该成对存在
CREATE PROCEDURE selectChildDisList(IN id VARCHAR(1000))
BEGIN
  DECLARE cache_id VARCHAR(1000) DEFAULT '';
  DECLARE done INTEGER DEFAULT 0;
  DECLARE cursor_dis CURSOR FOR SELECT d.id
                           FROM
                             t_distribut d
                           WHERE
                             d.superior_id = id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 含义是：若没有数据返回,程序继续,并将变量done设为1
  SET @@max_sp_recursion_depth = 10;  
  INSERT INTO tmp_dis_table VALUES (id); -- 插入数据到临时表，其中临时表tmp_dis_table为存储过程selectDisList中定义 
  OPEN cursor_dis; -- 打开游标 
  FETCH cursor_dis INTO cache_id; -- 获取游标当前记录并赋值给 cache_id 变量(FETCH 获取游标当前指针的记录)
  WHILE (done =0)
  DO
    CALL selectChildDisList(cache_id); -- 递归调用自己进行查询
    FETCH cursor_dis INTO cache_id;
  END WHILE;
  CLOSE cursor_dis; -- 关闭游标
END$$ -- 这个符号一定要写这里，不然就会出语法错误，具体问题还不知道
DELIMITER ;

2. 定义调用数据递归存储过程的存储过程

-- 定义调用数据递归存储过程的存储过程：selectDisList
DELIMITER $$
CREATE PROCEDURE selectDisList(IN id VARCHAR(1000))
BEGIN
  -- 这里通过临时表来获取得到的结果集数据
  DROP TEMPORARY TABLE IF EXISTS tmp_dis_table; 
  CREATE TEMPORARY TABLE tmp_dis_table(
	  disId VARCHAR(1000)
	);
  DELETE FROM tmp_dis_table;
  CALL selectChildDisList(id);
  SELECT DISTINCT disId
FROM
  tmp_dis_table ORDER BY disId;
END$$ 
DELIMITER ;

3. 存储过程的调用