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

bitsCN.com
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)  -------------------------------------------- 只要按我的做，百发百中弹无虚发，遇到问题万变不离其宗直接粘贴复制就是。。。  补充： 还可以做嵌套查询： 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
  bitsCN.com