数据库教程:在Mysql数据库里通过存储过程实现树形的遍历分享

关于多级别菜单栏或者权限系统中部门上下级的树形遍历，oracle中有connectby来实现，mysql没有这样的便捷途径，所以MySQL遍历数据表是我们经常会遇到的头痛问题，下面通过存储过程来实现。

1，建立测试表和数据：

DROPTABLEIFEXISTScsdn.channel; CREATETABLEcsdn.channel( idINT(11)NOTNULLAUTO_INCREMENT, cnameVARCHAR(200)DEFAULTNULL, parent_idINT(11)DEFAULTNULL, PRIMARYKEY(id) )ENGINE=INNODBDEFAULTCHARSET=utf8; INSERTINTOchannel(id,cname,parent_id) VALUES(13,'首页',-1), (14,'TV580',-1), (15,'生活580',-1), (16,'左上幻灯片',13), (17,'帮忙',14), (18,'栏目简介',17); DROPTABLEIFEXISTSchannel;

2，利用临时表和递归过程实现树的遍历（mysql的UDF不能递归调用）：

2.1，从某节点向下遍历子节点，递归生成临时表数据

--pro_cre_childlist DROPPROCEDUREIFEXISTScsdn.pro_cre_childlist CREATEPROCEDUREcsdn.pro_cre_childlist(INrootIdINT,INnDepthINT) DECLAREdoneINTDEFAULT0; DECLAREbINT; DECLAREcur1CURSORFORSELECTidFROMchannelWHEREparent_id=rootId; DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1; SETmax_sp_recursion_depth=12; INSERTINTOtmpLstVALUES(NULL,rootId,nDepth); OPENcur1; FETCHcur1INTOb; WHILEdone=0DO CALLpro_cre_childlist(b,nDepth+1); FETCHcur1INTOb; ENDWHILE; CLOSEcur1;

2.2，从某节点向上追溯根节点，递归生成临时表数据

--pro_cre_parentlist DROPPROCEDUREIFEXISTScsdn.pro_cre_parentlist CREATEPROCEDUREcsdn.pro_cre_parentlist(INrootIdINT,INnDepthINT) BEGIN DECLAREdoneINTDEFAULT0; DECLAREbINT; DECLAREcur1CURSORFORSELECTparent_idFROMchannelWHEREid=rootId; DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1; SETmax_sp_recursion_depth=12; INSERTINTOtmpLstVALUES(NULL,rootId,nDepth); OPENcur1; FETCHcur1INTOb; WHILEdone=0DO CALLpro_cre_parentlist(b,nDepth+1); FETCHcur1INTOb; ENDWHILE; CLOSEcur1;

2.3，实现类似OracleSYS_CONNECT_BY_PATH的功能，递归过程输出某节点id路径

--pro_cre_pathlist USEcsdn DROPPROCEDUREIFEXISTSpro_cre_pathlist CREATEPROCEDUREpro_cre_pathlist(INnidINT,INdelimitVARCHAR(10),INOUTpathstrVARCHAR(1000)) BEGIN DECLAREdoneINTDEFAULT0; DECLAREparentidINTDEFAULT0; DECLAREcur1CURSORFOR SELECTt.parent_id,CONCAT(CAST(t.parent_idASCHAR),delimit,pathstr) FROMchannelAStWHEREt.id=nid; DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1; SETmax_sp_recursion_depth=12; OPENcur1; FETCHcur1INTOparentid,pathstr; WHILEdone=0DO CALLpro_cre_pathlist(parentid,delimit,pathstr); FETCHcur1INTOparentid,pathstr; ENDWHILE; CLOSEcur1; DELIMITER;

2.4，递归过程输出某节点name路径

--pro_cre_pnlist USEcsdn DROPPROCEDUREIFEXISTSpro_cre_pnlist CREATEPROCEDUREpro_cre_pnlist(INnidINT,INdelimitVARCHAR(10),INOUTpathstrVARCHAR(1000)) BEGIN DECLAREdoneINTDEFAULT0; DECLAREparentidINTDEFAULT0; DECLAREcur1CURSORFOR SELECTt.parent_id,CONCAT(t.cname,delimit,pathstr) FROMchannelAStWHEREt.id=nid; DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1; SETmax_sp_recursion_depth=12; OPENcur1; FETCHcur1INTOparentid,pathstr; WHILEdone=0DO CALLpro_cre_pnlist(parentid,delimit,pathstr); FETCHcur1INTOparentid,pathstr; ENDWHILE; CLOSEcur1; DELIMITER;

2.5，调用函数输出id路径

--fn_tree_path DELIMITER DROPFUNCTIONIFEXISTScsdn.fn_tree_path CREATEFUNCTIONcsdn.fn_tree_path(nidINT,delimitVARCHAR(10))RETURNSVARCHAR(2000)CHARSETutf8 BEGIN DECLAREpathidVARCHAR(1000); SETpathid=CAST(nidASCHAR); CALLpro_cre_pathlist(nid,delimit,pathid); RETURNpathid; END

2.6，调用函数输出name路径

--fn_tree_pathname --调用函数输出name路径 DELIMITER DROPFUNCTIONIFEXISTScsdn.fn_tree_pathname CREATEFUNCTIONcsdn.fn_tree_pathname(nidINT,delimitVARCHAR(10))RETURNSVARCHAR(2000)CHARSETutf8 BEGIN DECLAREpathidVARCHAR(1000); SETpathid=''; CALLpro_cre_pnlist(nid,delimit,pathid); RETURNpathid; END DELIMITER;

2.7，调用过程输出子节点

--pro_show_childLst DELIMITER --调用过程输出子节点 DROPPROCEDUREIFEXISTSpro_show_childLst CREATEPROCEDUREpro_show_childLst(INrootIdINT) BEGIN DROPTEMPORARYTABLEIFEXISTStmpLst; CREATETEMPORARYTABLEIFNOTEXISTStmpLst (snoINTPRIMARYKEYAUTO_INCREMENT,idINT,depthINT); CALLpro_cre_childlist(rootId,0); SELECTchannel.id,CONCAT(SPACE(tmpLst.depth*2),'--',channel.cname)NAME,channel.parent_id,tmpLst.depth,fn_tree_path(channel.id,'/')path,fn_tree_pathname(channel.id,'/')pathname FROMtmpLst,channelWHEREtmpLst.id=channel.idORDERBYtmpLst.sno; END

2.8，调用过程输出父节点

--pro_show_parentLst DELIMITER --调用过程输出父节点 DROPPROCEDUREIFEXISTS`pro_show_parentLst` CREATEPROCEDURE`pro_show_parentLst`(INrootIdINT) BEGIN DROPTEMPORARYTABLEIFEXISTStmpLst; CREATETEMPORARYTABLEIFNOTEXISTStmpLst (snoINTPRIMARYKEYAUTO_INCREMENT,idINT,depthINT); CALLpro_cre_parentlist(rootId,0); SELECTchannel.id,CONCAT(SPACE(tmpLst.depth*2),'--',channel.cname)NAME,channel.parent_id,tmpLst.depth,fn_tree_path(channel.id,'/')path,fn_tree_pathname(channel.id,'/')pathname FROMtmpLst,channelWHEREtmpLst.id=channel.idORDERBYtmpLst.sno; END

3，开始测试：

3.1，从根节点开始显示，显示子节点集合：

mysql>CALLpro_show_childLst(-1); +----+-----------------------+-----------+-------+-------------+----------------------------+ |id|NAME|parent_id|depth|path|pathname| +----+-----------------------+-----------+-------+-------------+----------------------------+ |13|--首页|-1|1|-1/13|首页/| |16|--左上幻灯片|13|2|-1/13/16|首页/左上幻灯片/| |14|--TV580|-1|1|-1/14|TV580/| |17|--帮忙|14|2|-1/14/17|TV580/帮忙/| |18|--栏目简介|17|3|-1/14/17/18|TV580/帮忙/栏目简介/| |15|--生活580|-1|1|-1/15|生活580/| +----+-----------------------+-----------+-------+-------------+----------------------------+ 6rowsinset(0.05sec) QueryOK,0rowsaffected(0.05sec)

3.2，显示首页下面的子节点

CALLpro_show_childLst(13); mysql>CALLpro_show_childLst(13); +----+---------------------+-----------+-------+----------+-------------------------+ |id|NAME|parent_id|depth|path|pathname| +----+---------------------+-----------+-------+----------+-------------------------+ |13|--首页|-1|0|-1/13|首页/| |16|--左上幻灯片|13|1|-1/13/16|首页/左上幻灯片/| +----+---------------------+-----------+-------+----------+-------------------------+ 2rowsinset(0.02sec) QueryOK,0rowsaffected(0.02sec) mysql>

3.3，显示TV580下面的所有子节点

CALLpro_show_childLst(14); mysql>CALLpro_show_childLst(14); |id|NAME|parent_id|depth|path|pathname| |14|--TV580|-1|0|-1/14|TV580/| |17|--帮忙|14|1|-1/14/17|TV580/帮忙/| |18|--栏目简介|17|2|-1/14/17/18|TV580/帮忙/栏目简介/| 3rowsinset(0.02sec) QueryOK,0rowsaffected(0.02sec) mysql>

3.4，“帮忙”节点有一个子节点，显示出来：

CALLpro_show_childLst(17); mysql>CALLpro_show_childLst(17); |id|NAME|parent_id|depth|path|pathname| |17|--帮忙|14|0|-1/14/17|TV580/帮忙/| |18|--栏目简介|17|1|-1/14/17/18|TV580/帮忙/栏目简介/| 2rowsinset(0.03sec) QueryOK,0rowsaffected(0.03sec) mysql>

3.5，“栏目简介”没有子节点，所以只显示最终节点：

mysql>CALLpro_show_childLst(18); +--|id|NAME|parent_id|depth|path|pathname| |18|--栏目简介|17|0|-1/14/17/18|TV580/帮忙/栏目简介/| 1rowinset(0.36sec) QueryOK,0rowsaffected(0.36sec) mysql>

3.6，显示根节点的父节点

CALLpro_show_parentLst(-1); mysql>CALLpro_show_parentLst(-1); Emptyset(0.01sec) QueryOK,0rowsaffected(0.01sec) mysql>

3.7，显示“首页”的父节点

CALLpro_show_parentLst(13); mysql>CALLpro_show_parentLst(13); |id|NAME|parent_id|depth|path|pathname| |13|--首页|-1|0|-1/13|首页/| 1rowinset(0.02sec) QueryOK,0rowsaffected(0.02sec) mysql>

3.8，显示“TV580”的父节点，parent_id为-1

CALLpro_show_parentLst(14); mysql>CALLpro_show_parentLst(14); |id|NAME|parent_id|depth|path|pathname| |14|--TV580|-1|0|-1/14|TV580/| 1rowinset(0.02sec) QueryOK,0rowsaffected(0.02sec)

3.9，显示“帮忙”节点的父节点

CALLpro_show_parentLst(17); mysql>CALLpro_show_parentLst(17); |id|NAME|parent_id|depth|path|pathname| |17|--帮忙|14|0|-1/14/17|TV580/帮忙/| |14|--TV580|-1|1|-1/14|TV580/| 2rowsinset(0.02sec) QueryOK,0rowsaffected(0.02sec) mysql>

3.10，显示最低层节点“栏目简介”的父节点

CALLpro_show_parentLst(18); mysql>CALLpro_show_parentLst(18); |id|NAME|parent_id|depth|path|pathname| |18|--栏目简介|17|0|-1/14/17/18|TV580/帮忙/栏目简介/| |17|--帮忙|14|1|-1/14/17|TV580/帮忙/| |14|--TV580|-1|2|-1/14|TV580/| 3rowsinset(0.02sec) QueryOK,0rowsaffected(0.02sec) mysql>

上述就是数据库技术:在Mysql数据库里通过存储过程实现树形的遍历分享的全部内容，如果对大家有所用处且需要了解更多关于mysql数据库学习教程，希望大家多多关注—计算机技术网(www.ctvol.com)!