欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL多层级结构-区域表使用树详解

程序员文章站 2024-02-16 10:05:28
1.1. 前言 前面我们大概介绍了一下树结构表的基本使用。在我们项目中有好几块有用到多层级的概念。下面我们哪大家都比较熟悉的区域表来做演示。 1.2. 表结构和数据...

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