利用mysql的自定义函数实现父子级数据的递归查询
1. 简单的自下而上的查询
编辑自定义函数(自下而上实现递归查询)
delimiter $$ // 自定义结束符
drop function if exists get_parent_list$$ //删除同名的函数
create function get_parent_list(in_id varchar(100)) returns LONGTEXT // in_id 为输入参数 返回 longtext格式数据
begin
declare ids LONGTEXT; // 定义返回值
declare tempid varchar(100);
set tempid = in_id;
while tempid <> '-1' do
set ids = CONCAT_WS(',',ids,tempid);
select parent_id into tempid from organization where id = tempid;
end while;
return ids;
end
$$
delimiter ;
调动上述查询:
SELECT * FROM organization where FIND_IN_SET(id,get_parent_list('d322649cd48d4934bc79eb0059b053ec'))
此操作相对简单,因为自下而上查询都是单一的查询方式
现在我们加大难度,将传入的参数修改为数组,即多个参数传入查询
2. 多个参数自下而上递归
delimiter $$
drop function if exists get_parent_list$$
create function get_parent_list(in_id varchar(100)) returns LONGTEXT
begin
declare ids LONGTEXT;
declare tempids LONGTEXT;
set tempids = in_id;
while FIND_IN_SET('-1',tempids)=0 do
set ids = CONCAT_WS(',',ids,tempids);
select GROUP_CONCAT(parent_id) into tempids from organization where FIND_IN_SET(id,tempids)>0;
end while;
return ids;
end
$$
delimiter ;
FIND_IN_SET作为代替in作为判断依据
SELECT * FROM organization where FIND_IN_SET(id,get_parent_list('d322649cd48d4934bc79eb0059b053ec,f5cb76a3f74c46dcbd1a6e05f3733945'))