mysql递归查询所有子级节点 存储结构

CREATE DEFINER=`administrator`@`%` FUNCTION `getChildrenOrgOfHr`(orgid varchar(21845)) RETURNS varchar(21845) CHARSET utf8

BEGIN

DECLARE oTemp VARCHAR(21845);

DECLARE oTempChild VARCHAR(21845);

 

SET oTemp = '';

SET oTempChild = CAST(orgid AS CHAR);

 

WHILE oTempChild IS NOT NULL

DO

SET oTemp = CONCAT(oTemp,',',oTempChild);

SELECT GROUP_CONCAT(id) INTO oTempChild FROM tb_member WHERE FIND_IN_SET(invite,oTempChild) > 0;

END WHILE;

RETURN oTemp;

END

 

运行：

select id from  tb_member where FIND_IN_SET(id,getChildrenOrgOfHr('085cc1a1cb424e3b93db5321621fdbb2'));