mysql遞歸調用獲取樹節(jié)點子樹備課講稿

1、Good is good, but better carries it.精益求精，善益求善。mysql遞歸調用獲取樹節(jié)點子樹-創(chuàng)建樹形數據存儲表，及其數據：SETFOREIGN_KEY_CHECKS=0;-Tablestructurefortreenodes-DROPTABLEIFEXISTStreenodes;CREATETABLEtreenodes(idint(11)NOTNULL,nodenamevarchar(20)DEFAULTNULL,pidint(11)DEFAULTNULL,PRIMARYKEY(id)ENGINE=InnoDBDEFAULTCHARSET=utf8;-Reco

2、rdsoftreenodes-INSERTINTOtreenodesVALUES(1,A,0);INSERTINTOtreenodesVALUES(2,B,1);INSERTINTOtreenodesVALUES(3,C,1);INSERTINTOtreenodesVALUES(4,D,2);INSERTINTOtreenodesVALUES(5,E,2);INSERTINTOtreenodesVALUES(6,F,3);INSERTINTOtreenodesVALUES(7,G,6);INSERTINTOtreenodesVALUES(8,H,0);INSERTINTOtreenodesVA

3、LUES(9,I,8);INSERTINTOtreenodesVALUES(10,J,8);INSERTINTOtreenodesVALUES(11,K,8);INSERTINTOtreenodesVALUES(12,L,9);INSERTINTOtreenodesVALUES(13,M,0);INSERTINTOtreenodesVALUES(14,N,12);INSERTINTOtreenodesVALUES(15,O,12);INSERTINTOtreenodesVALUES(16,P,15);INSERTINTOtreenodesVALUES(17,Q,15);存儲過程：查詢參數節(jié)點下

4、的所有子節(jié)點入口：CREATEDEFINER=rootlocalhostPROCEDUREshowChildLst(INrootIdint)BEGIN#Routinebodygoeshere.setmax_sp_recursion_depth=225;CREATETEMPORARYTABLEIFNOTEXISTStmpLst(snointprimarykeyauto_increment,idint,depthint);DELETEFROMtmpLst;CALLcreateChildLst(rootId,0);selecttmpLst.*,treeNodes.*fromtmpLst,treeNo

5、deswheretmpLst.id=treeNodes.idorderbytmpLst.sno;END向臨時表插入數據CREATEDEFINER=rootlocalhostPROCEDUREcreateChildLst(INrootIdint,INnDepthint)BEGIN#Routinebodygoeshere.DECLAREdoneINTDEFAULT0;DECLAREbINT;DECLAREcur1CURSORFORSELECTidFROMtreeNodeswherepid=rootId;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;inser

6、tintotmpLstvalues(null,rootId,nDepth);OPENcur1;FETCHcur1INTOb;WHILEdone=0DOCALLcreateChildLst(b,nDepth+1);FETCHcur1INTOb;ENDWHILE;CLOSEcur1;END查詢參數節(jié)點下的所有父節(jié)點入口相同，只是插入數據過程不同CREATEDEFINER=rootlocalhostPROCEDUREcreateChildLst(INrootIdint,INnDepthint)BEGIN#Routinebodygoeshere.DECLAREdoneINTDEFAULT0;DECLAREbINT;DECLAREcur1CURSORFORSELECTpidFROMtreeNodeswhereid=rootId;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;insertintotmpLstvalues(null,rootId,nDepth);OPE