数据库对层次结构的处理模型有好多种，可以根据自己的需求来设计模型，当然最简单的也是最容易设计的模型就是所谓的邻接模型。在这方面，其他数据库比如Oracle 提供了现成的分析方法 connect by，而MySQL在这方面就显得有些薄弱了。 不过可以用MySQL的存储过程实现ORACLE类似的分析功能
这样，先来创建一个简单的数表。
|
1
2
|
create
table
country ( id number(2)
not
null
,
name
varchar
(60)
not
null
);
create
table
country_relation (id number(2), parentid number(2));
|
插入一些数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
-- Table country.
insert
into
country (id,
name
)
values
(0,
'Earth'
);
insert
into
country (id,
name
)
values
(2,
'North America'
);
insert
into
country (id,
name
)
values
(3,
'South America'
);
insert
into
country (id,
name
)
values
(4,
'Europe'
);
insert
into
country (id,
name
)
values
(5,
'Asia'
);
insert
into
country (id,
name
)
values
(6,
'Africa'
);
insert
into
country (id,
name
)
values
(7,
'Australia'
);
insert
into
country (id,
name
)
values
(8,
'Canada'
);
insert
into
country (id,
name
)
values
(9,
'Central America'
);
insert
into
country (id,
name
)
values
(10,
'Island Nations'
);
insert
into
country (id,
name
)
values
(11,
'United States'
);
insert
into
country (id,
name
)
values
(12,
'Alabama'
);
insert
into
country (id,
name
)
values
(13,
'Alaska'
);
insert
into
country (id,
name
)
values
(14,
'Arizona'
);
insert
into
country (id,
name
)
values
(15,
'Arkansas'
);
insert
into
country (id,
name
)
values
(16,
'California'
);
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
-- Table country_relation.
insert
into
country_relation (id,parentid)
values
(0,
NULL
);
insert
into
country_relation (id,parentid)
values
(2,0);
insert
into
country_relation (id,parentid)
values
(3,0);
insert
into
country_relation (id,parentid)
values
(4,0);
insert
into
country_relation (id,parentid)
values
(5,0);
insert
into
country_relation (id,parentid)
values
(6,0);
insert
into
country_relation (id,parentid)
values
(7,0);
insert
into
country_relation (id,parentid)
values
(8,2);
insert
into
country_relation (id,parentid)
values
(9,2);
insert
into
country_relation (id,parentid)
values
(10,2);
insert
into
country_relation (id,parentid)
values
(11,2);
insert
into
country_relation (id,parentid)
values
(12,11);
insert
into
country_relation (id,parentid)
values
(13,11);
insert
into
country_relation (id,parentid)
values
(14,11);
insert
into
country_relation (id,parentid)
values
(15,11);
insert
into
country_relation (id,parentid)
values
(16,11);
|
在Oracle 里面，对这些操作就比较简单了，都是系统提供的。
比如下面四种情形：
1）. 查看深度，
|
1
2
3
4
5
6
7
|
select
max
(
level
)
"level"
from
COUNTRY_RELATION a start
with
a.parentid
is
NULL
connect
by
PRIOR
a.id = a.PARENTID
order
by
level
;
level
----------
4
已用时间: 00: 00: 00.03
|
2）. 查看叶子节点
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
select
name
from
(
select
b.
name
, connect_by_isleaf
"isleaf"
from
COUNTRY_RELATION a
inner
join
country b
on
(a.id = b.id)
start
with
a.parentid
is
NULL
connect
by
prior
a.id = a.PARENTID
) T
where
T.
"isleaf"
= 1;
NAME
--------------------------------------------------
Canada
Central America
Island Nations
Alabama
Alaska
Arizona
Arkansas
California
South America
Europe
Asia
Africa
Australia
已选择13行。
已用时间: 00: 00: 00.01
|
3） 查看ROOT节点
|
1
2
3
4
5
6
7
|
select
connect_by_root b.
name
from
COUNTRY_RELATION a
inner
join
country b
on
(a.id = b.id)
start
with
a.parentid
is
NULL
connect
by
a.id = a.PARENTID
CONNECT_BY_ROOTB.
NAME
--------------------------------------------------
Earth
已用时间: 00: 00: 00.01
|
4）. 查看路径
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
select
sys_connect_by_path(b.
name
,
'/'
)
"path"
from
COUNTRY_RELATION a
inner
join
country b
on
(a.id = b.id)
start
with
a.parentid
is
NULL
connect
by
prior
a.id = a.PARENTID
order
by
level
,a.id;
path
--------------------------------------------------
/Earth
/Earth/North America
/Earth/South America
/Earth/Europe
/Earth/Asia
/Earth/Africa
/Earth/Australia
/Earth/North America/Canada
/Earth/North America/Central America
/Earth/North America/Island Nations
/Earth/North America/United States
/Earth/North America/United States/Alabama
/Earth/North America/United States/Alaska
/Earth/North America/United States/Arizona
/Earth/North America/United States/Arkansas
/Earth/North America/United States/California
已选择16行。
已用时间: 00: 00: 00.01
|
接下来我们看看在MySQL 里面如何实现上面四种情形：
前三种都比较简单，可以很容易写出SQL。
1）查看深度
|
1
2
3
4
5
6
7
8
|
mysql>
SELECT
COUNT
(
DISTINCT
IFNULL(parentid,-1))
AS
LEVEL
FROM
country_relation
;
+
-------+
|
LEVEL
|
+
-------+
| 4 |
+
-------+
1 row
in
set
(0.00 sec
|
)
2）查看ROOT节点
|
1
2
3
4
5
6
7
8
9
10
|
mysql>
SELECT
b.`
name
`
AS
root_node
FROM
-> (
->
SELECT
id
FROM
country_relation
WHERE
parentid
IS
NULL
-> )
AS
a, country
AS
b
WHERE
a.id = b.id;
+
-----------+
| root_node |
+
-----------+
| Earth |
+
-----------+
1 row
in
set
(0.00 sec)
|
3). 查看叶子节点
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
mysql>
SELECT
b.`
name
`
AS
leaf_node
FROM
-> (
->
SELECT
id
FROM
country_relation
WHERE
id
NOT
IN
(
SELECT
IFNULL(parentid,
-1)
FROM
country_relation)
-> )
AS
a, country
AS
b
WHERE
a.id = b.id;
+
-----------------+
| leaf_node |
+
-----------------+
| South America |
| Europe |
| Asia |
| Africa |
| Australia |
| Canada |
| Central America |
| Island Nations |
| Alabama |
| Alaska |
| Arizona |
| Arkansas |
| California |
+
-----------------+
13
rows
in
set
(0.00 sec)
mysql>
|
4） 查看路径
这一块没有简单的SQL实现，不过可以用MySQL的存储过程来实现同样的功能。
存储过程代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
DELIMITER $$
USE `t_girl`$$
DROP
PROCEDURE
IF EXISTS `sp_show_list`$$
CREATE
DEFINER=`root`@`localhost`
PROCEDURE
`sp_show_list`()
BEGIN
-- Created by ytt 2014/11/04.
-- Is equal to oracle's connect by syntax.
-- Body.
DROP
TABLE
IF EXISTS tmp_country_list;
CREATE
TEMPORARY
TABLE
tmp_country_list (node_level
INT
UNSIGNED
NOT
NULL
, node_path
VARCHAR
(1000)
NOT
NULL
);
-- Get the root node.
INSERT
INTO
tmp_country_list
SELECT
1, CONCAT('/
',id) FROM country_relation WHERE parentid IS NULL;
-- Loop within all parent node.
cursor1:BEGIN
DECLARE done1 INT DEFAULT 0;
DECLARE i1 INT DEFAULT 1;
DECLARE v_parentid INT DEFAULT -1;
DECLARE v_node_path VARCHAR(1000) DEFAULT '
';
DECLARE cr1 CURSOR FOR SELECT parentid FROM country_relation WHERE parentid IS NOT NULL GROUP BY parentid ORDER BY parentid ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;
OPEN cr1;
loop1:LOOP
FETCH cr1 INTO v_parentid;
IF done1 = 1 THEN
LEAVE loop1;
END IF;
SET i1 = i1 + 1;
label_path:BEGIN
DECLARE done2 INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1;
-- Get the upper path.
SELECT node_path FROM tmp_country_list WHERE node_level = i1 - 1 AND LOCATE(v_parentid,node_path) > 0 INTO v_node_path;
-- Escape the outer not found exception.
IF done2 = 1 THEN
SET done2 = 0;
END IF;
INSERT INTO tmp_country_list
SELECT i1,CONCAT(IFNULL(v_node_path,'
'),'
/
',id) FROM country_relation WHERE parentid = v_parentid;
END;
END LOOP;
CLOSE cr1;
END;
-- Update node'
s id
to
its
real
name
.
update_name_label:
BEGIN
DECLARE
cnt
INT
DEFAULT
0;
DECLARE
i2
INT
DEFAULT
0;
SELECT
MAX
(node_level)
FROM
tmp_country_list
INTO
cnt;
WHILE i2 < cnt
DO
UPDATE
tmp_country_list
AS
a, country
AS
b
SET
a.node_path =
REPLACE
(a.node_path,CONCAT(
'/'
,b.id),CONCAT(
'/'
,b.
name
))
WHERE
LOCATE(CONCAT(
'/'
,b.id),a.node_path) > 0;
SET
i2 = i2 + 1;
END
WHILE;
END
;
SELECT
node_path
FROM
tmp_country_list;
END
$$
DELIMITER ;
|
调用结果：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
mysql> CALL sp_show_list();
+
-----------------------------------------------+
| node_path |
+
-----------------------------------------------+
| /Earth |
| /Earth/North America |
| /Earth/South America |
| /Earth/Europe |
| /Earth/Asia |
| /Earth/Africa |
| /Earth/Australia |
| /Earth/North America/Canada |
| /Earth/North America/Central America |
| /Earth/North America/Island Nations |
| /Earth/North America/United States |
| /Earth/North America/United States/Alabama |
| /Earth/North America/United States/Alaska |
| /Earth/North America/United States/Arizona |
| /Earth/North America/United States/Arkansas |
| /Earth/North America/United States/California |
+
-----------------------------------------------+
16
rows
in
set
(0.04 sec)
Query OK, 0
rows
affected (0.08 sec)
mysql>
|
本文转自 david_yeung 51CTO博客，原文链接：http://blog.51cto.com/yueliangdao0608/1571772
，如需转载请自行联系原作者
