简介这是一份面向后端开发、全栈工程师及需要处理地址信息的项目开发者的MySQL行政区划数据资源以SQL脚本形式提供中国省、市、区县三级数据可直接导入数据库使用。压缩包内共1个PDF文件约495KB内容涵盖建表语句、字段说明与完整数据插入示例核心表db_yhm_city通过class_id、class_parent_id、class_name、class_type四个字段构建层级关系并配有class_parent_id与class_type索引以提升查询效率。资源中给出了按省份查城市、按城市查区县等典型SQL示例可支撑电商地址自动填充、物流配送路径规划及基于地理位置的数据分析等场景。目前已有1080人学习下载适合需要快速搭建地区数据表、减少手工整理成本的开发者参考使用。1. 一份能直接导入的 MySQL 省市区 SQL它到底省掉了哪些活做过后台的人都碰过这个场景运营要一个省市区三级联动下拉框前端催着要接口你打开数据库发现地址表是空的或者字段设计得乱七八糟——省市区混在一张表里没有层级查一个「广东省下面有哪些市」得写三段 UNION。这时候如果手里有一份结构清晰、能直接source进去的 SQL 文件十分钟就能把接口跑通。这份 MySQL 版中国省市区数据表 SQL 就是干这个的一张db_yhm_city表四个字段用class_parent_id把国家、省、市串成树class_type标记层级导入即用。它适合中小型 Web 项目、后台管理系统、电商收货地址模块这类不需要街道级别、只要省市两级就够的场景。下面我按「表结构怎么读 → 怎么导入 → 怎么写查询 → 哪里会翻车」的顺序拆一遍都是能直接抄的。2. 拆开 db_yhm_city四个字段怎么撑起三级行政区划2.1 字段设计背后的层级逻辑先看建表语句这是整份资源的骨架CREATE TABLE db_yhm_city ( class_id smallint(5) unsigned NOT NULL AUTO_INCREMENT, class_parent_id smallint(5) unsigned NOT NULL DEFAULT 0, class_name varchar(120) NOT NULL DEFAULT , class_type tinyint(1) NOT NULL DEFAULT 2, PRIMARY KEY (class_id), KEY class_parent_id (class_parent_id), KEY class_type (class_type) ) ENGINEMyISAM AUTO_INCREMENT1 DEFAULT CHARSETutf8;四个字段各司其职。class_id是自增主键也是每个行政区划的唯一身份证插入数据时是显式指定值的比如中国是 1北京是 2所以AUTO_INCREMENT1在这里更多是个形式实际 ID 由 INSERT 语句写死。class_parent_id是整张表的灵魂它指向父级记录的class_id中国的class_parent_id是 0顶层北京的class_parent_id是 1指向中国安庆的class_parent_id是 3指向安徽。class_name存名称class_type标记层级——从数据看0 是国家1 是省级2 是市级。这里有个容易忽略的点class_type的默认值是 2意味着如果你插入时漏写这个字段数据会被当成市级。批量导入自己补充的数据时这个默认值可能让省级数据悄悄变成市级查询时怎么都查不出来。我一般会在导入后跑一遍校验确认每个层级的数量对得上。2.2 索引为什么建在 parent_id 和 type 上两个 KEY 不是随便加的。省市区查询的典型模式是「给定父级 ID查它下面所有子级」也就是WHERE class_parent_id ?所以class_parent_id必须有索引否则每次联动查询都全表扫描。class_type的索引服务于「我要所有省份」这类查询WHERE class_type 1能快速过滤出省级记录。但要注意这份表用的是 MyISAM 引擎。MyISAM 不支持事务插入过程中断了不会回滚可能留下半截数据。对于省市区这种读多写少、导入一次基本不改的数据MyISAM 够用查询速度也不差。但如果你的项目已经在用 InnoDB或者需要外键约束建议把ENGINEMyISAM改成ENGINEInnoDB其余不动。改引擎不影响数据本身只是换个存储方式。2.3 数据覆盖范围与层级完整性从 INSERT 数据看省级覆盖了 34 个含香港、澳门、台湾市级数据按省逐个列出。以安徽为例class_parent_id3下面挂了安庆、蚌埠、巢湖、池州等 16 个市class_type都是 2。广东挂了 21 个市数据密度因省而异。需要提前说清楚这份数据是省市两级没有区县级别。摘要里提到「区县级别字段」但实际 INSERT 数据里class_type只出现 0、1、2 三种值没有 3。如果你的业务需要区县级联动这份资源不够用得自己补第三级数据或者换一份带区县的数据源。这一点在选型时就要确认别导进去才发现少一层。3. 导入实操从 source 命令到字符集校验3.1 导入前的环境确认导入之前先确认三件事MySQL 版本、目标库的字符集、SQL 文件编码。这份 SQL 用的是DEFAULT CHARSETutf8如果你的库是utf8mb4导入后中文不会乱码但表本身的字符集是 utf8。utf8 在 MySQL 里最多存 3 字节存不下 emoji 和部分生僻字。省市区名称一般用不到 4 字节字符但如果你的应用同一张库里有用户昵称之类的字段建议统一成 utf8mb4。确认命令# 查看 MySQL 版本 mysql --version # 登录后查看目标库字符集 mysql -u root -p -e SHOW CREATE DATABASE your_db\G如果目标库是 utf8mb4导入后可以手动改表字符集ALTER TABLE db_yhm_city CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;3.2 三种导入方式与适用场景方式一命令行 source适合本地开发mysql -u root -p your_db进入 MySQL 交互界面后source /path/to/db_yhm_city.sql;source命令要求文件路径是绝对路径或相对于 MySQL 启动目录的路径写相对路径经常找不到文件这是新手第一个坑。方式二重定向导入适合脚本自动化mysql -u root -p your_db /path/to/db_yhm_city.sql这种方式不需要进交互界面一条命令搞定适合写进部署脚本。注意是 shell 重定向不是 MySQL 命令。方式三Navicat 等工具导入适合不熟悉命令行的同学。右键目标库 → 运行 SQL 文件 → 选择文件 → 开始。工具会自动处理编码但导入大文件时可能超时省市区数据量不大一般没问题。导入完成后验证-- 确认总数 SELECT COUNT(*) FROM db_yhm_city; -- 按层级统计 SELECT class_type, COUNT(*) FROM db_yhm_city GROUP BY class_type; -- 抽查省级 SELECT * FROM db_yhm_city WHERE class_type 1 LIMIT 5;如果class_type1的数量明显不对比如只有几个大概率是导入中断或者字符集问题导致部分 INSERT 失败。3.3 导入后的字符集与排序规则检查导入后跑一条带中文的查询看返回是否正常SELECT class_name FROM db_yhm_city WHERE class_id 3;正常应该返回「安徽」。如果返回??或乱码说明客户端连接字符集不对。检查SHOW VARIABLES LIKE character_set%;重点看character_set_client、character_set_connection、character_set_results三个值。如果不是 utf8 或 utf8mb4在连接时指定mysql -u root -p --default-character-setutf8mb4 your_db或者在 MySQL 配置文件里改[client]段的default-character-set。字符集问题是导入环节最高频的翻车点没有之一。4. 查询实战省市区联动的 SQL 写法与性能边界4.1 查省份列表与查下级城市最基础的两个查询前端联动全靠它们-- 查所有省级class_type1 SELECT class_id, class_name FROM db_yhm_city WHERE class_type 1 ORDER BY class_id; -- 查某个省下面所有市以安徽为例class_id3 SELECT class_id, class_name FROM db_yhm_city WHERE class_parent_id 3 AND class_type 2 ORDER BY class_id;第一条走class_type索引第二条走class_parent_id索引都是毫秒级返回。注意第二条加了class_type 2条件虽然class_parent_id3下面目前只有市级数据但加上类型过滤更严谨防止未来混入其他层级数据。4.2 一次查出「省-市」两级结构如果不想发两次请求可以用自连接一次查出省和它下面的市SELECT p.class_id AS province_id, p.class_name AS province_name, c.class_id AS city_id, c.class_name AS city_name FROM db_yhm_city p LEFT JOIN db_yhm_city c ON c.class_parent_id p.class_id AND c.class_type 2 WHERE p.class_type 1 ORDER BY p.class_id, c.class_id;这条查询返回的结果集是「省 市」的笛卡尔展开前端拿到后按province_id分组即可。数据量方面34 个省加 300 多个市结果集几百行一次传输完全可接受。但要注意LEFT JOIN的使用——如果某个省下面没有市级数据city_id会是 NULL前端要处理这种情况。4.3 递归查询的替代方案MySQL 5.7 不支持 CTE 递归MySQL 8.0 才支持WITH RECURSIVE。这份数据只有两级用不上递归。但如果以后扩展到区县级三级查询某个省下面所有区县就需要递归或者多次查询。在 5.7 环境下常见做法是应用层做两次查询先查省下的市再用IN查这些市下的区县。-- 第一步查安徽下的所有市 ID SELECT class_id FROM db_yhm_city WHERE class_parent_id 3 AND class_type 2; -- 第二步用上一步的 ID 列表查区县假设有 class_type3 的数据 SELECT * FROM db_yhm_city WHERE class_parent_id IN (36,37,38,...) AND class_type 3;这种写法在市级数量不多时没问题但如果要查全国所有区县IN列表会很长建议改成 JOINSELECT d.* FROM db_yhm_city d INNER JOIN db_yhm_city c ON d.class_parent_id c.class_id WHERE c.class_parent_id 3 AND c.class_type 2 AND d.class_type 3;4.4 缓存策略与查询频率控制省市区数据几乎不变每次请求都查库是浪费。常见做法是在应用启动时把整张表加载到内存用class_parent_id做 key 建一个 Map。Java 里可以用MapInteger, ListCityPHP 里用数组Node 里用对象。加载一次后续联动查询全走内存数据库压力为零。如果不想全量加载至少给省级列表加缓存因为「查所有省份」这个查询每个用户打开地址表单都会触发。缓存时间可以设长一点比如 24 小时数据更新时手动刷新。5. 避坑排查导入和查询中最容易翻车的五个点5.1 导入报「Unknown command 」现象用source导入时终端刷出一堆Unknown command \或者ERROR at line ...。原因SQL 文件编码和 MySQL 客户端字符集不匹配常见于文件是 GBK 编码但客户端按 utf8 解析或者文件里有 BOM 头。解决用file命令确认文件编码如果是 GBK先转成 utf8iconv -f GBK -t UTF-8 db_yhm_city.sql -o db_yhm_city_utf8.sql然后用--default-character-setutf8重新导入。BOM 头可以用sed去掉sed -i 1s/^\xEF\xBB\xBF// db_yhm_city.sql5.2 中文变成问号或乱码现象导入成功但SELECT出来的中文全是???。原因连接字符集不是 utf8。MySQL 客户端、连接层、结果集三层字符集只要有一层不对中文就废了。解决在连接串里显式指定字符集。命令行加--default-character-setutf8mb4JDBC 加?useUnicodetruecharacterEncodingutf8PHP PDO 在 DSN 里加charsetutf8。导入前先SET NAMES utf8mb4;也能临时解决。5.3 class_type 默认值导致层级错乱现象自己补充了几个省的数据插入时没写class_type结果查省级列表查不到这些新增的省。原因class_type默认值是 2不写就变成市级。省级必须是 1国家是 0。解决插入时显式指定class_type。批量插入后跑校验SELECT class_id, class_name FROM db_yhm_city WHERE class_parent_id 1 AND class_type ! 1;这条查询会揪出所有挂在「中国」下面但不是省级的记录。5.4 MyISAM 表导入中断留下脏数据现象导入到一半网络断了或者终端关了重新导入时主键冲突。原因MyISAM 不支持事务已插入的数据不会回滚。重新执行CREATE TABLE前没有DROP TABLE或者DROP TABLE IF EXISTS没生效。解决SQL 文件开头有DROP TABLE IF EXISTS db_yhm_city;重新导入前先手动确认表已删除DROP TABLE IF EXISTS db_yhm_city;然后再source。如果用的是 InnoDB可以把整个导入包在一个事务里出错自动回滚。5.5 查询结果顺序不稳定现象同样的查询两次执行返回的城市顺序不一样。原因没有ORDER BYMySQL 不保证返回顺序。MyISAM 表尤其明显因为数据按插入顺序存储但查询优化器可能选择不同的索引。解决所有对外输出的查询都加ORDER BY class_id。如果业务需要按名称排序加ORDER BY class_name但要注意中文排序在 utf8_general_ci 下是按 Unicode 码点排的不是拼音顺序。需要拼音排序的话得额外加一个拼音字段。6. 进阶技巧把这份数据用出花来的三个习惯第一个习惯是导入后立刻跑一遍完整性校验。我一般会写一个校验脚本检查三件事省级数量是否等于 34、每个省下面是否至少有一个市、有没有孤儿记录class_parent_id指向不存在的class_id。孤儿记录的检查 SQL 是这样SELECT c.* FROM db_yhm_city c LEFT JOIN db_yhm_city p ON c.class_parent_id p.class_id WHERE c.class_parent_id ! 0 AND p.class_id IS NULL;正常应该返回空结果。如果有记录说明数据有断裂联动查询时这些城市永远显示不出来。第二个习惯是给表加一个sort_order字段。原始数据按class_id排序但class_id是插入顺序不是业务想要的顺序。比如直辖市应该排在省份前面或者按拼音排序。加字段的语句ALTER TABLE db_yhm_city ADD COLUMN sort_order INT NOT NULL DEFAULT 0 AFTER class_name;然后按需更新sort_order查询时ORDER BY sort_order, class_id。这个字段不影响原有逻辑但让前端展示更可控。第三个习惯是导出时带上CREATE TABLE和INSERT的完整语句方便迁移。用mysqldumpmysqldump -u root -p --no-create-db --skip-extended-insert your_db db_yhm_city db_yhm_city_backup.sql--skip-extended-insert让每条记录一行 INSERT方便 diff 和手动修改。不加这个参数的话所有记录会合并成一条长 INSERT改一个值要翻半天。还有一个实际项目里常用的技巧把省市区数据做成 JSON 文件前端直接加载完全不经过数据库。适合纯静态页面或者对接口延迟敏感的场景。导出 JSON 可以用 Python 脚本import pymysql import json conn pymysql.connect(hostlocalhost, userroot, password, dbyour_db, charsetutf8) cursor conn.cursor(pymysql.cursors.DictCursor) cursor.execute(SELECT class_id, class_parent_id, class_name, class_type FROM db_yhm_city ORDER BY class_id) rows cursor.fetchall() # 构建嵌套结构 provinces [r for r in rows if r[class_type] 1] result [] for p in provinces: cities [r for r in rows if r[class_parent_id] p[class_id] and r[class_type] 2] result.append({ id: p[class_id], name: p[class_name], cities: [{id: c[class_id], name: c[class_name]} for c in cities] }) with open(cities.json, w, encodingutf-8) as f: json.dump(result, f, ensure_asciiFalse, indent2)这段脚本把扁平表转成嵌套 JSON前端拿到后直接渲染三级联动省掉接口调用。注意ensure_asciiFalse保证中文正常输出indent2让文件可读。生成的 JSON 大概几百 KBgzip 后几十 KB加载速度没问题。从那以后我每次拿到一份新的地址数据 SQL都强制走一遍「导入 → 校验 → 导出 JSON → 前端联调」的完整链路确认每一环都通再往项目里合。省市区数据看着简单但字符集、层级、排序这三个地方各踩一次坑半天就没了。希望帮到你。本文还有配套的精品资源点击获取