多级树形目录mysql的使用_MySQL 实现树形的遍历（关于多级菜单栏以及多级上下部门的查询问题）...

前言：关于多级别菜单栏或者权限系统中部门上下级的树形遍历，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_childlistDELIMITER $$DROP PROCEDURE IF EXISTS csdn.pro_cre_childlist$$CREATE PROCEDURE csdn.pro_cre_childlist(IN rootId INT,IN nDepth INT)BEGINDECLARE 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 DOCALL pro_cre_childlist(b,nDepth+1);FETCH cur1 INTO b;END WHILE;CLOSE cur1;END$$2.2，从某节点向上追溯根节点，递归生成临时表数据-- pro_cre_parentlistDELIMITER $$DROP PROCEDURE IF EXISTS csdn.pro_cre_parentlist$$CREATE PROCEDURE csdn.pro_cre_parentlist(IN rootId INT,IN nDepth INT)BEGINDECLARE 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 DOCALL pro_cre_parentlist(b,nDepth+1);FETCH cur1 INTO b;END WHILE;CLOSE cur1;END$$2.3，实现类似Oracle SYS_CONNECT_BY_PATH的功能，递归过程输出某节点id路径-- pro_cre_pathlistDELIMITER $$USE csdn$$DROP PROCEDURE IF EXISTS pro_cre_pathlist$$CREATE PROCEDURE pro_cre_pathlist(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))BEGINDECLARE done INT DEFAULT 0;DECLARE parentid INT DEFAULT 0;DECLARE cur1 CURSOR FORSELECT 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&#