mysql树形遍历,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