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

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

2020-07-16 18:18服务器之家 Mysql

这篇文章主要介绍了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;

使用方法就是

1	`select` `lvtao_demo_a(5);`

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

1	`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 ;