MySQL版中国省市区数据表SQL：建模、导入与查询实战

简介这是面向开发者的 MySQL 版中国省市区数据表 SQL 文件核心是一份结构清晰的行政区划数据覆盖省、市、区县三级信息并包含 db_yhm_city 表的建表语句与完整 INSERT 数据。适合在电商、物流、后台管理、CRM 等需要地址选择的场景中快速搭建地区数据基础避免人工整理和维护成本。资源包共 1 个 PDF 文件大小约 495KB内容集中将建表语句、数据插入和查询示例汇于一册可直接复制使用。表结构采用 class_id、class_parent_id、class_name、class_type 四个字段通过 class_parent_id 构建父子层级配合索引可高效完成按省查市、按市查区县class_type 也能明确区分国家、省份、城市等类型为后续扩展预留空间。目前已有 1077 人学习下载对需要快速获取并初始化中国行政区划数据的开发者来说能有效缩短开发周期尤其适合作为中小型项目的地理区域模块基础。1. 大家都在找的 MySQL 版中国省市区数据表 SQL到底解决了什么后台管理系统做地区选择、订单地址校验、销售大屏按省汇总都绕不开一张中国省市区数据表。很多人以为去统计部门网页里拷一份 Excel 再导进库就行真做起来才发现清洗、去重、处理省市县三级归属关系就够折腾一整天Mysql 版中国省市区数据表 SQL 的价值就是把这个过程做成了成品建表语句和区划数据打包在一起导入直接用省掉中间所有转换环节。这份 SQL 覆盖的是省、市、区县两级基础数据成熟版本还会带上乡镇街道和邮政编码能支撑电商收货地址、物流分拣、BI 区域分析这类最常见的业务。适合三类人后端开发想快速给项目补一张区域基础表数据分析师要做省市区维度的汇总学生做课程设计需要一份干净可查的行政区划字典。它解决的核心问题是“数据可用性”不是复杂算法所以重头戏全在表结构设计和导入后的使用习惯上。2. 行政区划数据表怎么建模单表自关联与 GB/T 2260 编码拿到这份 SQL 之后第一件事不是急着导入而是看懂作者为什么这么建表。市面上的省市区数据表主要有两种建模思路一种拆成省、市、区三张表分别维护另一种用单表加父级编码形成树形结构。三张表的方案看着直观一旦遇到乡镇街道层级、区划合并调整就要改表结构甚至写迁移脚本。单表自关联的做法更接近行政区划数据的本质层级深度不固定归属关系靠一个 parent_code 字段表达数据行加进去就行不用动表结构。2.1 为什么用“单表自关联”而不是省市县三张表单表自关联的意思是同一张表里既有省份、也有城市和区县每一行通过 parent_code 指向上一级。中国行政区划是典型的树形结构省级节点下面挂市级市级下面挂区县部分地区区县下面还挂乡镇街道。用一张表维护这套树新增一个层级就是在父节点下插几行数据查询时用自连接或递归语法完成灵活性比三张表高得多。三张表的麻烦在于“市”这一级经常缺席。直辖市下面直接就是区省直辖县级市直接挂在省下面东莞、中山这类地级市下面不设区县、直接到镇街。如果按省表、市表、区表严格拆分这些特殊情况要么造一堆空壳记录要么在业务代码里写大量 if-else 判断。单表自关联配合 level 字段层级关系全部由数据自己表达联动的后端接口只需要根据 parent_code 返回“下一级”逻辑统一且不容易漏数据。2.2 建表语句按能跑十年的标准来建我一般会建议把这份 SQL 里的建表语句保留成下面这个格局字段名可以根据项目习惯调整但几个核心列不要动CREATE TABLE region ( code char(6) NOT NULL COMMENT 行政区划代码如 110000, name varchar(50) NOT NULL COMMENT 地区名称如 北京市、朝阳区, level tinyint NOT NULL COMMENT 层级1省 2市 3区县 4乡镇街道, parent_code char(6) DEFAULT NULL COMMENT 上级区划代码省级为 NULL, short_name varchar(50) DEFAULT NULL COMMENT 简称如 北京、朝阳, province_code char(6) DEFAULT NULL COMMENT 冗余省级代码便于统计, city_code char(6) DEFAULT NULL COMMENT 冗余市级代码区县级有效, district_code char(6) DEFAULT NULL COMMENT 冗余区县级代码乡镇级有效, pinyin varchar(100) DEFAULT NULL COMMENT 拼音用于搜索联想, postcode varchar(6) DEFAULT NULL COMMENT 邮政编码可为空, lng decimal(10,6) DEFAULT NULL COMMENT 经度, lat decimal(10,6) DEFAULT NULL COMMENT 纬度, sort int NOT NULL DEFAULT 0 COMMENT 同级排序数字越小越靠前, status tinyint NOT NULL DEFAULT 1 COMMENT 状态1启用 0停用, PRIMARY KEY (code), KEY idx_parent_code (parent_code), KEY idx_level (level), KEY idx_name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT中国省市区数据表;code 字段用 char(6) 不用 int理由是行政区划代码是定长六位数字不会参与数学运算用定长字符串既保持可读性也能避免数字类型把“110000”变成“110000”之外的意外。parent_code 建立索引是因为三级联动的每一次取数都是对 parent_code 做等值查询这个索引决定了联动的响应速度。level 字段单独索引用来快速过滤某一层级的数据比如只取省份做下拉框。编码字符集选择 utf8mb4是因为地区名称里可能出现生僻字utf8 的三字节编码存不下部分扩展区汉字。collation 选 utf8mb4_general_ci 而不是 utf8mb4_0900_ai_ci是为了兼容老版本 MySQL。很多还在运行的线上库是 5.78.0 默认的 utf8mb4_0900_ai_ci 在 5.7 里会直接报错这份 SQL 如果用 general_ci导入时少踩一个坑。名称字段加索引是为了支持地区搜索但后面会提到模糊搜索的场景下这个索引作用有限。2.3 数据行长什么样从省级到乡镇的典型结构只看表结构还不够得理解数据行的真实形态。一份完整的省市区数据表里北京市这一行大概是INSERT INTO region (code, name, level, parent_code) VALUES (110000, 北京市, 1, NULL), (110101, 东城区, 3, 110000), (110102, 西城区, 3, 110000);注意东城区的 parent_code 直接指向北京市level 是 3中间没有“北京市-市辖区”这一层。而广东省的数据则是 440000 广东省下面挂 440100 广州市再从 440100 往下挂 440103 荔湾区。同样是区县级数据直辖市下面的区和普通地级市下面的区它们的父级却不在同一个 level 上。这就是单表自关联的核心特征层级靠 parent_code 表达不靠“第几级”的固定模板。省直辖县级市的数据也遵循同样逻辑。比如河南省的济源市编码是 419001level 为 2parent_code 是 419000? 实际应为 410000河南省。这类数据的规律是从编码中间两位能看出端倪县级市代码的第三四位是 90表示“省直辖县级行政区划”。做数据处理时这可以作为一个快速识别规则但真正的层级关系仍然要交给 parent_code 判断不能只看 level 字段。理解了这套数据特征后面写三级联动接口才不会犯低级错误。3. 导入到你的本地 MySQL命令行、Navicat、Workbench 三种方式与自检 SQL表结构和数据都拿到手之后最关心的就是怎么把这份 SQL 完整跑进自己的库。导入方式取决于你的工作环境服务器上用命令行Windows 上常用 NavicatMac 上有人习惯 MySQL Workbench。三种方式没有本质区别核心都是执行一个包含建表语句和 INSERT 语句的 .sql 文件但字符集和导入中断的处理稍有不同。3.1 命令行导入最稳先建库再 source命令行导入是三种方式里最可控的出错时能看到完整日志。先登进 MySQL 建好目标库再用 source 命令执行 SQL 文件mysql -uroot -p # 登录后执行 CREATE DATABASE IF NOT EXISTS region_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE region_db; SOURCE /data/sql/region.sql;source 是 mysql 客户端的内置命令不是操作系统命令所以必须在 mysql 交互式界面里执行。它的好处是能实时打印每一批 INSERT 的执行结果哪一步报错一目了然。如果文件路径包含空格用引号包住路径Windows 下路径要写成反斜杠格式但建议把文件放到纯英文路径下避免编码转换引入乱码。不想进交互界面的话也可以直接用管道方式执行mysql -uroot -p --default-character-setutf8mb4 region_db /data/sql/region.sql--default-character-setutf8mb4 是最关键的一个参数。它告诉客户端“这个文件里存的是 utf8mb4 编码的文本”让导入过程不做错误的转码。缺了这个参数如果系统默认字符集是 latin1 或 gbk导入后查出来的地区名称基本就是乱码。另外注意 region_db 要提前存在这条命令不会帮你建库如果库不存在会报 Unknown database 错误。3.2 Navicat / MySQL Workbench 导入注意字符集别选错用 Navicat 导入时右键点击目标数据库连接选择“运行 SQL 文件”弹窗里选中 region.sql文件编码选 UTF-8。Navicat 界面上的 UTF-8 对应 MySQL 的 utf8mb4因为这两个在大多数场景下等价。关键点在于运行之前先确认连接属性里的编码设置连接属性中“编码”一项建议改成 utf8mb4否则即使文件本身是好的客户端传输过程中的转码也可能破坏数据。MySQL Workbench 的操作路径是 File - Open SQL Script打开文件后确认右上角选择的数据库是目标库再点闪电按钮执行。Workbench 默认把文件内容当作 UTF-8 处理但如果你下载的这份 SQL 文件头里带了 BOM执行时偶尔会把 BOM 字符拼进第一个语句报出语法错误。解决办法是用编辑器把文件另存为“UTF-8 无 BOM”格式再执行。两种图形化工具都适合小数据量导入但要注意如果同一个文件执行到一半失败第二次重新执行时通常会撞在重复主键上。原因很简单第一次插入的部分数据已经落库。图形界面不会自动清空历史数据所以第二次执行前得手动 truncate 表再重来。3.3 导入完先跑这几条自检 SQL别等业务报错才发现缺数据导入完成不等于万事大吉。我见过太多次“导入时报错但没仔细看上线后才发现某省数据缺失”的情况所以强烈建议导入后立刻跑一遍自检 SQL-- 1. 按层级统计看数据分布是否合理 SELECT level, COUNT(*) AS cnt FROM region GROUP BY level; -- 2. 检查孤儿数据parent_code 指向了不存在的父级 SELECT child.code, child.name FROM region child LEFT JOIN region parent ON child.parent_code parent.code WHERE child.parent_code IS NOT NULL AND parent.code IS NULL; -- 3. 抽查直辖市数据北京市下应该直接是区 SELECT code, name, level, parent_code FROM region WHERE code LIKE 11% ORDER BY code; -- 4. 检查省级编码是否满足六位且以 0000 结尾 SELECT code, name FROM region WHERE level 1 AND code NOT LIKE %0000;第一条用于快速判断这份 SQL 的粒度省市区三级版本通常有三千行上下包含乡镇街道的版本在四万行以上。第二条是数据完整性检查的重头戏只要查出任何一行父级不存在的记录就说明文件本身不完整或者导入过程中有人为删改。第三条抽查直辖市结构能验证你对单表自关联的理解是否正确。第四条校验编码规范性行政区划代码的省级编码后四位必须是 0000如果不满足说明文件里的编码规则和国标有偏差后续做统计关联时容易出错。这几条 SQL 跑完没有问题这张表才算是真正可以交给业务使用了。4. 省市区三级联动、区域统计、按名搜索业务里最常用的几个查询写法表导入成功只是开始真正考验 SQL 功底的是日常业务查询。本章写三个最常见的场景省市区的联动取数、订单表的区域维度统计、以及按名称搜索地区。这三个场景涵盖了后台管理系统里绝大多数的区域数据操作。4.1 省市区三级联动自连接分组取数比三张表省心得多三级联动接口的常见做法是前端先拿省份列表用户选中省后再请求该省下面的市选中市后再请求区。每次请求本质上是查 parent_code 等于某个值的所有行。这里的接口 SQL 可以统一写成-- 查询某个父级节点下的所有直接下级 SELECT code, name, level FROM region WHERE parent_code 440000 ORDER BY sort ASC, code ASC;执行结果会返回广东省下辖的所有地级市。用户继续点广州市时把条件换成 parent_code 440100 即可。这个写法简单但是有一个隐性要求parent_code 字段必须建索引否则每次联动请求都是全表扫描。如果这份 SQL 自带的建表语句里没有这个索引一定要自己补上尤其是数据量到了乡镇街道级别的时候全表扫描的代价会直接反映在接口延迟上。如果需要把某个省的省、市、区三级数据一次查出来给前端做缓存用两条自连接就能完成SELECT p.name AS province_name, c.name AS city_name, d.name AS district_name FROM region p LEFT JOIN region c ON c.parent_code p.code LEFT JOIN region d ON d.parent_code c.code WHERE p.code 440000;注意这里不能用 INNER JOIN否则直辖市下属区县会因为中间缺少“市”这一级而被过滤掉。用 LEFT JOIN 才能保证北京市、东莞市这类特殊层级的数据完整返回。如果这份 SQL 数据包含乡镇街道要展示四级联动就增加一层自连接或者改用递归查询MySQL 8.0 的 WITH RECURSIVE 语法更适合任意深度的树形结构。4.2 区域统计订单表存的 code 还是 name决定你的 SQL 长什么样电商系统里常见需求是统计各省的订单数量。这里有一个关键设计决策订单表里存 region_code 还是直接存地区名称字符串。存名称的查询最简单SELECT province_name, COUNT(*) AS order_cnt FROM orders GROUP BY province_name ORDER BY order_cnt DESC;但问题是如果随后区划调整、名称变更历史订单的省份名称就对不上新口径了。存 region_code 的统计 SQL 稍微复杂却稳定得多SELECT rp.name AS province_name, COUNT(*) AS order_cnt FROM orders o JOIN region r ON o.region_code r.code JOIN region rp ON r.province_code rp.code GROUP BY rp.code, rp.name ORDER BY order_cnt DESC;这里依赖了 region 表中的 province_code 冗余字段。为什么要冗余因为 orders 表里存的是最细粒度的区县代码单靠 r.parent_code 只能找到市级要一路向上回溯到省级需要多次自连接。province_code 这个冗余字段把回溯路径压缩成一次等值关联统计性能显著提升。如果还想要排行榜前几名可以用窗口函数SELECT province_name, order_cnt FROM ( SELECT rp.name AS province_name, COUNT(*) AS order_cnt, RANK() OVER (ORDER BY COUNT(*) DESC) AS rk FROM orders o JOIN region r ON o.region_code r.code JOIN region rp ON r.province_code rp.code GROUP BY rp.code, rp.name ) t WHERE rk 10;窗口函数在 MySQL 8.0 和 5.7 的某些版本里支持情况不同8.0 直接可用5.7 需要改写为临时表加变量实现。如果你的线上库是 5.7建议先用上一版的普通 GROUP BY 查全量再在应用层做截取。大数据量下这个查询的重点是 orders.region_code 要有索引否则关联扫描会拖垮整个统计任务。4.3 省市区搜索LIKE 和中文全文索引的选择地区搜索是后台系统的高频功能。最简单的是前缀匹配SELECT code, name, level FROM region WHERE name LIKE 朝阳%;前缀匹配可以命中 idx_name 索引执行计划里能看到 Using index condition。但如果用户只记得地区名的一部分或者输入的是中间关键字就不得不写成 %朝阳% 这种前后模糊的写法此时索引完全失效全表扫描的命运在所难免。数据量只有几千行时问题不大一旦到了包含乡镇街道的四万行级别每次搜索都全表扫描会让接口变慢。更靠谱的方案是给地区名称加全文索引。MySQL 5.7 以上支持中文全文索引前提是启用 ngram 解析器ALTER TABLE region ADD FULLTEXT INDEX ft_region_name (name) WITH PARSER ngram; SELECT code, name, level FROM region WHERE MATCH(name) AGAINST(朝阳 IN NATURAL LANGUAGE MODE) ORDER BY sort ASC;加了全文索引之后“朝阳”这种两字词也能正常匹配而且查询速度不会随表体积线性下降。注意全文索引的 MATCH 字段必须和定义索引时的字段完全一致否则会报参数错误。这个方案适合数据量较大、搜索频率高的场景如果只是后台管理里偶尔查一下保留 LIKE 前缀匹配就够了不必为了一个小需求增加全文索引的维护成本。另外多提醒一句任何来自用户输入的地区名搜索条件写进 SQL 之前都要做参数化处理。直接把表单里的字符串拼进 LIKE 语句等于给 SQL 注入留了后门这是帮用户做地区筛选时最容易忽略的安全问题。5. 避坑编码、旧版本兼容、层级错位导入省市区数据的五个翻车现场这类数据表 SQL 本身不复杂翻车几乎都集中在导入阶段和后续维护阶段。我把最常见的五类问题整理出来每一条都按“现象 - 原因 - 解决”来梳理都是我自己或身边同事踩过的真实情况。5.1 导入后名称变成乱码现象数据能查出来但省市区名称全部显示为类似 鍖椾含甯? 的乱码或者中文变成问号。原因字符集链路不一致。最常见的是 SQL 文件本身是 utf8 编码但 mysql 客户端的连接字符集是 gbk导入时 MySQL 按 gbk 解释 utf8 字节流落库就成了乱码。另一种情况是 SQL 文件在 Windows 上被编辑器转存成了 ANSI 编码文件内容已经被破坏。解决重新导入并显式指定字符集。命令行方式用 --default-character-setutf8mb4Navicat 运行 SQL 文件时文件编码选 UTF-8连接属性里编码也改成 utf8mb4。如果表里已经进了乱码数据用 UPDATE 很难还原因为原始字节已经不可逆最干净的方式是 TRUNCATE 后重新导入。这里用 DELETE 不够彻底TRUNCATE 会重置表并释放空间适合这种“推倒重来”的场景。5.2 报错 Unknown collation: utf8mb4_0900_ai_ci现象导入时 MySQL 报 1064 语法错误错误信息里出现 Unknown collation utf8mb4_0900_ai_ci。原因这份 SQL 可能是用 MySQL 8.0 导出或生成的而目标库是 MySQL 5.7。8.0 默认的排序规则在 5.7 中不存在MySQL 直接拒绝执行建表语句。很多 8.0 用户对此没概念文件分享出去后老版本库一导入就失败。解决全局替换排序规则为 5.7 支持的 utf8mb4_general_ci。Linux 和 Mac 下可以用 sed 直接处理文件再导入sed -i s/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g region.sqlWindows 用户用编辑器打开文件查找替换所有 utf8mb4_0900_ai_ci 为 utf8mb4_general_ci保存后再导入。如果拿到的文件里建表语句已经执行了一部分才报错先 truncate 对应的表再重新跑替换后的文件。5.3 直辖市、省直辖县级市、直筒子市三级联动分分钟丢数据现象页面上的省市区三级联动在广东省正常但选择北京市后市级下拉为空选择东莞市后区级下拉也为空。原因不是 SQL 错了是业务代码用“省-市-区”三段式模板处理所有数据。北京市下面没有地级市这一层东莞、中山、嘉峪关这些直筒子市下面不设区数据树深度比模板少了一层。如果代码强制要求“选完省必须选市”这些地区的数据就永远到不了区县。省直辖县级市同理济源市的 parent_code 直接指向河南省也被三段式模板忽略。解决联动组件不要写死三层逻辑改成“根据 parent_code 查直接下级”的通用接口。选择北京市后backend 查询 parent_code 110000返回的数据 level 是 3前端把它显示在“区”一级即可。这样数据层是什么结构界面就展示什么结构不强制所有分支都走省市区三级。判断省直辖县级市这种特殊节点可以用代码三到四位是否为 90 来辅助但核心还是跟着 parent_code 走。5.4 重复导入导致主键冲突SQL 文件跑一半再跑一次就全废了现象第一次导入时报错了修改文件后重新执行MySQL 报错 Duplicate entry 110000 for key PRIMARY。原因第一次虽然报错但报错前已经插入了一部分行。第二次执行建表语句时表已存在INSERT 再碰到相同主键就冲突。这是图形工具导入最容易遇到的窘境看起来是“重跑一遍”实际是“重复插入”。解决导入前先清空目标表再做导入。命令行方式mysql -uroot -p region_db -e TRUNCATE TABLE region; mysql -uroot -p --default-character-setutf8mb4 region_db region.sqlTRUNCATE 会清空表数据但保留表结构比 DROP TABLE 再重建多一层安全网也比 DELETE 更快。如果你不确定这份 SQL 文件里有没有 CREATE TABLE 语句导入前也可以先 DROP TABLE IF EXISTS region让文件自己建表但这样会丢掉表上自定义的索引和字段。稳妥做法还是 TRUNCATE 后直接导入保留表结构。5.5 自增 ID 做主键的隐患换一版数据业务关联全部错位现象业务表里存了 region_id 1 表示“北京市”运行一段时间后重新导入了更新版数据业务表里 region_id 1 变成了“石家庄市”。原因这份省市区 SQL 里有些版本会用自增整数 ID 做主键而不是用六位 code 做主键。数据文件的插入顺序在不同版本间可能变化同一行数据在不同文件里拿到的自增 ID 完全不同导致所有引用 ID 的业务表集体错位。解决第一优先是用 code 作为主键这也是前文建表方案里坚持 code 为主键的原因。如果业务表已经用了自增 ID 关联地区马上改成冗余 region_code 字段并把业务统计逻辑迁移过去。迁移时用一条 UPDATE 关联查询回写UPDATE business_table b JOIN region r ON b.region_id r.id SET b.region_code r.code;执行完后把 region_id 字段保留一段时间做兼容等确认没有旧的代码还在引用后再在业务表上移除该字段。这是典型的“后悔药”操作越早动手损失越小拖到数据量大了再迁移代价成倍增加。6. 进阶用自检脚本把省市区表维护成“基础数据底座”顺带做区划变更数据表导入正常、查询正常这事还不算完。行政区划不是一成不变的这几年撤县设区、新区托管、乡镇合并时有发生一套不维护的省市区数据用上两三年就会出现“老客户地址无法识别新区划”的问题。我的习惯是给这张表配两套维护手段定期自检 SQL 和区划变更处理流程。自检 SQL 主要用于发现数据被意外改动的问题。除了前面提到过的孤儿数据检查还可以加两条按 code 长度检查字段位数六位之外的直接报错定期统计总数并与历史基线对比如果总数异常减少说明有数据被误删。这些检查可以用系统 crontab 每月跑一次输出结果到监控告警不一定要做成在线服务。区划调整时千万不能直接 DELETE 旧代码。业务表里也许还挂着旧地址删了之后 JOIN 查不出地区名称历史订单直接变成“未知地区”。标准做法是把旧代码的 status 置为 0并保留记录UPDATE region SET status 0 WHERE code 330522; INSERT INTO region (code, name, level, parent_code, status) VALUES (330503, XX区, 3, 330500, 1);status0 的行不参与下拉联动取值但依然能支撑历史数据的名称展示。如果业务表已经在用旧 code可以再建一张新旧 code 映射表把已变更的 code 指向新 code查询时先做一层映射。这套流程不复杂却能避免每次区划调整都引发线上故障。最后分享一个我自己的使用习惯每次接到新的项目我都会先跑一遍第 3 章的自检 SQL确认底层数据没问题再开始搭建业务。这套省市区表看着不起眼却是订单分析、区域权限、物流调度共同依赖的地基地基歪一寸上面的业务就要歪一丈。希望这份梳理能帮你少踩几个坑把时间花在真正有价值的业务逻辑上。本文还有配套的精品资源点击获取