MySQL 实现树的遍历详解及简单实现示例

mysql 实现树的遍历

经常在一个表中有父子关系的两个字段，比如empno与manager，这种结构中需要用到树的遍历。在oracle 中可以使用connect by简单解决问题，但mysql 5.1中还不支持（据说已纳入to do中），要自己写过程或函数来实现。

一、建立测试表和数据：

drop table if exists `channel`; 
 
create table `channel` ( 
 `id` int(11) not null auto_increment,  
 `cname` varchar(200) default null, 
 `parent_id` int(11) default null, 
 primary key (`id`) 
) engine=myisam auto_increment=19 default charset=utf8; 
 
/*data for the table `channel` */ 
 
insert into `channel`(`id`,`cname`,`parent_id`)  
values (13,'首页',-1), 
    (14,'tv580',-1), 
    (15,'生活580',-1), 
    (16,'左上幻灯片',13), 
    (17,'帮忙',14), 
    (18,'栏目简介',17); 

 二、利用临时表和递归过程实现树的遍历（mysql的udf不能递归调用）：

delimiter $$ 
 
use `db1`$$ 
 
 
-- 从某节点向下遍历子节点 
-- 递归生成临时表数据 
drop procedure if exists `createchildlst`$$ 
 
create procedure `createchildlst`(in rootid int,in ndepth int) 
begin 
   declare done int default 0; 
   declare b int; 
   declare cur1 cursor for select id from channel where parent_id=rootid; 
   declare continue handler for not found set done = 1; 
   set max_sp_recursion_depth=12; 
   
   insert into tmplst values (null,rootid,ndepth); 
   
   open cur1; 
   
   fetch cur1 into b; 
   while done=0 do 
       call createchildlst(b,ndepth+1); 
       fetch cur1 into b; 
   end while; 
   
   close cur1; 
   end$$ 
 
 
-- 从某节点向上追溯根节点 
-- 递归生成临时表数据 
drop procedure if exists `createparentlst`$$ 
 
create procedure `createparentlst`(in rootid int,in ndepth int) 
begin 
   declare done int default 0; 
   declare b int; 
   declare cur1 cursor for select parent_id from channel where id=rootid; 
   declare continue handler for not found set done = 1; 
   set max_sp_recursion_depth=12; 
   
   insert into tmplst values (null,rootid,ndepth); 
   
   open cur1; 
   
   fetch cur1 into b; 
   while done=0 do 
       call createparentlst(b,ndepth+1); 
       fetch cur1 into b; 
   end while; 
   
   close cur1; 
   end$$ 
 
 
-- 实现类似oracle sys_connect_by_path的功能 
-- 递归过程输出某节点id路径 
drop procedure if exists `createpathlst`$$ 
 
create procedure `createpathlst`(in nid int,in delimit varchar(10),inout pathstr varchar(1000)) 
begin          
   declare done int default 0; 
   declare parentid int default 0;    
   declare cur1 cursor for  
   select t.parent_id,concat(cast(t.parent_id as char),delimit,pathstr) 
    from channel as t where t.id = nid; 
     
   declare continue handler for not found set done = 1; 
   set max_sp_recursion_depth=12;          
   
   open cur1; 
   
   fetch cur1 into parentid,pathstr; 
   while done=0 do       
       call createpathlst(parentid,delimit,pathstr); 
       fetch cur1 into parentid,pathstr; 
   end while; 
      
   close cur1;  
   end$$ 
 
 
-- 递归过程输出某节点name路径 
drop procedure if exists `createpathnamelst`$$ 
 
create procedure `createpathnamelst`(in nid int,in delimit varchar(10),inout pathstr varchar(1000)) 
begin          
   declare done int default 0; 
   declare parentid int default 0;    
   declare cur1 cursor for  
   select t.parent_id,concat(t.cname,delimit,pathstr) 
    from channel as t where t.id = nid; 
     
   declare continue handler for not found set done = 1; 
   set max_sp_recursion_depth=12;          
   
   open cur1; 
   
   fetch cur1 into parentid,pathstr; 
   while done=0 do       
       call createpathnamelst(parentid,delimit,pathstr); 
       fetch cur1 into parentid,pathstr; 
   end while; 
      
   close cur1;  
   end$$ 
 
 
-- 调用函数输出id路径 
drop function if exists `fn_tree_path`$$ 
 
create function `fn_tree_path`(nid int,delimit varchar(10)) returns varchar(2000) charset utf8 
begin  
 declare pathid varchar(1000); 
  
 set @pathid=cast(nid as char); 
 call createpathlst(nid,delimit,@pathid); 
  
 return @pathid; 
end$$ 
 
 
-- 调用函数输出name路径 
drop function if exists `fn_tree_pathname`$$ 
 
create function `fn_tree_pathname`(nid int,delimit varchar(10)) returns varchar(2000) charset utf8 
begin  
 declare pathid varchar(1000); 
  
 set @pathid='';   
 call createpathnamelst(nid,delimit,@pathid); 
  
 return @pathid; 
end$$ 
 
 
-- 调用过程输出子节点 
drop procedure if exists `showchildlst`$$ 
 
create procedure `showchildlst`(in rootid int) 
begin 
   drop temporary table if exists tmplst; 
   create temporary table if not exists tmplst  
    (sno int primary key auto_increment,id int,depth int);    
   
   call createchildlst(rootid,0); 
   
   select channel.id,concat(space(tmplst.depth*2),'--',channel.cname) name,channel.parent_id,tmplst.depth,fn_tree_path(channel.id,'/') path,fn_tree_pathname(channel.id,'/') pathname 
   from tmplst,channel where tmplst.id=channel.id order by tmplst.sno; 
   end$$ 
 
-- 调用过程输出父节点 
drop procedure if exists `showparentlst`$$ 
 
create procedure `showparentlst`(in rootid int) 
begin 
   drop temporary table if exists tmplst; 
   create temporary table if not exists tmplst  
    (sno int primary key auto_increment,id int,depth int);    
   
   call createparentlst(rootid,0); 
   
   select channel.id,concat(space(tmplst.depth*2),'--',channel.cname) name,channel.parent_id,tmplst.depth,fn_tree_path(channel.id,'/') path,fn_tree_pathname(channel.id,'/') pathname 
   from tmplst,channel where tmplst.id=channel.id order by tmplst.sno; 
   end$$ 
 
 
delimiter ; 

三、测试

call showchildlst(-1); 
call showchildlst(13); 
call showchildlst(14); 
call showchildlst(17); 
call showchildlst(18); 
 
call showparentlst(-1); 
call showparentlst(13); 
call showparentlst(14); 
call showparentlst(17); 
call showparentlst(18); 

四、遗留问题

1. 因为mysql对动态游标的支持不够，所以要想做成通用的过程或函数比较困难，可以利用两个临时表来转换（同时去掉了递归调用）是个相对通用的实现。

2. 目前来看无论哪种实现，效率都不太好，希望mysql自己能实现oracle 的connect by 功能，应该会比较优化。

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！