mysql树形查询 存储过程_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;