mysql存储过程之递归查询

最新推荐文章于 2023-04-22 22:44:10 发布

weixin_33729196

最新推荐文章于 2023-04-22 22:44:10 发布

阅读量120

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/geekLight/blog/406976

版权

2019独角兽企业重金招聘Python工程师标准>>>

对一颗树型结构存储的表进行递归查询，使用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

转载于:https://my.oschina.net/geekLight/blog/406976