mysql遞迴查詢

標籤：遞迴   方便   iter   roo   函數   --   sel   group   dep   

範例資料：

create table treeNodes(   id int primary key,   nodename varchar(20),   pid int );select * from treeNodes;+----+----------+------+| id | nodename | pid  |+----+----------+------+|  1 | A        |    0 ||  2 | B        |    1 ||  3 | C        |    1 ||  4 | D        |    2 ||  5 | E        |    2 ||  6 | F        |    3 ||  7 | G        |    6 ||  8 | H        |    0 ||  9 | I        |    8 || 10 | J        |    8 || 11 | K        |    8 || 12 | L        |    9 || 13 | M        |    9 || 14 | N        |   12 || 15 | O        |   12 || 16 | P        |   15 || 17 | Q        |   15 |+----+----------+------+17 rows in set (0.00 sec)

樹形圖如下

 1:A  +-- 2:B  |    +-- 4:D  |    +-- 5:E  +-- 3:C       +-- 6:F            +-- 7:G 8:H  +-- 9:I  |    +-- 12:L  |    |    +--14:N  |    |    +--15:O  |    |        +--16:P  |    |        +--17:Q  |    +-- 13:M  +-- 10:J  +-- 11:K  

建立一個function getChildLst, 得到一個由所有子節點號組成的字串.

 delimiter // CREATE FUNCTION `getChildLst`(rootId INT)     RETURNS varchar(1000)     BEGIN       DECLARE sTemp VARCHAR(1000);       DECLARE sTempChd VARCHAR(1000);           SET sTemp = ‘$‘;       SET sTempChd =cast(rootId as CHAR);           WHILE sTempChd is not null DO         SET sTemp = concat(sTemp,‘,‘,sTempChd);         SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;       END WHILE;       RETURN sTemp;     END     // delimiter ;

使用我們直接利用find_in_set函數配合這個getChildlst來尋找

select getChildLst(1);+-----------------+| getChildLst(1)  |+-----------------+| $,1,2,3,4,5,6,7 |+-----------------+select * from treeNodes where FIND_IN_SET(id, getChildLst(1));+----+----------+------+| id | nodename | pid  |+----+----------+------+|  1 | A        |    0 ||  2 | B        |    1 ||  3 | C        |    1 ||  4 | D        |    2 ||  5 | E        |    2 ||  6 | F        |    3 ||  7 | G        |    6 |+----+----------+------+select * from treeNodes where FIND_IN_SET(id, getChildLst(3));+----+----------+------+| id | nodename | pid  |+----+----------+------+|  3 | C        |    1 ||  6 | F        |    3 ||  7 | G        |    6 |+----+----------+------+

優點：簡單，方便，沒有遞迴調用層次深度的限制 (max_sp_recursion_depth,最大255);

缺點：長度受限，雖然可以擴大 RETURNS varchar(1000)，但總是有最大限制的。

mysql遞迴查詢