MySQL实现树的遍历

经常在一个表中有父子关系的两个字段，比如empno与manager，这种结构中需要用到树的遍历。在Oracle 中可以使用connect by简单解决问题，参见http://blog.csdn.net/wzy0623/archive/2007/06/18/1656345.aspx，但MySQL 5.1中还不支持（据说已纳入to do中），要自己写过程或函数来实现。
一、建立测试表和数据：
DROP TABLE IF EXISTS `channel`;
CREATE TABLE `channel` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(200) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
/*Data for the table `channel` */
insert into `channel`(`id`,`cname`,`parent_id`)
values (13,'首页',-1),
(14,'TV580',-1),
(15,'生活580',-1),
(16,'左上幻灯片',13),
(17,'帮忙',14),
(18,'栏目简介',17);

二、利用临时表和递归过程实现树的遍历（mysql的UDF不能递归调用）：
DELIMITER $$
USE `db1`$$
-- 从某节点向下遍历子节点
-- 递归生成临时表数据
DROP PROCEDURE IF EXISTS `createChildLst`$$
CREATE PROCEDURE `createChildLst`(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 createChildLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;

CLOSE cur1;
END$$
-- 从某节点向上追溯根节点
-- 递归生成临时表数据
DROP PROCEDURE IF EXISTS `createParentLst`$$
CREATE PROCEDURE `createParentLst`(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