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

2024-03-10 05:59:05 151

关于多级别菜单栏或者权限系统中部门上下级的树形遍历，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数据库里通过存储过程实现树形的遍历

热门推荐

随机推荐