mysql存储过程递归查询语句_mysql存储过程之递归查询

对一颗树型结构存储的表进行递归查询，使用navicat for mysql 创建存储过程 pro_show_knowledge， 参数 --  IN `rootId` int   ，通过知识点查询所有子节点数据并插入到临时表 tmp_knowledge_data中。

注意深度的设置 ， set max_sp_recursion_depth = 100 ; 这句话必须加上。

函数定义前添加DETERMINISTIC，任务计划添加：

SET GLOBAL event_scheduler = ON;

SET @@global.event_scheduler = ON;

SET GLOBAL event_scheduler = 1;

SET @@global.event_scheduler = 1;

BEGIN

#设置递归查询的层深上限

set max_sp_recursion_depth = 100;

#创建临时表tmp_knowledge_data,用于存储某个知识点下的所有子节点数据

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_knowledge_data

(`id` int(11) NOT NULL,

`name` varchar(50) DEFAULT NULL,

`pId` int(11) DEFAULT NULL,

`create_time` datetime DEFAULT NULL,

`modify_time` datetime DEFAULT NULL,

`nDepth` int(11) DEFAULT NULL,

`is_parent` int DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `id_index` (`id`) USING BTREE,

KEY `pid_index` (`pId`) USING BTREE)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELETE FROM tmp_knowledge_data;

CALL pro_create_childLst(rootId,0);

select * from tmp_knowledge_data;

END

子节点插入临时表之前判断数据是否为父节点，并将isparent属性存入临时表

BEGIN

#开始循环

DECLARE done INT DEFAULT 0;

DECLARE b INT;

DECLARE cur1 CURSOR FOR SELECT id FROM mooc_si_knowledge_tree where pId=rootId and delete_flag=0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

FETCH cur1 INTO b;

if done =0

THEN

insert into tmp_knowledge_data (id,name,pId,create_time,modify_time,nDepth,is_parent)

(select id,name,pId,create_time,modify_time,nDepth,1 AS is_parent from mooc_si_knowledge_tree where id = rootId);

ELSE

insert into tmp_knowledge_data (id,name,pId,create_time,modify_time,nDepth,is_parent)

(select id,name,pId,create_time,modify_time,nDepth,0 AS is_parent from mooc_si_knowledge_tree where id = rootId);

END IF;

WHILE done=0 DO

CALL pro_create_childLst(b,nDepth+1);

FETCH cur1 INTO b;

END WHILE;

CLOSE cur1;

#循环结束

END