mysql的逆袭：如何做递归层次查询

最近在做一个从oracle数据库到mysql数据库的移植，遇到一个这样的问题    

在Oracle 中我们知道有一个 Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但shi，在MySQL的目前版本中还没有对应的函数！！！

换句话来说，想要用mysql实现递归查询，根本做不到！！！
可是经过我数天茶不思饭不想的刻苦琢磨，终于想到了一个合理的 适用于mysql和其他sql的 解决方案案案案。。。。

方案一出，就秋风扫落叶之势，席卷整个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

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');

---------------------------------------------------
上边是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 ;


使用我们直接利用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)

--------------------------------------------
只要按我的做，百发百中弹无虚发，遇到问题万变不离其宗直接粘贴复制就是。。。

评论

1 楼 曾老师 2012-09-02  

还可以做嵌套查询：
select id,pid from treeNodes where id in(
     select id from treeNodes where FIND_IN_SET(id, getChildLst(3))
);
子查询的结果集是

+--------+
id
----
3
6
7
+-------+
然后经过外层查询就是

id  pid
3   1
6   3
6   6
---------
好了 Perfect