MySQL多层级结构-区域表树改造
时间:2016-07-16 23:58 来源:linux.it.net.cn 作者:IT
1.1. 前言
之前我们的地区表的层级结构，可以说是一颗数树的祖先是 '中国'。在一个树的结构下当数据量大的时候要更新或添加一个地区的时候跟新的数据量平均是半个表。这看以来显然是不合理的。
1.2. 单树到多树的演变
原来我们的是以中国为粒度来维护整张表的层级关系。现在我们将变成以 '省' 的粒度来维护地区的层级关系。并且往往我们使用也都是以省来做最大的粒度。演变图如下：
1.3. 结构改造
由于我们的粒度变成了 '省'，就代表我们之后的操作都是基于某个省下面所有地区进行的。因此我们需要为每个地区添加一个字段标识了他是属于哪个 '省' 的。
1
2
ALTER TABLE area
ADD top_layer_id INT NOT NULL DEFAULT 0;
将每个地区的 top_layer_id 更新成自己的 '省' ID。
-
找出所有的省
1
2
3
4
5
6
7
8
9
SELECT * FROM area WHERE pid = 0;
+---------+--------------------------+-----------+------+----------+-----------+--------------+
| area_id | name | area_code | pid | left_num | right_num | top_layer_id |
+---------+--------------------------+-----------+------+----------+-----------+--------------+
| 1 | 北京 | 110000 | 0 | 7178 | 7219 | 0 |
... omit ...
| 3523 | 澳门特别行政区 | 820000 | 0 | 10 | 15 | 0 |
| 3524 | 海外 | 990000 | 0 | 2 | 9 | 0 |
+---------+--------------------------+-----------+------+----------+-----------+--------------+
-
跟新地区top_layer_id为自己的省ID
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
DROP PROCEDURE IF EXISTS set_top_layer_id;
DELIMITER //
CREATE PROCEDURE set_top_layer_id()
BEGIN
DECLARE num INT;
DECLARE cur_area_id INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur_area CURSOR FOR
SELECT area_id
FROM area
WHERE pid = 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur_area;
read_loop: LOOP
FETCH cur_area INTO cur_area_id;
IF done THEN
LEAVE read_loop;
END IF;
UPDATE area ,(
SELECT c.area_id
FROM area AS p, area AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num
AND p.area_id = cur_area_id
) AS tmp
SET area.top_layer_id = cur_area_id
WHERE tmp.area_id = area.area_id;
END LOOP;
CLOSE cur_area;
COMMIT;
END //
DELIMITER ;
CALL set_top_layer_id;
DROP PROCEDURE IF EXISTS set_top_layer_id;
1.4. 对表进行操作
-
查看 '广州' 的相关信息
1
2
3
4
5
6
SELECT * FROM area WHERE name LIKE '%广州%';
+---------+-----------+-----------+------+----------+-----------+--------------+
| area_id | name | area_code | pid | left_num | right_num | top_layer_id |
+---------+-----------+-----------+------+----------+-----------+--------------+
| 2148 | 广州市 | 440100 | 2147 | 2879 | 2906 | 2147 |
+---------+-----------+-----------+------+----------+-----------+--------------+
-
查看 '广州' 所有孩子
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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
AND p.top_layer_id = 2147;
+---------+-----------+-----------+------+----------+-----------+--------------+
| area_id | name | area_code | pid | left_num | right_num | top_layer_id |
+---------+-----------+-----------+------+----------+-----------+--------------+
| 2148 | 广州市 | 440100 | 2147 | 2879 | 2906 | 2147 |
| 3612 | 南沙区 | 440115 | 2148 | 2880 | 2881 | 2147 |
| 2161 | 从化市 | 440184 | 2148 | 2882 | 2883 | 2147 |
| 2160 | 增城市 | 440183 | 2148 | 2884 | 2885 | 2147 |
| 2159 | 花都区 | 440114 | 2148 | 2886 | 2887 | 2147 |
| 2158 | 番禺区 | 440113 | 2148 | 2888 | 2889 | 2147 |
| 2157 | 黄埔区 | 440112 | 2148 | 2890 | 2891 | 2147 |
| 2156 | 白云区 | 440111 | 2148 | 2892 | 2893 | 2147 |
| 2154 | 天河区 | 440106 | 2148 | 2894 | 2895 | 2147 |
| 2153 | 海珠区 | 440105 | 2148 | 2896 | 2897 | 2147 |
| 2152 | 越秀区 | 440104 | 2148 | 2898 | 2899 | 2147 |
| 2151 | 荔湾区 | 440103 | 2148 | 2900 | 2901 | 2147 |
| 2150 | 东山区 | 230406 | 2148 | 2902 | 2903 | 2147 |
| 2149 | 其它区 | 440189 | 2148 | 2904 | 2905 | 2147 |
+---------+-----------+-----------+------+----------+-----------+--------------+
-
查看 '广州' 所有孩子 和 深度 并显示层级关系
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
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
AND parent.top_layer_id = 2147
) 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
AND parent.top_layer_id = 2147
) 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 | depth | name |
+---------+-------+-------------+
| 2148 | 0 | 广州市 |
| 3612 | 1 | 南沙区 |
| 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
AND p.top_layer_id = 2147;
+---------+-----------+-----------+------+----------+-----------+--------------+
| area_id | name | area_code | pid | left_num | right_num | top_layer_id |
+---------+-----------+-----------+------+----------+-----------+--------------+
| 2147 | 广东省 | 440000 | 0 | 2580 | 2907 | 2147 |
| 2148 | 广州市 | 440100 | 2147 | 2879 | 2906 | 2147 |
+---------+-----------+-----------+------+----------+-----------+--------------+
-
向 '广州' 插入一个地区 '北沙区'
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
-- 更新左右值
--------------
-- 这边我们关注影响的行数，明细比之前全表更新的少。
--------------
UPDATE area
SET left_num = left_num + 2
WHERE left_num > 2879
AND top_layer_id = 2147;
Query OK, 13 rows affected (0.03 sec)
Rows matched: 13 Changed: 13 Warnings: 0
UPDATE area
SET right_num = right_num + 2
WHERE right_num > 2879
AND top_layer_id = 2147;
Query OK, 15 rows affected (0.01 sec)
Rows matched: 15 Changed: 15 Warnings: 0
-- 插入 '北沙区' 信息
INSERT INTO area
SELECT NULL, '北沙区', '440116', 2148, left_num + 1, left_num + 2, 2147
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 | top_layer_id |
+---------+-----------+-----------+------+----------+-----------+--------------+
| 2148 | 广州市 | 440100 | 2147 | 2879 | 2908 | 2147 |
| 3613 | 北沙区 | 440116 | 2148 | 2880 | 2881 | 2147 |
| 3612 | 南沙区 | 440115 | 2148 | 2882 | 2883 | 2147 |
| 2161 | 从化市 | 440184 | 2148 | 2884 | 2885 | 2147 |
| 2160 | 增城市 | 440183 | 2148 | 2886 | 2887 | 2147 |
| 2159 | 花都区 | 440114 | 2148 | 2888 | 2889 | 2147 |
| 2158 | 番禺区 | 440113 | 2148 | 2890 | 2891 | 2147 |
| 2157 | 黄埔区 | 440112 | 2148 | 2892 | 2893 | 2147 |
| 2156 | 白云区 | 440111 | 2148 | 2894 | 2895 | 2147 |
| 2154 | 天河区 | 440106 | 2148 | 2896 | 2897 | 2147 |
| 2153 | 海珠区 | 440105 | 2148 | 2898 | 2899 | 2147 |
| 2152 | 越秀区 | 440104 | 2148 | 2900 | 2901 | 2147 |
| 2151 | 荔湾区 | 440103 | 2148 | 2902 | 2903 | 2147 |
| 2150 | 东山区 | 230406 | 2148 | 2904 | 2905 | 2147 |
| 2149 | 其它区 | 440189 | 2148 | 2906 | 2907 | 2147 |
| 1997 | 湖南省 | 430000 | 0 | 2908 | 3209 | 1997 |
+---------+-----------+-----------+------+----------+-----------+--------------+
(责任编辑：IT)
1.1. 前言之前我们的地区表的层级结构，可以说是一颗数树的祖先是 '中国'。在一个树的结构下当数据量大的时候要更新或添加一个地区的时候跟新的数据量平均是半个表。这看以来显然是不合理的。 1.2. 单树到多树的演变原来我们的是以中国为粒度来维护整张表的层级关系。现在我们将变成以 '省' 的粒度来维护地区的层级关系。并且往往我们使用也都是以省来做最大的粒度。演变图如下：
1.3. 结构改造由于我们的粒度变成了 '省'，就代表我们之后的操作都是基于某个省下面所有地区进行的。因此我们需要为每个地区添加一个字段标识了他是属于哪个 '省' 的。
将每个地区的 top_layer_id 更新成自己的 '省' ID。
1.4. 对表进行操作
(责任编辑：IT) |