MySQL implements functions similar to connect_by_isleaf. MySQL methods or stored procedures, connectbyisleaf

MySQL implements functions similar to connect_by_isleaf. MySQL methods or stored procedures, connectbyisleaf

Recently, there was a special abnormal business demand, with a table

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;

This is probably the case. There are more than 3 million rows of records, and the top-level category of the ID recorded by each pid. tid is its parent category!
Now, you need to specify an ID to find all its subset members and modify the value of this pid to a new value !!
I ran it in PHP and it took about 50 seconds to execute it. It was very painful !!!
You need to recursively find all subsets and modify their pid. The workload is quite large.

One method in oracle is connect_by_isleaf, which can easily find all subsets, but I am MySQL ......

So here is a simple example of using the MySQL method or stored procedure implementation experience.

First: MySQL Method

CREATE DEFINER=`root`@`localhost` FUNCTION `lvtao_demo_a`(rootId int) RETURNS text CHARSET utf8  READS SQL DATA  COMMENT 'demo'BEGINDECLARE 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;

The usage is

select lvtao_demo_a(5);

But when I was testing, 3 million of the data basically crashed !!!

Data too long for column 'sTemp' at row 1

Advantages: it is simple and convenient, and there is no limit on the depth of recursive calling layers (max_sp_recursion_depth, Max. 255 );
Disadvantage: The length is limited.

Type 2: Stored Procedure + intermediate table

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 ;

Haha, 3 million of the data is also flushed into the ball ~~~

Try again ~~~~ No worries