關(guān)于多級別菜單欄或者權(quán)限系統(tǒng)中部門上下級的樹形遍歷，oracle中有connect by來實現(xiàn)，mysql沒有這樣的便捷途徑，所以MySQL遍歷數(shù)據(jù)表是我們經(jīng)常會遇到的頭痛問題，下面通過存儲過程來實現(xiàn)。
1，建立測試表和數(shù)據(jù)：
| 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，利用臨時表和遞歸過程實現(xiàn)樹的遍歷（mysql的UDF不能遞歸調(diào)用）：
2.1，從某節(jié)點向下遍歷子節(jié)點，遞歸生成臨時表數(shù)據(jù)
| -- 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，從某節(jié)點向上追溯根節(jié)點，遞歸生成臨時表數(shù)據(jù)
| -- 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，實現(xiàn)類似Oracle SYS_CONNECT_BY_PATH的功能，遞歸過程輸出某節(jié)點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，遞歸過程輸出某節(jié)點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 ; |
