MySql创建树结构递归查询存储过程

在实现F2工作流底层多数据库支持时发现Oracel和mssql都有提供递归子查询，而MySql却没有，没办法需要自己构建存储过程来提供这个递归子查询的功能。

-- 当前节点及子节点 
-- 参数说明：idd 要查询的资源ID值，idFieldName ID字段名，parentIdFieldName 上级ID字段名，tableName 表名，isContainMySelf 是否包含自己
DROP procedure IF EXISTS pro_getChildrenList;
create procedure pro_getChildrenList(in idd varchar(100),in idFieldName varchar(100),in parentIdFieldName varchar(100),in tableName varchar(100),isContainMySelf int)
begin
declare lev int;
set lev=1;
drop table if exists tmp1;
    CREATE TABLE tmp1(ID VARCHAR(100),ParentID varchar(100) ,levv INT);
set @strsql = CONCAT('INSERT tmp1' ,' SELECT ',idFieldName ,',' ,parentIdFieldName,',',1, ' FROM ', tableName,' WHERE ',parentIdFieldName,'=','''', idd,'''');
prepare strsql from @strsql;
execute strsql;
while  row_count()>0
do
		set lev=lev+1;
		set @strsql = CONCAT('INSERT tmp1' ,' SELECT ','t.',idFieldName,',' ,' t.',parentIdFieldName,',' ,lev, ' FROM ', tableName,' t join tmp1 a on ','t.',parentIdFieldName,'=a.ID And levv=', lev-1);
		prepare strsql from @strsql;
    execute strsql;
end while ;
   
    SET @myself='';
		IF (isContainMySelf=1) THEN 
		SET @myself = concat(' or ',idFieldName,'=','''',idd,''''); 
		ELSE 
		SET @myself = ''; 
		END IF; 

		set @strsql = CONCAT('SELECT * from ', tableName,' where ',idFieldName,' in ( SELECT ID from tmp1)',@myself);
		prepare strsql from @strsql;
    execute strsql;
end;

　　

转载于:https://www.cnblogs.com/f2flow/p/6001889.html