拓冰网站定制 
简介面向需要集成中国行政区划信息的开发者这份 MySQL 版中国省市区数据表 SQL 文档提供了完整的建表语句以及省、市、区县三级行政区划数据。表结构包含 class_id、class_parent_id、class_name、class_type 四个核心字段class_type 可区分国家、省、市等层级通过父级 ID 清晰构建上下级关系。开发者可直接导入 MySQL 使用并用简单 SQL 查询快速获取某省份下所有城市或某城市下所有区县适用于电商地址自动填充、物流配送路线优化、基于地理位置的数据分析等场景。文档还提供了创建表和插入数据的示例语句并对字段含义做了说明便于初学者理解行政区划的层级模型。资源包共 1 个 PDF 文件整体大小约 495KB内容涵盖建表 SQL 与全国省份、城市、区县的基础插入数据基本做到导入后即可使用。该资源已有 1077 人浏览学习适合初中级后端开发者和数据库学习者快速落地省市区数据模块。1. 一份能直接跑的 MySQL 省市区表从建表语句到业务落地做电商、做物流、做门店系统的同行多半被「省市区三级联动」这个看似简单的东西恶心过。网上搜 mysql sql 省市区数据表要么是收费资源要么数据老得连城市都没更新要么字段设计得根本没法写查询。这份 MySQL 版中国省市区数据表 SQL 属于拿来就能用的资源一条建表语句加几百条 INSERT把 34 个省级行政区和 300 多个地级市一次性铺好字段只有 class_id、class_parent_id、class_name、class_type 四个层级完全靠 parent_id 串起来。适合中小型项目快速铺地基也适合拿来当数据字典或测试数据填充。下面直接拆这份 SQL讲清楚怎么导入、怎么查、业务里怎么组树再重点说几个数据本身的历史遗留坑最后给你一套自己能持续维护的打补丁方法。2. 表结构拆解class_parent_id 自关联设计为什么比行政区划代码更耐造2.1 四个字段的职责class_type 这一列决定了整棵树的深度建表语句是这套资源的骨架复制到任何 MySQL 5.5 以上的环境都能直接执行。class_id 是自增主键class_parent_id 指向上级记录的 class_idclass_name 存中文名称class_type 区分层级。站在现在的时间点回头看这四个字段的设计其实是典型的「邻接表Adjacency List」模型每行只记一个父节点层级关系靠递归或多次联结展开。它不像行政区划代码GB/T 2260那样把省市县编码塞进固定长度字段好处是区划变更时不需要改编码规则只要调整行记录坏处是查询任意层级的完整路径要额外花心思。很多新项目组会直接把六位数字行政编码塞进表里查询时用 LIKE 前缀匹配去逐级筛省市县遇到编码被重新分配的历史问题就得改大段数据。class_parent_id 这种设计没有这个包袱任何一级调整都只影响相邻两行。class_type 的取值在数据里只出现了 0、1、2 三种0 是根节点「中国」1 是省级行政区2 是市级行政区。摘要描述里提到 class_type3 代表区县但翻完整份 INSERT 语句实际上并没有 class_type3 的记录也就是说这份资源只铺到了地级市这一层。这点必须先说清楚避免你导入后以为数据不完整。-- 按 class_type 分组统计各级数据量验证这张表到底有几层 SELECT class_type, COUNT(*) AS cnt FROM db_yhm_city GROUP BY class_type;这段查询建议在导入后第一时间执行。class_type0 应该只有 1 条也就是「中国」这个根节点class_type1 应该是 34 条省级行政区含港澳台地区class_type2 是全部地级市与省直辖县级行政单位大概在 340 条上下。如果你查出来 class_type3 是 0 条说明这份资源的粒度就是省市两级不是数据导坏了而是它本来就没铺区县。再抠两个容易看走眼的字段细节。class_parent_id 列定义了 DEFAULT 0意思是没指定父级时默认挂到根上这个默认值有两个作用一是防止程序插入时漏传字段导致 NULLNULL 会在层级查询里把整条链断掉二是让「中国」这样的根节点能和其他节点共用同一个查询逻辑。class_id 的类型是 smallint(5) unsigned这个 unsigned 是关键它把上限从 32767 提到 65535全国区划加上自己扩展的区县数据也远够用。括号里的 5 只是显示宽度不是可存位数上限可视化工具里显示成 5 位不要误以为只能存 5 位数。2.2 MyISAM 引擎与字符集老资源的时代印记要不要迁移建表语句末尾写了 ENGINEMyISAM DEFAULT CHARSETutf8这两项都是当年配套环境的产物。MyISAM 不支持事务、不支持外键、崩溃恢复能力弱但胜在查询快、占用简单作为只读字典表完全够用。现在 MySQL 5.7 以上已经把 MyISAM 标记为弃用8.0 里更是默认 InnoDB如果这个库里有其他业务表在跑事务建议把引擎迁过去避免备份恢复时出现引擎兼容方面的幺蛾子。-- 将只读字典表迁移成 InnoDB不影响任何查询和联表 ALTER TABLE db_yhm_city ENGINE InnoDB; ALTER TABLE db_yhm_city CONVERT TO CHARACTER SET utf8mb4;为什么要转 utf8mb4MySQL 里的 utf8 实际是 utf8mb3只能存基本多语言平面内的字符部分生僻字和特殊字符存不进去。行政区划名称里虽然没有 emoji但少数民族地区有不少生僻字地名而且如果业务库统一用 utf8mb4这张表不转会在联表查询时出现字符集不一致轻则不索引、重则乱码。CONVERT 只改表定义和存储编码不改业务逻辑最后查一遍 SELECT COUNT(*) 确认行数不变就行。顺带说一句密钥索引的事KEY class_parent_id 和 KEY class_type 这两个二级索引在邻接表模型里是查询命脉。按父级查全部子级、按类型筛省市区全靠这两个索引撑着。class_type 是 tinyint(1)取值范围 0-255当业务枚举用刚好。表名 db_yhm_city 里的 db_yhm 前缀看起来是从某套商城系统带出来的历史痕迹不影响使用。如果业务规范要求统一命名导入后执行 ALTER TABLE db_yhm_city RENAME TO region_city 即可改名不动数据但如果有视图直接引用旧表名记得一起改。3. 导入与验证让这份 SQL 在 MySQL 5.7 / 8.0 上跑起来3.1 命令行导入source 一条解决拿到这份 SQL 文件的常规路径是命令行。MySQL 的 source 命令会把文件里的建表语句和 INSERT 一次性执行完中间不会因为客户端超时卡住。这份资源只有一条建表语句加几百条 INSERT文件体量很小source 执行基本是秒级。先建一个独立库避免把表混进业务库后面维护和替换都方便。CREATE DATABASE IF NOT EXISTS region DEFAULT CHARACTER SET utf8mb4; USE region; SOURCE /data/sql/db_yhm_city.sql;把路径换成你本地实际路径。SOURCE 是 MySQL 客户端命令不是 SQL 语句只能在 mysql 命令行工具里用不能在 Navicat 的查询窗口里敲。导入完成后执行 SELECT COUNT(*) FROM db_yhm_city确认总行数在 380 上下说明文件完整执行了。命令行导入最常翻车的点是编码。文件本身是 utf8但 Windows 下用记事本另存过会变成带 BOM 的 utf8BOM 会被当成不可见字符塞进第一条记录导致「中国」变成乱码。检查办法是看文件头三个字节是否为 EF BB BF# Linux / mac 下看文件头三个字节是否为 BOM 标记 head -c 3 db_yhm_city.sql | xxd如果输出 EF BB BF说明带 BOM用 sed 去掉再导入# 去掉首行 BOM 后重新导入 sed -i 1s/^\xEF\xBB\xBF// db_yhm_city.sqlmacOS 的 sed 和 Linux 略有差异macOS 上要写成 sed -i 1s/^\xEF\xBB\xBF//。这是 Windows 用户跨平台倒数据最常见的坑我自己接过不止一次同事发来的「乱码版建表语句」最后都是 BOM 惹的祸。3.2 图形化导入Navicat 与 MySQL Workbench 的操作差异不习惯命令行的用 Navicat for MySQL 导入也简单。新建查询把整个 SQL 文件内容粘贴进去执行更推荐的是右键目标库选「运行 SQL 文件」Navicat 会按文件逐段执行并在下方面板显示错误。MySQL Workbench 里路径是菜单 File - Open SQL Script打开后点闪电图标执行。两个工具的差异值得记一下。Navicat 的「运行 SQL 文件」默认按 utf8 读取遇到文件里带 SET NAMES 或字符集不一致会报错执行前先确认目标库的字符集为 utf8mb4。Workbench 对 SQL 文件的兼容性稍好但执行 INSERT 较多的大文件时会一条条跑速度明显慢这份数据体量无所谓以后换大文件时就要注意。团队协作中如果你把 SQL 交给别人导入建议附一句「用 source 或直接执行别用图形化工具的导入外部数据向导」因为导入向导会把它当 CSV 解析把整份文件当成一列导入完表结构全乱。3.3 数据完整性校验三分钟确认数据没导坏数据导进去之后不要立刻在页面上挂联动先跑三条校验 SQL总数、省级数、孤儿数据。总数和省级数的 SQL 在第 2 章已经给过这里重点说孤儿数据也就是「引用了一个不存在的父级」的脏记录。邻接表模型最怕这种数据它会让递归查询静默中断前端下拉框里出现一个点了没反应的空白分组。-- 孤儿数据检查所有 class_parent_id 都能在表里找到对应 class_id -- 正常情况下这类记录应为 0 行 SELECT a.class_id, a.class_name, a.class_parent_id FROM db_yhm_city a LEFT JOIN db_yhm_city b ON a.class_parent_id b.class_id WHERE b.class_id IS NULL AND a.class_parent_id 0;这段 SQL 的逻辑是以 a 为主体左连接 b 找父级如果父级不存在b.class_id 就是 NULL。过滤条件里排除 class_parent_id0 的根节点「中国」因为它的父级是虚拟的 0不在表里。跑出来 0 行说明整棵树的引用关系是完整的可以放心用递归查询。团队里多人协作时SQL 文件往往拆成多个比如 region_province.sql、region_city.sql。不少人常问 mysql 创建多个数据表的格式该怎么处理多个文件其实在命令行里循环导入即可# 批量导入同一目录下多个 region 开头的 SQL 文件 for f in /data/sql/region_*.sql; do mysql -uroot -p --databaseregion --default-character-setutf8 $f done 重定向等价于 source但可以放进脚本循环适合一次导入多个表文件。注意密码提示会打断循环无人值守环境建议用 mysql_config_editor 配置登录凭据别把密码直接写进命令行进程列表里能看得到。4. 业务落地省市区联动下拉框与递归查询的两种正确写法4.1 全量加载组树一次查询顶掉 N 次递归省市区联动最土也最稳的做法是把整张表一次性查出来在应用内存里组树。这张表长期看也就几百行全表查出来几十 KB比用户每次切换省份都打一次数据库的 N1 查询省太多。新手最容易写的翻车代码是「选中省之后再 SELECT 一次市」本地开发看不出问题一上生产网络延迟一高下拉框就卡出玄学延迟。推荐的后端做法是查全表按 class_parent_id 分组再逐层挂 children。以 Python 为例import pymysql conn pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databaseregion, charsetutf8mb4 ) with conn.cursor(pymysql.cursors.DictCursor) as cur: # 一次全量查出省市两级字典游标让每行变成 dict cur.execute(SELECT class_id, class_parent_id, class_name, class_type FROM db_yhm_city ORDER BY class_id) rows cur.fetchall() # 按父级 ID 分桶避免后续每层都遍历全表 bucket {} for row in rows: bucket.setdefault(row[class_parent_id], []).append(row) def build_children(parent_id): # 递归拼装树结构返回该父级下的完整子树 children [] for row in bucket.get(parent_id, []): row[children] build_children(row[class_id]) children.append(row) return children tree build_children(0) # 根节点 id0即为「中国」 print(tree[0][class_name], len(tree[0][children]))这段代码最关键的是先建 bucket 再递归。如果不建桶直接在 build_children 里每次遍历全表找子节点几百行数据还好真要把表铺到区县、几万行时复杂度就上去了。前端拿到的 tree 结构直接就能喂给 el-cascader 或 picker 组件。注意 pymysql 连接串里的 password 要换成实际密码别把 root 密码写进代码仓库。这里必须补一句安全底线前端拿到的省市区 ID 会回传给后端接口里凡是拼 SQL 的地方都要做参数化或强制类型转换。class_id 是数字型如果代码里直接字符串拼接 WHERE class_id id攻击者传个 2 OR 11 就能把整表拖出来SQL 注入那套老把戏在字典接口上照样好用。至少强制 (int)$id 或 intval()这是所有字典接口的底线。4.2 递归查询MySQL 8.0 的 WITH RECURSIVE 与慢 SQL 边界如果不想在应用层组树想在数据库里直接查出某个省级行政区下所有层级的完整列表MySQL 8.0 可以写递归 CTE。注意 5.7 及以下不支持 WITH RECURSIVE这类老库上要么升版本要么退回应用层组树。以前 5.7 时代有人写存储过程做递归一层层拿游标查又慢又难维护8.0 的递归 CTE 直接替代了那种写法。WITH RECURSIVE region_tree AS ( -- 锚点从根节点出发深度记为 1 SELECT class_id, class_parent_id, class_name, class_type, 1 AS depth FROM db_yhm_city WHERE class_parent_id 0 UNION ALL -- 递归分支每层向下挂子节点直到没有可挂的行自动停止 SELECT c.class_id, c.class_parent_id, c.class_name, c.class_type, rt.depth 1 FROM db_yhm_city c INNER JOIN region_tree rt ON c.class_parent_id rt.class_id ) SELECT class_id, class_name, depth FROM region_tree ORDER BY depth, class_id;depth 列是递归深度锚点层是 1往下逐层加 1。UNION ALL 的递归分支会反复执行直到没有新行产生。这段 SQL 用在省市区表上完全没有性能问题表行数不到 400递归深度最多 3 层。但如果哪天你接入了几万行的全国小区数据递归 CTE 的中间临时表膨胀会让执行计划不可控这时候优先考虑物化路径方案把路径前缀存成一个字段查询时直接 LIKE 前缀不再递归。慢 SQL 优化在这个场景下的正确姿势是索引是现成的class_parent_id 和 class_type 上各有一个 KEY按父级查子级会走索引。别盲加索引如果发现没走用 EXPLAIN SELECT ... WHERE class_parent_id 3 看一眼 type 列不是 ALL 就说明没问题。真正容易慢的是在 class_name 上做模糊查询这个表才几百行不用太纠结但别写 SELECT * 去前台页面兜底只查需要的列。5. 避坑指南这份老数据里的五个历史遗留问题和脏数据5.1 巢湖市已被撤销class_id38 还挂在安徽下面现象查安徽的市级列表巢湖依然出现在结果里而现实中 2011 年地级巢湖市已撤销原辖区分拆给合肥、芜湖、马鞍山。原因这份 SQL 的数据快照来自行政区划调整之前巢湖不是个别手误而是整份数据的时间戳决定的必然结果。凡是这种「现场能跑、年份不明」的区划数据都默认带同一批过时点。解决按自己业务口径决定处理方式。展示型系统把巢湖记录删掉就行如果历史单据要溯源保留它并在应用层加失效标记字段不要直接 DELETE。这张表只有两级巢湖节点下没有子市级记录可以直接-- 若业务不需要展示旧区划直接删掉巢湖记录 DELETE FROM db_yhm_city WHERE class_id 38;删除前先确认没有其他业务表用 class_id38 做外键不然删完会留一堆脏引用。5.2 襄樊早在 2010 年改名为襄阳表里还是老名字现象湖北节点下显示「襄樊」现实中 2010 年襄樊市已经更名为襄阳市2012 年后「襄樊」只作为历史地名存在。原因数据快照早于更名时间点。这类地名变更在省市区数据里非常常见乐山、普洱、崇左都是同类型的改名案例。解决改名用 UPDATE 而不是删了重建因为 class_id193 可能已经被你的业务表关联删掉重插会改变主键所有关联数据跟着遭殃-- 湖北襄樊改名襄阳保持主键不变避免业务表外键失联 UPDATE db_yhm_city SET class_name 襄阳 WHERE class_id 193 AND class_name 襄樊;WHERE 里带 class_name 条件是个好习惯防止误更新到其他同名记录。这类「改名字但不改 ID」的操作就是维护历史数据字典的标准姿势。5.3 表里根本没有区县数据别被「省市区」三个字误导现象很多人以为这份 SQL 自带区县级数据导入后想直接用区县下拉框结果发现 class_type3 一条都没有。原因资源的实际粒度只到地级市共 300 多条市级记录区县那一层压根没铺。摘要里写的「区县级别字段」是理想结构说明不是这份 SQL 的实际交付内容。解决做电商收货地址这类硬需求建议另找自带区县的完整资源或者按第 6 章的模板给这张表补区县数据。临时要用某几个城市的区县可以手工 INSERT下一章会给全建表补数据的例子。5.4 省直辖县级行政单位冒充「市级」层级语义不干净现象湖北下面挂着仙桃、潜江、天门、神农架林区河南有济源海南有儋州、东方它们的 class_parent_id 指向省级、class_type2但行政级别上其实是省直辖县级行政单位不是地级市。原因这份数据为了省事把所有 class_type2 的节点都标成「市级别」省直辖县也因此被塞进市级维度导致前端展示时它们和地级市平级实际级别差一级。解决如果业务对行政级别敏感把这些记录的类型改成不冲突的枚举值查询时单独处理。改 class_type 不影响层级关系因为层级只认 class_parent_id-- 把湖北的省直辖县级单位单独标记出来避免和地级市混为一谈 UPDATE db_yhm_city SET class_type 21 WHERE class_parent_id 13 AND class_name IN (仙桃, 潜江, 天门, 神农架林区);class_type 的设计本就是给业务留的口子改成一个不冲突的枚举值比在应用层写死「湖北的这几个市特殊处理」干净得多。注意别用 3第 5.3 节说了 3 在理想设计里是区县占用它会让后续扩展区县数据冲突。5.5 列表顺序不按拼音也不按编码是插入序现象看安徽的市级列表安庆、蚌埠、巢湖、池州……既不是拼音序也不是行政代码序有人以为数据乱序。原因class_id 是自增主键默认 ORDER BY class_id 就是当年的插入顺序不是排序字段。建表语句里没有按拼音排序列MySQL 的 utf8 排序规则对中文是按字节序排的不是拼音序。解决前台展示一般按拼音排序。MySQL 里最常见的中文按拼音排序做法是转成 gbk 再比利用 gbk 编码按拼音编排的特性-- 中文按拼音排序利用 gbk 编码的特性 SELECT class_name FROM db_yhm_city WHERE class_parent_id 3 ORDER BY CONVERT(class_name USING gbk);CONVERT(class_name USING gbk) 会把 utf8 字段按 gbk 重新编码后再排序gbk 编码的汉字按拼音顺序排列所以结果是安庆、蚌埠、亳州、巢湖……这种顺序。MySQL 5.7 以上都能跑字符集要支持 gbk默认安装都带。这个方法我一直在用比在应用层引入排序库省事得多。6. 数据更新与自维护把一份静态 SQL 变成可维护的数据字典6.1 用 UPDATE 和 DELETE 打补丁撤并城市不丢主键上面说的巢湖、襄阳只是老数据的一个断面。实际业务里区划变更的三种常见形态是撤县设区、地级市整体并入、自治州改名处理原则都一样「能改先改能合并就合并别删主键」。主键是业务表外键的锚点删掉再插等于把所有关联数据推倒重来。莱芜是个现成案例2019 年地级莱芜市撤销并入济南class_id290 还在山东下面挂着。我不建议 DELETE INSERT两条语句就够-- 第一步把莱芜记录改归属到济南 UPDATE db_yhm_city SET class_parent_id 283 -- 济南的 class_id WHERE class_id 290; -- 如果莱芜下面还有子区县第二步把它们一起挂到济南下 UPDATE db_yhm_city SET class_parent_id 283 WHERE class_parent_id 290;合并场景里父级改了子级自动跟着新父级走。第三步才是 DELETE 掉已经变成空壳的莱芜节点。顺序反了会先把子级删成孤儿再想挂到济南就找不着了。6.2 扩展区县层级把表设计里的 class_type3 真正用起来这份资源的设计里预留了 class_type3 是区县但数据没铺。你完全可以在不改表结构的前提下按同一套规则补齐区县数据。INSERT 模板最重要的一条是新记录的 class_parent_id 必须指向父级城市的 class_id不能指向省级否则区县会错误地挂在省下。-- 给北京补两条区县示例父级必须是北京class_id2不能写成省级 id1 INSERT INTO db_yhm_city (class_id, class_parent_id, class_name, class_type) VALUES (4001, 2, 东城区, 3), (4002, 2, 西城区, 3);class_id 从当前最大 ID 往后排避免未来再用这份旧 SQL 重复导入造成主键冲突。如果表里区县数据量大了按区县名做唯一索引不现实因为不同城市可能有同名区县唯一性还是靠 class_id 保证别去动表结构。从那以后我每次拿到这类「现场能跑、数据有年份」的行政区划 SQL第一件事不是直接挂页面而是先跑三条查询总数校验、孤儿数据检查、按 class_type 分组统计。确认数据快照的时间戳和口径之后才进业务代码。毕竟地址字典这种东西出错了用户不会报 bug只会默默选错收货地址等快递送错了才发现。希望帮到你。本文还有配套的精品资源点击获取 MySQL省市区表实战：从建表到递归查询与数据维护
简介面向需要集成中国行政区划信息的开发者这份 MySQL 版中国省市区数据表 SQL 文档提供了完整的建表语句以及省、市、区县三级行政区划数据。表结构包含 class_id、class_parent_id、class_name、class_type 四个核心字段class_type 可区分国家、省、市等层级通过父级 ID 清晰构建上下级关系。开发者可直接导入 MySQL 使用并用简单 SQL 查询快速获取某省份下所有城市或某城市下所有区县适用于电商地址自动填充、物流配送路线优化、基于地理位置的数据分析等场景。文档还提供了创建表和插入数据的示例语句并对字段含义做了说明便于初学者理解行政区划的层级模型。资源包共 1 个 PDF 文件整体大小约 495KB内容涵盖建表 SQL 与全国省份、城市、区县的基础插入数据基本做到导入后即可使用。该资源已有 1077 人浏览学习适合初中级后端开发者和数据库学习者快速落地省市区数据模块。1. 一份能直接跑的 MySQL 省市区表从建表语句到业务落地做电商、做物流、做门店系统的同行多半被「省市区三级联动」这个看似简单的东西恶心过。网上搜 mysql sql 省市区数据表要么是收费资源要么数据老得连城市都没更新要么字段设计得根本没法写查询。这份 MySQL 版中国省市区数据表 SQL 属于拿来就能用的资源一条建表语句加几百条 INSERT把 34 个省级行政区和 300 多个地级市一次性铺好字段只有 class_id、class_parent_id、class_name、class_type 四个层级完全靠 parent_id 串起来。适合中小型项目快速铺地基也适合拿来当数据字典或测试数据填充。下面直接拆这份 SQL讲清楚怎么导入、怎么查、业务里怎么组树再重点说几个数据本身的历史遗留坑最后给你一套自己能持续维护的打补丁方法。2. 表结构拆解class_parent_id 自关联设计为什么比行政区划代码更耐造2.1 四个字段的职责class_type 这一列决定了整棵树的深度建表语句是这套资源的骨架复制到任何 MySQL 5.5 以上的环境都能直接执行。class_id 是自增主键class_parent_id 指向上级记录的 class_idclass_name 存中文名称class_type 区分层级。站在现在的时间点回头看这四个字段的设计其实是典型的「邻接表Adjacency List」模型每行只记一个父节点层级关系靠递归或多次联结展开。它不像行政区划代码GB/T 2260那样把省市县编码塞进固定长度字段好处是区划变更时不需要改编码规则只要调整行记录坏处是查询任意层级的完整路径要额外花心思。很多新项目组会直接把六位数字行政编码塞进表里查询时用 LIKE 前缀匹配去逐级筛省市县遇到编码被重新分配的历史问题就得改大段数据。class_parent_id 这种设计没有这个包袱任何一级调整都只影响相邻两行。class_type 的取值在数据里只出现了 0、1、2 三种0 是根节点「中国」1 是省级行政区2 是市级行政区。摘要描述里提到 class_type3 代表区县但翻完整份 INSERT 语句实际上并没有 class_type3 的记录也就是说这份资源只铺到了地级市这一层。这点必须先说清楚避免你导入后以为数据不完整。-- 按 class_type 分组统计各级数据量验证这张表到底有几层 SELECT class_type, COUNT(*) AS cnt FROM db_yhm_city GROUP BY class_type;这段查询建议在导入后第一时间执行。class_type0 应该只有 1 条也就是「中国」这个根节点class_type1 应该是 34 条省级行政区含港澳台地区class_type2 是全部地级市与省直辖县级行政单位大概在 340 条上下。如果你查出来 class_type3 是 0 条说明这份资源的粒度就是省市两级不是数据导坏了而是它本来就没铺区县。再抠两个容易看走眼的字段细节。class_parent_id 列定义了 DEFAULT 0意思是没指定父级时默认挂到根上这个默认值有两个作用一是防止程序插入时漏传字段导致 NULLNULL 会在层级查询里把整条链断掉二是让「中国」这样的根节点能和其他节点共用同一个查询逻辑。class_id 的类型是 smallint(5) unsigned这个 unsigned 是关键它把上限从 32767 提到 65535全国区划加上自己扩展的区县数据也远够用。括号里的 5 只是显示宽度不是可存位数上限可视化工具里显示成 5 位不要误以为只能存 5 位数。2.2 MyISAM 引擎与字符集老资源的时代印记要不要迁移建表语句末尾写了 ENGINEMyISAM DEFAULT CHARSETutf8这两项都是当年配套环境的产物。MyISAM 不支持事务、不支持外键、崩溃恢复能力弱但胜在查询快、占用简单作为只读字典表完全够用。现在 MySQL 5.7 以上已经把 MyISAM 标记为弃用8.0 里更是默认 InnoDB如果这个库里有其他业务表在跑事务建议把引擎迁过去避免备份恢复时出现引擎兼容方面的幺蛾子。-- 将只读字典表迁移成 InnoDB不影响任何查询和联表 ALTER TABLE db_yhm_city ENGINE InnoDB; ALTER TABLE db_yhm_city CONVERT TO CHARACTER SET utf8mb4;为什么要转 utf8mb4MySQL 里的 utf8 实际是 utf8mb3只能存基本多语言平面内的字符部分生僻字和特殊字符存不进去。行政区划名称里虽然没有 emoji但少数民族地区有不少生僻字地名而且如果业务库统一用 utf8mb4这张表不转会在联表查询时出现字符集不一致轻则不索引、重则乱码。CONVERT 只改表定义和存储编码不改业务逻辑最后查一遍 SELECT COUNT(*) 确认行数不变就行。顺带说一句密钥索引的事KEY class_parent_id 和 KEY class_type 这两个二级索引在邻接表模型里是查询命脉。按父级查全部子级、按类型筛省市区全靠这两个索引撑着。class_type 是 tinyint(1)取值范围 0-255当业务枚举用刚好。表名 db_yhm_city 里的 db_yhm 前缀看起来是从某套商城系统带出来的历史痕迹不影响使用。如果业务规范要求统一命名导入后执行 ALTER TABLE db_yhm_city RENAME TO region_city 即可改名不动数据但如果有视图直接引用旧表名记得一起改。3. 导入与验证让这份 SQL 在 MySQL 5.7 / 8.0 上跑起来3.1 命令行导入source 一条解决拿到这份 SQL 文件的常规路径是命令行。MySQL 的 source 命令会把文件里的建表语句和 INSERT 一次性执行完中间不会因为客户端超时卡住。这份资源只有一条建表语句加几百条 INSERT文件体量很小source 执行基本是秒级。先建一个独立库避免把表混进业务库后面维护和替换都方便。CREATE DATABASE IF NOT EXISTS region DEFAULT CHARACTER SET utf8mb4; USE region; SOURCE /data/sql/db_yhm_city.sql;把路径换成你本地实际路径。SOURCE 是 MySQL 客户端命令不是 SQL 语句只能在 mysql 命令行工具里用不能在 Navicat 的查询窗口里敲。导入完成后执行 SELECT COUNT(*) FROM db_yhm_city确认总行数在 380 上下说明文件完整执行了。命令行导入最常翻车的点是编码。文件本身是 utf8但 Windows 下用记事本另存过会变成带 BOM 的 utf8BOM 会被当成不可见字符塞进第一条记录导致「中国」变成乱码。检查办法是看文件头三个字节是否为 EF BB BF# Linux / mac 下看文件头三个字节是否为 BOM 标记 head -c 3 db_yhm_city.sql | xxd如果输出 EF BB BF说明带 BOM用 sed 去掉再导入# 去掉首行 BOM 后重新导入 sed -i 1s/^\xEF\xBB\xBF// db_yhm_city.sqlmacOS 的 sed 和 Linux 略有差异macOS 上要写成 sed -i 1s/^\xEF\xBB\xBF//。这是 Windows 用户跨平台倒数据最常见的坑我自己接过不止一次同事发来的「乱码版建表语句」最后都是 BOM 惹的祸。3.2 图形化导入Navicat 与 MySQL Workbench 的操作差异不习惯命令行的用 Navicat for MySQL 导入也简单。新建查询把整个 SQL 文件内容粘贴进去执行更推荐的是右键目标库选「运行 SQL 文件」Navicat 会按文件逐段执行并在下方面板显示错误。MySQL Workbench 里路径是菜单 File - Open SQL Script打开后点闪电图标执行。两个工具的差异值得记一下。Navicat 的「运行 SQL 文件」默认按 utf8 读取遇到文件里带 SET NAMES 或字符集不一致会报错执行前先确认目标库的字符集为 utf8mb4。Workbench 对 SQL 文件的兼容性稍好但执行 INSERT 较多的大文件时会一条条跑速度明显慢这份数据体量无所谓以后换大文件时就要注意。团队协作中如果你把 SQL 交给别人导入建议附一句「用 source 或直接执行别用图形化工具的导入外部数据向导」因为导入向导会把它当 CSV 解析把整份文件当成一列导入完表结构全乱。3.3 数据完整性校验三分钟确认数据没导坏数据导进去之后不要立刻在页面上挂联动先跑三条校验 SQL总数、省级数、孤儿数据。总数和省级数的 SQL 在第 2 章已经给过这里重点说孤儿数据也就是「引用了一个不存在的父级」的脏记录。邻接表模型最怕这种数据它会让递归查询静默中断前端下拉框里出现一个点了没反应的空白分组。-- 孤儿数据检查所有 class_parent_id 都能在表里找到对应 class_id -- 正常情况下这类记录应为 0 行 SELECT a.class_id, a.class_name, a.class_parent_id FROM db_yhm_city a LEFT JOIN db_yhm_city b ON a.class_parent_id b.class_id WHERE b.class_id IS NULL AND a.class_parent_id 0;这段 SQL 的逻辑是以 a 为主体左连接 b 找父级如果父级不存在b.class_id 就是 NULL。过滤条件里排除 class_parent_id0 的根节点「中国」因为它的父级是虚拟的 0不在表里。跑出来 0 行说明整棵树的引用关系是完整的可以放心用递归查询。团队里多人协作时SQL 文件往往拆成多个比如 region_province.sql、region_city.sql。不少人常问 mysql 创建多个数据表的格式该怎么处理多个文件其实在命令行里循环导入即可# 批量导入同一目录下多个 region 开头的 SQL 文件 for f in /data/sql/region_*.sql; do mysql -uroot -p --databaseregion --default-character-setutf8 $f done 重定向等价于 source但可以放进脚本循环适合一次导入多个表文件。注意密码提示会打断循环无人值守环境建议用 mysql_config_editor 配置登录凭据别把密码直接写进命令行进程列表里能看得到。4. 业务落地省市区联动下拉框与递归查询的两种正确写法4.1 全量加载组树一次查询顶掉 N 次递归省市区联动最土也最稳的做法是把整张表一次性查出来在应用内存里组树。这张表长期看也就几百行全表查出来几十 KB比用户每次切换省份都打一次数据库的 N1 查询省太多。新手最容易写的翻车代码是「选中省之后再 SELECT 一次市」本地开发看不出问题一上生产网络延迟一高下拉框就卡出玄学延迟。推荐的后端做法是查全表按 class_parent_id 分组再逐层挂 children。以 Python 为例import pymysql conn pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databaseregion, charsetutf8mb4 ) with conn.cursor(pymysql.cursors.DictCursor) as cur: # 一次全量查出省市两级字典游标让每行变成 dict cur.execute(SELECT class_id, class_parent_id, class_name, class_type FROM db_yhm_city ORDER BY class_id) rows cur.fetchall() # 按父级 ID 分桶避免后续每层都遍历全表 bucket {} for row in rows: bucket.setdefault(row[class_parent_id], []).append(row) def build_children(parent_id): # 递归拼装树结构返回该父级下的完整子树 children [] for row in bucket.get(parent_id, []): row[children] build_children(row[class_id]) children.append(row) return children tree build_children(0) # 根节点 id0即为「中国」 print(tree[0][class_name], len(tree[0][children]))这段代码最关键的是先建 bucket 再递归。如果不建桶直接在 build_children 里每次遍历全表找子节点几百行数据还好真要把表铺到区县、几万行时复杂度就上去了。前端拿到的 tree 结构直接就能喂给 el-cascader 或 picker 组件。注意 pymysql 连接串里的 password 要换成实际密码别把 root 密码写进代码仓库。这里必须补一句安全底线前端拿到的省市区 ID 会回传给后端接口里凡是拼 SQL 的地方都要做参数化或强制类型转换。class_id 是数字型如果代码里直接字符串拼接 WHERE class_id id攻击者传个 2 OR 11 就能把整表拖出来SQL 注入那套老把戏在字典接口上照样好用。至少强制 (int)$id 或 intval()这是所有字典接口的底线。4.2 递归查询MySQL 8.0 的 WITH RECURSIVE 与慢 SQL 边界如果不想在应用层组树想在数据库里直接查出某个省级行政区下所有层级的完整列表MySQL 8.0 可以写递归 CTE。注意 5.7 及以下不支持 WITH RECURSIVE这类老库上要么升版本要么退回应用层组树。以前 5.7 时代有人写存储过程做递归一层层拿游标查又慢又难维护8.0 的递归 CTE 直接替代了那种写法。WITH RECURSIVE region_tree AS ( -- 锚点从根节点出发深度记为 1 SELECT class_id, class_parent_id, class_name, class_type, 1 AS depth FROM db_yhm_city WHERE class_parent_id 0 UNION ALL -- 递归分支每层向下挂子节点直到没有可挂的行自动停止 SELECT c.class_id, c.class_parent_id, c.class_name, c.class_type, rt.depth 1 FROM db_yhm_city c INNER JOIN region_tree rt ON c.class_parent_id rt.class_id ) SELECT class_id, class_name, depth FROM region_tree ORDER BY depth, class_id;depth 列是递归深度锚点层是 1往下逐层加 1。UNION ALL 的递归分支会反复执行直到没有新行产生。这段 SQL 用在省市区表上完全没有性能问题表行数不到 400递归深度最多 3 层。但如果哪天你接入了几万行的全国小区数据递归 CTE 的中间临时表膨胀会让执行计划不可控这时候优先考虑物化路径方案把路径前缀存成一个字段查询时直接 LIKE 前缀不再递归。慢 SQL 优化在这个场景下的正确姿势是索引是现成的class_parent_id 和 class_type 上各有一个 KEY按父级查子级会走索引。别盲加索引如果发现没走用 EXPLAIN SELECT ... WHERE class_parent_id 3 看一眼 type 列不是 ALL 就说明没问题。真正容易慢的是在 class_name 上做模糊查询这个表才几百行不用太纠结但别写 SELECT * 去前台页面兜底只查需要的列。5. 避坑指南这份老数据里的五个历史遗留问题和脏数据5.1 巢湖市已被撤销class_id38 还挂在安徽下面现象查安徽的市级列表巢湖依然出现在结果里而现实中 2011 年地级巢湖市已撤销原辖区分拆给合肥、芜湖、马鞍山。原因这份 SQL 的数据快照来自行政区划调整之前巢湖不是个别手误而是整份数据的时间戳决定的必然结果。凡是这种「现场能跑、年份不明」的区划数据都默认带同一批过时点。解决按自己业务口径决定处理方式。展示型系统把巢湖记录删掉就行如果历史单据要溯源保留它并在应用层加失效标记字段不要直接 DELETE。这张表只有两级巢湖节点下没有子市级记录可以直接-- 若业务不需要展示旧区划直接删掉巢湖记录 DELETE FROM db_yhm_city WHERE class_id 38;删除前先确认没有其他业务表用 class_id38 做外键不然删完会留一堆脏引用。5.2 襄樊早在 2010 年改名为襄阳表里还是老名字现象湖北节点下显示「襄樊」现实中 2010 年襄樊市已经更名为襄阳市2012 年后「襄樊」只作为历史地名存在。原因数据快照早于更名时间点。这类地名变更在省市区数据里非常常见乐山、普洱、崇左都是同类型的改名案例。解决改名用 UPDATE 而不是删了重建因为 class_id193 可能已经被你的业务表关联删掉重插会改变主键所有关联数据跟着遭殃-- 湖北襄樊改名襄阳保持主键不变避免业务表外键失联 UPDATE db_yhm_city SET class_name 襄阳 WHERE class_id 193 AND class_name 襄樊;WHERE 里带 class_name 条件是个好习惯防止误更新到其他同名记录。这类「改名字但不改 ID」的操作就是维护历史数据字典的标准姿势。5.3 表里根本没有区县数据别被「省市区」三个字误导现象很多人以为这份 SQL 自带区县级数据导入后想直接用区县下拉框结果发现 class_type3 一条都没有。原因资源的实际粒度只到地级市共 300 多条市级记录区县那一层压根没铺。摘要里写的「区县级别字段」是理想结构说明不是这份 SQL 的实际交付内容。解决做电商收货地址这类硬需求建议另找自带区县的完整资源或者按第 6 章的模板给这张表补区县数据。临时要用某几个城市的区县可以手工 INSERT下一章会给全建表补数据的例子。5.4 省直辖县级行政单位冒充「市级」层级语义不干净现象湖北下面挂着仙桃、潜江、天门、神农架林区河南有济源海南有儋州、东方它们的 class_parent_id 指向省级、class_type2但行政级别上其实是省直辖县级行政单位不是地级市。原因这份数据为了省事把所有 class_type2 的节点都标成「市级别」省直辖县也因此被塞进市级维度导致前端展示时它们和地级市平级实际级别差一级。解决如果业务对行政级别敏感把这些记录的类型改成不冲突的枚举值查询时单独处理。改 class_type 不影响层级关系因为层级只认 class_parent_id-- 把湖北的省直辖县级单位单独标记出来避免和地级市混为一谈 UPDATE db_yhm_city SET class_type 21 WHERE class_parent_id 13 AND class_name IN (仙桃, 潜江, 天门, 神农架林区);class_type 的设计本就是给业务留的口子改成一个不冲突的枚举值比在应用层写死「湖北的这几个市特殊处理」干净得多。注意别用 3第 5.3 节说了 3 在理想设计里是区县占用它会让后续扩展区县数据冲突。5.5 列表顺序不按拼音也不按编码是插入序现象看安徽的市级列表安庆、蚌埠、巢湖、池州……既不是拼音序也不是行政代码序有人以为数据乱序。原因class_id 是自增主键默认 ORDER BY class_id 就是当年的插入顺序不是排序字段。建表语句里没有按拼音排序列MySQL 的 utf8 排序规则对中文是按字节序排的不是拼音序。解决前台展示一般按拼音排序。MySQL 里最常见的中文按拼音排序做法是转成 gbk 再比利用 gbk 编码按拼音编排的特性-- 中文按拼音排序利用 gbk 编码的特性 SELECT class_name FROM db_yhm_city WHERE class_parent_id 3 ORDER BY CONVERT(class_name USING gbk);CONVERT(class_name USING gbk) 会把 utf8 字段按 gbk 重新编码后再排序gbk 编码的汉字按拼音顺序排列所以结果是安庆、蚌埠、亳州、巢湖……这种顺序。MySQL 5.7 以上都能跑字符集要支持 gbk默认安装都带。这个方法我一直在用比在应用层引入排序库省事得多。6. 数据更新与自维护把一份静态 SQL 变成可维护的数据字典6.1 用 UPDATE 和 DELETE 打补丁撤并城市不丢主键上面说的巢湖、襄阳只是老数据的一个断面。实际业务里区划变更的三种常见形态是撤县设区、地级市整体并入、自治州改名处理原则都一样「能改先改能合并就合并别删主键」。主键是业务表外键的锚点删掉再插等于把所有关联数据推倒重来。莱芜是个现成案例2019 年地级莱芜市撤销并入济南class_id290 还在山东下面挂着。我不建议 DELETE INSERT两条语句就够-- 第一步把莱芜记录改归属到济南 UPDATE db_yhm_city SET class_parent_id 283 -- 济南的 class_id WHERE class_id 290; -- 如果莱芜下面还有子区县第二步把它们一起挂到济南下 UPDATE db_yhm_city SET class_parent_id 283 WHERE class_parent_id 290;合并场景里父级改了子级自动跟着新父级走。第三步才是 DELETE 掉已经变成空壳的莱芜节点。顺序反了会先把子级删成孤儿再想挂到济南就找不着了。6.2 扩展区县层级把表设计里的 class_type3 真正用起来这份资源的设计里预留了 class_type3 是区县但数据没铺。你完全可以在不改表结构的前提下按同一套规则补齐区县数据。INSERT 模板最重要的一条是新记录的 class_parent_id 必须指向父级城市的 class_id不能指向省级否则区县会错误地挂在省下。-- 给北京补两条区县示例父级必须是北京class_id2不能写成省级 id1 INSERT INTO db_yhm_city (class_id, class_parent_id, class_name, class_type) VALUES (4001, 2, 东城区, 3), (4002, 2, 西城区, 3);class_id 从当前最大 ID 往后排避免未来再用这份旧 SQL 重复导入造成主键冲突。如果表里区县数据量大了按区县名做唯一索引不现实因为不同城市可能有同名区县唯一性还是靠 class_id 保证别去动表结构。从那以后我每次拿到这类「现场能跑、数据有年份」的行政区划 SQL第一件事不是直接挂页面而是先跑三条查询总数校验、孤儿数据检查、按 class_type 分组统计。确认数据快照的时间戳和口径之后才进业务代码。毕竟地址字典这种东西出错了用户不会报 bug只会默默选错收货地址等快递送错了才发现。希望帮到你。本文还有配套的精品资源点击获取