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

区域表数据:
导入到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 |
+---------+-----------+-----------+------+----------+-----------+