MySQL实现递归

一 创建函数

--org_id 为函数id
--parent_id为父级Id
--organization 为表名
DROP FUNCTION IF EXISTS queryChildrenOrg;
DELIMITER ;;

CREATE FUNCTION queryChildrenOrg(org_id BIGINT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp='$';
SET sTempChd = CAST(org_id AS CHAR);
WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM organization WHERE FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
;;
DELIMITER ;

--how to Use?
----SELECT * FROM organization WHERE FIND_IN_SET(id,queryChildrenOrg(3642));