189 8069 5689
mysql create table treeNodes
创新互联建站主营射洪网站建设的网络公司,主营网站建设方案,成都App定制开发,射洪h5小程序设计搭建,射洪网站营销推广欢迎射洪等地区企业咨询
- (
- id int primary key,
- nodename varchar(20),
- pid int
- );
Query OK, 0 rows affected (0.09 sec)
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)
mysql的逆袭:如何做递归层次查询
最近在做一个从oracle数据库到mysql数据库的移植，遇到一个这样的问题
在Oracle 中我们知道有一个 Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但shi，在MySQL的目前版本中还没有对应的函数！！！
换句话来说，想要用mysql实现递归查询，根本做不到！！！
可是经过我数天茶不思饭不想的刻苦琢磨，终于想到了一个合理的，适用于mysql和其他sql的解决方案。
方案一出，就秋风扫落叶之势，席卷整个dao层~~~所到之处，所有问题迎刃而解，让所有问题都不再为问题 都成为了我这个函数的炮灰而已。。。
一般比较普遍的就是四种方法：（具体见 SQL Anti-patterns这本书）
因为不支持递归查询，所以Mysql官方当时推荐的是第三种方式：Nested Sets。我是用过的，非常难受。现在支持递归查询，我们可以使用第一种。
其实我还用了一种，一列维持顺序，一列维持层级，有空可以把实现写出来看看。
英文好的朋友请移步到这里： Managing Hierarchical Data in MySQL Using the Adjacency List Model ，不好的同学跟我一起来。
直接语句说明：
好，就这些吧。我是姜友华，下次见。
首先创建一个熟悉的机构表
插入几条测试数据：
union all上面的是初始化语句，只会执行一次，查到了 开发部 这一行记录。
接下来下面的join会用初始化的语句去原来的organization表去join获取所有 开发部的子部门 ，然后再用这些 子部门 去join更下面的部门。
执行的结果如下：
如下想查询开发部的所有上级部门的话上面的递归查询语句简单改一下就可以了：
执行结果如下：
Recursive Common Table Expression 'temp' can contain neither
aggregation nor window functions in recursive query block
mysql
mysql对递归的深度是有限制的，默认的递归深度是1000。
可以通过 show variables like 'cte_max_recursion_depth'; 进行查看
也可以通过select语句最大执行时间对递归加以显示， show variables lile 'max_execution_time';
给你个网上写的比较好的例子：
方法一：利用函数来得到所有子节点号。
创建一个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,
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
|
|
|
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)