全国省份城市数据库表：MySQL行政区划表设计与导入实战

简介这份资源面向需要在中国行政区划数据上做开发的 MySQL 使用者提供一份可直接导入的全国省份城市数据库表脚本适合搭建地理信息系统、物流管理、人口统计分析等需要地域信息的应用场景也适合作为学习 SQL 建表与层级数据设计的练习素材。压缩包内共 1 个文件为 sql 脚本整体约 69KB脚本中通常包含建表与数据插入语句可快速生成带层级关系的行政区划表。目前已有 638 人学习下载说明其在同类数据资源中具备一定参考价值。读者导入后即可获得省、市、区县等层级数据并可通过父级 ID 关联构建完整行政链便于与人口、公司地址等业务表联合查询同时可参考其字段设计与索引思路用于优化地域筛选性能并据此定期更新以适配行政区划变更。1. 全国省份城市数据库表一份被低估的“地基级”数据资产做后端的人大概都经历过这种场景产品经理丢过来一句“用户注册要选地区省市区三级联动”你打开数据库一看只有一张用户表地区字段还是 varchar(50) 随手填的。这时候临时去网上找一份省市区数据格式五花八门有的用拼音当主键有的把“市辖区”这种虚拟层级也塞进去导入之后才发现对不上号。全国省份城市数据库表mysql这个方向解决的正是这类“地基级”问题——它把全国省、市、区县的行政区划数据整理成可直接导入 MySQL 的结构化表让你在用户地址、订单归属、物流分区、数据看板这些场景里不用再手搓字典。这份数据适合谁做电商、SaaS、本地生活、CRM 的后端和全栈开发者尤其是需要做地区级联选择、按区域统计、地址解析的团队。它的价值不在于技术多高深而在于“省事且不出错”——行政区划代码是国家标准自己维护迟早会翻车。接下来我会按“表怎么设计、数据怎么导、查询怎么写、坑在哪”的顺序把这份数据库表从拿到手到跑通的全过程讲清楚。2. 表结构怎么设计三张表还是单表自关联2.1 行政区划的层级本质与两种建模思路全国行政区划在国家标准里是三级为主、部分四级的结构省级省、自治区、直辖市、特别行政区、地级地级市、地区、自治州、盟、县级市辖区、县级市、县、自治县、旗等部分地方还有乡镇街道级。做业务系统时绝大多数场景只需要到县级乡镇级数据量大且变动频繁一般按需再补。建模上有两条路。第一条是单表自关联一张region表字段包含id、parent_id、name、level省级记录的parent_id为 0市级指向省级 id县级指向市级 id。优点是表少、扩展灵活加乡镇级只是多一层缺点是查询三级联动要递归或多次查询写 SQL 时容易绕。第二条是三张独立表province、city、district各自带province_id、city_id外键。优点是查询直观、联表快、前端拿数据简单缺点是层级固定加层级要改表结构。我一般会选单表自关联因为行政区划本身是树用树的方式存最自然而且很多开源地区数据包也是这个结构迁移成本低。下面给出我常用的建表语句字段命名和索引都按实际查询习惯来。CREATE TABLE region ( id INT UNSIGNED NOT NULL COMMENT 行政区划主键建议直接用国标6位码, parent_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 父级id省级为0, name VARCHAR(64) NOT NULL COMMENT 行政区划名称, short_name VARCHAR(32) DEFAULT NULL COMMENT 简称如“京”“沪”, level TINYINT UNSIGNED NOT NULL COMMENT 层级1省 2市 3区县, code CHAR(6) NOT NULL COMMENT 国家标准6位行政区划代码, pinyin VARCHAR(64) DEFAULT NULL COMMENT 拼音用于搜索, lng DECIMAL(10,6) DEFAULT NULL COMMENT 经度, lat DECIMAL(10,6) DEFAULT NULL COMMENT 纬度, sort SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 排序权重, PRIMARY KEY (id), UNIQUE KEY uk_code (code), KEY idx_parent (parent_id), KEY idx_level (level), KEY idx_name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT全国行政区划表;这里有几个参数值得说。id直接用国标 6 位码而不是自增好处是业务里存地区 id 时天然可读比如 110000 就是北京110101 就是东城区排查数据时一眼能看出层级关系。parent_id省级填 0这样查询“所有省”就是WHERE parent_id 0比level 1更符合树结构习惯。code加唯一索引防止导入重复数据。pinyin字段别省前端做地区搜索时“beijing”能匹配到北京体验提升明显。lng、lat用于地图打点或距离计算没有就留空。2.2 字段类型与索引的取舍细节name用VARCHAR(64)而不是CHAR因为“内蒙古自治区”“新疆维吾尔自治区”这类名称长度不一CHAR会浪费空间。level用TINYINT足够1 到 4 的取值范围。sort字段用于控制前端下拉框顺序比如直辖市排前面或者按拼音排序导入数据时给个默认值后续运营可调。索引方面idx_parent是三级联动查询的核心SELECT * FROM region WHERE parent_id 110000走这个索引。idx_level用于按层级筛选比如只查省级。idx_name支持名称模糊搜索但注意LIKE %北京%这种前置通配符用不上索引如果搜索频繁建议上全文索引或外部搜索引擎这个后面避坑章节会细说。提示如果业务确定永远只用三级且对查询性能极度敏感三张独立表也是合理选择。但大多数项目活不过三年就会遇到“加个街道级”的需求单表自关联的扩展性更稳。3. 数据导入实战从 zip 到可查询的完整流程3.1 解压后先看清文件格式再动手拿到全国省份城市数据库表mysql.zip 之后别急着往数据库里灌。先解压看目录结构常见的有几种一种是直接给.sql文件INSERT语句已经写好导入即用一种是给.csv或.txt需要自己写LOAD DATA还有一种是给.json得用脚本转换。不同格式处理方式差别很大先确认再操作能省掉大量返工。假设解压后得到region.sql里面是建表加插入语句那最省事。但实际项目中我更推荐拿到原始数据后自己控制导入过程因为直接执行别人的.sql可能字符集不对、sql_mode不兼容或者插入顺序导致外键报错。下面按“先建表、再批量导入、最后校验”的流程走。# 解压后查看文件列表和大小确认数据格式 unzip -l 全国省份城市数据库表mysql.zip # 假设解压出 region.sql先看前 50 行了解结构 unzip -p 全国省份城市数据库表mysql.zip region.sql | head -n 50 # 检查文件编码避免中文乱码 file -i region.sqlunzip -l只列出内容不解压适合快速判断。unzip -p把文件输出到标准输出配合head看开头不用先解压到磁盘。file -i看编码如果是iso-8859-1或gbk导入前要转成utf-8否则中文名称会变问号。这一步很多新手跳过结果导入后满屏乱码回头查半天。3.2 用 LOAD DATA 批量导入 CSV 的完整命令如果数据是 CSV 格式用LOAD DATA LOCAL INFILE比逐条INSERT快一个数量级。假设 CSV 每行是id,parent_id,name,level,code没有表头字段用逗号分隔字符串用双引号包裹。先建好上面的region表然后执行导入。-- 导入前临时关闭外键检查避免插入顺序问题 SET FOREIGN_KEY_CHECKS 0; LOAD DATA LOCAL INFILE /path/to/region.csv INTO TABLE region CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (id, parent_id, name, level, code) SET short_name NULL, pinyin NULL, lng NULL, lat NULL, sort 0; SET FOREIGN_KEY_CHECKS 1;CHARACTER SET utf8mb4必须和文件实际编码一致否则中文出错。OPTIONALLY ENCLOSED BY 处理带引号的字段比如名称里本身有逗号的情况。IGNORE 1 LINES跳过表头如果 CSV 没有表头就去掉这行。最后的SET子句给未提供的字段填默认值避免NULL约束报错。导入完成后用SELECT COUNT(*)核对行数再抽查几个省级记录确认层级正确。-- 校验省级数量应为 34 左右含港澳台 SELECT COUNT(*) FROM region WHERE parent_id 0; -- 校验每个省下面的市数量是否合理 SELECT p.name AS province, COUNT(c.id) AS city_count FROM region p LEFT JOIN region c ON c.parent_id p.id WHERE p.parent_id 0 GROUP BY p.id ORDER BY city_count DESC LIMIT 10;第二条查询能快速发现数据缺失比如某个省下面只有 1 个市大概率是导入不完整。正常省份下辖市数量在几个到二十几个之间异常值要人工核对。3.3 导入后必做的三项数据校验导入不是终点校验才是。第一项查孤儿记录parent_id指向的 id 不存在。第二项查层级矛盾市级记录的parent_id对应的是不是省级。第三项查代码重复code唯一索引虽然能挡但导入时如果用了IGNORE会静默跳过得主动查。-- 孤儿记录父级不存在 SELECT r.id, r.name, r.parent_id FROM region r LEFT JOIN region p ON r.parent_id p.id WHERE r.parent_id ! 0 AND p.id IS NULL; -- 层级矛盾市级但父级不是省级 SELECT c.id, c.name, c.level, p.level AS parent_level FROM region c JOIN region p ON c.parent_id p.id WHERE c.level 2 AND p.level ! 1; -- 代码重复检查唯一索引存在时一般不会但导入脚本可能绕过 SELECT code, COUNT(*) FROM region GROUP BY code HAVING COUNT(*) 1;这三条查询跑完没问题数据基本可用。如果发现孤儿记录多半是 CSV 里父级 id 写错或缺失需要回到源文件修正后重新导入。层级矛盾常见于把“市辖区”当成市级处理实际它属于县级level应为 3。4. 查询与接口三级联动、按名搜索和区域统计怎么写4.1 三级联动的两条 SQL 与缓存策略前端三级联动最典型的请求是初始化加载所有省选中省后加载对应市选中市后加载对应区县。对应三条查询但本质是同一条 SQL 换parent_id。-- 加载所有省级 SELECT id, name, short_name FROM region WHERE parent_id 0 ORDER BY sort, id; -- 加载某省下的市假设省 id 为 110000 SELECT id, name FROM region WHERE parent_id 110000 ORDER BY sort, id; -- 加载某市下的区县假设市 id 为 110100 SELECT id, name FROM region WHERE parent_id 110100 ORDER BY sort, id;这三条查询都走idx_parent索引数据量小响应在毫秒级。但高并发下每次都查库不划算我一般会在应用层加缓存省级数据几乎不变启动时加载到内存或 Redis市级和县级按需缓存key 用region:children:{parentId}。行政区划调整频率很低缓存过期时间可以设长比如 24 小时配合手动刷新接口应对变更。注意缓存 key 别用中文名称用 id。名称可能重复比如多个省都有“城关区”用 id 才能唯一区分。4.2 按名称或拼音搜索地区的实现用户输入“朝阳”想找朝阳区输入“beijing”想找北京这类搜索用LIKE能实现但性能差。数据量在三千条左右时LIKE %朝阳%还能接受但如果有乡镇级数据到几万条就得优化。-- 简单模糊搜索数据量小时可用 SELECT id, name, level, parent_id FROM region WHERE name LIKE CONCAT(%, 朝阳, %) OR pinyin LIKE CONCAT(%, beijing, %) LIMIT 20; -- 优化前缀匹配能用上索引 SELECT id, name FROM region WHERE name LIKE 北京% OR pinyin LIKE beijing%;前缀匹配LIKE 北京%能用上idx_name但%北京%不行。如果搜索需求强建议加全文索引或者把地区数据同步到搜索引擎。另一个实用技巧是给pinyin字段存全拼和首字母缩写比如北京存beijing和bj用户输入bj也能匹配。-- 增加首字母缩写字段后搜索更灵活 ALTER TABLE region ADD COLUMN pinyin_abbr VARCHAR(16) DEFAULT NULL COMMENT 拼音首字母缩写; -- 假设已填充数据查询时 SELECT id, name FROM region WHERE pinyin_abbr bj OR pinyin LIKE beijing%;4.3 按区域聚合统计的 SQL 写法数据看板常需要按省或按市统计订单量、用户数。如果业务表里存的是地区 id直接GROUP BY再关联region表取名称即可。-- 按省统计订单量假设 orders 表有 region_id 字段 SELECT p.name AS province, COUNT(o.id) AS order_count FROM orders o JOIN region r ON o.region_id r.id JOIN region c ON r.parent_id c.id JOIN region p ON c.parent_id p.id WHERE p.parent_id 0 GROUP BY p.id ORDER BY order_count DESC;这里假设orders.region_id存的是县级 id通过两次自关联上溯到省级。如果业务表直接存了省级 id就省掉中间关联。关联层级多时注意索引region表的parent_id索引在这里起作用。统计结果为空通常是region_id存了不存在的值或者层级上溯断了用前面的孤儿记录查询能定位。5. 避坑与排查导入和使用中最容易翻车的五个点5.1 中文乱码现象是名称显示问号原因是字符集不统一导入后SELECT出来中文变成???或乱码九成是字符集问题。CSV 文件可能是 GBK 编码而数据库连接和表都是 utf8mb4中间转换丢失。解决分三步先用file -i确认文件编码用iconv -f GBK -t UTF-8转成 UTF-8导入时LOAD DATA指定CHARACTER SET utf8mb4建库建表统一用 utf8mb4连接串也加characterEncodingutf8。三处一致才不会乱。5.2 层级错乱现象是三级联动选不出区县原因是 parent_id 对不上前端选完市之后区县列表为空查库发现该市下面没有记录或者记录的parent_id指向了别的市。这通常是导入时 CSV 的父级 id 列错位或者源数据本身把某个区县挂错了父级。排查用前面给的孤儿记录查询和层级矛盾查询定位到具体记录后回源文件修正。预防办法是导入前先用脚本校验parent_id是否都能在id列中找到。5.3 重复导入现象是数据翻倍原因是没做唯一约束或用了 INSERT IGNORE第二次执行导入脚本时数据量变成两倍因为INSERT没有去重。解决是给code加唯一索引导入用INSERT ... ON DUPLICATE KEY UPDATE或REPLACE INTO或者导入前TRUNCATE TABLE清空重来。生产环境慎用TRUNCATE如果表被其他业务外键引用会报错改用DELETE加条件。5.4 查询慢现象是地区下拉加载卡顿原因是全表扫描或缓存缺失数据量不大时一般不会慢但如果parent_id没索引或者搜索用了LIKE %关键词%就会全表扫描。先EXPLAIN看执行计划确认走没走索引。另一个原因是每次请求都查库没缓存加 Redis 缓存后 QPS 能上去。还有个小坑是ORDER BY sort, id如果sort没索引排序也会耗时数据量小时不明显大了要补索引。5.5 行政区划变更现象是用户反馈“我们这撤县设区了”原因是数据没更新行政区划不是一成不变的撤县设区、合并、更名每年都有。静态导入的数据过一两年就可能过时。解决是建立更新机制定期从权威渠道获取最新数据对比code和name变化增量更新。业务上如果地区 id 已经写入订单变更时不要改 id只改名称避免历史数据关联断裂。这个坑不常遇到但遇到就是数据一致性问题提前留好更新入口。6. 进阶技巧把地区表用出花来的三个习惯第一个习惯是给地区表加一个“路径”字段存从省到当前的完整 id 路径比如110000,110100,110101。这样查“某省下所有订单”不用递归上溯直接WHERE region_path LIKE 110000%就能命中配合前缀索引效率很高。代价是插入和更新时要维护路径但行政区划几乎不变一次生成长期受益。ALTER TABLE region ADD COLUMN path VARCHAR(128) DEFAULT NULL COMMENT 层级路径如110000,110100,110101; -- 生成路径的更新语句需按层级顺序执行 UPDATE region SET path CONCAT(parent_id, ,, id) WHERE level 2; UPDATE region r JOIN region p ON r.parent_id p.id SET r.path CONCAT(p.path, ,, r.id) WHERE r.level 3;第二个习惯是导出时保留一份 JSON 树结构前端直接拿树渲染级联组件省掉多次请求。用一条 SQL 查出所有数据在应用层组装成嵌套结构或者用 MySQL 8 的递归 CTE 直接出树。-- MySQL 8 递归 CTE 查完整树 WITH RECURSIVE region_tree AS ( SELECT id, parent_id, name, level, CAST(id AS CHAR(200)) AS path FROM region WHERE parent_id 0 UNION ALL SELECT r.id, r.parent_id, r.name, r.level, CONCAT(rt.path, ,, r.id) FROM region r JOIN region_tree rt ON r.parent_id rt.id ) SELECT * FROM region_tree ORDER BY path;第三个习惯是给地区表配一个轻量校验接口业务写入地址前先校验region_id是否存在且层级正确避免脏数据进订单表。校验逻辑很简单查一次region表确认 id 存在再确认其level符合预期。这个接口调用频繁走缓存即可。我自己踩过最深的坑是早期做项目时图省事把地区名称直接存进订单表后来一个市更名历史订单和统计报表全对不上只能写脚本批量刷数据。从那以后我坚持只存 id名称通过关联查询或缓存取。这个习惯看起来多一步但省掉了后面无数对账的麻烦。希望帮到你。本文还有配套的精品资源点击获取