欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL实现类似于connect_by_isleaf的功能MySQL方法或存储过程

程序员文章站 2023-12-10 23:01:58
最近有个特别变态的业务需求,有一张表 create table `demo` ( `id` int(11) unsigned not null auto_in...

最近有个特别变态的业务需求,有一张表

create table `demo` (
 `id` int(11) unsigned not null auto_increment,
 `tid` int(11) default '0',
 `pid` int(11) default '1',
 primary key (`id`)
) engine=innodb auto_increment=3000124 default charset=utf8;

大概就是这样,有300多万行记录,每个pid记录的这个id的最*分类,tid是它的上级分类!
现在需求是:通过指定一个id,查找出它的所有子集成员,并修改这个pid的值为新指定的值!!
在php中跑了一下,执行时间大概需要50秒+,很是痛苦!!!
需要递归找出所有子集,修改它的pid,工作量还是蛮大的。

而oracle中有一个方法是connect_by_isleaf,可以很方便的找出所有子集,但我是mysql......

所以用这儿简单的写写用mysql的方法或存储过程实现的经验

第一种:mysql的方法

create definer=`root`@`localhost` function `lvtao_demo_a`(rootid int) returns text charset utf8
  reads sql data
  comment 'demo'
begin

declare stemp text;
declare stempchd text;

set stempchd =cast(rootid as char);
set stemp = '';

while stempchd is not null do
  set stemp = concat(stemp,',',stempchd);
  select group_concat(id) into stempchd from demo where find_in_set(tid,stempchd)>0;
end while;

return stemp;

end;

使用方法就是

select lvtao_demo_a(5);

但我在测试的时候,300万的数据基本上就是崩溃!!!

data too long for column 'stemp' at row 1

优点: 简单,方便,没有递归调用层次深度的限制 (max_sp_recursion_depth,最大255) ;
缺点:长度受限。

第二种:存储过程+中间表

delimiter ;;
create definer=`root`@`localhost` procedure `sss`(in pid int, in rootid int)
begin
   
declare done int default false;
declare id text;
declare cur1 cursor for select id from demo where tid=pid;
declare continue handler for not found set done = true;

set max_sp_recursion_depth = 200;
 
open cur1;
  read_loop: loop
    fetch cur1 into id;
    if done then
      leave read_loop;
    end if;
    insert into temp (rootid,zid) values (rootid, id);
    call sss(id,rootid);
  end loop;
close cur1;

end;;
delimiter ;

哈哈,300万数据也卡成球了~~~

再想办法吧~~~~不折腾了