mysql 存储过程 计数_MYSQL中递归存储过程获取分层数据的性能

我有餐桌员工喜欢

雇员

(

emp_id int主键,

emp_name varchar(50),

mngr_id int)

此处mngr_id将为null或包含有效的emp_id.这样,它形成了组织中员工的层次结构.

为了遍历整个层次结构,我必须编写递归存储过程. (在Oracle中,使用CONNECT BY .. START WITH很容易)

因此,问题在于,如果层次结构的级别不超过10个级别,那么这种存储过程对性能的影响是什么！

还有其他方法可以达到相同目的吗？

最佳答案

一个相当简单的迭代邻接表数据库服务器端解决方案：http://pastie.org/1056977

delimiter ;

drop procedure if exists employee_hier;

delimiter #

create procedure employee_hier

(

in p_emp_id smallint unsigned

)

begin

declare p_done tinyint unsigned default(0);

declare p_depth smallint unsigned default(0);

create temporary table hier(

boss_id smallint unsigned,

emp_id smallint unsigned,

depth smallint unsigned

)engine = memory;

insert into hier values (null, p_emp_id, p_depth);

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table emps engine=memory select * from hier;

while p_done <> 1 do

if exists( select 1 from employee e inner join hier on e.boss_id = hier.emp_id and hier.depth = p_depth) then

insert into hier select e.boss_id, e.emp_id, p_depth + 1

from employee e inner join emps on e.boss_id = emps.emp_id and emps.depth = p_depth;

set p_depth = p_depth + 1;

truncate table emps;

insert into emps select * from hier where depth = p_depth;

else

set p_done = 1;

end if;

end while;

select

e.emp_id,

e.name as emp_name,

b.emp_id as boss_emp_id,

b.name as boss_name,

hier.depth

from

hier

inner join employee e on hier.emp_id = e.emp_id

inner join employee b on hier.boss_id = b.emp_id;

drop temporary table if exists hier;

drop temporary table if exists emps;

end #

delimiter ;

call employee_hier(1);

call employee_hier(3);