MySQL函数实现递归查询子节点

根据父节点查找所有的子节点

创建函数getChildrenList

SQL语句如下:

delimiter //
CREATE FUNCTION `getChildrenList`(rootId INT)
RETURNS varchar(1000)

BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);

SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);

WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM province where FIND_IN_SET(pid,sTempChd)>0;
END WHILE;
RETURN sTemp;
END //

delimiter ;

执行查询语句:select * from province where FIND_IN_SET(id,getChildrenList(1))