mysql递归调用获取树节点子树备课讲稿

1、Good is good, but better carries it.精益求精，善益求善。mysql递归调用获取树节点子树-创建树形数据存储表，及其数据：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);存储过程：查询参数节点下

4、的所有子节点入口：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查询参数节点下的所有父节点入口相同，只是插入数据过程不同CREATEDEFINER=rootlocalhostPROCEDUREcreateChildLst(INrootIdint,INnDepthint)BEGIN#Routinebodygoeshere.DECLAREdoneINTDEFAULT0;DECLAREbINT;DECLAREcur1CURSORFORSELECTpidFROMtreeNodeswhereid=rootId;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;insertintotmpLstvalues(null,rootId,nDepth);OPE