简介包含中国全量省市区数据的MySQL脚本资源面向需要快速接入行政区域数据的Web开发者与数据分析人员解决地理信息存储与检索时的重复建表问题。压缩包内只有1个sql文件整体大小126KB内含建表语句与完整数据插入脚本字段覆盖省份、城市、区县名称并为每条记录提供电话区号、拼音和经纬度坐标导入后即可直接用于地址校验、地图定位与距离计算等场景。已有2934人学习下载适合电商、物流、政务等项目中实现省市区三级联动、拼音模糊搜索和地理位置筛选。相比手工收集和维护地区数据该脚本通过标准SQL完成一次性数据初始化降低重复劳动开发者还能直接基于其清晰表结构进行扩展例如增加时区、邮编或行政区划变更记录快速支撑本地化业务需求。1. 一份能直接入库的省市区表先看它解决什么问题接到后台管理系统需求要做一个订单地址的下拉框省、市、区三级联动。当时想的是前端组件现成的后端存个编码就行最多一小时能搞定。真正动手才发现卡住进度的不是组件而是手头根本没有一套干净、完整的省市区数据。网上东拼西凑的 JSON 不是缺区就是乱码还有的干脆把乡镇街道混在区里面导入后前端选项直接多出一大截。后来找到 mysql 省市区表完整版文件思路立刻变了数据以 SQL 形式提供包含建表语句和全国省市县三级记录拉到库里就是一个标准字典表业务侧只管查就行。这篇文章就是要说清楚这份文件怎么选、怎么导、怎么验以及哪些地方容易翻车。2. 拆开完整版文件三级结构、编码规则与建表选型拿到 mysql 省市区表完整版文件第一件事不是急着执行而是先看里面的表结构和数据格式。常见做法是两种一种是一张表带 parent_code 自关联字段另一种是省份、城市、区县三张固定表。大多数完整版资源采用前者因为查询层级关系方便而且插入、更新只需要维护一张表。完整版文件里的建表语句一般长这样CREATE TABLE region ( id int NOT NULL AUTO_INCREMENT, code varchar(12) NOT NULL COMMENT 行政区划代码, name varchar(64) NOT NULL COMMENT 名称, parent_code varchar(12) DEFAULT NULL COMMENT 父级编码省级为NULL, level tinyint NOT NULL DEFAULT 1 COMMENT 层级1省 2市 3区, PRIMARY KEY (id), UNIQUE KEY uk_code (code), KEY idx_parent (parent_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT省市区三级行政区划表;这段建表语句的核心设计是先通过level区分层级再用parent_code挂载父子关系。code字段存行政区划代码这是整个表的灵魂省是两位市是四位区是六位。UNIQUE KEY保证编码不重复idx_parent索引让按父级查子集的查询走索引而不是全表扫。为什么用varchar(12)而不是int因为行政区划代码是带有前导零的字符串比如某直辖市的编码是“110000”虽然表现成数字但本质是码值用 int 会把前导零吃掉后续匹配和排序都会出问题。utf8mb4字符集也是必要选项区县名称里偶尔会出现生僻字utf8 只有三字节生僻字直接转成问号。2.1 编码与层级看懂这份表才能不踩数据坑行政区划代码遵循的是 GB/T 2260 标准结构是 AABBCCAA 表示省级BB 表示地级市CC 表示区县。这份表里的逻辑也沿用了这套规则。看懂规则后有三个立竿见影的用法。一是判断级别不需要只看level字段也能从编码位数据猜出来比如编码只有两位说明是省级记录四位的必然是市级六位的是区县级二是校验父子关系时可以把parent_code跟code做字符串前缀匹配比如某个市级编码是“1101”它的下级区县编码前四位一定是“1101”三是新增自定义区域时编码要避开已有的六位码段不然唯一索引会直接拦住。-- 按编码长度判断层级适用于没有 level 字段的历史表 SELECT code, name, CASE CHAR_LENGTH(code) WHEN 2 THEN 省 WHEN 4 THEN 市 WHEN 6 THEN 区 END AS level_name FROM region LIMIT 20;这段 SQL 的价值在于不依赖level字段也能在数据文件缺字段时快速确认每条记录的行政级别。CHAR_LENGTH和LENGTH不同前者按字符计数中英文都按一个算后者按字节计数中文字符在 utf8mb4 下占四个字节。判断行政区划代码这种纯数字字符串用CHAR_LENGTH不会出错。2.2 选型InnoDB、字符集和导入方式怎么定表引擎选 InnoDB 基本没有悬念原因有两个省市区表虽然数据量只有几千行但会被订单表、用户表反复关联查询InnoDB 的行锁和主键聚簇组织方式在高并发读多写少的场景下表现稳定另一个是它支持事务万一导入中途出错可以回滚重来。MyISAM 虽然压缩后体积更小但表锁会让并发写被堵住不值得在这个字典表上省那点空间。字符集建议直接选utf8mb4排序规则选utf8mb4_general_ci或utf8mb4_unicode_ci都行。区别是前者性能稍好后者排序更符合 Unicode 规范。省市区表的数据量小到可以忽略性能差异两个都能用但要避免只改库不改表导致表是 utf8 库是 utf8mb4数据导入时出现隐式转换。导入方式的选择也很影响体验。最常见的是用 MySQL 命令行source命令执行 SQL 文件操作简单且能看到执行日志。另一种是用可视化客户端导入适合对命令行不熟的开发者。数据量大到一定程度时也可以用LOAD DATA INFILE走 csv 导入但省市区表最多几万行直接用 SQL 脚本反而更稳。3. 从 SQL 文件到业务库导入实操与参数设置导入这份文件之前我习惯先建一个独立库比如region_db不跟业务库混在一起。这么做的好处是后续如果要换表结构或者清空重导删库不影响线上业务。库建好后再执行完整版文件里的建表和插入语句。CREATE DATABASE IF NOT EXISTS region_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE region_db; -- 假设完整版文件已经下载到本地路径/data/sql/region_full.sql SOURCE /data/sql/region_full.sql;SOURCE是 mysql 命令行客户端的内建命令不是 SQL 语句所以只能在 mysql 交互环境下执行不能写进 SQL 脚本里再嵌套调用。执行结束后命令窗口会打印每条语句的成功或失败信息重点看尾部是否有ERROR字样。如果用的是完整版文件里自带的多行插入语句一个 INSERT 带几千行 values 时中途任何一个字段格式错误会导致整条语句失败报错信息会直接定位到第几行方便排查。3.1 导入前看一眼 SQL 文件的前后内容完整版文件在下载后、导入前我强烈建议先用文本编辑器打开看看开头和结尾而不是直接丢进数据库执行。开头一般是建表语句和注释结尾通常是提交事务或索引建立语句。有些精校版本会在末尾补上ALTER TABLE加索引的操作跳过它们虽然数据没问题但查询性能会打折。# 查看文件前 50 行 head -n 50 region_full.sql # 查看文件末尾 30 行 tail -n 30 region_full.sql # 统计文件里的 INSERT 语句数量和总行数 grep -c INSERT INTO region_full.sql grep -o ([0-9]\{6\} region_full.sql | wc -l这几条命令用来做导入前体检head和tail确认文件不残缺grep -c看插入语句是否分段最后一条统计六位数字编码出现的频率大概能估算出行数。正常情况下全国省市区县总数在三千上下如果统计结果明显偏少说明文件可能被裁剪过直接导入会在业务查询时缺数据。3.2 两种导入路径与导入后的自检命令行导入适合服务器环境可视化工具导入适合本地开发。命令行方式比较直接执行source时注意当前所在的数据库要切到region_db否则表会建在默认库下面。可视化工具一般右键选择“运行 SQL 文件”选择路径后点击开始。这两种方式本质上都是把文件里的 SQL 逐条扔给服务端执行最终效果一致。-- 导入后三连查总数、三级各多少、有无孤儿数据 SELECT COUNT(*) FROM region; SELECT level, COUNT(*) FROM region GROUP BY level; SELECT COUNT(*) FROM region r LEFT JOIN region p ON r.parent_code p.code WHERE r.level ! 1 AND p.code IS NULL;第一条看总量是否在合理范围第二条看三个层级的分布。正常分布大致是一级三十几个二级三百多个三级两千八百多个。第三条是孤儿数据排查如果查出来大于零说明某些区的parent_code指向了不存在的市这种数据在级联查询时会出现点击某市后区县列表为空。出现孤儿的原因大多是下载的版本混入了非标准区域或者建表时parent_code字段类型不匹配导致关联失败。4. 数据别急着上线三张校验 SQL 与级联查询写法导入成功不等于数据可用完整版文件也可能带瑕疵。业务方最在意的问题是下拉框能不能完整显示选了市级能不能正确筛出区县编码能不能和外部系统对接这三个问题靠三张校验 SQL 就能提前确认。不要等上线后接到“某省下面只有两个市”的反馈再去翻数据。-- 校验 1按省份分组统计市级数量排查省份下缺少市级的情况 SELECT p.name AS province, COUNT(c.code) AS city_cnt FROM region p LEFT JOIN region c ON c.parent_code p.code AND c.level 2 WHERE p.level 1 GROUP BY p.code, p.name ORDER BY city_cnt ASC LIMIT 20; -- 校验 2查找名称重复的区县 SELECT name, COUNT(*) FROM region WHERE level 3 GROUP BY name HAVING COUNT(*) 1; -- 校验 3查找编码前缀与父级编码不一致的记录 SELECT code, name, parent_code FROM region WHERE level 3 AND LEFT(code, 4) ! parent_code LIMIT 20;第一张 SQL 能看出数据完整性。正常情况下每个省份下至少有若干个地级市如果查出某个省市级数量为零说明该省的数据没导全。第二张查重很有必要不同省份下确实存在同名区县比如“开发区”“高新区”这类名称业务上允许重复但如果重复出现在同一父级下就是脏数据。第三张 SQL 用LEFT(code, 4)截取区县编码前四位跟parent_code比较不一致的记录会影响按编码识别的第三方接口对接。4.1 三级联动查询一条 SQL 串起省市区前端下拉框的三级联动通常要请求三次后端接口查所有省、根据省查市、根据市查区。后端写法其实很简单核心就是按parent_code过滤。但有一点要注意查询参数的类型要跟字段类型保持一致parent_code是 varchar前端传过来的参数如果是数字MySQL 会做隐式转换导致索引用不上。# FastAPI 风格的三级联动后端查询示例 from fastapi import FastAPI from pydantic import BaseModel import pymysql app FastAPI() class RegionOut(BaseModel): code: str name: str def get_conn(): return pymysql.connect( host127.0.0.1, userapp_user, passwordyour_password, databaseregion_db, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor ) app.get(/api/region/children, response_modellist[RegionOut]) def get_children(parent_code: str): # parent_code 为 0 时返回省级列表 if parent_code 0: sql SELECT code, name FROM region WHERE level 1 ORDER BY code else: sql SELECT code, name FROM region WHERE parent_code %s ORDER BY code with get_conn() as conn: with conn.cursor() as cur: cur.execute(sql, (parent_code,)) return cur.fetchall()这段代码的思路是统一暴露一个子级查询接口前端第一次传0拿到省级之后每次传选中项的code拿到下级列表。ORDER BY code是容易被忽略的细节行政区划代码的排序基本等同行政区划的官方顺序华北、东北、华东这样的顺序跟人脑习惯一致不排序就会出现省市随机排列的观感。代码里用了pymysql的上下文管理确保查询后连接能正常释放。4.2 编码冗余设计业务表里存什么订单表、用户表里存储地区信息时建议只存六位区县编码不要连省名市名一起存更不要存一个 JSON 数组。原因很简单行政区划可能调整区县可能被合并或改名业务表只存编码地区名称实时关联省市区表查询这样上游改名后业务表无需更新。省市区表在业务链路里应当扮演“字典表”的角色它的数据变更只影响展示层不影响历史记录。-- 订单表推荐结构片段 CREATE TABLE order_addr ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, district_code varchar(6) NOT NULL COMMENT 区县编码即最细粒度区域, detail varchar(255) DEFAULT COMMENT 街道门牌详情, PRIMARY KEY (id), KEY idx_district (district_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这段表设计把冗余降到了最低。常见误区是在订单表里同时存province_code、city_code、district_code三个字段理由是查询方便。但省市区三级本来就是可以通过编码推导的有了六位区县编码前四位截出来就是市级前两位截出来就是省级。多存两个字段只会增加不一致的风险比如出现省编码和区县编码对不上的情况。省市区表本身的parent_code已经保证了关系完整性业务表只挂最细粒度编码即可。5. 省市区表导入避坑5 个真实翻车现场完整版文件从下载到上线我见过不少同行在这里栽跟头。以下五个问题属于高发情况每条都按现象、原因、解决三个维度拆开方便遇到时直接对照处理。5.1 导入后中文全是问号现象是表建好了行数也对但查出来的name字段全是???。原因多半是客户端连接字符集跟库表字符集不一致SQL 文件本身如果是 utf8 编码而命令行客户端用默认的 latin1 去解析中文字符就会被转换坏。解决方法是导入前在 mysql 命令行里执行SET NAMES utf8mb4;让客户端、连接、返回结果三端字符集对齐。如果是用工具导入检查工具连接配置里的编码选项是否也是 utf8mb4。注意有些完整版文件头部会写SET NAMES utf8mb4但如果你跳过了文件开头直接执行插入语句这个设置就没生效。5.2 source 导入时报错却定位不到行现象是执行SOURCE后程序报ERROR 1062或ERROR 1366文件名和行号都有但打开文件核对那一行看不出任何问题。这通常是因为完整版文件的换行符是 Windows 风格CRLF而 MySQL 在 Linux 下解析时多了一个回车字符导致字段值末尾混入\r。解决方法是导入前用工具转换换行符# 将 CRLF 转为 LF sed -i s/\r$// region_full.sql这里用sed做原地替换\r$匹配行尾的回车符。转换后再执行source大部分诡异报错都会消失。另外顺手检查文件编码是否是带 BOM 的 UTF-8BOM 头会让第一条 SQL 语句报语法错误用sed -i 1s/^\xEF\xBB\xBF//可以去掉。5.3 索引创建失败导致慢查询现象是数据导入成功但前端级联接口每次要一两秒才返回。原因有两个可能性一是完整版文件末尾的索引语句因为重复执行而报错忽略索引没建成二是导完数据后没有执行ANALYZE TABLE统计信息不准确导致优化器选错执行计划。省市区表数据量不大全表扫描理论上也就几千行但如果每次联动请求都要扫一遍并发上来依然会拖慢接口。解决办法是主动确认索引存在SHOW INDEX FROM region; ANALYZE TABLE region;SHOW INDEX查看Key_name是否包含idx_parent和uk_code。ANALYZE TABLE会重新统计索引基数让优化器做出更合理的判断。执行完这两条后接口响应基本回到毫秒级。这属于典型的“数据没问题元数据没就位”的坑。5.4 导入过程中途断开导致半截数据现象是执行导入时报连接丢失重新导入后查总行数只有几千行但总感觉少了点。原因是 SQL 文件过大或网络不稳定时TCP 连接被中间设备掐断。省市区表文件一般不大但如果完整版里附带了几十年的历史变更记录文件体积就会明显膨胀。解决方法是导入前用pkill -f mysql确认没有残留连接然后用mysql --max-allowed-packet128M加大允许的最大数据包再执行SOURCE。还有一种思路是把大文件拆成多个小 SQL 文件分段执行方便定位中断点。导入完成后用前文的总数查询验证行数是否与文件内统计一致。5.5 误把乡镇街道数据当成区县现象是下拉框里区县数量明显偏多有的省份下面出现几百条“区”。原因是部分完整版文件为了凑“完整”把乡镇街道也塞进了表里level字段标成 3parent_code指向区县编码和真正的区县数据混在一起。业务只想要省市区三级这些多出来的数据会让前端下拉框暴长。解决办法是导入后按层级过滤清洗-- 删除 level3 且名称以常见乡级后缀结尾的行政区划数据 DELETE FROM region WHERE level 3 AND (name LIKE %街道 OR name LIKE %镇 OR name LIKE %乡);执行前建议先 SELECT 确认数量避免误删。真正的区县名称通常以“区、县、市”结尾乡镇街道以后缀“街道、镇、乡”结尾。清洗后重新统计每个省份的区县数量应该回落到正常范围。这条坑很值得留意因为不少所谓完整版正是靠塞入乡镇街道撑起文件体积的。6. 把省市区表用出生产力一个小巧的树形查询改造省市区表在大部分系统里只承担展示功能做一次树形查询改造就能复用所有层级数据不需要额外引入组织架构表。MySQL 8.0 支持递归 CTE可以在单条 SQL 里把某省下面的完整树路径查出来这个能力在筛选条件多变的报表场景里非常实用。-- 递归查出某省级编码下属的所有市和区县包含层级路径 WITH RECURSIVE region_tree AS ( SELECT code, name, parent_code, level, name AS path FROM region WHERE code 某省级编码 UNION ALL SELECT r.code, r.name, r.parent_code, r.level, CONCAT(rt.path, /, r.name) FROM region r INNER JOIN region_tree rt ON r.parent_code rt.code ) SELECT code, name, level, path FROM region_tree ORDER BY code;递归 CTE 的逻辑分两部分锚点查询先取出省级记录递归查询不断用子级拼回父级。path字段用CONCAT拼接出完整路径类似“某省/某市/某区”数据导出给运营看非常直观。这个改造的附加价值是让省市区表不只服务于下拉框还能直接用于导出报表、统计逐级汇总。运行前提是 MySQL 版本不低于 8.0如果是 5.7 及以下版本建议用应用层递归或者直接多次查询后在内存里组树不要把递归写成存储过程硬扛。我在实际项目里养成了一个习惯任何业务系统初始化时不管当下是否需要省市区联动都会把这份完整版表作为基础字典导入库中放在独立的region_db里并标记为“只读字典”。上线后一旦业务提需求要加地区维度的报表直接关联这张表就能出数不用临时找数据、清清洗、对编码。历史订单已经存了区县编码后来某个区被合并改名报表只需更新字典表以前的数据全部自动映射成新名称。这个收益不是一次性的而是随业务累积越来越明显。希望帮到你。本文还有配套的精品资源点击获取