经常在一个表中有父子关系的两个字段，比如empno与manager，这种结构中需要用到树的遍历。在Oracle 中可以使用connect by简单解决问题，但MySQL 5.1中还不支持（据说已纳入to do中），要自己写过程或函数来实现。

一、建立测试表和数据：

[c-sharp]

1. DROP TABLE IF EXISTS `channel`;  
3. CREATE TABLE `channel` (  
4.   `id` int(11) NOT NULL AUTO_INCREMENT,    
5.   `cname` varchar(200) DEFAULT NULL,  
6.   `parent_id` int(11) DEFAULT NULL,  
7.   PRIMARY KEY (`id`)  
8. ) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;  
10. /*Data for the table `channel` */
12. insert  into `channel`(`id`,`cname`,`parent_id`)   
13. values (13,'首页',-1),  
14.        (14,'TV580',-1),  
15.        (15,'生活580',-1),  
16.        (16,'左上幻灯片',13),  
17.        (17,'帮忙',14),  
18.        (18,'栏目简介',17);  

二、利用临时表和递归过程实现树的遍历（mysql的UDF不能递归调用）：

[c-sharp]

1. DELIMITER $$  
3. USE `db1`$$  
6. -- 从某节点向下遍历子节点  
7. -- 递归生成临时表数据  
8. DROP PROCEDURE IF EXISTS `createChildLst`$$  
10. CREATE PROCEDURE `createChildLst`(IN rootId INT,IN nDepth INT)  
11. BEGIN  
12.       DECLARE done INT DEFAULT 0;  
13.       DECLARE b INT;  
14.       DECLARE cur1 CURSOR FOR SELECT id FROM channel WHERE parent_id=rootId;  
15.       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
16.       SET max_sp_recursion_depth=12;  
18.       INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);  
20.       OPEN cur1;  
22.       FETCH cur1 INTO b;  
23.       WHILE done=0 DO  
24.               CALL createChildLst(b,nDepth+1);  
25.               FETCH cur1 INTO b;  
26.       END WHILE;  
28.       CLOSE cur1;  
29.      END$$  
32. -- 从某节点向上追溯根节点  
33. -- 递归生成临时表数据  
34. DROP PROCEDURE IF EXISTS `createParentLst`$$  
36. CREATE PROCEDURE `createParentLst`(IN rootId INT,IN nDepth INT)  
37. BEGIN  
38.       DECLARE done INT DEFAULT 0;  
39.       DECLARE b INT;  
40.       DECLARE cur1 CURSOR FOR SELECT parent_id FROM channel WHERE id=rootId;  
41.       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
42.       SET max_sp_recursion_depth=12;  
44.       INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);  
46.       OPEN cur1;  
48.       FETCH cur1 INTO b;  
49.       WHILE done=0 DO  
50.               CALL createParentLst(b,nDepth+1);  
51.               FETCH cur1 INTO b;  
52.       END WHILE;  
54.       CLOSE cur1;  
55.      END$$  
58. -- 实现类似Oracle SYS_CONNECT_BY_PATH的功能  
59. -- 递归过程输出某节点id路径  
60. DROP PROCEDURE IF EXISTS `createPathLst`$$  
62. CREATE PROCEDURE `createPathLst`(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))  
63. BEGIN                    
64.       DECLARE done INT DEFAULT 0;  
65.       DECLARE parentid INT DEFAULT 0;        
66.       DECLARE cur1 CURSOR FOR   
67.       SELECT t.parent_id,CONCAT(CAST(t.parent_id AS CHAR),delimit,pathstr)  
68.         FROM channel AS t WHERE t.id = nid;  
70.       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
71.       SET max_sp_recursion_depth=12;                    
73.       OPEN cur1;  
75.       FETCH cur1 INTO parentid,pathstr;  
76.       WHILE done=0 DO             
77.               CALL createPathLst(parentid,delimit,pathstr);  
78.               FETCH cur1 INTO parentid,pathstr;  
79.       END WHILE;  
81.       CLOSE cur1;   
82.      END$$  
85. -- 递归过程输出某节点name路径  
86. DROP PROCEDURE IF EXISTS `createPathnameLst`$$  
88. CREATE PROCEDURE `createPathnameLst`(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))  
89. BEGIN                    
90.       DECLARE done INT DEFAULT 0;  
91.       DECLARE parentid INT DEFAULT 0;        
92.       DECLARE cur1 CURSOR FOR   
93.       SELECT t.parent_id,CONCAT(t.cname,delimit,pathstr)  
94.         FROM channel AS t WHERE t.id = nid;  
96.       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
97.       SET max_sp_recursion_depth=12;                    
99.       OPEN cur1;  
101.       FETCH cur1 INTO parentid,pathstr;  
102.       WHILE done=0 DO             
103.               CALL createPathnameLst(parentid,delimit,pathstr);  
104.               FETCH cur1 INTO parentid,pathstr;  
105.       END WHILE;  
107.       CLOSE cur1;   
108.      END$$  
111. -- 调用函数输出id路径  
112. DROP FUNCTION IF EXISTS `fn_tree_path`$$  
114. CREATE FUNCTION `fn_tree_path`(nid INT,delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8  
115. BEGIN    
116.   DECLARE pathid VARCHAR(1000);  
118.   SET @pathid=CAST(nid AS CHAR);  
119.   CALL createPathLst(nid,delimit,@pathid);  
121.   RETURN @pathid;  
122. END$$  
125. -- 调用函数输出name路径  
126. DROP FUNCTION IF EXISTS `fn_tree_pathname`$$  
128. CREATE FUNCTION `fn_tree_pathname`(nid INT,delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8  
129. BEGIN    
130.   DECLARE pathid VARCHAR(1000);  
132.   SET @pathid='';      
133.   CALL createPathnameLst(nid,delimit,@pathid);  
135.   RETURN @pathid;  
136. END$$  
139. -- 调用过程输出子节点  
140. DROP PROCEDURE IF EXISTS `showChildLst`$$  
142. CREATE PROCEDURE `showChildLst`(IN rootId INT)  
143. BEGIN  
144.       DROP TEMPORARY TABLE IF EXISTS tmpLst;  
145.       CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst   
146.        (sno INT PRIMARY KEY AUTO_INCREMENT,id INT,depth INT);        
148.       CALL createChildLst(rootId,0);  
150.       SELECT channel.id,CONCAT(SPACE(tmpLst.depth*2),'--',channel.cname) NAME,channel.parent_id,tmpLst.depth,fn_tree_path(channel.id,'/') path,fn_tree_pathname(channel.id,'/') pathname  
151.       FROM tmpLst,channel WHERE tmpLst.id=channel.id ORDER BY tmpLst.sno;  
152.      END$$  
154. -- 调用过程输出父节点  
155. DROP PROCEDURE IF EXISTS `showParentLst`$$  
157. CREATE PROCEDURE `showParentLst`(IN rootId INT)  
158. BEGIN  
159.       DROP TEMPORARY TABLE IF EXISTS tmpLst;  
160.       CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst   
161.        (sno INT PRIMARY KEY AUTO_INCREMENT,id INT,depth INT);        
163.       CALL createParentLst(rootId,0);  
165.       SELECT channel.id,CONCAT(SPACE(tmpLst.depth*2),'--',channel.cname) NAME,channel.parent_id,tmpLst.depth,fn_tree_path(channel.id,'/') path,fn_tree_pathname(channel.id,'/') pathname  
166.       FROM tmpLst,channel WHERE tmpLst.id=channel.id ORDER BY tmpLst.sno;  
167.      END$$  
170. DELIMITER ;  

三、测试

[c-sharp]

1. CALL showChildLst(-1);  
2. CALL showChildLst(13);  
3. CALL showChildLst(14);  
4. CALL showChildLst(17);  
5. CALL showChildLst(18);  
7. CALL showParentLst(-1);  
8. CALL showParentLst(13);  
9. CALL showParentLst(14);  
10. CALL showParentLst(17);  
11. CALL showParentLst(18);  

四、遗留问题 1. 因为mysql对动态游标的支持不够，所以要想做成通用的过程或函数比较困难，可以利用两个临时表来转换（同时去掉了递归调用），是个相对通用的实现。 2. 目前来看无论哪种实现，效率都不太好，希望mysql自己能实现oracle 的connect by 功能，应该会比较优化。

参考：MySQL中进行树状所有子节点的查询