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 | + — — — — – + — — — — — — — — — — — — — + — — — — — – + — — — + — — — — — + — — — — — – + — — — — — — — + |
| 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 | + — — — — – + — — — — — – + — — — — — – + — — — + — — — — — + — — — — — – + — — — — — — — + |
文章转载来自：trustauth.cn
亿网