MySQL省市区名称表设计与导入实战：邻接模型、对账与查询优化

简介：面向需要在中国站点或应用中实现地址选择、区域检索与地理定位功能的开发者，这份 MySQL 省市区数据表以单表结构组织全国省、市、区县信息，便于直接导入业务库使用。压缩包共 1 个文件，为 region.sql 脚本，体积约 126KB，内含建表与数据写入语句，可快速生成行政区划表，并支撑按名称、区号、拼音及经纬度等字段的查询。已有 2939 人学习，说明其在地址校验、配送范围判断、地图定位和区域统计等场景中具有一定参考价值。读者可获得完整的省市区基础数据与字段设计思路，减少手工整理行政区划的成本，也便于在此基础上扩展层级编码、拼音搜索和距离计算等功能。

1. 拿到 mysql 省市区名称表先别急着 source：三类库的落地边界

mysql 省市区名称这类表，真正麻烦的不是建表，而是拿到一份“完整版” SQL 文件后，发现省、市、区县三级层级和业务系统里的地址字段对不上。这个资源是一份可直接导入 MySQL 的省市区数据文件，核心价值是把“省—市—区县”三级名称、父级关系、层级和编码一次性落库，省掉手工拼地址字典的时间。适合做收货地址、门店区域、配送范围、客户地址、后台级联下拉的团队，也适合把地址选择从硬编码改成配置表的后端。

新手可以按后面的建表、导入、对账步骤走；熟手更该关注字段选型、编码规则和覆盖更新的边界。先别急着source，先判断你的业务要的是名称展示、编码映射，还是路径反查，这三件事决定表怎么设计。

2. 表结构选型：单表邻接模型还是三表分离，字段怎么定

2.1 为什么省市区三级优先用单表邻接模型

省市区是固定三级的树，节点数量有限，查询方式集中在“按父级取下一级”“按名称搜索”“由区县反查完整路径”。这种场景下，单表邻接模型最省事：一条记录一个节点，parent_id指向父节点，level标出层级，code存业务编码。导入时只需要一张表，校验也只需要围绕一张表做，不需要在三张表之间来回对账。

三表分离（省表、市表、区县表）看起来更“规范”，但在实际业务里经常变成负担：新增一个区县要同时改两张表的关系，按名称搜索要写三段UNION，由区县反查省市区要联三次表。闭包表适合路径查询特别重的系统，但导入和维护成本高，省市区这种固定三级用它有点杀鸡用牛刀。

我一般会保留parent_id做层级关系，同时把code做成唯一业务键，前端展示用name，后端关联尽量用code。id只做内部主键，不暴露给前端，也不建议业务表直接拿它当外键，原因在后面覆盖更新的坑里会细说。

2.2 一份可直接执行的建表 SQL 与索引取舍

下面这份 DDL 是按“单表 + 邻接模型 + 业务编码”落地的版本，字段名和长度可以根据现有系统改，但索引思路建议保留。

CREATE TABLE sys_area ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '内部主键，不建议暴露给前端', parent_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父级内部主键，根节点为0', name VARCHAR(64) NOT NULL COMMENT '省、市、区县名称', short_name VARCHAR(32) DEFAULT NULL COMMENT '简称，展示用', level TINYINT UNSIGNED NOT NULL COMMENT '层级：1省 2市 3区县', code CHAR(6) NOT NULL COMMENT '业务编码，示例用占位编码', pinyin VARCHAR(128) DEFAULT NULL COMMENT '拼音，可选', first_letter CHAR(1) DEFAULT NULL COMMENT '首字母，可选', status TINYINT NOT NULL DEFAULT 1 COMMENT '1启用 0停用', sort_order INT NOT NULL DEFAULT 0 COMMENT '展示排序', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_code (code), KEY idx_parent_level (parent_id, level), KEY idx_level_status (level, status), KEY idx_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='省市区名称表';

这段 SQL 的逻辑很直接：id自增主键负责内部关联，parent_id建联合索引时把level带上，是因为级联下拉通常同时带父级和层级条件；code加唯一索引，防止完整版文件重复导入或文件本身有重复编码；name加普通索引，主要是给“前缀搜索”用，LIKE '%关键词%'仍然用不上它，这一点要有心理预期。

参数上，VARCHAR(64)对省市区名称足够，别为了省空间压到VARCHAR(16)，遇到长名称会被截断；code用CHAR(6)而不是INT，因为编码可能有前导零，INT会把000000变成0；字符集统一utf8mb4，排序规则用utf8mb4_general_ci兼容面更广，MySQL 8 也可以换成utf8mb4_0900_ai_ci，但同一套表里不要混用。status不要用ENUM，后面加“隐藏”“待审核”之类状态时改表很别扭。

如果完整版文件还带经纬度、电话区号、邮编，可以按需扩展，但不要在核心表里堆太多展示字段。扩展字段的常见做法是：

ALTER TABLE sys_area ADD COLUMN lng DECIMAL(10,6) DEFAULT NULL COMMENT '经度，可选', ADD COLUMN lat DECIMAL(10,6) DEFAULT NULL COMMENT '纬度，可选', ADD COLUMN post_code VARCHAR(12) DEFAULT NULL COMMENT '邮编，可选';

这段ALTER的意图是把“查询和关联必须用”的字段留在主表，把“展示或地图用”的字段做成可空列。参数上，经纬度用DECIMAL(10,6)比FLOAT更稳，邮编用字符串而不是数字，避免前导零丢失。加列时如果表里已有数据，DEFAULT NULL不会锁太久，但在生产上仍然建议低峰执行。

2.3 字段长度、编码与状态位的一次性定稿

level用TINYINT存 1、2、3，不要存“省”“市”“区县”字符串。字符串看起来直观，但排序、比较、索引都更重，而且一旦有人写成“省份”“地市”，查询条件就会漏数据。parent_id根节点用0，不要用NULL，因为NULL在唯一索引和联表条件里容易出幺蛾子，0更直观。

code是这份资源里最值得定清楚的字段。完整版文件可能只给名称和父级编码，没有内部自增id，导入时就要先把原始parent_code保留下来，再回填parent_id。如果业务表已经用area_id做外键，后面换一份文件时自增id可能变化，外键就会断。血泪经验是：业务侧尽量存code，或者存code加一份名称快照，别把自增id当稳定业务键。

status和sort_order是展示层字段，status控制是否在下拉里出现，sort_order控制默认排序。不要让前端按name拼音排序，不同排序规则下结果会漂移。created_at、updated_at建议保留，排查“这条数据什么时候被覆盖”时很有用。

3. 把完整版文件导进 MySQL：从文件体检到行数对账

3.1 导入前的文件体检与库参数确认

拿到area_full.sql这类文件，先别往生产库灌。第一步是看文件编码、看文件头有没有SET NAMES、看建表语句的字符集，再确认目标库的参数。常见做法是先建一个area_stage临时库，所有导入和校验都在临时库做，确认没问题再切。

# 看文件编码和类型，避免 GBK 文件按 UTF-8 硬导 file -i /tmp/area_full.sql # 看前 30 行，确认有没有建库建表、SET NAMES、字符集声明 head -n 30 /tmp/area_full.sql # 看目标库的包大小、字符集和超时参数 mysql -uroot -p -e " SHOW VARIABLES LIKE 'max_allowed_packet'; SHOW VARIABLES LIKE 'character_set_server'; SHOW VARIABLES LIKE 'wait_timeout'; "

这段命令的逻辑是先做“文件体检”再做“库体检”。file -i输出里如果看到charset=iso-8859-1或unknown-8bit，不要赌，先转成 UTF-8 再导入；head看文件头是为了确认文件里有没有DROP TABLE、CREATE DATABASE这类会误伤现有库的语句；max_allowed_packet太小会在导入大INSERT时直接报错，wait_timeout太短会让大文件导入中途断开。

参数上，max_allowed_packet可以临时调到256M，导入完再调回去；character_set_server建议是utf8mb4，不是的话至少客户端连接要显式指定；wait_timeout不是必须改，但导入大文件时可以用mysql --max-allowed-packet=256M在会话级兜底。不要把生产库的sql_mode随便改，先看文件里有没有依赖宽松模式的写法。

3.2 用命令行导入并保留失败现场

导入时建议显式指定字符集，并把标准错误重定向到日志文件。不要用--force跳过错误，省市区表一旦中间断几条，级联下拉就会出现“市下面没有区县”的玄学问题。

# 建临时库，字符集和排序规则与目标表保持一致 mysql -uroot -p --default-character-set=utf8mb4 -e " CREATE DATABASE IF NOT EXISTS area_stage DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; " # 导入临时库，保留错误日志 mysql -uroot -p \ --default-character-set=utf8mb4 \ --max-allowed-packet=256M \ --show-warnings \ area_stage < /tmp/area_full.sql \ 2> /tmp/area_import.err # 看错误日志尾部，确认有没有被忽略的警告 tail -n 50 /tmp/area_import.err

这段脚本的关键点是“临时库 + 显式字符集 + 错误日志”。--default-character-set=utf8mb4保证客户端发送和接收都按 UTF-8 处理；--max-allowed-packet=256M是会话级包大小，避免单条INSERT过大被服务端拒绝；--show-warnings会把警告打出来，比如字段截断、日期默认值问题；2>把错误留到文件里，失败了还能回看是哪一行出的问题。

如果文件里自带CREATE TABLE，导入前先确认表名是否和现有表冲突。更稳的做法是把文件里的建表语句改成CREATE TABLE IF NOT EXISTS area_stage.sys_area，或者先手工建表再只导数据。导入完成后不要马上切生产，先跑对账。

3.3 导入后的五项对账 SQL

省市区表的对账不需要花哨，抓住层级分布、孤儿记录、重复编码、空名称、根节点数量这五项，大部分文件问题都能暴露出来。

-- 1. 层级分布：正常应只有 1、2、3 三级 SELECT level, COUNT(*) AS cnt FROM area_stage.sys_area GROUP BY level ORDER BY level; -- 2. 孤儿记录：父级 id 找不到对应节点 SELECT c.id, c.name, c.parent_id FROM area_stage.sys_area c LEFT JOIN area_stage.sys_area p ON p.id = c.parent_id WHERE c.parent_id <> 0 AND p.id IS NULL LIMIT 20; -- 3. 重复编码：code 应该唯一 SELECT code, COUNT(*) AS cnt FROM area_stage.sys_area GROUP BY code HAVING COUNT(*) > 1; -- 4. 空名称和异常层级 SELECT COUNT(*) AS bad_name_cnt FROM area_stage.sys_area WHERE name IS NULL OR TRIM(name) = ''; -- 5. 层级连续性：子节点层级应等于父节点层级 + 1 SELECT c.id, c.name, c.level, p.level AS parent_level FROM area_stage.sys_area c JOIN area_stage.sys_area p ON p.id = c.parent_id WHERE c.level <> p.level + 1 LIMIT 20;

第一项看层级分布，如果出现level=4或者只有level=1，说明文件结构和表设计不匹配；第二项找孤儿记录，parent_id不为 0 但父级不存在，级联下拉会在这里断掉；第三项查重复code，重复编码会让唯一索引导入失败，或者让业务按编码查询时返回多条；第四项查空名称，空名称在下拉里就是一条空白选项；第五项查层级连续性，防止文件里把区县直接挂在省下面，level却写成 3。

这些 SQL 的LIMIT 20只是抽样，真正上线前建议把LIMIT去掉跑全量，或者用COUNT(*)包一层确认问题条数。对账通过后再把area_stage.sys_area切到正式库，切换前记得备份旧表。

4. 省市区名称查询：按父级、按关键词、反查完整路径

4.1 按父级取下级与名称模糊搜索的索引边界

最常见的查询是级联下拉：传父级parent_id，取下一级名称。这个查询走idx_parent_level就够了，前提是条件里带上status时不要把索引顺序写反。

-- 级联下拉：按父级取下一级，sort_order 控制展示顺序 SELECT id, name, code, level FROM sys_area WHERE parent_id = 100 AND status = 1 ORDER BY sort_order, id LIMIT 100; -- 名称前缀搜索：能用上 idx_name SELECT id, name, level, code FROM sys_area WHERE name LIKE '示例%' AND status = 1 LIMIT 20; -- 名称前后模糊：用不上 B+ 树索引，数据量大时要另做检索 SELECT id, name, level, code FROM sys_area WHERE name LIKE '%示例%' AND status = 1 LIMIT 20;

第一段 SQL 的逻辑是标准邻接表查询，parent_id是驱动条件，status过滤停用节点，ORDER BY sort_order, id保证展示顺序不乱。参数上，parent_id = 100只是示例，实际传父级主键；LIMIT 100是兜底，正常一个市下面的区县不会超过这个量级。第二段LIKE '示例%'是前缀匹配，idx_name能帮上忙；第三段LIKE '%示例%'是前后模糊，B+ 树索引基本失效，数据量小还能忍，数据量大就要考虑单独的前缀字段或独立检索服务。

status的选择性很低，单独给status建索引意义不大，放在联合索引里更合适。ORDER BY sort_order, id可能会产生 filesort，但省市区表数据量有限，通常可以接受；如果确实慢，再考虑把sort_order放进联合索引，但不要为了一个排序把索引堆得太宽。

4.2 MySQL 8 递归 CTE 反查省市区路径

业务里经常遇到“用户选了一个区县，后台要显示省/市/区县完整路径”。MySQL 8 可以用递归 CTE 从子节点往上找父级，直到根节点。

WITH RECURSIVE area_path AS ( -- 起点：传入区县 id SELECT id, parent_id, name, level, code, 1 AS depth FROM sys_area WHERE id = 300 UNION ALL -- 递归：找父级，depth 防止异常数据导致死循环 SELECT p.id, p.parent_id, p.name, p.level, p.code, c.depth + 1 FROM sys_area p JOIN area_path c ON p.id = c.parent_id WHERE c.parent_id <> 0 AND c.depth < 5 ) SELECT id, parent_id, name, level, code, depth FROM area_path ORDER BY depth DESC;

这段 CTE 的逻辑是从传入的区县节点开始，每次拿当前节点的parent_id去 join 父节点，depth每层加一，最后按depth DESC排序，输出就是“省—市—区县”。参数上，WHERE id = 300换成实际区县主键；c.depth < 5是保险丝，正常三级最多递归三次，加这个条件是为了防止文件里出现环状父级导致查询跑飞；UNION ALL比UNION快，因为省市区路径正常不会重复。

如果表里还有status，递归过程中要不要过滤停用节点要想清楚：路径展示通常需要把历史停用节点也显示出来，否则用户以前选的地址会变成空白。我的做法是路径查询不过滤status，只在级联下拉里过滤。

4.3 MySQL 5.7 的自连接写法与分页陷阱

MySQL 5.7 没有递归 CTE，省市区固定三级用两次LEFT JOIN就够，不需要上存储过程。

SELECT p.name AS province_name, c.name AS city_name, d.name AS district_name FROM sys_area d LEFT JOIN sys_area c ON d.parent_id = c.id LEFT JOIN sys_area p ON c.parent_id = p.id WHERE d.id = 300;

这段自连接从区县表d出发，先找市c，再找省p。LEFT JOIN是为了防止某一级缺失时整行查不出来，缺失的那一级会显示NULL，排查数据问题反而更方便。参数上，d.id = 300是区县主键；如果业务传的是code，把条件改成d.code = '900003'，但要确保code有唯一索引。

分页是另一个容易翻车的地方。地址搜索页如果写LIMIT 10000, 20，偏移量越大越慢，因为 MySQL 要扫掉前面一万行。常见做法是用游标分页：

-- 第一页 SELECT id, name, level, code FROM sys_area WHERE name LIKE '示例%' AND status = 1 ORDER BY id LIMIT 20; -- 后续页：传上一页最后一条的 id SELECT id, name, level, code FROM sys_area WHERE name LIKE '示例%' AND status = 1 AND id > 1024 ORDER BY id LIMIT 20;

游标分页的逻辑是用id > 上一页最后一条 id代替OFFSET，避免扫描无用行。参数上，id > 1024是示例，实际传上一页返回的最后一条主键；ORDER BY id要固定，不能这一页按id排、下一页按name排，否则分页会重复或漏数据。

5. 避坑与排查：导入、编码、层级和覆盖更新的翻车记录

5.1 编码与排序规则导致的乱码和匹配失败

现象：导入后名称显示成问号或乱码，前端下拉里全是“锟斤拷”；用WHERE name = '示例省'查不到，但表里明明有这条数据。

原因：SQL 文件的编码、客户端连接字符集、表字符集三者不一致。常见组合是文件是 GBK，客户端按 UTF-8 发，表又是utf8而不是utf8mb4，中文在某一环被转了一次。排序规则不一致也会导致匹配失败，比如utf8mb4_general_ci和utf8mb4_0900_ai_ci对某些字符的比较行为不同。

解决：先file -i看文件编码，再用SHOW CREATE TABLE sys_area看表字符集，最后确认连接字符集。导入命令加--default-character-set=utf8mb4，建库建表统一utf8mb4，不要混用utf8。如果文件已经是乱码，不要试图在 SQL 里REPLACE，重新用正确编码导入一遍更省时间。

SHOW VARIABLES LIKE 'character_set_%'; SHOW VARIABLES LIKE 'collation_%'; SHOW CREATE TABLE sys_area;

这三条 SQL 用来定位编码问题出在服务端、连接层还是表定义。参数上，character_set_client、character_set_connection、character_set_results三个都应该是utf8mb4；collation_connection和表的排序规则要能对上。不要只看character_set_server，客户端连接字符集经常是单独设置的。

5.2 层级断裂、重复编码与孤儿记录

现象：级联下拉选完省之后，市出来了，但选完市没有区县；或者某个区县在列表里能看到，点进去父级是空的；再或者按编码查询返回两条同名记录。

原因：完整版文件可能用parent_code表示父级，而不是内部自增id，导入时没有回填parent_id，所有子节点的parent_id都是 0；文件本身有重复编码，或者多次导入同一份文件，唯一索引没建，重复数据就进去了。

解决：导入临时表时保留原始parent_code，用一条UPDATE ... JOIN回填parent_id，再跑孤儿记录检查。重复编码用ROW_NUMBER()或临时表去重，MySQL 5.7 没有窗口函数，就用GROUP BY code取最小id再删其余。

-- 临时表增加 parent_code 列，导入时保留原始父级编码 ALTER TABLE area_stage.sys_area ADD COLUMN parent_code CHAR(6) DEFAULT NULL COMMENT '原始文件里的父级编码'; -- 用父级编码回填 parent_id UPDATE area_stage.sys_area c JOIN area_stage.sys_area p ON p.code = c.parent_code SET c.parent_id = p.id WHERE c.parent_id = 0 AND c.parent_code IS NOT NULL AND c.parent_code <> '';

这段 SQL 的逻辑是先保留原始父级编码，再用自连接把parent_code映射成内部parent_id。参数上，parent_code要和文件里的列名一致，c.parent_id = 0只回填还没映射的节点，避免覆盖已有关系。回填完再跑第 3 章的五项对账，尤其是孤儿记录和层级连续性。

5.3 大文件导入中断与覆盖更新引发的外键断裂

现象：导入到一半报MySQL server has gone away或Packet too large；或者导入成功了，但业务表里一批area_id找不到对应行，历史订单的地址显示空白。

原因：前者通常是max_allowed_packet太小、单条INSERT太大、连接超时；后者是用TRUNCATE TABLE或DROP TABLE重新导入，自增id重新分配，业务表里存的旧id对不上新数据。REPLACE INTO也会先删后插，同样会改变自增id。

解决：导入大文件时调大包大小，必要时把文件拆成多个批次；覆盖更新不要直接TRUNCATE，用INSERT ... ON DUPLICATE KEY UPDATE按code更新，保留已有id。如果业务表已经存了area_id，切换前先做映射校验，别直接切。

INSERT INTO sys_area (id, parent_id, name, level, code, status) VALUES (?, ?, ?, ?, ?, 1) ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), level = VALUES(level), status = VALUES(status);

这段 upsert 的逻辑是以code唯一索引为冲突键，存在就更新名称、父级、层级和状态，不存在才插入。参数上，id传原表主键或NULL让自增分配；VALUES()写法在 MySQL 8.0.20 之后有替代语法，但为了兼容 5.7 可以继续用。注意不要用REPLACE INTO做这件事，它会把旧行删掉再插新行，自增id会变，业务外键就断了。

5.4 查询慢与 LIKE 通配符的误用

现象：地址搜索页输入两个字要等一两秒，EXPLAIN一看是全表扫；级联下拉偶尔也慢，但数据量明明不大。

原因：LIKE '%示例%'前导通配符让 B+ 树索引失效；status单独建索引选择性太低；ORDER BY sort_order, id没有合适索引时会产生 filesort；分页用大OFFSET也会拖慢。

解决：能前缀搜索就不要前后模糊；必须前后模糊时限制level范围，比如只在区县层搜；数据量大就加一个name_pinyin前缀字段，或者把搜索放到独立检索服务。分页改成游标分页，排序字段固定。

-- 用 EXPLAIN 看访问类型，重点看 type 和 rows EXPLAIN SELECT id, name, level, code FROM sys_area WHERE name LIKE '示例%' AND status = 1 ORDER BY id LIMIT 20;

这段EXPLAIN的逻辑是确认查询有没有走idx_name，type是range或index比ALL好，rows估算值不要接近全表行数。参数上，LIKE '示例%'是前缀匹配，LIKE '%示例%'不要拿来做高频搜索；ORDER BY id比ORDER BY sort_order, id更容易用上主键顺序。如果业务一定要前后模糊，至少加level条件把扫描范围压小。

6. 进阶用法：版本化发布与发布前校验清单

省市区表不是导入一次就永远不动，后面换文件、补区县、改名称都可能发生。我一般会加一张版本表，把每次导入的版本标识、源文件哈希、行数和导入时间记下来，出问题能回滚到上一版。

CREATE TABLE area_version ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, version_tag VARCHAR(32) NOT NULL COMMENT '版本标识，例如 2024Q1', source_hash CHAR(64) NOT NULL COMMENT '源文件哈希，防重复导入', row_count INT NOT NULL COMMENT '导入行数', imported_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_version (version_tag) );

发布流程我习惯分三步：先导入area_stage，再跑第 3 章的五项对账，最后在低峰期用RENAME TABLE做切换，旧表保留成备份表。切换前把下面这张检查表过一遍，比事后拍脑袋强。

有一次我图省事，直接把新文件source到生产库，结果文件编码不对，级联下拉一半是空白，排查了半小时才发现是客户端字符集没指定。从那以后我每次导入省市区表都强制走一遍临时库、五项对账和版本表登记，不再直接碰生产。希望帮到你。

本文还有配套的精品资源，点击获取