經常在一個表中有父子關系的兩個字段，比如empno與manager，這種結構中需要用到樹的遍歷。在Oracle 中可以使用connect by簡單解決問題，但MySQL 5.1中還不支持（據說已納入to do中），要自己寫過程或函數來實現。
一、建立測試表和數據：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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不能遞歸調用）：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
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 (NULL,rootId,nDepth); OPEN cur1; FETCH cur1 INTO b; WHILE done=0 DO CALL createParentLst(b,nDepth+1); FETCH cur1 INTO b; END WHILE; CLOSE cur1; END$$ -- 實現類似Oracle SYS_CONNECT_BY_PATH的功能 -- 遞歸過程輸出某節點id路徑 DROP PROCEDURE IF EXISTS `createPathLst`$$ CREATE PROCEDURE `createPathLst`(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 createPathLst(parentid,delimit,pathstr); FETCH cur1 INTO parentid,pathstr; END WHILE; CLOSE cur1; END$$ -- 遞歸過程輸出某節點name路徑 DROP PROCEDURE IF EXISTS `createPathnameLst`$$ CREATE PROCEDURE `createPathnameLst`(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 createPathnameLst(parentid,delimit,pathstr); FETCH cur1 INTO parentid,pathstr; END WHILE; CLOSE cur1; END$$ -- 調用函數輸出id路徑 DROP FUNCTION IF EXISTS `fn_tree_path`$$ CREATE FUNCTION `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 createPathLst(nid,delimit,@pathid); RETURN @pathid; END$$ -- 調用函數輸出name路徑 DROP FUNCTION IF EXISTS `fn_tree_pathname`$$ CREATE FUNCTION `fn_tree_pathname`(nid INT,delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8 BEGIN DECLARE pathid VARCHAR(1000); SET @pathid=''; CALL createPathnameLst(nid,delimit,@pathid); RETURN @pathid; END$$ -- 調用過程輸出子節點 DROP PROCEDURE IF EXISTS `showChildLst`$$ CREATE PROCEDURE `showChildLst`(IN rootId INT) BEGIN DROP TEMPORARY TABLE IF EXISTS tmpLst; CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst (sno INT PRIMARY KEY AUTO_INCREMENT,id INT,depth INT); CALL createChildLst(rootId,0); 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 FROM tmpLst,channel WHERE tmpLst.id=channel.id ORDER BY tmpLst.sno; END$$ -- 調用過程輸出父節點 DROP PROCEDURE IF EXISTS `showParentLst`$$ CREATE PROCEDURE `showParentLst`(IN rootId INT) BEGIN DROP TEMPORARY TABLE IF EXISTS tmpLst; CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst (sno INT PRIMARY KEY AUTO_INCREMENT,id INT,depth INT); CALL createParentLst(rootId,0); 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 FROM tmpLst,channel WHERE tmpLst.id=channel.id ORDER BY tmpLst.sno; END$$ DELIMITER ; |
三、測試
|
1
2
3
4
5
6
7
8
9
10
11
|
CALL showChildLst(-1); CALL showChildLst(13); CALL showChildLst(14); CALL showChildLst(17); CALL showChildLst(18); CALL showParentLst(-1); CALL showParentLst(13); CALL showParentLst(14); CALL showParentLst(17); CALL showParentLst(18); |
四、遺留問題
1. 因為mysql對動態游標的支持不夠，所以要想做成通用的過程或函數比較困難，可以利用兩個臨時表來轉換（同時去掉了遞歸調用）是個相對通用的實現。
2. 目前來看無論哪種實現，效率都不太好，希望mysql自己能實現Oracle 的connect by 功能，應該會比較優化。
感謝閱讀，希望能幫助到大家，謝謝大家對本站的支持！
原文鏈接：http://blog.csdn.net/wzy0623/article/details/53924307
