前言：
　　关于多级别菜单栏或者权限系统中部门上下级的树形遍历，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_childlist
DELIMITER
DROP PROCEDURE IF EXISTS csdn.pro_cre_childlist
CREATE PROCEDURE csdn.pro_cre_childlist(IN rootId INT，IN nDepth INT)
BEGIN
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;
END
　　2.2，从某节点向上追溯根节点，递归生成临时表数据
-- pro_cre_parentlist
DELIMITER
DROP PROCEDURE IF EXISTS csdn.pro_cre_parentlist
CREATE 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;
END
　　2.3，实现类似Oracle SYS_CONNECT_BY_PATH的功能，递归过程输出某节点id路径L
-- pro_cre_pathlist
DELIMITER
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))
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;
END
DELIMITER ;
　　2.4，递归过程输出某节点name路径
-- pro_cre_pnlist
DELIMITER
USE csdn
DROP PROCEDURE IF EXISTS pro_cre_pnlist
CREATE 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;
END
DELIMITER ;
　　2.5，调用函数输出id路径
　　-- fn_tree_path
　　DELIMITER
　　DROP FUNCTION IF EXISTS csdn.fn_tree_path
　　CREATE FUNCTION csdn.fn_tree_path(nid INT，delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8
　　BEGIN
　　DECLARE pathid VARCHAR(1000);
　　SET pathid=CAST(nid AS CHAR);
　　CALL pro_cre_pathlist(nid，delimit，pathid);
　　RETURN pathid;
　　END