关于多级别菜单栏或者权限系统中部门上下级的树形遍历，oracle中有connect by来实现，mysql没有这样的便捷途径，所以MySQL遍历数据表是我们经常会遇到的头痛问题，下面通过存储过程来实现。
1，建立测试表和数据：
| DROP TABLE IF EXISTS csdn.channel; CREATE TABLE csdn.channel ( id INT(11) NOT NULL AUTO_INCREMENT, cname VARCHAR(200) DEFAULT NULL, parent_id INT(11) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO channel(id,cname,parent_id) VALUES (13,'首页',-1), (14,'TV580',-1), (15,'生活580',-1), (16,'左上幻灯片',13), (17,'帮忙',14), (18,'栏目简介',17); DROP TABLE IF EXISTS channel; |
2，利用临时表和递归过程实现树的遍历（mysql的UDF不能递归调用）：
2.1，从某节点向下遍历子节点，递归生成临时表数据
| -- pro_cre_childlistDROP PROCEDURE IF EXISTS csdn.pro_cre_childlistCREATE PROCEDURE csdn.pro_cre_childlist(IN rootId INT,IN nDepth INT)DECLARE done INT DEFAULT 0;DECLARE b INT;DECLARE cur1 CURSOR FOR SELECT id FROM channel WHERE parent_id=rootId;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;SET max_sp_recursion_depth=12;INSERT INTO tmpLst VALUES (NULL,rootId,nDepth); OPEN cur1; FETCH cur1 INTO b; WHILE done=0 DO CALL pro_cre_childlist(b,nDepth+1); FETCH cur1 INTO b; END WHILE; CLOSE cur1; |
2.2，从某节点向上追溯根节点，递归生成临时表数据
| -- pro_cre_parentlistDROP PROCEDURE IF EXISTS csdn.pro_cre_parentlistCREATE PROCEDURE csdn.pro_cre_parentlist(IN rootId INT,IN nDepth INT) BEGIN DECLARE done INT DEFAULT 0; DECLARE b INT; DECLARE cur1 CURSOR FOR SELECT parent_id FROM channel WHERE id=rootId; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SET max_sp_recursion_depth=12; INSERT INTO tmpLst VALUES (NULL,rootId,nDepth); OPEN cur1; FETCH cur1 INTO b; WHILE done=0 DO CALL pro_cre_parentlist(b,nDepth+1); FETCH cur1 INTO b; END WHILE; CLOSE cur1; |
2.3，实现类似Oracle SYS_CONNECT_BY_PATH的功能，递归过程输出某节点id路径
| -- pro_cre_pathlistUSE csdnDROP PROCEDURE IF EXISTS pro_cre_pathlistCREATE PROCEDURE pro_cre_pathlist(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(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; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SET max_sp_recursion_depth=12; OPEN cur1; FETCH cur1 INTO parentid,pathstr; WHILE done=0 DO CALL pro_cre_pathlist(parentid,delimit,pathstr); FETCH cur1 INTO parentid,pathstr; END WHILE; CLOSE cur1; DELIMITER ; |
2.4，递归过程输出某节点name路径
| -- pro_cre_pnlistUSE csdnDROP PROCEDURE IF EXISTS pro_cre_pnlistCREATE PROCEDURE pro_cre_pnlist(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))BEGIN DECLARE done INT DEFAULT 0; DECLARE parentid INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT t.parent_id,CONCAT(t.cname,delimit,pathstr) FROM channel AS t WHERE t.id = nid; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SET max_sp_recursion_depth=12; OPEN cur1; FETCH cur1 INTO parentid,pathstr; WHILE done=0 DO CALL pro_cre_pnlist(parentid,delimit,pathstr); FETCH cur1 INTO parentid,pathstr; END WHILE; CLOSE cur1; DELIMITER ; |
