mysql递归查询的笨拙实现 - 不抛弃，不放弃 - ITPUB个人空间 - powered by X-Space

最近做oracle到mysql的数据库开发。发现了很多问题。

最头大的就是oracle中start with connect by prior在mysql中用不了了。查阅了很多mysql迷们自己实现的递归查询：

1.usegroup_concat  find_in_set循环查询结果集，把结果集返回。用存储过程实现。这个有弊端的，而且有个bug。不解原因。限制是procedure对varchar的返回有长度限制，不过可以用text.但是有些树枝查不出来 不知道原因。

2 用临时表，把结果集全部放入临时表。存储过程实现。次实现方法不错，但是效率不是很高。

3. 我的实现方法和2差不多。基本思路是把所有层的sql用unionall连接 。返回结果集市一个sql语句 而不是sql 这样成语可以拼接sql一次性查询。方便写程序。而且可以实现表的向上，向下递归。但是效率也不是很高，因为unionall连接的那些子句是需要通过单独的sql去做递归查询的。当有下一层数据的时候就需要unionall。在这块的一个优化处理方法是实施欧诺个limit尽量减少放回结果。代码如下：

 

delimiter //
CREATE DEFINER ='root'@'%'PROCEDURE `recursive_select`(in table_name VARCHAR(128),in

child_col_name   VARCHAR(32),
                                 in parent_col_name  VARCHAR(32),
                                 in child_value      varchar(256),     
                                 in parent_value     varchar(256),
                                 out result  text                                 
                                 )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
/* 此过程可以实现对表的递归查询，通过调换child_col,parent_col可以实现向上或向下递归。必须保证

chail_value和parent_value只有一个值存在*/
begin

declare sqltext text;
IF (child_value IS NOT NULL && parent_value is  null ) or (child_value IS  NULL &&

parent_value is NOT  null) then
if child_value IS NOT NULL && parent_value is  null then
set @sql=CONCAT(' select ',child_col_name,' into @sqlresult  ',
' from ', table_name,' where ', child_col_name, ' =''',child_value,''' limit 0, 1'
 );
set sqltext=CONCAT(' select ',child_col_name,
' from ', table_name,' where ', child_col_name, ' =''',child_value,'''');
set result =sqltext;
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
while  @sqlresult is not NULL do
begin
set @sqlresult =NULL;
  set @sql=CONCAT(' select ', child_col_name ,' into @sqlresult' ,' from ',
   table_name ,' where ',  parent_col_name, ' in ( ', sqltext,')',' limit 0, 1 ');

  set sqltext =concat(' select ', child_col_name ,' from ', table_name ,
  ' where  ',parent_col_name, ' in ( ',sqltext,')');
 
  set result = concat(result,' union all ' ,sqltext);
  prepare stmt from @sql;
  execute stmt;
  deallocate prepare stmt;
  end;
end while;

else
####
set @sql=CONCAT(' select ',child_col_name,' into @sqlresult  ',
' from ', table_name,' where ', parent_col_name, ' =''',parent_value,''' limit 0, 1'
 );
set sqltext=CONCAT(' select ',child_col_name,
' from ', table_name,' where ', parent_col_name, ' =''',parent_value,'''');
set result =sqltext;
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
while  @sqlresult is not NULL do
begin
set @sqlresult =NULL;
  set @sql=CONCAT(' select ', child_col_name ,' into @sqlresult' ,' from ',
   table_name ,' where ',  parent_col_name, ' in ( ', sqltext,')',' limit 0, 1 ');

  set sqltext =concat(' select ', child_col_name ,' from ', table_name ,
  ' where  ',parent_col_name, ' in ( ',sqltext,')');
 
  set result = concat(result,' union all ' ,sqltext);
  prepare stmt from @sql;
  execute stmt;
  deallocate prepare stmt;
  end;
end while;

end if;
end IF;
end;
//
delimiter ;

以上实现有几个不足之处 一个不能灵活实现向上递归 一个是不能实现start with connect by 中带子查询。所以重新写了2个如下：思路是一样的。而且修正了以前返回结果中最后一个多余的子句

 

允许带子句的

delimiter //
drop procedure if exists  recursive_select_sql  //
CREATE DEFINER ='root'@'%'PROCEDURE `recursive_select_sql`(in table_name
 VARCHAR(128),in child_col_name   VARCHAR(32),
                                 in parent_col_name  VARCHAR(32),
                                 in child_value_sql      varchar(256),     
                                 in parent_value_sql     varchar(256),
                                 in return_col_name     varchar(32),                                
                                 out result  text                                 
                                 )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
begin

declare sqltext text;
declare sqlresults text;
#declare sqlresultsql  text;
IF (child_value_sql IS NOT NULL && parent_value_sql is  null ) or (
child_value_sql IS  NULL && parent_value_sql is NOT  null) then
if child_value_sql IS NOT NULL && parent_value_sql is  null then
set @sql=CONCAT(' select ',return_col_name,' into @sqlresult  ',
' from ', table_name,' where ', child_col_name, ' in (',child_value_sql,
') limit 0, 1'
 );
set sqltext=CONCAT(' select ',child_col_name,
' from ', table_name,' where ', child_col_name, ' in (',child_value_sql,')');

set result =CONCAT(' select ',return_col_name,
' from ', table_name,' where ', child_col_name, ' in (',child_value_sql,')');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
while  @sqlresult is not NULL do
begin
set @sqlresult =NULL;
  set @sql=CONCAT(' select ', return_col_name ,' into @sqlresult' ,' from ',
   table_name ,' where ',  parent_col_name, ' in ( ', sqltext,')',
   ' limit 0, 1 ');
 
  set sqlresults=concat(' select ', return_col_name ,' from ', table_name ,
  ' where  ',parent_col_name, ' in ( ',sqltext,')');

  set sqltext =concat(' select ', child_col_name ,' from ', table_name ,
  ' where  ',parent_col_name, ' in ( ',sqltext,')');
 
 
  prepare stmt from @sql;
  execute stmt;
  deallocate prepare stmt;
  if @sqlresult is not NULL THEN
  set result = concat(result,' union all ' ,sqlresults);
  end if;
 
  end;
end while;

else

set @sql=CONCAT(' select ',return_col_name,' into @sqlresult  ',
' from ', table_name,' where ', parent_col_name, ' in (',parent_value_sql,
') limit 0, 1'
 );
set sqltext=CONCAT(' select ',child_col_name,
' from ', table_name,' where ', parent_col_name, ' in(',parent_value_sql,')');

set result =CONCAT(' select ',return_col_name,
' from ', table_name,' where ', parent_col_name, ' in(',parent_value_sql,')');

 

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

while  @sqlresult is not NULL do
begin
set @sqlresult =NULL;
  set @sql=CONCAT(' select ', return_col_name ,' into @sqlresult' ,' from ',
   table_name ,' where ',  parent_col_name, ' in ( ', sqltext,')',
   ' limit 0, 1 ');
 
  set sqlresults=concat(' select ', return_col_name ,' from ', table_name ,
  ' where  ',parent_col_name, ' in ( ',sqltext,')');

  set sqltext =concat(' select ', child_col_name ,' from ', table_name ,
  ' where  ',parent_col_name, ' in ( ',sqltext,')');
   
  prepare stmt from @sql;
  execute stmt;
  deallocate prepare stmt;
  if @sqlresult is not NULL THEN
  set result = concat(result,' union all ' ,sqlresults);
  end if;
 
  end;
end while;

end if;
end IF;
end;
//
delimiter ;

 

对第一个修改后的

delimiter //

drop PROCEDURE  if exists recursive_select //

CREATE DEFINER ='root'@'%'PROCEDURE `recursive_select`(in table_name VARCHAR(128),in child_col_name   VARCHAR(32),
                                 in parent_col_name  VARCHAR(32),
                                 in child_value      varchar(256),     
                                 in parent_value     varchar(256),
                                 in  return_col_name VARCHAR(32),
                                 out result  text                                 
                                 )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
begin
declare sqltext text;
declare sqlresults text;

IF(child_value IS NOT NULL && parent_value is  null ) or(child_value IS  NULL && parent_value is NOT  null) then
if child_value IS NOT NULL && parent_value is  null then
set @sql=CONCAT(' select ',return_col_name,' into @sqlresult  ',
' from ', table_name,' where ', child_col_name, ' =''',child_value,''' limit 0, 1'
 );
set sqltext=CONCAT(' select ',child_col_name,
' from ', table_name,' where ', child_col_name, ' =''',child_value,'''');
set result =CONCAT(' select ',return_col_name,
' from ', table_name,' where ', child_col_name, ' =''',child_value,'''');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
while  @sqlresult is not NULL do
begin
set @sqlresult =NULL;
  set @sql=CONCAT(' select ', return_col_name ,' into @sqlresult' ,' from ',
   table_name ,' where ',  parent_col_name, ' in ( ', sqltext,')',' limit 0, 1 ');  
  set sqlresults=concat(' select ', return_col_name ,' from ', table_name ,
  ' where  ',parent_col_name, ' in ( ',sqltext,')'); 
  set sqltext =concat(' select ', child_col_name ,' from ', table_name ,
  ' where  ',parent_col_name, ' in ( ',sqltext,')');  
  prepare stmt from @sql;
  execute stmt;
  deallocate prepare stmt;
  if @sqlresult is not NULL then
  set result = concat(result,' union all ' ,sqlresults);
  end if; 
  end;
end while;

else

set @sql=CONCAT(' select ',return_col_name,' into @sqlresult  ',
' from ', table_name,' where ', parent_col_name, ' =''',parent_value,''' limit 0, 1'
 );
set sqltext=CONCAT(' select ',child_col_name,
' from ', table_name,' where ', parent_col_name, ' =''',parent_value,'''');
set result = CONCAT(' select ',return_col_name,
' from ', table_name,' where ', parent_col_name, ' =''',parent_value,'''');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
while  @sqlresult is not NULL do
begin
set @sqlresult =NULL;
  set @sql=CONCAT(' select ', return_col_name ,' into @sqlresult' ,' from ',
   table_name ,' where ',  parent_col_name, ' in ( ', sqltext,')',' limit 0, 1 ');  
  set sqlresults=concat(' select ', return_col_name ,' from ', table_name ,
  ' where  ',parent_col_name, ' in ( ',sqltext,')'); 
  set sqltext =concat(' select ', child_col_name ,' from ', table_name ,
  ' where  ',parent_col_name, ' in ( ',sqltext,')');  
  prepare stmt from @sql;
  execute stmt;
  deallocate prepare stmt;
  if @sqlresult is not NULL then
  set result = concat(result,' union all ' ,sqlresults);
  end if; 
  end;
end while;

end if;
end IF;
end;
//
delimiter ;