全国省市区三级联动表：MySQL导入与查询实战指南

简介这份资源是2024年最新整理的MySQL全国省市区三级联动数据表面向后端开发、数据库设计人员以及需要地址级联选择功能的前端工程师可解决地理信息查询与行政区域联动维护的问题。压缩包共2个文件以sql数据脚本和zip归档为主整体约162.23MB其中sql文件包含省、市、区三级表结构定义与初始化数据zip则便于整体传输与备份。已有569人学习下载说明其在同类数据资源中具备一定参考价值。数据采用上级编码外键关联的设计省级为一级节点、城市为二级、区县为三级通过索引优化外键查询效率并覆盖行政区划合并、拆分等特殊情况的处理思路。读者可直接导入使用快速搭建省市区联动下拉数据也可参考其表结构与字段设计用于地址管理、地理信息系统等场景的数据建模与维护。1. 全国省市区三级联动表一份能直接导入 MySQL 的行政区划数据做过后台系统的人大概率都碰过这个场景用户注册要选地区运营后台要按区域筛选订单物流系统要算配送范围。前端三个下拉框联动省一变市跟着变市一变区跟着变。看起来简单但数据从哪来自己爬格式乱、层级对不上、直辖市和特别行政区结构特殊光清洗就能耗掉两天。这份 2024 年最新的 MySQL 全国省市区三级联动表解决的就是这个「数据源」问题——它把省、市、区三级行政区划整理成结构化的 SQL 表直接导入就能用。适合谁正在做 JavaWeb 项目、小程序后端、管理系统的开发者尤其是需要快速搭起地区选择功能、又不想在数据清洗上浪费时间的场景。表结构通常围绕province、city、area三张表或一张自关联表设计字段包含行政区划代码和名称配合parent_id或pid做层级关联。下面从表结构设计讲到导入验证再到联动查询和踩坑排查一步步拆开。2. 表结构设计与导入三张表还是一张自关联表拿到一份省市区 SQL 文件第一件事不是急着source导入而是先看它的表结构设计。不同来源的数据包设计思路差别很大直接决定了你后面写查询顺不顺手。常见的有两种流派三张独立表province / city / area或者一张自关联表比如region表带parent_id。两种都能用但适用场景不同。2.1 三表分离结构字段清晰联表查询直观三表分离是最传统的做法每级一张表字段冗余少语义明确。典型结构如下-- 省级表 CREATE TABLE province ( id int(11) NOT NULL AUTO_INCREMENT COMMENT 省份主键, code varchar(6) NOT NULL COMMENT 省级行政区划代码如 110000, name varchar(50) NOT NULL COMMENT 省份名称, PRIMARY KEY (id), UNIQUE KEY uk_code (code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT省级行政区划表; -- 市级表 CREATE TABLE city ( id int(11) NOT NULL AUTO_INCREMENT COMMENT 城市主键, code varchar(6) NOT NULL COMMENT 市级行政区划代码如 110100, name varchar(50) NOT NULL COMMENT 城市名称, province_code varchar(6) NOT NULL COMMENT 所属省份代码, PRIMARY KEY (id), KEY idx_province_code (province_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT市级行政区划表; -- 区县表 CREATE TABLE area ( id int(11) NOT NULL AUTO_INCREMENT COMMENT 区县主键, code varchar(6) NOT NULL COMMENT 区县行政区划代码如 110101, name varchar(50) NOT NULL COMMENT 区县名称, city_code varchar(6) NOT NULL COMMENT 所属城市代码, PRIMARY KEY (id), KEY idx_city_code (city_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT区县行政区划表;这里用code而不是自增id做关联字段原因是行政区划代码本身有国家标准GB/T 2260前两位代表省中间两位代表市后两位代表区县天然带层级信息。用code关联的好处是即使数据重新导入、自增 id 变了关联关系也不会断。province_code和city_code上建了普通索引因为联动查询时WHERE province_code ?是高频操作。导入时注意字符集。省市区名称里有生僻字比如「儋州」「亳州」如果数据库或表用utf8而不是utf8mb4某些四字节字符会插入失败或变问号。建库时就定好CREATE DATABASE region_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE region_db; -- 然后依次 source 三个表的 sql 文件 SOURCE /path/to/province.sql; SOURCE /path/to/city.sql; SOURCE /path/to/area.sql;导入完成后先别急着写业务代码跑一遍计数验证SELECT COUNT(*) AS province_cnt FROM province; SELECT COUNT(*) AS city_cnt FROM city; SELECT COUNT(*) AS area_cnt FROM area;正常情况下省级 34 条左右含港澳台市级 340 条上下区县 2800 条以上。如果数字差太多说明文件不完整或者导入中途报错被忽略了。SOURCE命令在 MySQL 命令行里执行如果文件很大建议用mysql -u root -p region_db province.sql的方式在系统 shell 里导入速度更快报错也更明显。2.2 单表自关联结构查询灵活但索引要设计好另一种常见设计是一张region表搞定用parent_id指向父级CREATE TABLE region ( id int(11) NOT NULL AUTO_INCREMENT, code varchar(6) NOT NULL COMMENT 行政区划代码, name varchar(50) NOT NULL COMMENT 名称, parent_id int(11) NOT NULL DEFAULT 0 COMMENT 父级 id省级为 0, level tinyint(1) NOT NULL COMMENT 层级1 省 2 市 3 区, PRIMARY KEY (id), KEY idx_parent (parent_id), KEY idx_level (level) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这种结构的好处是扩展方便——以后要加街道四级不用新建表改level就行。但查询时要注意parent_id必须建索引否则每次联动都全表扫描。另外level字段不是必须的但加上它能避免递归查询时反复判断层级属于用空间换时间的做法。导入单表数据后验证层级关系是否正确-- 检查有没有孤儿节点parent_id 指向不存在的记录 SELECT r.id, r.name, r.parent_id FROM region r LEFT JOIN region p ON r.parent_id p.id WHERE r.parent_id ! 0 AND p.id IS NULL;这条查询返回空结果才说明层级完整。如果有孤儿节点联动时会出现「选了省但市出不来」的情况。提示不管用哪种结构导入前都建议先SET NAMES utf8mb4;避免命令行客户端编码和文件编码不一致导致中文乱码。3. 三级联动查询从 SQL 到接口的落地写法数据导入只是第一步真正在项目里用起来核心是「根据父级代码查子级列表」这个动作。前端每次切换省份后端就要返回对应的市列表切换市返回区列表。这个查询本身不复杂但写法上有几个细节直接影响性能和体验。3.1 基础联动查询与索引命中以三表结构为例查某个省下的所有市-- 根据省份 code 查城市列表 SELECT code, name FROM city WHERE province_code 440000 -- 广东省 ORDER BY code;这条查询走idx_province_code索引速度很快。但要注意ORDER BY code这个排序——行政区划代码本身是按国家标准编排的排序后基本符合习惯顺序省会城市通常排在前面。如果数据包里没有按 code 排序或者你想按名称拼音排序那就得加ORDER BY CONVERT(name USING gbk)但这样会触发 filesort数据量大时区县级别会有明显延迟。查区县同理SELECT code, name FROM area WHERE city_code 440300 -- 深圳市 ORDER BY code;实际项目里我一般会把这三条查询封装成一个接口用参数控制层级-- 通用查询根据层级和父级代码查子级 -- level1 查省level2 查市需传 province_codelevel3 查区需传 city_code SELECT code, name FROM province ORDER BY code; -- level1 SELECT code, name FROM city WHERE province_code ? ORDER BY code; -- level2 SELECT code, name FROM area WHERE city_code ? ORDER BY code; -- level3参数说明?是占位符实际执行时替换为前端传来的父级 code。用占位符而不是字符串拼接是为了防 SQL 注入——地区选择虽然看起来是「可信输入」但前端传参永远不可信。如果用的是单表自关联结构查询变成-- 查某省下的市先拿到省的 id再查 parent_id 等于该 id 的记录 SELECT c.code, c.name FROM region c JOIN region p ON c.parent_id p.id WHERE p.code 440000 AND c.level 2 ORDER BY c.code;这里JOIN的条件是c.parent_id p.id走的是idx_parent索引。注意c.level 2这个条件不能省——如果以后加了街道四级不加 level 过滤会把街道也查出来。3.2 缓存策略别每次都查库省市区数据有个特点几乎不变。2024 年的数据和 2023 年比最多就是个别区县改名或撤并变动频率极低。所以每次前端切换都查一次数据库属于典型的浪费。常见做法是在应用层加缓存比如 Redis 或者本地 Caffeine。我一般会这样处理应用启动时把全部省市区数据加载到本地 Mapkey 是父级 codevalue 是子级列表。查询时直接查内存响应时间从几毫秒降到微秒级。如果不想占内存用 Redis 缓存也行但要注意设置合理的过期时间——虽然数据不变但万一有更新缓存不刷新就会一直返回旧数据。-- 如果走 Redis 缓存首次查库后把结果序列化存进去 -- 伪代码逻辑 -- 1. 根据 parent_code 拼 key如 region:city:440000 -- 2. 查 Redis命中则直接返回 -- 3. 未命中则查 MySQL结果写入 Redis设置过期时间如 24 小时参数上过期时间不建议设太长比如永久因为行政区划调整虽然少但一旦发生用户看到旧数据会投诉。24 小时是个折中值既能挡住绝大部分查询又能保证一天内最终一致。注意如果用本地缓存多实例部署时每个实例各存一份数据更新需要重启或手动刷新。用 Redis 则天然共享但多了一次网络开销。小项目本地缓存足够大项目建议 Redis。4. 避坑与排查导入和联动时最容易翻车的几个点这份数据包本身不复杂但实际用起来翻车的地方往往不在 SQL 语法而在编码、字符集、数据完整性这些「看起来没问题」的地方。下面几条是我自己和身边同事踩过的坑按「现象 → 原因 → 解决」整理。4.1 中文乱码导入后名称全是问号现象SELECT * FROM province;查出来省份名称显示为???或者乱码字符。原因SQL 文件的编码和数据库连接的字符集不一致。常见情况是文件是 UTF-8但导入时客户端用了 latin1或者表建的时候用了utf8而不是utf8mb4。解决先确认文件编码file -i province.sql然后在导入前执行SET NAMES utf8mb4;建表时统一用DEFAULT CHARSETutf8mb4。如果已经导入乱了只能删表重建再导。4.2 外键关联断裂选了省市列表为空现象前端选了「广东省」但市的下拉框是空的数据库里明明有数据。原因city表的province_code和province表的code对不上。可能是数据包里 code 格式不一致有的带前导零有的被当成数字丢了零或者导入时字段类型不对导致截断。解决跑一遍关联检查SELECT DISTINCT c.province_code FROM city c LEFT JOIN province p ON c.province_code p.code WHERE p.code IS NULL;返回的 code 就是「有市无省」的孤儿数据。如果是前导零丢失把code字段改成varchar而不是int重新导入。4.3 直辖市结构特殊北京的用户选不到「区」现象北京市的用户选了「北京市」之后市一级没有选项直接卡住。原因直辖市北京、上海、天津、重庆在行政区划里是省级但下面直接就是区没有「市」这一级。如果数据包按标准三级结构处理直辖市的「市」这一级可能是空的或者重复的。解决常见做法是把直辖市的「市」一级设为一个虚拟节点比如 name 也是「北京市」code 用省级 code区县直接挂在这个虚拟市下面。导入后验证-- 检查直辖市下是否有区县 SELECT p.name AS province, c.name AS city, COUNT(a.id) AS area_cnt FROM province p JOIN city c ON c.province_code p.code LEFT JOIN area a ON a.city_code c.code WHERE p.name IN (北京市,上海市,天津市,重庆市) GROUP BY p.name, c.name;如果area_cnt为 0说明区县没挂上需要调整数据或改查询逻辑。4.4 港澳台数据缺失或结构不同现象前端地区选择里找不到「香港」「澳门」「台湾」。原因部分数据包只收录大陆 31 个省级行政区不含港澳台。或者收录了但层级结构和大陆不同比如香港下面直接是区没有市。解决先确认数据包是否包含。如果不包含需要自己补三条省级记录下级数据按实际需要决定是否补全。如果包含但结构不同查询时要做兼容——比如香港的「市」一级可以留空前端选中后直接展示区列表。4.5 导入大文件超时max_allowed_packet 报错现象导入区县表时提示MySQL server has gone away或Packet too large。原因区县数据量大单条 INSERT 语句太长超过了 MySQL 默认的max_allowed_packet通常 4MB 或 16MB。解决临时调大参数再导入SET GLOBAL max_allowed_packet 64 * 1024 * 1024; -- 64MB或者把大 SQL 文件拆成多个小文件分批导入。导入完成后再改回默认值避免长期占用过多内存。5. 进阶技巧用存储过程批量校验数据完整性数据导入后除了前面零散的检查我习惯写一个存储过程做一次全量校验把「省-市-区」三级的关联完整性、代码格式、重复记录一次性查清楚。这样比一条条手写 SQL 省事也避免遗漏。DELIMITER $$ CREATE PROCEDURE check_region_integrity() BEGIN -- 1. 检查市级孤儿数据 SELECT 市级孤儿 AS check_item, COUNT(*) AS cnt FROM city c LEFT JOIN province p ON c.province_code p.code WHERE p.code IS NULL; -- 2. 检查区级孤儿数据 SELECT 区级孤儿 AS check_item, COUNT(*) AS cnt FROM area a LEFT JOIN city c ON a.city_code c.code WHERE c.code IS NULL; -- 3. 检查 code 长度不是 6 位的异常记录 SELECT 省级code异常 AS check_item, COUNT(*) AS cnt FROM province WHERE CHAR_LENGTH(code) ! 6; SELECT 市级code异常 AS check_item, COUNT(*) AS cnt FROM city WHERE CHAR_LENGTH(code) ! 6; SELECT 区级code异常 AS check_item, COUNT(*) AS cnt FROM area WHERE CHAR_LENGTH(code) ! 6; -- 4. 检查重复 code SELECT 省级重复code AS check_item, COUNT(*) AS cnt FROM (SELECT code FROM province GROUP BY code HAVING COUNT(*) 1) t; SELECT 市级重复code AS check_item, COUNT(*) AS cnt FROM (SELECT code FROM city GROUP BY code HAVING COUNT(*) 1) t; SELECT 区级重复code AS check_item, COUNT(*) AS cnt FROM (SELECT code FROM area GROUP BY code HAVING COUNT(*) 1) t; END$$ DELIMITER ; -- 调用 CALL check_region_integrity();这里用DELIMITER $$是因为存储过程内部有分号不换分隔符 MySQL 会提前截断。CHAR_LENGTH而不是LENGTH是因为LENGTH返回字节数UTF-8 下中文一个字占 3 字节用LENGTH判断会误判。每个检查项返回cnt为 0 才算通过。调用后如果发现异常根据check_item定位到具体表再针对性修复。比如「市级孤儿」不为 0就去查是哪些province_code对不上手动补省级记录或者修正市级数据的关联字段。从那以后我每次拿到新的地区数据包都强制走一遍这个存储过程确认全绿了再往业务库里导。省得上线后用户反馈「选不了地区」再回头查那时候数据已经混进生产环境清理起来更麻烦。希望帮到你。本文还有配套的精品资源点击获取