MySQL多层级结构-区域表树改造

更新：2022-05-23 13:19:03编辑：Story归类：数据库人气：206

1.1. 前言

之前我们的地区表的层级结构，可以说是一颗数树的祖先是 '中国'。在一个树的结构下当数据量大的时候要更新或添加一个地区的时候跟新的数据量平均是半个表。这看以来显然是不合理的。

1.2. 单树到多树的演变

原来我们的是以中国为粒度来维护整张表的层级关系。现在我们将变成以 '省' 的粒度来维护地区的层级关系。并且往往我们使用也都是以省来做最大的粒度。演变图如下：

mysql

1.3. 结构改造

由于我们的粒度变成了 '省'，就代表我们之后的操作都是基于某个省下面所有地区进行的。因此我们需要为每个地区添加一个字段标识了他是属于哪个 '省' 的。

ALTER TABLE area
ADD top_layer_id INT NOT NULL DEFAULT 0;

1 2	ALTER TABLE area ADD top_layer_id INT NOT NULL DEFAULT 0 ;

将每个地区的 top_layer_id 更新成自己的 '省' ID。

找出所有的省

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 |
+---------+--------------------------+-----------+------+----------+-----------+--------------+

SELECT * FROM area WHERE pid = 0 ;

+ -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- - + -- -- -- + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- -- -- -- +

+ -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- - + -- -- -- + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- -- -- -- +

| 1 | 北京 | 110000 | 0 | 7178 | 7219 | 0 |

. . . omit . . .

| 3523 | 澳门特别行政区 | 820000 | 0 | 10 | 15 | 0 |

| 3524 | 海外 | 990000 | 0 | 2 | 9 | 0 |

+ -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- - + -- -- -- + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- -- -- -- +

跟新地区top_layer_id为自己的省ID

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;

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. 对表进行操作

查看 '广州' 的相关信息

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 |
+---------+-----------+-----------+------+----------+-----------+--------------+

SELECT * FROM area WHERE name LIKE '%广州%' ;

+ -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- -- -- -- +

+ -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- -- -- -- +

| 2148 | 广州市 | 440100 | 2147 | 2879 | 2906 | 2147 |

+ -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- -- -- -- +

查看 '广州' 所有孩子

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 |
+---------+-----------+-----------+------+----------+-----------+--------------+

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 ;

+ -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- -- -- -- +

+ -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- -- -- -- +

| 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 |

+ -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- -- -- -- +

查看 '广州' 所有孩子和深度并显示层级关系

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 |   其它区    |
+---------+-------+-------------+

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 | 其它区 |

+ -- -- -- -- - + -- -- -- - + -- -- -- -- -- -- - +

显示 '广州' 的直系祖先(包括自己)

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 |
+---------+-----------+-----------+------+----------+-----------+--------------+

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 ;

+ -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- -- -- -- +

+ -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- -- -- -- +

| 2147 | 广东省 | 440000 | 0 | 2580 | 2907 | 2147 |

| 2148 | 广州市 | 440100 | 2147 | 2879 | 2906 | 2147 |

+ -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- + -- -- -- -- -- + -- -- -- -- -- - + -- -- -- -- -- -- -- +

向 '广州' 插入一个地区 '北沙区'

-- 更新左右值
--------------
-- 这边我们关注影响的行数，明细比之前全表更新的少。
--------------
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 |
+---------+-----------+-----------+------+----------+-----------+--------------+