利用find_in_set()函数和group_concat()函数实现递归查询:
drop function if exists queryChildrenAreaInfo;
create function queryChildrenAreaInfo(areaId int)
returns varchar(4000)
begin
declare sTemp varchar(4000);
declare sTempChd varchar(4000);
set sTemp = '$';
set sTempChd = CAST(areaId as char);
while sTempChd is not null do
set sTemp = CONCAT(sTemp,',',sTempChd);
select GROUP_CONCAT(id) into sTempChd from t_areainfo where FIND_IN_SET(parentId,sTempChd) > 0;
end while;