资源描述：
《mysql递归调用获取树节点子树》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。
1、创建树形数据存储表，及其数据：SETFOREIGN_KEY_CHECKS=0;--------------------------------Tablestructurefor`treenodes`------------------------------DROPTABLEIFEXISTS`treenodes`;CREATETABLE`treenodes`(`id`int(11)NOTNULL,`nodename`varchar(20)DEFAULTNULL,`pid`int(11)DEFAULTNULL,PRIMARYKEY(`id`))EN
2、GINE=InnoDBDEFAULTCHARSET=utf8;--------------------------------Recordsoftreenodes------------------------------INSERTINTO`treenodes`VALUES('1','A','0');INSERTINTO`treenodes`VALUES('2','B','1');INSERTINTO`treenodes`VALUES('3','C','1');INSERTINTO`treenodes`VALUES('4','D','2');I
3、NSERTINTO`treenodes`VALUES('5','E','2');INSERTINTO`treenodes`VALUES('6','F','3');INSERTINTO`treenodes`VALUES('7','G','6');INSERTINTO`treenodes`VALUES('8','H','0');INSERTINTO`treenodes`VALUES('9','I','8');INSERTINTO`treenodes`VALUES('10','J','8');INSERTINTO`treenodes`VALUES('1
4、1','K','8');INSERTINTO`treenodes`VALUES('12','L','9');INSERTINTO`treenodes`VALUES('13','M','0');INSERTINTO`treenodes`VALUES('14','N','12');INSERTINTO`treenodes`VALUES('15','O','12');INSERTINTO`treenodes`VALUES('16','P','15');INSERTINTO`treenodes`VALUES('17','Q','15');存储过程：查询参
5、数节点下的所有子节点入口：CREATEDEFINER=`root`@`localhost`PROCEDURE`showChildLst`(IN`rootId`int)BEGIN#Routinebodygoeshere...setmax_sp_recursion_depth=225;CREATETEMPORARYTABLEIFNOTEXISTStmpLst(snointprimarykeyauto_increment,idint,depthint);DELETEFROMtmpLst;CALLcreateChildLst(rootId,0);sele
6、cttmpLst.*,treeNodes.*fromtmpLst,treeNodeswheretmpLst.id=treeNodes.idorderbytmpLst.sno;END向临时表插入数据CREATEDEFINER=`root`@`localhost`PROCEDURE`createChildLst`(IN`rootId`int,IN`nDepth`int)BEGIN#Routinebodygoeshere...DECLAREdoneINTDEFAULT0;DECLAREbINT;DECLAREcur1CURSORFORSELECTidF
7、ROMtreeNodeswherepid=rootId;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;insertintotmpLstvalues(null,rootId,nDepth);OPENcur1;FETCHcur1INTOb;WHILEdone=0DOCALLcreateChildLst(b,nDepth+1);FETCHcur1INTOb;ENDWHILE;CLOSEcur1;END查询参数节点下的所有父节点入口相同，只是插入数据过程不同CREATEDEFINER=`root`@`localho
8、st`PROCEDURE`createChildLst`(IN`rootId`int,IN`nDepth`int)BEGIN#Routi
