MySQL实现类似于connect_by_isleaf的功能MySQL方法或存储过程
最近有个特别变态的业务需求,有一张表
CREATETABLE`demo`( `id`int(11)unsignedNOTNULLAUTO_INCREMENT, `tid`int(11)DEFAULT'0', `pid`int(11)DEFAULT'1', PRIMARYKEY(`id`) )ENGINE=InnoDBAUTO_INCREMENT=3000124DEFAULTCHARSET=utf8;
大概就是这样,有300多万行记录,每个pid记录的这个ID的最顶级分类,tid是它的上级分类!
现在需求是:通过指定一个ID,查找出它的所有子集成员,并修改这个pid的值为新指定的值!!
在PHP中跑了一下,执行时间大概需要50秒+,很是痛苦!!!
需要递归找出所有子集,修改它的pid,工作量还是蛮大的。
而oracle中有一个方法是connect_by_isleaf,可以很方便的找出所有子集,但我是MySQL......
所以用这儿简单的写写用MySQL的方法或存储过程实现的经验
第一种:MySQL的方法
CREATEDEFINER=`root`@`localhost`FUNCTION`lvtao_demo_a`(rootIdint)RETURNStextCHARSETutf8 READSSQLDATA COMMENT'demo' BEGIN DECLAREsTemptext; DECLAREsTempChdtext; SETsTempChd=cast(rootIdasCHAR); SETsTemp=''; WHILEsTempChdisnotnullDO SETsTemp=concat(sTemp,',',sTempChd); SELECTgroup_concat(id)INTOsTempChdFROMdemowhereFIND_IN_SET(tid,sTempChd)>0; ENDWHILE; RETURNsTemp; END;
使用方法就是
selectlvtao_demo_a(5);
但我在测试的时候,300万的数据基本上就是崩溃!!!
Datatoolongforcolumn'sTemp'atrow1
优点:简单,方便,没有递归调用层次深度的限制(max_sp_recursion_depth,最大255);
缺点:长度受限。
第二种:存储过程+中间表
DELIMITER;; CREATEDEFINER=`root`@`localhost`PROCEDURE`sss`(INpidint,INrootidint) BEGIN DECLAREdoneINTDEFAULTFALSE; DECLAREidTEXT; DECLAREcur1CURSORFORSELECTidFROMdemoWHEREtid=pid; DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE; setmax_sp_recursion_depth=200; OPENcur1; read_loop:LOOP FETCHcur1INTOid; IFdoneTHEN LEAVEread_loop; ENDIF; INSERTINTOtemp(rootid,zid)values(rootid,id); callsss(id,rootid); ENDLOOP; CLOSEcur1; END;; DELIMITER;
哈哈,300万数据也卡成球了~~~
再想办法吧~~~~不折腾了