mysql的逆襲:如何做遞歸層次查詢
最近在做一個從oracle數據庫到mysql數據庫的移植，遇到一個這樣的問題
在Oracle 中我們知道有一個 Hierarchical Queries 通過CONNECT BY 我們可以方便的查了所有當前節點下的所有子節點。但shi，在MySQL的目前版本中還沒有對應的函數！！！
換句話來說，想要用mysql實現遞歸查詢，根本做不到！！！
可是經過我數天茶不思飯不想的刻苦琢磨，終於想到了一個合理的，適用於mysql和其他sql的解決方案。
www.2cto.com
方案一出，就秋風掃落葉之勢，席卷整個dao層~~~所到之處，所有問題迎刃而解，讓所有問題都不再為問題 都成為了我這個函數的炮灰而已。。。
話不多說待我把解決方法仔細道來~~~~~
下面是sql腳本，想要運行一下 把下邊的粘貼復制下來，做一個treenodes.sq直接運行便是。。。
/*
Navicat MySQL Data Transfer
Source Server : mysql_demo3
Source Server Version : 50521
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 50521
File Encoding : 65001
www.2cto.com
Date: 2012-09-02 21:16:03
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `treenodes`
-- ----------------------------
DROP TABLE IF EXISTS `treenodes`;
CREATE TABLE `treenodes` (
`id` int(11) NOT NULL,
`nodename` varchar(20) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of treenodes
-- ----------------------------
INSERT INTO `treenodes` VALUES ('1', 'A', '0');
INSERT INTO `treenodes` VALUES ('2', 'B', '1');
INSERT INTO `treenodes` VALUES ('3', 'C', '1');
INSERT INTO `treenodes` VALUES ('4', 'D', '2');
INSERT INTO `treenodes` VALUES ('5', 'E', '2');
INSERT INTO `treenodes` VALUES ('6', 'F', '3');
INSERT INTO `treenodes` VALUES ('7', 'G', '6');
INSERT INTO `treenodes` VALUES ('8', 'H', '0');
INSERT INTO `treenodes` VALUES ('9', 'I', '8');
INSERT INTO `treenodes` VALUES ('10', 'J', '8');
INSERT INTO `treenodes` VALUES ('11', 'K', '8');
INSERT INTO `treenodes` VALUES ('12', 'L', '9');
INSERT INTO `treenodes` VALUES ('13', 'M', '9');
INSERT INTO `treenodes` VALUES ('14', 'N', '12');
INSERT INTO `treenodes` VALUES ('15', 'O', '12');
INSERT INTO `treenodes` VALUES ('16', 'P', '15');
INSERT INTO `treenodes` VALUES ('17', 'Q', '15');
www.2cto.com
---------------------------------------------------
上邊是sql腳本，在執行select * 之後顯示的結果集如下所示：
mysql> 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
--------------------------------------------
如果給你一個這樣的table，讓你查詢根節點為1下的所有節點記錄（注意也包括根節點），，腫麽辦？？？？？
可能有不少人想到connect by 函數，但是我灰常遺憾的告訴你，咱這兒是mysql！！！
好，客觀您勒上眼，，我的解決辦法是
利用函數來得到所有子節點號。
閒話少續，看我的解決方法
創建一個function getChildLst, 得到一個由所有子節點號組成的字符串.
mysql> delimiter //
mysql>
mysql> 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
-> //
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
www.2cto.com
使用我們直接利用find_in_set函數配合這個getChildlst來查找
mysql> select getChildLst(1);
+-----------------+
| getChildLst(1) |
+-----------------+
| $,1,2,3,4,5,6,7 |
+-----------------+
1 row in set (0.00 sec)
mysql> 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 |
+----+----------+------+
7 rows in set (0.01 sec)
mysql> select * from treeNodes
-> where FIND_IN_SET(id, getChildLst(3));
+----+----------+------+
| id | nodename | pid |
+----+----------+------+
| 3 | C | 1 |
| 6 | F | 3 |
| 7 | G | 6 |
+----+----------+------+
3 rows in set (0.01 sec)
--------------------------------------------
只要按我的做，百發百中彈無虛發，遇到問題萬變不離其宗直接粘貼復制就是。。。
補充：
還可以做嵌套查詢：
select id,pid from treeNodes where id in(
select id from treeNodes where FIND_IN_SET(id, getChildLst(3))
);
子查詢的結果集是
www.2cto.com
+--------+
id
----
3
6
7
+-------+
然後經過外層查詢就是
id pid
3 1
6 3
6 6
---------
好了 Perfect
