1.1. 前言
前面我们大概介绍了一下树结构表的基本使用。在我们项目中有好几块有用到多层级的概念。下面我们哪大家都比较熟悉的区域表来做演示。
1.2. 表结构和数据
区域表基本结构,可能在你的项目中还有包含其他字段。这边我只展示我们关心的字段:
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE
TABLE
`
area
`
(
`
area_id
`
int
(
11
)
NOT
NULL
AUTO_INCREMENT
COMMENT
'地区ID'
,
`
name
`
varchar
(
40
)
NOT
NULL
DEFAULT
'unkonw'
COMMENT
'地区名称'
,
`
area_code
`
varchar
(
10
)
NOT
NULL
DEFAULT
'unkonw'
COMMENT
'地区编码'
,
`
pid
`
int
(
11
)
DEFAULT
NULL
COMMENT
'父id'
,
`
left_num
`
mediumint
(
8
)
unsigned
NOT
NULL
COMMENT
'节点左值'
,
`
right_num
`
mediumint
(
8
)
unsigned
NOT
NULL
COMMENT
'节点右值'
,
PRIMARY
KEY
(
`
area_id
`
)
,
KEY
`
idx
$
area
$
pid
`
(
`
pid
`
)
,
KEY
`
idx
$
area
$
left_num
`
(
`
left_num
`
)
,
KEY
`
idx
$
area
$
right_num
`
(
`
right_num
`
)
)
|
区域表数据: area
导入到test表
1
|
mysql
-
uroot
-
proot
test
<
area
.
sql
|
1.1. 区域表的基本操作
查看 '广州' 的相关信息
1
2
3
4
5
6
|
SELECT *
FROM
area
WHERE
name
LIKE
'%广州%'
;
+
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
+
--
--
--
--
--
+
--
--
--
--
--
-
+
|
area_id
|
name
|
area_code
|
pid
|
left_num
|
right_num
|
+
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
+
--
--
--
--
--
+
--
--
--
--
--
-
+
|
2148
|
广州市
|
440100
|
2147
|
2879
|
2904
|
+
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
+
--
--
--
--
--
+
--
--
--
--
--
-
+
|
查看 '广州' 所有孩子
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SELECT
c
.
*
FROM
area
AS
p
,
area
AS
c
WHERE
c
.
left_num
BETWEEN
p
.
left_num
AND
p
.
right_num
AND
p
.
area_id
=
2148
;
+
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
+
--
--
--
--
--
+
--
--
--
--
--
-
+
|
area_id
|
name
|
area_code
|
pid
|
left_num
|
right_num
|
+
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
+
--
--
--
--
--
+
--
--
--
--
--
-
+
|
2148
|
广州市
|
440100
|
2147
|
2879
|
2904
|
|
2161
|
从化市
|
440184
|
2148
|
2880
|
2881
|
|
2160
|
增城市
|
440183
|
2148
|
2882
|
2883
|
|
2159
|
花都区
|
440114
|
2148
|
2884
|
2885
|
|
2158
|
番禺区
|
440113
|
2148
|
2886
|
2887
|
|
2157
|
黄埔区
|
440112
|
2148
|
2888
|
2889
|
|
2156
|
白云区
|
440111
|
2148
|
2890
|
2891
|
|
2154
|
天河区
|
440106
|
2148
|
2892
|
2893
|
|
2153
|
海珠区
|
440105
|
2148
|
2894
|
2895
|
|
2152
|
越秀区
|
440104
|
2148
|
2896
|
2897
|
|
2151
|
荔湾区
|
440103
|
2148
|
2898
|
2899
|
|
2150
|
东山区
|
230406
|
2148
|
2900
|
2901
|
|
2149
|
其它区
|
440189
|
2148
|
2902
|
2903
|
+
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
+
--
--
--
--
--
+
--
--
--
--
--
-
+
|
查看 '广州' 所有孩子 和 深度 并显示层级关系
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
|
SELECT
sub_child
.
area_id
,
(
COUNT
(
sub_parent
.
name
)
-
1
)
AS
depth
,
CONCAT
(
REPEAT
(
' '
,
(
COUNT
(
sub_parent
.
name
)
-
1
)
)
,
sub_child
.
name
)
AS
name
FROM
(
SELECT
child
.
*
FROM
area
AS
parent
,
area
AS
child
WHERE
child
.
left_num
BETWEEN
parent
.
left_num
AND
parent
.
right_num
AND
parent
.
area_id
=
2148
)
AS
sub_child
,
(
SELECT
child
.
*
FROM
area
AS
parent
,
area
AS
child
WHERE
child
.
left_num
BETWEEN
parent
.
left_num
AND
parent
.
right_num
AND
parent
.
area_id
=
2148
)
AS
sub_parent
WHERE
sub_child
.
left_num
BETWEEN
sub_parent
.
left_num
AND
sub_parent
.
right_num
GROUP
BY
sub_child
.
area_id
ORDER
BY
sub_child
.
left_num
;
+
--
--
--
--
-
+
--
--
--
--
--
--
-
+
--
--
--
-
+
|
area_id
|
name
|
depth
|
+
--
--
--
--
-
+
--
--
--
--
--
--
-
+
--
--
--
-
+
|
2148
|
广州市
|
0
|
|
2161
|
从化市
|
1
|
|
2160
|
增城市
|
1
|
|
2159
|
花都区
|
1
|
|
2158
|
番禺区
|
1
|
|
2157
|
黄埔区
|
1
|
|
2156
|
白云区
|
1
|
|
2154
|
天河区
|
1
|
|
2153
|
海珠区
|
1
|
|
2152
|
越秀区
|
1
|
|
2151
|
荔湾区
|
1
|
|
2150
|
东山区
|
1
|
|
2149
|
其它区
|
1
|
+
--
--
--
--
-
+
--
--
--
--
--
--
-
+
--
--
--
-
+
|
显示 '广州' 的直系祖先(包括自己)
1
2
3
4
5
6
7
8
9
10
11
|
SELECT
p
.
*
FROM
area
AS
p
,
area
AS
c
WHERE
c
.
left_num
BETWEEN
p
.
left_num
AND
p
.
right_num
AND
c
.
area_id
=
2148
;
+
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
+
--
--
--
--
--
+
--
--
--
--
--
-
+
|
area_id
|
name
|
area_code
|
pid
|
left_num
|
right_num
|
+
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
+
--
--
--
--
--
+
--
--
--
--
--
-
+
|
2147
|
广东省
|
440000
|
0
|
2580
|
2905
|
|
2148
|
广州市
|
440100
|
2147
|
2879
|
2904
|
|
3611
|
中国
|
100000
|
-
1
|
1
|
7218
|
+
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
+
--
--
--
--
--
+
--
--
--
--
--
-
+
|
向 '广州' 插入一个地区 '南沙区'
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
|
--
更新左右值
UPDATE
area
SET
left_num
=
left_num
+
2
WHERE
left_num
>
2879
;
UPDATE
area
SET
right_num
=
right_num
+
2
WHERE
right_num
>
2879
;
--
插入
'南沙区'
信息
INSERT
INTO
area
SELECT
NULL
,
'南沙区'
,
'440115'
,
2148
,
left_num
+
1
,
left_num
+
2
FROM
area
WHERE
area_id
=
2148
;
--
查看是否满足要求
SELECT
c
.
*
FROM
area
AS
p
,
area
AS
c
WHERE
c
.
left_num
BETWEEN
p
.
left_num
AND
p
.
right_num
AND
p
.
area_id
=
2148
;
+
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
+
--
--
--
--
--
+
--
--
--
--
--
-
+
|
area_id
|
name
|
area_code
|
pid
|
left_num
|
right_num
|
+
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
+
--
--
--
--
--
+
--
--
--
--
--
-
+
|
2148
|
广州市
|
440100
|
2147
|
2879
|
2906
|
|
3612
|
南沙区
|
440115
|
2148
|
2880
|
2881
|
|
2161
|
从化市
|
440184
|
2148
|
2882
|
2883
|
|
2160
|
增城市
|
440183
|
2148
|
2884
|
2885
|
|
2159
|
花都区
|
440114
|
2148
|
2886
|
2887
|
|
2158
|
番禺区
|
440113
|
2148
|
2888
|
2889
|
|
2157
|
黄埔区
|
440112
|
2148
|
2890
|
2891
|
|
2156
|
白云区
|
440111
|
2148
|
2892
|
2893
|
|
2154
|
天河区
|
440106
|
2148
|
2894
|
2895
|
|
2153
|
海珠区
|
440105
|
2148
|
2896
|
2897
|
|
2152
|
越秀区
|
440104
|
2148
|
2898
|
2899
|
|
2151
|
荔湾区
|
440103
|
2148
|
2900
|
2901
|
|
2150
|
东山区
|
230406
|
2148
|
2902
|
2903
|
|
2149
|
其它区
|
440189
|
2148
|
2904
|
2905
|
+
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
--
--
-
+
--
--
--
+
--
--
--
--
--
+
--
--
--
--
--
-
+
|
昵称:HH
QQ:275258836
ttlsa群交流沟通(QQ群②:6690706 QQ群③:168085569 QQ群④:415230207(新) 微信公众号:ttlsacom)
感觉本文内容不错,读后有收获?
逛逛衣服店,鼓励作者写出更好文章。
收 藏
转载请注明:成长的对话 » MySQL多层级结构-区域表使用树