mysql 向上递归查询_mysql递归查询的笨拙实现

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

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

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

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

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