在MYSQL中执行递归存储过程以获取分层数据

3 回答

• 0

  Tomalak：“......我会在我的应用程序的表示层中进行递归...”

  这意味着每次递归发生时，另一个调用将从表示层发送到数据库服务器 . 那将是非常缓慢的 .

  回复于 2024-05-05T15:05:17+08:00
• 1

  一个相当简单的迭代邻接列表数据库服务器端解决方案：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);
  

  回复于 2024-05-05T15:05:17+08:00
• 3

  关于最后一个问题："What is the most efficient/elegant way to parse a flat table into a tree?"有几个不错的选择

  您还应该考虑在中间表中缓存递归的结果 . 如果仅在更新到层次结构表时更改它，则递归性能命中可以忽略不计 .

  编辑：我个人会在我的应用程序的表示层进行递归，例如在Web服务器上 . 与SQL中可实现的功能相比，这提供了更大的灵活性，您还可以使用会话或应用程序级缓存 . （尽管使用预先构建的DB表来保持最新的触发器，但永远不会让您使用过时的缓存 . ）

  回复于 2024-05-05T15:05:17+08:00