转：MySql 树形结构，获取所有的子节点

 

参考地址：https://blog.csdn.net/qiaqia609/article/details/52678393

 

DELIMITER $$

USE `database_xx`$$

DROP FUNCTION IF EXISTS `getChildList`$$

CREATE DEFINER=`数据库用户`@`%` FUNCTION `getChildList`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
DECLARE sChildList VARCHAR(1000); 
DECLARE sChildTemp VARCHAR(1000); 
SET sChildTemp =CAST(rootId AS CHAR); 
WHILE sChildTemp IS NOT NULL DO 
IF (sChildList IS NOT NULL) THEN 
SET sChildList = CONCAT(sChildList,',',sChildTemp); 
ELSE 
SET sChildList = CONCAT(sChildTemp); 
END IF; 
SELECT GROUP_CONCAT(DictID) INTO sChildTemp FROM cdictionary WHERE FIND_IN_SET(ParentDictID,sChildTemp)>0; 
END WHILE; 
RETURN sChildList; 
END$$

DELIMITER ;