快捷導(dǎo)航

MySQL實(shí)現(xiàn)類似于connect_by_isleaf的功能MySQL方法或存儲過程

更新時間：2017年02月05日 20:18:07 投稿：mdxy-dxy

這篇文章主要介紹了MySQL實(shí)現(xiàn)類似于connect_by_isleaf的功能MySQL方法或存儲過程,需要的朋友可以參考下

最近有個特別變態(tài)的業(yè)務(wù)需求，有一張表

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是它的上級分類！
現(xiàn)在需求是：通過指定一個ID，查找出它的所有子集成員，并修改這個pid的值為新指定的值??！
在PHP中跑了一下，執(zhí)行時間大概需要50秒+，很是痛苦！?。?br /> 需要遞歸找出所有子集，修改它的pid，工作量還是蠻大的。

而oracle中有一個方法是connect_by_isleaf，可以很方便的找出所有子集，但我是MySQL......

所以用這兒簡單的寫寫用MySQL的方法或存儲過程實(shí)現(xiàn)的經(jīng)驗(yàn)

第一種：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萬的數(shù)據(jù)基本上就是崩潰！??！

Data too long for column 'sTemp' at row 1

優(yōu)點(diǎn): 簡單，方便，沒有遞歸調(diào)用層次深度的限制 (max_sp_recursion_depth,最大255) ；
缺點(diǎn)：長度受限。

第二種：存儲過程+中間表

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萬數(shù)據(jù)也卡成球了~~~

再想辦法吧~~~~不折騰了

您可能感興趣的文章: