MySQL多層級結構-區域表使用樹詳解

1.1. 前言

前面我們大概介紹了一下樹結構表的基本使用。在我們項目中有好幾塊有用到多層級的概念。下面我們哪大家都比較熟悉的區域表來做演示。
1.2. 表結構和數據

區域表基本結構,可能在你的項目中還有包含其他字段。這邊我只展示我們關心的字段:

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表

mysql -uroot -proot test < area.sql

1.1. 區域表的基本操作

查看 '廣州' 的相關信息

SELECT * FROM area WHERE name LIKE '%廣州%';
+---------+-----------+-----------+------+----------+-----------+
| area_id | name   | area_code | pid | left_num | right_num |
+---------+-----------+-----------+------+----------+-----------+
|  2148 | 廣州市  | 440100  | 2147 |   2879 |   2904 |
+---------+-----------+-----------+------+----------+-----------+

查看 '廣州' 所有孩子

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

查看 '廣州' 所有孩子 和 深度 並顯示層級關系

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

顯示 '廣州' 的直系祖先(包括自己)

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

向 '廣州' 插入一個地區 '南沙區'

-- 更新左右值
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 |
+---------+-----------+-----------+------+----------+-----------+