Recursive query of MySQL stored procedure

Recursive querying of tables stored in a tree structure, using navicat for MySQL to create stored procedures Pro_show_knowledge, Parameters-in ' rootid ' int, querying all child node data through a knowledge point and inserting it into a temporary table Tmp_knowl The Edge_data.

Note the setting of the depth, set max_sp_recursion_depth = 100; This remark must be added.

begin# set the maximum layer depth  set max_sp_recursion_depth = 100; #创建临时表tmp_knowledge_data for recursive queries, Used to store all child node data under a knowledge point     CREATE TEMPORARY TABLE IF NOT EXISTS  Tmp_knowledge_data (' id '  int (one)  NOT NULL,                            ' name '  varchar ( DEFAULT NULL,           )                 ' PId '  int (11)  DEFAULT NULL,                            ' Create_time '  datetime  default null,                            ' Modify_time '  datetime DEFAULT NULL,                    ' ndepth '  int (one)   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

Child nodes Determine whether the data is a parent before inserting the temporary table, and save the Isparent property to a temporary table

begin# Start Cycle 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 =0theninsert 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);  ELSEinsert 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 docall pro_create_chIldlst (b,ndepth+1); fetch cur1 into b; end while;        close cur1; #循环结束END

Recursive query of MySQL stored procedure