依然是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
纯手打,欢迎拍砖,也希望大哥大姐们能解决我上述不理解的疑惑,感谢~