数据库教程:MySQL树形结构查询代码实例

mysql树形结构查询代码实例

需要了解更多数据库技术:MySQL树形结构查询代码实例，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!

  drop procedure if exists query_node_deep;  create procedure query_node_deep(in rootid int(11))  begin      create temporary table if not exists tmptable(          sno int primary key auto_increment,          nid int(11),          deep int(11)      );      delete from tmptable;        call create_node_deep(rootid, 0, 1);      select tmptable.*,api_node.* from tmptable,api_node where tmptable.nid=api_node.id order by tmptable.sno;    end;    drop procedure if exists create_node_deep;  create procedure create_node_deep(in rootid int(11),in ndepth int, in max_n int)      begin              declare done int default 0;              declare b varchar(20);              declare cur1 cursor for select id from api_node where parent_id=rootid;              declare continue handler for not found set done = 1;          if (max_n > 0) then              insert into tmptable values (null,rootid,ndepth);              set @@max_sp_recursion_depth = 7;              open cur1;                fetch cur1 into b;              while done=0 do                  call create_node_deep(b,ndepth+1, max_n-1);                  fetch cur1 into b;              end while;              close cur1;          end if;      end;      call query_node_deep(17);