MySql-树形结构：根据父id获取所有子级id

创建函数

CREATE DEFINER=`root`@`%` FUNCTION `getParLst`(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(oid) INTO sTempChd FROM  p_organization where FIND_IN_SET(parens_id,sTempChd)>0;
   	END WHILE;
    RETURN sTemp; 
END

调用函数

select getParLst(1)
返回当前和子级的id