资源描述：
《mysql递归调用获取树节点(子树)》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、创建树形数据存储表，及其数据：SETFOREIGN_KEY_CHECKS=0;--------------------------------Tablestructurefor`treenodes`------------------------------DROPTABLEIFEXISTS`treenodes`;CREATETABLE`treenodes`(`id`int(11)NOTNULL,`nodename`varchar(20)DEFAULTNULL,`pid`int(11)DEFAULTN
2、ULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;--------------------------------Recordsoftreenodes------------------------------INSERTINTO`treenodes`VALUES('1','A','0');INSERTINTO`treenodes`VALUES('2','B','1');INSERTINTO`treenodes`VALUES('3','C','
3、1');INSERTINTO`treenodes`VALUES('4','D','2');INSERTINTO`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');INS
4、ERTINTO`treenodes`VALUES('10','J','8');INSERTINTO`treenodes`VALUES('11','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');IN
5、SERTINTO`treenodes`VALUES('16','P','15');INSERTINTO`treenodes`VALUES('17','Q','15');存储过程：查询参数节点下的所有子节点入口：CREATEDEFINER=`root`@`localhost`PROCEDURE`showChildLst`(IN`rootId`int)BEGIN#Routinebodygoeshere...setmax_sp_recursion_depth=225;CREATETEMPORARYTABLE
6、IFNOTEXISTStmpLst(snointprimarykeyauto_increment,idint,depthint);DELETEFROMtmpLst;CALLcreateChildLst(rootId,0);selecttmpLst.*,treeNodes.*fromtmpLst,treeNodeswheretmpLst.id=treeNodes.idorderbytmpLst.sno;END向临时表插入数据CREATEDEFINER=`root`@`localhost`PROCEDUR
7、E`createChildLst`(IN`rootId`int,IN`nDepth`int)BEGIN#Routinebodygoeshere...DECLAREdoneINTDEFAULT0;DECLAREbINT;DECLAREcur1CURSORFORSELECTidFROMtreeNodeswherepid=rootId;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;insertintotmpLstvalues(null,rootId,nDepth);O
8、PENcur1;FETCHcur1INTOb;WHILEdone=0DOCALLcreateChildLst(b,nDepth+1);FETCHcur1INTOb;ENDWHILE;CLOSEcur1;END查询参数节点下的所有父节点入口相同，只是插入数据过程不同CREATEDEFINER=`root`@`localhost`PROCEDURE`createChildLst`(IN`rootId`int,IN`nDepth`int)BEGIN#Routi
