mysql遍历子节点_MySQL 树节点递归遍历所以子节点

DELIMITER $$

DROP FUNCTION IF EXISTS `getChildList`$$

CREATE FUNCTION `getChildList`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8 READS SQL DATA

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(`deptId`) INTO `sTempChd` FROM dept WHERE FIND_IN_SET(`parentId`,`sTempChd`)>0;

END WHILE;

RETURN sTemp;

END$$

DELIMITER ;

SELECT getChildList(1);

-- 获取父ID

DELIMITER $$

DROP FUNCTION IF EXISTS `getParList`$$

CREATE FUNCTION `getParList`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8 READS SQL DATA

BEGIN

DECLARE sTemp VARCHAR(1000);

DECLARE sTempPar VARCHAR(1000);

SET sTemp = '';

SET sTempPar =rootId;

WHILE sTempPar is not null DO

SET sTemp = concat(sTemp,',',sTempPar);

SELECT group_concat(parent_id) INTO sTempPar FROM doc where parent_id<>id and FIND_IN_SET(id,sTempPar)>0;

END WHILE;

RETURN sTemp;

END$$

DELIMITER ;