mysql存储过程 递归_MySQL递归查找存储过程

依然是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. 存储过程的调用

CALL selectDisList('id');

数据测试：

根据上述的数据进行测试，首先测试查找id为'57adca6415a414043183326f'下的数据，结果为：

再测试查找id为'57adca6415a414043183326f'下的数据，结果为：

经过对照，测试结果没有问题，到此为止，一切OK。

仍然存在不理解的地方：

1. DELIMITER 中定义分隔符一定要写在  END后

2. SET @@max_sp_recursion_depth = 10; 此处的含义不是很理解。如果没有还会抛出

参考博文：http://blog.csdn.net/u012501459/article/details/12945267

http://www.cnblogs.com/sk-net/archive/2011/09/07/2170224.html

纯手打，欢迎拍砖，也希望大哥大姐们能解决我上述不理解的疑惑，感谢~