【MySQL数据库入门】自关联
文章目录
1. 自关联定义2. 自关联举例2.1. 需求2.2. 设计2.3. 简化2.4. 结论
3. MySQL实现3.1. 创建数据表3.2. 批量插入数据3.3. 演练
1. 自关联定义
所谓自关联是指，一个数据表中的某个字段关联了该数据表中的另外一个字段。
2. 自关联举例
下面我们通过设计一套数据表，实现一个具体需求，来通俗解释什么叫自关联。
2.1. 需求
假设现在需要设计一套数据表，使这套数据表可以通过结构化的方式存储全国的省、市、区/县的名称和层级从属关系。如：安徽省–>合肥市–>包河区；江苏省–>南京市–>江宁区等。
2.2. 设计
首先，第一反应是按照上述三个行政层级分别创建一个数据表，即省级信息数据表、市级信息数据表、区/县级数据表，对于省级信息数据表，如表1所示：
表1 省级信息数据表
id省份1安徽省2江苏省3江西省……
对于市级信息表，因为要体现市和省的层级关系，在市级信息数据表2中加了parent_id字段，该字段存储了表1对应省的主键，如景德镇市隶属于江西省，则其parent_id取值为江西在表1中的id值3。
表2 市级信息数据表
id市parent_id1合肥市12安庆市13南京市24无锡市25南昌市36景德镇市3………
对于区/县级信息数据表，类似于表2设计得到了表3，即表3中字段parent_id存储了表2中对应市的主键。
表3 区/县级信息数据表
id区/县parent_id1包河区12庐阳区13宿松县24望江县25江宁区36雨花台区37崇安区48北塘区4………
2.3. 简化
上述的设计最终将产生3张数据表，实际使用时可能较为复杂，而且如果后续希望将地区信息进一步细化，如：区/县下设乡镇，乡镇下设村等，则会进一步增加数据表数量。
鉴于上述考虑，我们思考是否可以仅通过一张数据表实现所有层级数据的存储。通过观察表1、2、3我们发现，其格式都可以统一为表4所示，只是对于表1，可以将parent_id全部视为null。
表4 行政层级统一数据表
id行政层级名称parent_id………
基于表4的设计，我们可以将表1、2、3合并为表5，即：
表5 行政区域信息统一数据表
id行政区域名称parent_id1安徽省null2江苏省null3江西省null4合肥市15安庆市16南京市27无锡市28南昌市39景德镇市310包河区411庐阳区412宿松县513望江县514江宁区6………
2.4. 结论
如表5所示，第9条数据中，parent_id为3，即取值为江西省的id值。则表5中字段id和parent_id之间即构成了自关联。
3. MySQL实现
3.1. 创建数据表
按照表5结构在数据库test（此处创建数据库过程省略，具体请见MySQL数据库入门（2）——数据库操作基础）中创建一个名为areas的数据表。
create table areas(
id int primary key,
title varchar(20),
parent_id int
);
3.2. 批量插入数据
数据准备 所需批量数据请见中国省市插入sql语句，请将所有数据复制到txt文件后，将.txt后缀更改为.sql。插入准备 首先按照下图所示做好数据批量插入的准备。 批量插入 在mysql>后输入以下语句，从而实现数据批量插入。
source areas.sql
3.3. 演练
-- 查询数据表areas，确定所有parent_id字段为null的记录，即全国的省级行政区域。
select * from areas where parent_id is null;
-- 查询数据表areas，确定安徽省的所有市级行政区域
-- 第一步：查询出安徽省的id字段值
select id from areas where title="安徽省";
-- 第二部：根据安徽省的id字段值，查询出安徽省的所有市级行政区域
select * from areas where parent_id=340000;
上述案例通过两条SQL语句实现了需求，即：先通过一条SQL语句查询areas表获取了安徽省的字段id值，然后基于表5的特征，即市级行政区域的parent_id等于省级行政区域的id，从而得出结果。
下面考虑如何仅通过一条SQL语句实现相同功能，问题在于此处仅有一张数据表，如果省级行政区域和市级行政区域各有一张表，则可以通过MySQL数据库入门（3）——数据库查询操作中学习过连接查询实现，即使用类似以下的SQL语句实现。
-- 假设有两张数据表，分别为province和city，且其结构都如表5所示
select province inner join city on province.id=city.id having province.title="安徽省";
基于上述考虑，即使现在只有一个统一的数据表情况下，也可以实现上述需求，因为可以将数据表areas同时使用两次，分别作为内连接查询语句的左表和右表，即：
-- 将数据表areas分别取别名province和city
select * from areas as province inner join areas as city on city.parent_id=province.id having province.title="安徽省";