前言:
关于多级别菜单栏或者权限系统中部门上下级的树形遍历,oracle中有connect by来实现,mysql没有这样的便捷途径,所以MySQL遍历数据表是我们经常会遇到的头痛问题,下面通过存储过程来实现。
1、建立测试表和数据:
DROP TABLE IF EXISTStest.channel;CREATE TABLEtest.channel (
idINT(11) NOT NULLAUTO_INCREMENT,
cnameVARCHAR(200) DEFAULT NULL,
parent_idINT(11) DEFAULT NULL,PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTOchannel(id,cname,parent_id)VALUES (13,'首页',-1),
(14,'TV580',-1),
(15,'生活580',-1),
(16,'左上幻灯片',13),
(17,'帮忙',14),
(18,'栏目简介',17);
2、用临时表和递归过程实现树的遍历(mysql的UDF不能递归调用):
2.1、递归过程输出某节点id路径,类似Oracle SYS_CONNECT_BY_PATH的功能
--递归输出某节点id路径
DELIMITER //
DROP PROCEDURE IF EXISTSpro_cre_pathlist;CREATE PROCEDURE pro_cre_pathlist(IN nid INT,IN delimit VARCHAR(10),
INOUT pathstrVARCHAR(1000))BEGIN
DECLARE done INT DEFAULT 0;DECLARE parentid INT DEFAULT 0;DECLARE cur1 CURSOR FOR
SELECT t.parent_id,CONCAT(CAST(t.parent_id AS CHAR),delimit,pathstr)from channel AS t WHERE t.id =nid;--下面这行表示若没有数据返回,程序继续,并将变量done设为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;--mysql中可以利用系统参数 max_sp_recursion_depth 来控制递归调用的层数上限。
SET max_sp_recursion_depth=12;OPENcur1;--游标向下走一步
FETCH cur1 INTOparentid,pathstr;WHILE done=0DO
CALL pro_cre_pathlist(parentid,delimit,pathstr);--游标向下走一步
FETCH cur1 INTOparentid,pathstr;END WHILE;CLOSEcur1;END //DELIMITER ;
测试:
SET @str='16';
CALL pro_cre_pathlist(16,'/',@str);SELECT @str;
测试结果: