Mysql自定义函数替代oracle递归算法start with connect by prior

需求：查询某用户所在部门及其子部门的ID

oracle中的实现：

select depart_id
from t_depart d
start with d.depart_id = (select depart_id from t_user where user_id = 1) connect by d.parent_depart_id = prior d.depart_id

结果：

Mysql中没有改递归算法，只能通过自定义函数实现：

自定义函数：

CREATE DEFINER=`root`@`%` FUNCTION `getRecursionList`(rootIds varchar(1000), tableName varchar(30), recursionType varchar(10)) RETURNS varchar(4000) CHARSET utf8
BEGIN 
	-- 函数作用：oralce递归算法start with connect by prior替代函数
	-- 参数说明：rootIds根节点值（如果多个用,号分割） tableName（表名区分哪个表的递归遍历） recursionType递归类型（parent向父节点递归 child向子节点递归）
	DECLARE sList VARCHAR(4000);
	DECLARE sTemp VARCHAR(4000);
	SET sTemp = rootIds;
	SET tableName = lower(tableName);
	SET recursionType = lower(recursionType);
	WHILE sTemp IS NOT NULL DO
		IF (sList IS NOT NULL) THEN
			SET sList = CONCAT(sList,',',sTemp);
		ELSE
			SET sList = CONCAT(sTemp);
		END IF;
		IF (tableName = 't_depart') THEN
			IF (recursionType = 'parent') THEN
				SELECT GROUP_CONCAT(PARENT_DEPART_ID) INTO sTemp FROM T_DEPART WHERE FIND_IN_SET(DEPART_ID, sTemp)>0;
			ELSE
				SELECT GROUP_CONCAT(DEPART_ID) INTO sTemp FROM T_DEPART WHERE FIND_IN_SET(PARENT_DEPART_ID, sTemp)>0;
			END IF;
		ELSEIF (tableName = 't_business') THEN
			IF (recursionType = 'parent') THEN
				SELECT GROUP_CONCAT(PARENT_BUSINESS_ID) INTO sTemp FROM T_BUSINESS WHERE FIND_IN_SET(BUSINESS_ID, sTemp)>0;
			ELSE
				SELECT GROUP_CONCAT(T_BUSINESS) INTO sTemp FROM T_BUSINESS WHERE FIND_IN_SET(PARENT_BUSINESS_ID, sTemp)>0;
			END IF;
		END IF;
	END WHILE;
	RETURN sList; 
END

使用：

SELECT depart_id
FROM t_depart d
WHERE FIND_IN_SET(d.depart_id, getRecursionList((select depart_id from t_user where user_id = 1), 't_depart', 'child'))

结果：

批量递归子节点的实现示例：

oracle：

Mysql: