2019独角兽企业重金招聘Python工程师标准>>> 
#说明：area_id表示当前节点，are_area_id表示父节点
#mysql向下递归查询子节点
CREATE FUNCTION getChildListById(id varchar(32))
RETURNS varchar(4000)
BEGIN
DECLARE str varchar(4000);
DECLARE cid varchar(4000);
DECLARE area_id_str varchar(8000);
select area_id into cid from area where area_id = id;
#GROUP_CONCAT将某一字段的值按指定的字符进行累加，系统默认的分隔符是逗号，默认可以累加的字符长度为1024字节
SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;
SET str = '';
SET area_id_str = null;
WHILE cid is not null DO
SET str = concat(str, ',', cid);
SELECT group_concat(area_id) INTO cid FROM area where FIND_IN_SET(are_area_id, cid) > 0;
END WHILE;
select group_concat(area_id) into area_id_str from area where find_in_set(area_id, str);
RETURN area_id_str;
END;
#mysql向上递归查询子节点
CREATE FUNCTION getParentListById(id varchar(32))
RETURNS varchar(4000)
BEGIN
DECLARE str varchar(4000);
DECLARE cid varchar(4000);
DECLARE area_id_str varchar(8000);
select area_id into cid from area where area_id = id;
#GROUP_CONCAT将某一字段的值按指定的字符进行累加，系统默认的分隔符是逗号，默认可以累加的字符长度为1024字节
SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;
SET str = '';
SET area_id_str = null;
WHILE cid is not null DO
SET str = concat(str, ',', cid);
SELECT group_concat(are_area_id) INTO cid FROM area where FIND_IN_SET(area_id, cid) > 0;
END WHILE;
select group_concat(area_id) into area_id_str from area where find_in_set(area_id, str);
RETURN area_id_str;
END;
函数查询出来的结果是节点id以逗号分隔的字符串(如：a,b,c...形式)
要查询列表可以再使用:
select * from area t where FIND_IN_SET(t.area_id,getParentListById('510100'));
但这种方式查询效率非常低下，可以优化
select * from area t,(select getParentListById('510100') id) a where FIND_IN_SET(t.area_id,a.id);