## 找出所有后代数据

with getTree as (

select parent_id, id from a where a.id = #{id};

UNION all

select a.parent_id, a.id from a inner join getTree on a.parent_id = getTree.id

)

delimiter $$

DROP PROCEDURE IF EXISTS findTreeList;

DROP PROCEDURE IF EXISTS getTreeResult;

CREATE PROCEDURE `findTreeList`( in id varchar(50) )

BEGIN

DECLARE child_id VARCHAR(50) DEFAULT '';

DECLARE done INTEGER DEFAULT 1;

DECLARE cur CURSOR FOR

-- 对应的递归查询，由递归语句改来：select a.parent_id, a.id from a inner join getTree on a.parent_id = getTree.id

-- 此处递归方法主要是找好父子级对应关系，“`findTreeList`( in id varchar(50) )”此参数 id 便是父级id, 匹配的便是子级的parent_id

SELECT a.id FROM a WHERE a.parent_id = id;

DECLARE CONTINUE HANDLER FOR NOT found SET done=0;

INSERT INTO tmp VALUES (tree_id);

SET @@max_sp_recursion_depth = 50;

OPEN cur;

FETCH cur INTO child_id;

WHILE (done=1) DO

CALL findTreeList(child_id);

FETCH cur INTO child_id;

END WHILE;

CLOSE cur;

END;

CREATE PROCEDURE `getTreeResult`( IN id VARCHAR(50) )

DETERMINISTIC

BEGIN

DROP TEMPORARY TABLE IF EXISTS tmp;

-- 结果保存在临时表中

CREATE TEMPORARY TABLE tmp(tree_id VARCHAR(50));

DELETE FROM tmp;

set @tree_id = (SELECT tree_id FROM i_file_type WHERE is_enabled =1 and i_file_type.file_type_id = file_type_id);

-- 开始递归

CALL findTreeList(@tree_id);

-- 可以在此处利用临时表的结果，写其他查询语句

select * from a where a.id = tmp.id;

END;

CALL getTreeResult(#{id});

DROP TEMPORARY TABLE IF EXISTS tmp;

DROP PROCEDURE IF EXISTS findTreeList;

DROP PROCEDURE IF EXISTS getTreeResult;

$$

delimiter ;