mysql实现递归查询的函数

1 DELIMITER $$ 2 CREATE DEFINER=`root`@`%` FUNCTION `queryChildrenAreaInfo`(areaIdVal int) RETURNS varchar(4000) CHARSET utf8 3     READS SQL DATA 4 begin  5     DECLARE sTemp varchar(4000); 6     DECLARE sTempChd varchar(4000); 7     set sTemp = '$'; 8     set sTempChd = cast(areaIdVal as char); 9     while sTempChd is not null DO10     set sTemp = concat(sTemp,',',sTempChd);11     select group_concat(areaId) into sTempChd from nms_res_area where find_in_set(parentId, sTempChd) > 0;12     end while;13     return sTemp;14 end$$15 DELIMITER ;

 配合find_in_set函数

1 select * from nms_res_area where find_in_set(areaId, (select queryChildrenAreaInfo(3)));

group_concat(distinct areaId)查找过滤重复的areaId