MySQL implements functionality similar to connect_by_isleaf MySQL methods or stored procedures
- 2021-07-03 00:59:19
- OfStack
Recently, there is a special abnormal business requirement 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;
That's about it. There are more than 3 million lines of records, and each pid records the top-level classification of this ID, and tid is its superior classification!
Now the requirement is: by specifying an ID, find out all its subset members, and modify the value of this pid to the newly specified value! !
Run 1 time in PHP, and the execution time takes about 50 seconds +, which is very painful! ! !
Need to find out all subsets recursively, modify its 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 use here to simply write the experience of implementing with MySQL methods or stored procedures
Method 1: 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;
How to use it is
select lvtao_demo_a(5);
But when I was testing, the data of 3 million basically crashed! ! !
Data too long for column 'sTemp' at row 1
Advantages: Simple and convenient, without the limit of recursive call hierarchy depth (max_sp_recursion_depth, max 255);
Disadvantages: Limited length.
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 data is stuck into a ball ~ ~ ~
Think of a way again ~ ~ ~ ~ Don't toss about