mysql存储过程之递归查询

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

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

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