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