快捷導(dǎo)航

MySQL實(shí)現(xiàn)類(lèi)似于connect_by_isleaf的功能MySQL方法或存儲(chǔ)過(guò)程

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

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

最近有個(gè)特別變態(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多萬(wàn)行記錄，每個(gè)pid記錄的這個(gè)ID的最頂級(jí)分類(lèi),tid是它的上級(jí)分類(lèi)！
現(xiàn)在需求是：通過(guò)指定一個(gè)ID，查找出它的所有子集成員，并修改這個(gè)pid的值為新指定的值?。?br /> 在PHP中跑了一下，執(zhí)行時(shí)間大概需要50秒+，很是痛苦?。?！
需要遞歸找出所有子集，修改它的pid，工作量還是蠻大的。

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

所以用這兒簡(jiǎn)單的寫(xiě)寫(xiě)用MySQL的方法或存儲(chǔ)過(guò)程實(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);

但我在測(cè)試的時(shí)候，300萬(wàn)的數(shù)據(jù)基本上就是崩潰！??！

Data too long for column 'sTemp' at row 1

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

第二種：存儲(chǔ)過(guò)程+中間表

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

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

您可能感興趣的文章: