Mysql 5 递归

最近在弄SQL server 转MySQL，需要将SQL server 的函数转成MySQL 函数，SQL server 用到了

with cte as （）select * cte 

这是一个递归函数

WITH cte AS (
		SELECT p.*
		FROM
		pt_permission p
		WHERE 
		( p.sys_id = 0 or p.sys_id = #sysId# ) and /* 20160627 cyx*/
		p.parent_id = #parentId# and p.disabled=0 and permission_code not IN('PT_PROJECT_LIST','PT_USER_ADMINLIST','PT_EQU_ENTITY_LIST','DATA_DTU_NEW','DATA_DTU_LAST','DATA_XT_NEW','PT_USER_EXCEL','PT_SMS_COUNT')
		UNION ALL
		SELECT pe.*
		FROM
		pt_permission pe
		INNER JOIN cte c ON c.id = pe.parent_id and pe.disabled=0
		) 
		SELECT
			*
		FROM
			cte;

现在要转成MySQL 实现递归，不过目前我们公司用的MySQL 5 没有with cte as 函数，MySQL 8 倒是有了with cte as ，不过现在要自己实现MySQL 递归。

一，自定义函数

1.1 创建函数 

        

CREATE DEFINER=`root`@`localhost` FUNCTION `get_value_of`(sys_id varchar(1000)) RETURNS varchar(1000) CHARSET utf8
begin 
 declare ids varchar(1000) default ''; 
 declare tempids varchar(1000); 
 
 set tempids = sys_id; 
 while tempids is not null do 
  set ids = CONCAT_WS(',',ids,tempids);
  select GROUP_CONCAT(id) into tempids from pt_permission where FIND_IN_SET(parent_id,tempids)>0;
 end while; 
 return ids; 
end

• 自定义函数名称为：get_value_of
• pt_permission：表名称
• sys_id：参数

1.2 根据上面SQL server 的语句逻辑，编写sql

SELECT DISTINCT * from pt_permission pe WHERE FIND_IN_SET(id,get_value_of(

	(SELECT
		GROUP_CONCAT(p.id )  
	FROM
		pt_permission p 
	WHERE
	 p.sys_id = 0 or p.sys_id = '2'  -- 参数p.sys_id
	and 
		p.parent_id = '0' -- 参数p.parent_id
		AND p.disabled = 0 
		AND permission_code NOT IN ( 'PT_PROJECT_LIST', 'PT_USER_ADMINLIST', 'PT_EQU_ENTITY_LIST', 'DATA_DTU_NEW', 'DATA_DTU_LAST', 'DATA_XT_NEW', 'PT_USER_EXCEL', 'PT_SMS_COUNT' ) 
		)

)) AND pe.disabled = 0 

1.3 思路

        首选是梳理你的递归，是向上递归，还是向下递归，然后梳理递归的参数，参数是一个值，还是一个数组，