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

田野里,高粱像喝醉了酒,频频点头;玉米正在变黄了的衣服里睡大觉;大豆也小坡了肚皮,蹦了出来;小白菜像列队的士兵整齐地排列在在菜地里。农民正忙忙碌碌地收获着一年的成果,田野里不时传出阵阵欢笑声。啊,秋天的景色真美啊!

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

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万数据也卡成球了~~~

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

以上就是MySQL如何实现类似于connect_by_isleaf的功能MySQL方法或存储过程。失败好比被人打,你不反抗他就会有下一次!更多关于MySQL如何实现类似于connect_by_isleaf的功能MySQL方法或存储过程请关注haodaima.com其它相关文章!

您可能有感兴趣的文章
浅谈Mysql中类似于nvl()函数的ifnull()函数

centos7 mysql5.7主从同步配置

centos7安装MySQL教程

达梦数据库DM8表空间管理之删除表空间

达梦数据库DM8表空间管理之创建表空间