简介这份 MySQL 版中国省市区数据表 SQL 资源面向需要处理行政区划信息的后端开发者、电商与物流系统设计人员以及正在学习数据库表结构设计的学习者。它提供了一张可直接导入使用的db_yhm_city表通过class_id、class_parent_id、class_name、class_type四个字段构建起国家、省、市、区县的层级关系并配有主键及class_parent_id、class_type索引便于按上级 ID 逐级查询城市或区县。资源包共 1 个文件为 PDF 格式约 495KB内容涵盖建表语句、省市区数据插入示例以及常用查询 SQL可直接参考或迁移到实际项目中。目前已有 1081 人学习下载适合需要快速搭建地址库、实现地址自动填充或进行地理维度数据分析的开发者参考使用。1. 从一张只有四个字段的表说起为什么省市区数据总在项目里翻车做电商下单页、物流面单、用户地址管理绕不开省市区三级联动。很多人第一反应是找一份 JSON 前端写死或者干脆调第三方接口。前端写死的问题在于数据更新要发版第三方接口的问题在于限流和网络抖动一旦挂了整个下单流程就卡住。这份 MySQL 版中国省市区数据表 SQL 走的是另一条路把行政区划直接落库用一张自增主键加父级 ID 的表把国家、省、市三级串起来查询靠class_parent_id递归不依赖任何外部服务。它适合中小型业务快速接入也适合作为地址模块的底座数据。表结构极简四个字段class_id、class_parent_id、class_name、class_type但正是这种极简设计让不少人在导入和查询时踩了坑。下面从建表、导入、查询到避坑一步步拆开讲。2. 建表与字段设计四个字段怎么撑起三级联动2.1 表结构逐字段拆解拿到 SQL 文件第一段就是建表语句。表名db_yhm_city存储引擎 MyISAM字符集 utf8。四个字段各司其职字段名类型作用关键点class_idsmallint(5) unsigned行政区划唯一标识自增主键手动插入时需显式指定值否则自增序列会乱class_parent_idsmallint(5) unsigned上级行政区划 ID顶级节点中国为 0省为 1市为对应省 IDclass_namevarchar(120)行政区划名称utf8 下中文占 3 字节120 足够长名称class_typetinyint(1)层级类型0 国家、1 省、2 市查询时靠它过滤层级避免混查class_type是这份数据表设计里最值得说的一个字段。没有它你查“所有省”只能靠class_parent_id 1来推断但一旦数据里混入直辖市或特别行政区逻辑就会变得脆弱。有了class_typeWHERE class_type 1直接锁定省级语义清晰。class_parent_id上建了普通索引class_type上也建了索引这两个索引是后续所有查询的性能基础。2.2 建表语句执行与字符集确认把建表段单独拿出来执行建议先确认数据库默认字符集。如果库是utf8mb4表是utf8跨表 JOIN 时可能出现排序规则冲突。常见做法是统一改成utf8mb4或者在建表时显式指定COLLATE utf8_general_ci。-- 先建库如果还没有字符集与表保持一致 CREATE DATABASE IF NOT EXISTS demo_region DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE demo_region; -- 建表注意 ENGINE 和 CHARSET 与原始 SQL 一致 DROP TABLE IF EXISTS db_yhm_city; 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;这段代码里DROP TABLE IF EXISTS是必须保留的因为原始 SQL 文件通常包含它重复导入时不会报错。AUTO_INCREMENT1配合后续 INSERT 里显式指定的class_id实际自增值会被推到最大 ID 之后。MyISAM 引擎不支持事务导入中途失败不会回滚所以导入前最好先备份或确认表是空的。提示如果业务需要事务支持可以把ENGINEMyISAM改成ENGINEInnoDB字段和索引定义不用动。InnoDB 在并发查询下表现更稳代价是插入速度略慢。2.3 数据层级关系验证建完表先别急着写业务代码用几条查询确认层级关系是否正确。原始数据里class_id1是中国class_parent_id0class_type0class_id2是北京class_parent_id1class_type1class_id52是北京下的“北京”class_parent_id2class_type2。这种“省名和市名相同”的情况在直辖市里很常见查询时要注意区分。-- 查所有省级节点 SELECT class_id, class_name FROM db_yhm_city WHERE class_type 1 ORDER BY class_id; -- 查北京省下的所有市级节点 SELECT class_id, class_name FROM db_yhm_city WHERE class_parent_id 2 AND class_type 2; -- 验证层级完整性有没有市级节点的父级不是省级 SELECT c.class_id, c.class_name, c.class_parent_id FROM db_yhm_city c LEFT JOIN db_yhm_city p ON c.class_parent_id p.class_id WHERE c.class_type 2 AND (p.class_id IS NULL OR p.class_type ! 1);第三条查询是数据质量检查的常用手段。如果返回空结果说明市级节点的父级都能正确指向省级节点。如果返回了记录说明数据里有“孤儿节点”或者父级类型不对需要手动修正。这类检查在导入任何层级数据后都值得跑一遍。3. 数据导入批量 INSERT 的三种姿势与性能对比3.1 直接执行原始 SQL 文件最省事的方式是用命令行一次性导入。假设文件名为db_yhm_city.sql放在当前目录# 方式一mysql 命令直接导入 mysql -u root -p demo_region db_yhm_city.sql # 方式二进入 mysql 后用 source mysql -u root -p USE demo_region; SOURCE db_yhm_city.sql;方式一适合脚本化部署方式二适合交互式调试。注意SOURCE命令的路径是相对于 mysql 客户端当前工作目录的不是相对于 SQL 文件所在目录。如果文件很大导入过程中可以用SHOW PROCESSLIST;另开一个会话查看进度。3.2 用 Python 脚本拆分导入并做校验原始 SQL 文件里 INSERT 语句是一条一条写的几千条数据直接执行没问题但如果后续要增量更新或做数据清洗用脚本处理更灵活。下面这段 Python 用pymysql连接数据库逐条读取 SQL 文件里的 INSERT 并执行同时统计各层级数量。import re import pymysql # 连接配置按实际环境改 conn pymysql.connect( host127.0.0.1, userroot, passwordyour_password, databasedemo_region, charsetutf8 ) cursor conn.cursor() # 读取 SQL 文件提取所有 INSERT 语句 with open(db_yhm_city.sql, r, encodingutf-8) as f: content f.read() # 匹配 INSERT INTO db_yhm_city VALUES (...); pattern re.compile(rINSERT INTO db_yhm_city VALUES \((.?)\);) matches pattern.findall(content) inserted 0 for values in matches: sql fINSERT INTO db_yhm_city VALUES ({values}) try: cursor.execute(sql) inserted 1 except Exception as e: print(f失败: {sql[:80]}... 原因: {e}) conn.commit() # 校验各层级数量 cursor.execute(SELECT class_type, COUNT(*) FROM db_yhm_city GROUP BY class_type) for row in cursor.fetchall(): print(fclass_type{row[0]}, 数量{row[1]}) print(f共导入 {inserted} 条) cursor.close() conn.close()正则INSERT INTO \db_yhm_city VALUES ((.?));里的.?是非贪婪匹配确保每条 INSERT 单独捕获。class_type 分组统计能快速看出数据是否完整正常应该有 1 条国家、30 多条省级、几百条市级。如果某个层级数量明显偏少说明 SQL 文件可能被截断需要检查文件末尾是否完整。3.3 导入后的索引重建与统计信息更新MyISAM 表在大量 INSERT 之后索引统计信息可能不是最新的。虽然 MyISAM 不像 InnoDB 那样有复杂的统计信息但执行一次ANALYZE TABLE可以让优化器更准确地选择索引。-- 重建索引统计信息 ANALYZE TABLE db_yhm_city; -- 查看表状态确认行数和索引情况 SHOW TABLE STATUS LIKE db_yhm_city; -- 确认最大 ID后续新增数据时参考 SELECT MAX(class_id) FROM db_yhm_city;SHOW TABLE STATUS返回的Rows字段在 MyISAM 下是精确值可以直接用来核对导入条数。MAX(class_id)决定了后续如果手动插入新节点应该从哪个 ID 开始避免主键冲突。4. 查询实战从省查市、从市查区、递归查全路径4.1 基础两级查询与索引命中最常见的需求是“给定省 ID查出所有市”。原始数据里省级class_type1市级class_type2父级关系靠class_parent_id。-- 查广东省class_id6下的所有市 SELECT class_id, class_name FROM db_yhm_city WHERE class_parent_id 6 AND class_type 2 ORDER BY class_id;这条查询会命中class_parent_id索引。用EXPLAIN看一下执行计划EXPLAIN SELECT class_id, class_name FROM db_yhm_city WHERE class_parent_id 6 AND class_type 2;如果key列显示class_parent_id说明索引生效。如果显示NULL或走了全表扫描可能是数据量太小优化器认为全表更快或者索引统计信息过期执行ANALYZE TABLE后再试。4.2 递归查询全路径从区县反查省市业务里经常需要把“区县 ID”还原成“省-市-区”完整地址。这份数据表只有两级父级关系市→省→国用两次 JOIN 就能拼出全路径。-- 假设区县 ID 为 100反查省、市、区名称 SELECT province.class_name AS province_name, city.class_name AS city_name, district.class_name AS district_name FROM db_yhm_city AS district LEFT JOIN db_yhm_city AS city ON district.class_parent_id city.class_id LEFT JOIN db_yhm_city AS province ON city.class_parent_id province.class_id WHERE district.class_id 100;这里用了两次自连接。district是区县节点city是它的父级province是父级的父级。LEFT JOIN保证即使某一级缺失也能返回部分结果方便排查数据问题。如果数据里区县层级是class_type3但原始 SQL 里只到市级那么这条查询的district部分需要根据实际数据调整。4.3 用变量实现递归查询MySQL 8.0 以下MySQL 8.0 之前没有 CTE 递归但可以用会话变量模拟。下面这段 SQL 从任意节点向上追溯所有祖先-- 从 class_id100 向上查所有祖先 SELECT class_id, class_name, class_parent_id, class_type FROM ( SELECT class_id, class_name, class_parent_id, class_type, pid : class_parent_id AS next_pid, lvl : lvl 1 AS level FROM db_yhm_city, (SELECT pid : 100, lvl : 0) vars WHERE class_id pid UNION ALL SELECT c.class_id, c.class_name, c.class_parent_id, c.class_type, pid : c.class_parent_id, lvl : lvl 1 FROM db_yhm_city c JOIN (SELECT pid : pid) tmp WHERE c.class_id pid AND pid ! 0 ) AS tree ORDER BY level;这段 SQL 依赖会话变量pid在每一行更新后传递给下一行。写法比较绕实际项目中更推荐在应用层用循环查询可读性更好。如果数据库是 MySQL 8.0直接用WITH RECURSIVE更清晰WITH RECURSIVE region_tree AS ( SELECT class_id, class_name, class_parent_id, class_type, 0 AS level FROM db_yhm_city WHERE class_id 100 UNION ALL SELECT c.class_id, c.class_name, c.class_parent_id, c.class_type, rt.level 1 FROM db_yhm_city c INNER JOIN region_tree rt ON c.class_id rt.class_parent_id ) SELECT * FROM region_tree ORDER BY level;WITH RECURSIVE的终止条件是INNER JOIN找不到匹配的父级自然停止。level字段从 0 开始递增方便前端按层级展示。5. 避坑与排查导入和查询中最容易翻车的五个点5.1 现象导入后中文显示乱码查询出来是问号原因数据库、表、连接三者的字符集不一致。原始 SQL 是utf8但客户端连接可能默认latin1或gbk。解决在连接字符串里显式指定charsetutf8建库时也指定DEFAULT CHARACTER SET utf8。如果已经乱码需要重新导入不能靠ALTER TABLE修复已损坏的数据。5.2 现象class_parent_id索引没生效查询慢原因MyISAM 表的索引统计信息过期或者查询条件里对class_parent_id做了函数运算如WHERE class_parent_id 0 6。解决执行ANALYZE TABLE db_yhm_city;更新统计信息查询时保持字段裸用不要在索引列上做运算。5.3 现象直辖市查询结果重复北京省下还有一个北京市原因原始数据里直辖市既作为省级节点class_id2class_type1又作为市级节点class_id52class_type2。解决查询时严格用class_type过滤。前端联动时省级选中北京后市级列表里会出现“北京”这是正常设计不要误删。5.4 现象AUTO_INCREMENT冲突新增节点报主键重复原因原始 SQL 里显式插入了class_id但表的AUTO_INCREMENT值没有同步更新。解决导入后执行SELECT MAX(class_id) FROM db_yhm_city;然后ALTER TABLE db_yhm_city AUTO_INCREMENT 最大值1;。或者新增节点时也显式指定class_id不依赖自增。5.5 现象MyISAM 表在并发写入时锁表下单高峰期卡顿原因MyISAM 只支持表级锁写操作会阻塞所有读。解决如果业务有频繁写入地址数据的需求把引擎改成 InnoDB。改法ALTER TABLE db_yhm_city ENGINEInnoDB;。改完后确认索引还在InnoDB 支持行级锁并发读写不会互相阻塞。6. 进阶技巧把省市区数据用出花来的三个习惯第一个习惯是给class_name加前缀索引。如果业务需要按名称模糊搜索比如用户输入“广”要匹配“广东”“广西”“广州”直接LIKE %广%会全表扫描。可以加一个KEY idx_name (class_name(10))让前缀匹配走索引。虽然%广%这种前后模糊仍然无法命中但LIKE 广%可以。-- 添加名称前缀索引 ALTER TABLE db_yhm_city ADD KEY idx_name (class_name(10)); -- 前缀匹配走索引 EXPLAIN SELECT * FROM db_yhm_city WHERE class_name LIKE 广%;第二个习惯是定期用CHECKSUM TABLE校验数据一致性。如果有多套环境开发、测试、生产导入同一份 SQL 后执行CHECKSUM TABLE db_yhm_city;比对校验和是否一致。不一致说明某个环境的导入过程出了问题比如文件截断或字符集转换错误。-- 校验数据一致性 CHECKSUM TABLE db_yhm_city;第三个习惯是导出时用mysqldump带--no-create-info只导数据不导建表语句方便在已有表结构的环境里增量更新。# 只导出数据不导出建表语句 mysqldump -u root -p --no-create-info demo_region db_yhm_city city_data_only.sql # 导入时用 INSERT IGNORE 避免主键冲突 mysql -u root -p demo_region --executeSET SESSION sql_mode; SOURCE city_data_only.sql;--no-create-info适合表结构已经存在、只需要刷新数据的场景。配合INSERT IGNORE或REPLACE INTO可以在不删表的情况下更新行政区划数据。我一般会在每次数据更新前先CHECKSUM TABLE记录旧值导入后再校验一次确认数据确实变了且没有丢行。从那以后我每次导入省市区数据都强制走一遍“建表→导入→ANALYZE→CHECKSUM→抽样查询”的流程再也没出现过上线后地址下拉框空白的事故。希望帮到你。本文还有配套的精品资源点击获取
