简介面向需要接入中国行政区划数据的后端开发者这份 PDF 文档提供 MySQL 版中国省市区数据表的完整建表与初始化 SQL。资源以 db_yhm_city 为主表采用父子层级结构通过 class_id 自增主键、class_parent_id 上级 ID、class_name 名称、class_type 类型0 国家、1 省份、2 城市、3 区县四个字段表达省市区三级关系文档同时给出按省份查城市、按城市查区县等典型查询示例例如根据 class_parent_id 与 class_type 组合筛选整体结构一目了然可直接迁移到电商地址库、物流配送、后台管理地区选择等场景。资源为单个 PDF 文件大小约 495KB内容涵盖建表语句、全国省份及各地市/区县数据插入语句和字段说明对照复制即可快速在 MySQL 中初始化使用也可作为学习层级表设计的参考样例。该资源已有 1077 人学习下载适合需要快速获取标准行政区划数据、减少重复建模工作的 MySQL 开发者及相关课程学习者。1. 一张能直接落库的全国省市区数据表SQL三级地址字典值不值得自己养做后台管理系统时几乎每个项目都要一套供“省、市、区”三级下拉框使用的地址字典。Mysql 版中国省市区数据表SQL 指的就是一份能直接执行的 SQL 脚本通常包含建表语句和几百上千行 INSERT把全国行政区划一次性灌进 MySQL省去调第三方地理接口的成本。这套数据看着普通坑却不少网上流传的版本编码新旧不一、层级口径混乱直接导入后大概率出现“市辖区失踪”“数据对不上”这类问题。这篇笔记把数据编码规则、建表字段、导入方式和排错清单一次讲透适合正在做后台、电商地址库或报表维表层准备自己维护一份区划数据的后端和数据分析师。2. 行政区划数据从哪来GB/T 2260 编码规则与省市区三级结构2.1 六位行政区划代码省、市、区分别藏在哪几位标准行政区划代码采用 6 位数字最直观的读法是“从左往右分组”前 2 位是省级代码中间 2 位是市级代码最后 2 位是区县级代码。用浙江省举例330000表示浙江省330100表示杭州市330102表示杭州市西湖区。抓规则的时候容易绕晕我一般用两个判断条件去区分级别看第 3、4 位是不是00再看第 5、6 位是不是00。若后四位全是0比如330000是省级若第 3、4 位不为00但后两位是00比如330100是市级若后两位也不为00比如330102是区县级。这个规则对大多数地区成立但有两个特例必须提前知道。一是直辖市代码形态里会出现一个“市辖区”的中间层比如110100这一类它本身不是真实的地级行政单位下面才挂各区二是省直辖县级市部分县级单位直接挂在省级下面没有地级市这一层如果用level 2去查“下一个市级列表”这些数据会被漏掉。这也是我后来坚持用parent_code而不是level做联动查询的原因后面避坑章节会展开。拿到一份 SQL 脚本时还要先确认代码位数。网上流传的源文件里“行政区划代码”和“统计用区划代码”经常被混用后者会多出 3 位城乡分类码变成 9 位。如果你的表结构里code字段建成了varchar(6)导 9 位数据直接报 Data too long。所以打开脚本后先不要急着执行查一下 INSERT 里 code 值的长度再决定改表结构还是过滤数据。2.2 单表 region 还是三张表 province / city / district数据组织方式上常见做法有两种一种是拆成province、city、district三张表每张表只管一级另一种是做单表region用level和parent_code字段维护层级关系。两种我都用过生产环境我更推荐单表。方案结构优点缺点三表分开每表各带 code、name查询直观新人也能直接 join行政区划调整时三处维护容易漏直辖市和省直辖县很难填层级单表 regioncode、name、level、parent_code一个维度表管理全部联动接口一条 SQL 搞定写查询必须带 level 或 parent_code不熟的人会查错三表方案最容易翻车的点是行政区划有变动时只更新了 district 表却忘了同步 city 表结果“上有市、下无区”的数据越来越多。单表方案因为所有数据在同一张表里靠父级代码把层级串起来调整时只需要改相关行的parent_code和level一致性更容易保证。后端做三级联动时单表方案也更顺手。接口永远只需要“根据 parent_code 查下一级”一条 SQL 动态拼不用为省、市、区分别写三个查询。报表按区域维度分组时用 level 过滤或者通过视图展开成宽表即可。所以如果你拿到的脚本是分三张表的我会建议后面花十分钟合并成单表结构省掉后续无数麻烦。2.3 常见SQL脚本里都有哪些字段先看前30行再动手导不同来源的 region.sql 字段设计差异很大。最精简的版本只有 code、name、level 三列完整一点的会带 parent_code、pinyin、sort更讲究的版本会加 is_active 或者 valid_flag用来标记历史停用区划。我拿到新脚本后的第一个动作不是导入而是先看前 30 行确认四件事表名是什么、主键是什么、字符集是什么、有没有 DROP TABLE 和 CREATE DATABASE。很多 SQL 文件第一行是SET NAMES utf8mb4;或者CREATE DATABASE IF NOT EXISTS这种文件直接 source 有一半概率把数据导进错误的库。我一般会把脚本里的库名先全局替换成自己的库名或者把 CREATE DATABASE / USE 语句删掉再用命令行指定目标库导入。此外要注意脚本里如果带了DROP TABLE IF EXISTS region;就说明作者默认允许覆盖导入如果没有这句你自己要决定是追加还是清空避免重复数据。3. 用CREATE TABLE和INSERT把省市区数据落成MySQL表字段设计与脚本改造3.1 主键为什么用行政区划代码而不是自增ID很多业务表挂省市区时习惯存一个自增region_id我强烈不建议这么干。自增 id 没有业务含义换一版数据、重新导入一次id 顺序就可能全变了业务表里已经存好的旧 id 全部错位到时候只能写脚本硬刷。六位区划代码本身是国家标准里的稳定编码适合直接做自然主键。就算未来区划调整旧 code 被废弃也只会导致个别行失效不会像自增 id 那样整体错位。用 code 做主键还有一个额外好处业务表只需要存一个 6 位字符串就能同时推算出省、市、区的 code 前缀报表统计时用LEFT(region_code, 2)就能按省汇总不需要每次都 join 地区表。下面是推荐的单表结构CREATE TABLE region ( code VARCHAR(6) NOT NULL COMMENT 6位行政区划代码, name VARCHAR(100) NOT NULL COMMENT 省/市/区名称, level TINYINT NOT NULL DEFAULT 3 COMMENT 层级1省 2市 3区县, parent_code VARCHAR(6) DEFAULT NULL COMMENT 上级行政区划代码省级为NULL, pinyin VARCHAR(200) DEFAULT NULL COMMENT 拼音或首字母用于排序, is_active TINYINT NOT NULL DEFAULT 1 COMMENT 1有效 0历史停用, PRIMARY KEY (code), KEY idx_parent_level (parent_code, level) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT全国省市区行政区划维度表;字段类型上有两个细节。第一code 用varchar(6)而不是char(6)或int因为代码可能带前导零int 会丢掉虽然标准 6 位数字一般不会出现前导零问题但统一用 varchar 更稳。第二parent_code和level要建联合索引因为前端联动接口的查询条件是WHERE parent_code ?走这个索引才能避免每次全表扫描。InnoDB 是默认选择支持事务和行级锁字典表虽然不需要事务但恢复和备份时更友好。3.2 最小INSERT示例看懂数据行的三种形态完整脚本的 INSERT 内容很多但单条数据的形态就是下面三行INSERT INTO region (code, name, level, parent_code, pinyin) VALUES (330000, 浙江省, 1, NULL, zhejiangsheng), (330100, 杭州市, 2, 330000, hangzhoushi), (330102, 西湖区, 3, 330100, xihuchengqu);注意看三种级别的 parent_code省级是 NULL市级指向省级 code区县级指向市级 code。这就是后面所有联动的数据基础。网上整理的完整脚本一般会把几百行数据拼在一条 INSERT 语句里形如INSERT INTO region ... VALUES (...), (...), ...;这种扩展插入比逐条 INSERT 快得多。但也带来一个隐患网络传输时文件容易被截断行末少一个逗号或括号导入时直接报语法错误。所以拿到完整脚本后不要直接信任先跑一遍数量验证。下面这句可以在导入后立即检查各级别数量是否正常SELECT level, COUNT(*) AS total FROM region GROUP BY level ORDER BY level;正常情况返回三行level1、2、3 的行数。具体数量和你拿到的数据版本口径有关重点是看有没有哪一级是 0或者其中一级数量少得离谱。如果发现某个 level 为 0说明脚本里的 level 字段定义和你的预期不一致先排查字段语义再去套业务逻辑。3.3 三张表转单表用 UNION ALL 合并并推导 level如果你的源数据是province、city、district三张表可以拆成一个单表。前提是原表里每个地区都有一个独立的 code 字段。合并脚本如下-- 先将三张旧表的数据合并进 region INSERT INTO region (code, name, level, parent_code) SELECT code, name, 1, NULL FROM province UNION ALL SELECT code, name, 2, CONCAT(LEFT(code, 2), 0000) FROM city UNION ALL SELECT code, name, 3, CONCAT(LEFT(code, 4), 00) FROM district;这段脚本用LEFT(code, 2)推导省级 parent_code用LEFT(code, 4)推导市级 parent_code对大多数数据是成立的。但要特别注意省直辖县级市会在这里翻车。比如某个县级市的 code 是XXXXYY它是直接挂在省下面的并没有对应的“市级” code你用CONCAT(LEFT(code, 4), 00)推出来的父级可能指向一条不存在的记录产生孤儿数据。稳定做法是先看一下 city 表末尾有没有“无对应地级市”的数据。如果存在合并后要单独修正这几行的parent_code和level。我的习惯是在合并前先把 city 表的 code 和 name 导出来人工确认一遍数量不大十分钟内能看完但能省掉后续排查的时间。3.4 用视图把单表展开成省、市、区三列方便BI和报表取数单表结构用起来舒服但 BI 同学做报表时往往不习惯层级关联更想要一张宽表一行数据里同时有省名、市名、区名。这个需求不用改表建一个视图就行CREATE OR REPLACE VIEW v_region_flat AS SELECT p.code AS province_code, p.name AS province_name, c.code AS city_code, c.name AS city_name, d.code AS district_code, d.name AS district_name FROM region p LEFT JOIN region c ON c.parent_code p.code AND c.level 2 LEFT JOIN region d ON d.parent_code c.code AND d.level 3 WHERE p.level 1;需要注意这个视图会把直辖市的“市辖区”虚拟层当成市显示出来结果就是“北京市 → 市辖区 → 朝阳区”这种三段。如果报表里不需要“市辖区”这一层可以在 city join 条件里加AND c.name 市辖区但更通用的做法是在表里给这类虚拟行加标记列比如is_virtual然后视图里过滤掉。视图适合给 BI 和离线报表用不适合直接给在线接口做省市区联动因为一次查询会返回全国所有数据在线接口最好还是按 parent_code 逐级查。4. 从SQL文件到MySQL数据库source导入、Navicat导入与验证4.1 mysql命令行source导入字符集参数是首要导入省市区 SQL 文件我最推荐的方式是 MySQL 命令行客户端的SOURCE命令出错时定位比图形工具更清楚。步骤如下# 1. 进入命令行客户端并选择目标库 mysql -uroot -p --default-character-setutf8mb4 # 2. 在客户端里执行 SET NAMES utf8mb4; USE mydb; SOURCE /data/region.sql;--default-character-setutf8mb4指定的是客户端连接字符集必须和 SQL 文件的编码一致。绝大多数下载到的 region.sql 是 UTF-8 编码所以用 utf8mb4。这里千万不能用通配的utf8MySQL 的utf8实际只支持部分字符虽然省市区中文用 utf8 也够但表里以后如果要存生僻地名直接 utf8mb4 一步到位。SOURCE是 mysql 客户端的命令不是标准 SQL。它会逐条执行文件里的语句并把出错信息回显到终端这样哪一行语法有问题一眼就能看到。如果不想进交互式界面也可以用重定向mysql -uroot -p mydb --default-character-setutf8mb4 /data/region.sql两种方式效果接近我习惯用SOURCE因为可以先登录、再USE确认库名避免数据导错地方。另外提醒一下在 Linux 上执行前可以用file命令确认编码file /data/region.sql输出如果是ISO-8859 text或Non-ISO extended-ASCII说明文件不是 UTF-8直接导入大概率中文乱码。4.2 Navicat for MySQL 和 MySQL Workbench 导入差异图形工具里Navicat for MySQL 的导入入口在“连接 → 右键目标数据库 → 运行 SQL 文件”选择文件后点开始。Navicat 在导入时会把整个文件当作一次查询执行中间出错的定位能力比命令行弱但省市区数据文件一般不到 1MB实际体验差别不大。要注意的是脚本里如果有CREATE DATABASE或USE语句Navicat 的“运行 SQL 文件”也会执行结果是数据落到脚本指定的库里而不是你右键的那个库。稳妥做法是先检查文件头部删掉这类语句再导入。MySQL Workbench 的流程略有不同菜单栏 File → Open SQL Script打开后点闪电图标执行或者用菜单里的 Data Import / Restore。不过 Data Import / Restore 主要用于 MySQL 备份文件的恢复对普通 SQL 文件反而不直观。Workbench 连接数据库时也要确认连接参数里的字符集默认经常是utf8如果文件里含特殊字符应当在连接配置中选择utf8mb4。我的实际建议是省市区字典表数据量不大但一次导入失败后的“半导入”状态很恶心。图形工具的优势是可视化劣势是错误中断后不容易看清执行到哪一步。生产环境导入这种字典表直接用 4.1 的命令行方式图形工具留给开发和测试环境用。4.3 三条验证SQL先确认数量、重复、孤儿导入完成不等于数据能用我会固定执行三条验证 SQL缺一不可-- 1. 看各级别数量 SELECT level, COUNT(*) AS total FROM region GROUP BY level ORDER BY level; -- 2. 看是否有重复code SELECT code, COUNT(*) AS cnt FROM region GROUP BY code HAVING cnt 1; -- 3. 看是否有parent_code指向不存在的行孤儿数据 SELECT r.code, r.name, r.parent_code FROM region r LEFT JOIN region p ON r.parent_code p.code WHERE r.level 1 AND p.code IS NULL LIMIT 20;第一条前面提过确认三个 level 都有值。第二条必须返回空结果如果出现重复 code说明脚本里同一个区划被插入了两次后面业务表 join 时会一拉多行统计直接翻倍。第三条允许有极少量孤儿行吗我的标准是零。出现孤儿行说明某些行的父级缺失三级联动时这些地区会显示出来但点不进去。最常见的来源是省直辖县级市推导 parent_code 出错可以回查 3.3 的合并逻辑。再加一条乱码检查这条在导入后立刻做最有效SELECT code, name, HEX(LEFT(name, 1)) AS hex_first_char FROM region WHERE name IS NOT NULL LIMIT 5;UTF-8 编码下中文字符的首字节十六进制通常以E开头形如E6、E5。如果看到返回值是3F说明这个字符已经被转成了问号表里中文全废了别想着原地修复直接清表重导。4.4 重复导入会叠加导入前先DROP很多人拿到 SQL 文件直接 source 两次第二次要么主键冲突报错要么数据翻倍。省市区数据脚本里不一定有 DROP TABLE所以在确认这是独立字典表、没有业务外键依赖后导入前手动执行清理DROP TABLE IF EXISTS region;如果业务上已经有外键引用这张表不能用 DROP用 TRUNCATETRUNCATE TABLE region;注意 TRUNCATE 会重置自增 ID但 region 表主键是 code自增 ID 不涉及。如果业务表通过 code 关联TRUNCATE 后重灌同一份数据不会影响关联关系这就是用自然主键的另一个好处。导完记得跑一遍验证。5. 避坑导入省市区SQL文件时最常翻车的五个问题现象、原因与解决5.1 导入后中文全是问号或乱码现象SELECT 出来的 name 是???或者浙江çœ这种乱码。原因SQL 文件是 UTF-8但客户端连接、数据库表两者中有一个用了 latin1或者文件本身被 Windows 记事本另存成了 ANSI 编码。解决先用file /data/region.sql或 VSCode 右下角确认文件编码是 UTF-8表结构用DEFAULT CHARSETutf8mb4连接时指定--default-character-setutf8mb4。如果已经导入了乱码数据不要试图用 UPDATE 修先把表 DROP 掉重新导。乱码的本质是字节已经被错误转换UPDATE 也救不回来。5.2 SQL文件第一行报语法错误其实文件带了BOM头现象使用 SOURCE 导入时第一条就报ERROR 1064 (42000): You have an error in your SQL syntax但打开文件看第一行很正常。原因文件被保存成 UTF-8 with BOM 格式文件最前面有EF BB BF三个不可见字节CREATE TABLE 语句前面多了一个“看不见的字符”导致 MySQL 解析失败。解决用 VSCode 或 Notepad 打开文件另存为 UTF-8 无 BOM 格式再导入。Linux 下可以直接检查文件头部head -c 3 /data/region.sql | xxd输出是ef bb bf就是带 BOM。删掉 BOM 的最快做法是sed -i 1s/^\xEF\xBB\xBF// /data/region.sql5.3 省直辖县级单位在三级联动里失踪现象前端省、市、区联动某几个地方在“市”一级列表里找不到只能在“区县”里看到。原因部分县级单位不经过地级市直接挂在省级之下用WHERE level 2查询当然查不到它们。解决所有联动查询都用 parent_code不要用 level 判断层级。查省级下一级时-- 查省级下一级不管是地级市还是省直辖县 SELECT code, name FROM region WHERE parent_code 330000 ORDER BY code;如果业务上需要知道“这个节点下面还有没有子节点”用 EXISTS 判断而不是 levelSELECT r.code, r.name, EXISTS (SELECT 1 FROM region c WHERE c.parent_code r.code) AS has_children FROM region r WHERE r.parent_code 330000;这样省直辖县即使没有市级父级也能正确返回 has_children 状态。5.4 换新版本后业务表里的旧code在region中查不到现象业务表 join 地区表后部分行的地区名为空报表按省统计时缺了一块。原因行政区划发生过调整例如撤县设区老 code 被废弃新表里没有这条数据。解决先跑差异扫描把业务表里的失效 code 全部捞出来SELECT DISTINCT b.region_code FROM business_table b LEFT JOIN region_new n ON b.region_code n.code WHERE n.code IS NULL;如果拿到了一张旧码到新码的映射表 region_map(old_code, new_code)可以直接用 UPDATE JOIN 批量刷新UPDATE business_table b JOIN region_map m ON b.region_code m.old_code SET b.region_code m.new_code;UPDATE 之前务必先备份业务表或者导出受影响行清单。没有可靠映射时不要手动猜区划合并的历史特别容易踩错。5.5 导入慢、在线查询慢的排查现象几千行数据导入跑了十几分钟或者下拉列表接口经常要等一两秒。原因脚本本身是逐条 INSERT 且没有合并或者 region 表没有建索引或者查询时没有走 parent_code 索引。解决导入优先用 SOURCE完整给的脚本如果是逐条 INSERT可以手动把多条合并成扩展插入再执行。排查慢查询用 EXPLAINEXPLAIN SELECT code, name FROM region WHERE parent_code 330100\G看到typeref、keyidx_parent_level说明索引生效看到typeALL就是全表扫描需要补索引。几千行数据全表扫描本身不慢但一旦和业务表 JOINMySQL 可能把它当驱动表反复扫描慢 SQL 就是这么来的。养成用 EXPLAIN 看一眼的习惯比靠感觉调优靠谱得多。6. 进阶用窗口函数去重、按拼音排序、做省市区三级联动SQL6.1 用ROW_NUMBER清理历史版本里的重复code拿到混合版本脚本时同一 code 可能出现多行其中一行是历史数据、一行是当前数据。MySQL 8.0 起可以用窗口函数按 code 分组、只保留有效行SELECT code, name, level FROM ( SELECT region.*, ROW_NUMBER() OVER (PARTITION BY code ORDER BY is_active DESC, code) AS rn FROM region ) t WHERE t.rn 1;这里is_active DESC保证有效行排在最前如果表里没有 is_active 字段就换成你信得过的排序字段。查询结果确认无误后可以把它塞进一张临时表再替换原表。注意这个操作只适合确认过重复来源的场景乱跑可能把多条真实历史数据误删。6.2 三级联动查询跳过“市辖区”虚拟层做省市区联动接口时单表 parent_code 是最省事的玩法-- 根据当前code查下一级 SELECT code, name FROM region WHERE parent_code :currentCode ORDER BY level, code;这个查询天然兼容省直辖县因为它的条件就是“父级等于当前节点”不依赖 level。唯一的特殊处理是直辖市北京市下面会先出现“市辖区”这一层前端多一跳体验很怪。我的做法是在建表时加一列is_virtual把这些虚拟层标记为 1查询时过滤掉SELECT code, name FROM region WHERE parent_code 110000 AND is_virtual 0 ORDER BY code;比在 SQL 里写死name 市辖区更通用因为不同数据源对虚拟层的叫法不完全一样。你可以用一条 UPDATE 把这类行标记出来UPDATE region SET is_virtual 1 WHERE name 市辖区 AND level 2;6.3 业务表字段挂接建议业务场景推荐字段理由电商/表单地址region_code varchar(6)存 6 位码省市区逐级可回溯报表维度province_code / city_code / district_code 三个字段避免每行都去 join 树形表老系统兼容保留旧表 新建映射表历史数据对不上时还能追溯我个人踩过最大的坑项目上线半年后换了新版区划表业务表里区划码全部错位报表对不上的血泪教训。从那以后我给自己定了个规矩任何生产环境要启用这套数据先跑两次验证一是 GROUP BY level 看分层数量二是查重复 code 和孤儿数据全过了才允许上线。建议把这几条验证 SQL 存成一个verify_region.sql文件以后每次换版本都跑一遍花不了两分钟但能省掉后面几天的排查时间。希望帮到你。本文还有配套的精品资源点击获取