包含mysql 递归查询父节点 和子节点

包含mysql 递归查询父节点 和子节点

mysql递归查询，查父集合，查子集合

查子集合 

--drop FUNCTION `getChildList`  
CREATE FUNCTION `getChildList`(rootId varchar(100))   
RETURNS varchar(2000)  
BEGIN   
DECLARE str varchar(2000);  
DECLARE cid varchar(100);   
SET str = '$';   
SET cid = rootId;   
WHILE cid is not null DO   
    SET str = concat(str, ',', cid);   
    SELECT group_concat(id) INTO cid FROM treeNodes where FIND_IN_SET(parentid, cid) > 0;   
END WHILE;   
RETURN str;   
END  

 

select getParentList('001001001');
select * from sbkfwh where FIND_IN_SET(id,getChildList('001001001'))

对上面的改进： 

set global log_bin_trust_function_creators = 1;

CREATE FUNCTION `getChildList`(rootId varchar(100),which_level INTEGER)     
RETURNS varchar(2000)  
BEGIN   
DECLARE str varchar(2000);  这里可能容纳不下，可以打点。
DECLARE cid varchar(100);  这里可能太短，导致返回的str太小，可以大点。
  
DECLARE curr_level integer ;


SET str = '';   
SET cid = rootId;   
set curr_level = 1;

 
WHILE (cid is not null ) and  ( curr_level <=  which_level )   DO   
        
    SELECT group_concat(id) INTO cid FROM users where  FIND_IN_SET(parent_id, cid) > 0;
    set curr_level = curr_level + 1;
    SET str = concat(str, ',', cid);   

   
END WHILE;   
RETURN   trim( BOTH ',' FROM   str );
END  

 

 

 

 

上面程序运行如下：

输入1,1， 结果：4,5,14,15,16,17,18,19,1001,1002,1006,1013,1014

输入1,2 :结果:4,5,14,15,16,17,18,19,1001,1002,1006,1013,1014,7,8