全球地区维表设计：国家省市三级建模与数据清洗导入避坑

简介一套包含全球主要国家、省/州及城市的地区数据库以中英文双版本提供适用于Web开发、数据分析、地图应用及行政区划映射场景。数据采用XML格式组织结构清晰便于直接解析和二次加工中英文文件可独立使用也可按需对照。压缩包内共2个XML文件整体体积约99KB轻量易部署不会增加项目加载负担。已有1527人学习下载常用于地区选择联动、物流区域匹配、统计报表维度构建等实际任务。通过这份资源开发者可快速获得规范化的全球区域层级列表省去手工搜集与翻译多语言数据的繁琐过程同时为原型系统或正式项目提供可直接引用的基础数据源无论是前端地区选择组件、后端数据字典初始化还是统计报表的维度整理都能直接复用有效缩短前期数据准备时间。1. 一份全球地区数据库当基础维表省下的不只是开发时间“全球地区 国家 省/洲 城市”这组数据在真实项目里属于那种不起眼、但缺了就要命的基础配置。做跨境电商结算页用户要选国家、省或州、城市三层做物流运费模板要按省/州设价格做后台菜单需要三层联动下拉框做区域销售分析又得拿城市维表做汇总。要是没有一份可靠的双语基础数据就只能自己从零攒过程中还要跟乱码、拼音误写、重名地区反复拉扯。这份资源把国家、省/州、城市按三层拆成关联表中英文共存于同一行拿到手导入数据库基本就能当基础维表用。适合后端开发、数据工程师、BI 分析师也适合被数据治理折腾到崩溃的运营同学。2. 数据模型与建表三级表拆开存查询和统计才不会翻车2.1 三个层级为什么要各建一张表先解决一个最核心的问题为什么要把地区数据拆成三张表而不是一张表加 parent_id 搞定。原因是全球行政体系层级深度不统一。中国是省—市两级美国是州—县—市日本是县—市英国是郡—区。如果全塞进一张表有些行是一级有些行又是二级每条记录既要挂 parent_id 又要标 level查整棵树的 SQL 写起来满天飞。等数据量上了十万一次递归查询就可能把数据库连接拖到超时。拆成三张独立表之后每一层的职责很明确t_country 只存国家t_province 存省级和州级t_city 存市级。父子关系直接靠外键绑定业务查询永远只碰一层。比如做下拉框第一步按SELECT id, name_en, name_cn FROM t_country WHERE is_active 1取国家第二步再按WHERE country_id ?取省份每一条查询都能走一层索引逻辑上就是“选什么查什么”。这样即使放在“国家—省/州—城市”三层结构跨度很大的项目里也不会出现一个查询把整棵地理树捞出来的尴尬场景。另外三张表拆开之后更容易在 BI 工具里关联建模。国家、省、市三张表天然对应星型模型中的三级维表订单表只要挂上 city_id就能逐层上卷到省级、国家级汇总。如果当初拍平到一张大宽表报表开发就得每天处理“某城市存在两个父级”这种结构性错误。2.2 中英双字段放同一行查询省一次 join校正少一半工作量很多初接手的人会问“中英文为什么不做成两张表或者干脆放两个字段就行”这里的关键是中英文放在同一行有两个实打实的好处一是查询时不用额外 join 语言表一条 select 直接拿全两个字段二是做译名校正时同一行里同时有 name_en 和 name_cn可以用脚本逐行比对发现问题直接 update 那一行。如果拆成中英文两张表还得维护 language_code字段数量翻倍且做一次批量同步随时出现遗漏。字段长度上英文名我给 varchar(128)中文名给 varchar(64)。这不是拍脑袋。英文行政名的完整拼写经常超长某些州级单位的名称为“District of Columbia”这类如果只留 32 字符写入的时候就会被 truncate等到前端展示才发现少一段。中文名 64 字符看起来富余很多但考虑到少数地区的官方中文名包含括号和“自治州”等后缀多留一点空间总是对的。表默认字符集务必使用 utf8mb4而不是 utf8。原因是生僻字和部分扩展区符号只有 utf8mb4 能完整存储曾经有人因为表字符集配成 utf8mb3结果某个自治州名称写入直接报错整批数据倒腾了一遍才解决这种教训提前避掉不亏。2.3 建表脚本与索引策略一份能直接落地的 DDL这里给出在 MySQL 8.0 上可直接执行的建表脚本三张表依次对应国家、省/州、城市三层-- 地区数据库国家、省/州、城市三张维表 CREATE DATABASE IF NOT EXISTS region_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE region_db; -- 国家表 CREATE TABLE t_country ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, iso_alpha2 CHAR(2) NOT NULL COMMENT ISO 二位字母码, iso_alpha3 CHAR(3) NOT NULL COMMENT ISO 三位字母码, name_en VARCHAR(128) NOT NULL COMMENT 英文名称, name_cn VARCHAR(64) NOT NULL COMMENT 中文名称, region VARCHAR(32) DEFAULT NULL COMMENT 所属大洲或统计区域, is_active TINYINT NOT NULL DEFAULT 1 COMMENT 1启用 0停用, UNIQUE KEY uk_iso2 (iso_alpha2), KEY idx_country_name_en (name_en) ) ENGINEInnoDB COMMENT国家维表; -- 省级/州级表 CREATE TABLE t_province ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, country_id INT UNSIGNED NOT NULL COMMENT 所属国家id, province_code VARCHAR(16) NOT NULL COMMENT 省/州代码, name_en VARCHAR(128) NOT NULL COMMENT 英文名称, name_cn VARCHAR(64) NOT NULL COMMENT 中文名称, is_active TINYINT NOT NULL DEFAULT 1, UNIQUE KEY uk_country_province (country_id, province_code), KEY idx_province_name_cn (name_cn), CONSTRAINT fk_province_country FOREIGN KEY (country_id) REFERENCES t_country (id) ) ENGINEInnoDB COMMENT省级维表; -- 市级表 CREATE TABLE t_city ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id INT UNSIGNED NOT NULL COMMENT 所属省/州id, city_code VARCHAR(16) NOT NULL COMMENT 市级代码, name_en VARCHAR(128) NOT NULL COMMENT 英文名称, name_cn VARCHAR(64) NOT NULL COMMENT 中文名称, is_active TINYINT NOT NULL DEFAULT 1, UNIQUE KEY uk_province_city (province_id, city_code), KEY idx_city_name_en (name_en), CONSTRAINT fk_city_province FOREIGN KEY (province_id) REFERENCES t_province (id) ) ENGINEInnoDB COMMENT市级维表;脚本里几个细节值得留意。首先是唯一键的设计t_province 用 (country_id, province_code) 做联合唯一键t_city 用 (province_id, city_code) 做联合唯一键。这个设计决定了表能否承受重复导入。如果只依赖自增 id 做主键第二次倒入同一份源数据必然整表重复。有了联合唯一键重复导入最多报 Duplicate entry不会产生脏数据。外键我在建表时直接加上了。有人担心外键拖慢导入但几十万行以内的数据量分批导入时外键检查的消耗没那么明显反而能在第一时间发现“城市挂到错误的省份下面”这类血缘错误。后面如果数据量真到百万级可以再讨论临时关闭外键检查的利弊后面避坑章节也会单独讲。索引方面除了唯一键我在 name_en 和 name_cn 上各建了一个普通二级索引。这套索引看起来简单但覆盖了最常见的按名称查地区场景。用户在前端输入“San Francisco”直接搜城市走索引比全表扫描快一个数量级运营后台按中文名搜省份也同理只要表数据量上到百万行这种差值就会特别明显。3. 数据清洗与转换把原始文件整成字典级干净记录的全过程3.1 源文件常见的三类脏数据无论这份资源给你的是 CSV、Excel 还是 JSON我拿到手之后的动作基本都是先盘点三件事重复、空值、编码。重复最典型的是同一 ISO 两位码出现两行或者同一 wharf。比如某个国家拆分前后两份历史数据被合并到同一份文件里没有做过去重处理直接用会让导入阶段不停报主键冲突。空值则集中在 name_cn因为很多城市只有英文名中文是从另一张翻译表里拼接过来的拼接漏了就会留空。编码问题更是经典源文件有时是 GBK 保存你按 utf-8 读第一行就开始乱码导出再读又遇到 UnicodeDecodeError一晚上就这么烧掉了。还有一个容易被新手忽略的点——“重名”。同名城市在全世界非常多某个中文名为“新城”的城市在不同省份下可能有七八个。如果只拿城市名去重会把属于不同省份的同城直接给删掉一行这种误伤比漏掉重复数据更可怕。正确做法是重名问题永远要结合“父级代码”一起看。换句话说只有“同一个国家 同一个省份代码 同一个城市代码”才判定为重复。3.2 清洗脚本去重、兜底和列映射的具体写法下面这段脚本假定你已经拿到一份原始 CSV列包含 iso_alpha2、province_code、city_code、name_en、name_cn。我用 pandas 做清洗注释里写了每一步的意图# -*- coding: utf-8 -*- import pandas as pd # 读取原始文件如果源文件是 GBK 编码把 encoding 改成 gbk df_raw pd.read_csv(raw_region.csv, encodingutf-8-sig) # 统一转成字符串并去掉首尾空格避免 A市 与 A市 变成两行 for col in [iso_alpha2, province_code, city_code, name_en, name_cn]: df_raw[col] df_raw[col].astype(str).str.strip() # 按国家、省/州、城市三层代码组合去重不能单看城市名 df_raw df_raw.drop_duplicates( subset[iso_alpha2, province_code, city_code] ) # 中文名缺失时先用英文名兜底后续再单独补译名 df_raw[name_cn] df_raw[name_cn].fillna(df_raw[name_en]) # 过滤掉省份代码明显非法的行比如空值或带空格 df_raw df_raw[df_raw[province_code].str.match(r^[A-Za-z0-9\-]$)] # 输出清洗后的文件 df_raw.to_csv(cleaned_region.csv, indexFalse, encodingutf-8-sig)这段脚本做了四个关键动作。第一所有字段先转字符串再 strip这一步能把“北京市”和“北京市 ”这类肉眼看不出来的重复消掉。第二用三层代码组合键做去重保证不同省份下的同名城市不被误删。第三中文名缺失时填英文名至少让后续导入不会出现空选项。第四用正则过滤掉 province_code 里带空格、全角符号等非法字符的行这类行导入后迟早是外键或查询的隐患。要注意fillna 的兜底策略不是所有场景都适用。如果这份数据是要给中文 BI 报表用的中文名缺失时顶一个英文名报表里会出现一半英文一半中文的混杂状态但如果只是给后台表单做地区选项英文名兜底反而比空值好看。所以清洗之前要先想清楚最终做成什么再决定“空值丢弃”还是“空值兜底”。3.3 导入前自检三张表的一致性清单清洗完不能直接导入我一般还会在导入前做一轮一致性校验步骤不多但能拦住八成以上的导入失败第一步检查国家表。每个 iso_alpha2 都应该刚好两个字母且是小写转大写后的结果如果出现 NULL直接剔除或补录。第二步检查省级表。每个国家至少有一行省级数据如果没有说明该国家在源数据里是“无省国家”——这类国家在行政划分上直接到城市需要人工补一个虚拟省级代码比如N/A否则城市表会出现无父级指向的孤儿记录。第三步检查市级表。每个省份至少有一行市级数据同时所有 city_code 能对应到已清洗好的省份代码。这里最容易出问题的是上一个版本行政区的城市代码作废导致城市表关联不到省份。这些自检步骤看着基础但真能拦下后期才炸的问题。例如市级数据里出现省份代码根本不存在如果跳过清洗直接导入外键报错会把你拖进半小时的排查循环而提前做一遍 left join 空值扫描几分钟就能定位。3.4 译名对齐警惕拼音被当成英文名的场景做中英文地区数据有一个特别容易踩的“软性坑”某些中文源的提供方会把中文名转成拼音直接填进 name_en 字段比如把“Beijing”写成“bei jing”或者“Hangzhou”写成“hang zhou”。清洗时如果只做空格去除那得到的还是小写拼音跟国际标准拼写存在差异。我的处理方法是单独写一轮拼音识别逻辑不是对所有英文列生效。判断条件大致是某个英文名字段里出现“小写拼音 空格 小写拼音”这种连续模式而且该国家明确在东亚地区范围内就把它修正为标准拼写如果字段本来是全英文单词比如“New York”就没必要碰。这套逻辑听起来像玄学但它比统一转首字母大写要安全得多因为统一转大写会把真正的英文词组里的每个单词都变成标题格式反而破坏了原文本。4. 导入与排查避坑乱码、外键、唯一键和慢查询的四个真实场景4.1 导入顺序和导入方式怎么选三张表的依赖关系决定了导入顺序必须是国家 → 省份 → 城市。顺序一旦反了第一次插子表就会报外键约束错误。我第一次导入时图省事先把城市表 insert 进去结果 MySQL 直接拒绝当时怀疑是数据有问题逐行排查了半天最后才发现是导入顺序写反。后来我把这个顺序写进项目文档里团队里每个人导入参考同一步骤再没人犯过同样的错。导入方式分两种来看。本地开发库几千行数据直接用 INSERT 语句也没问题线上库数据量大可以用 MySQL 的 LOAD DATA LOCAL INFILE 加速写入。不过我更建议在导入时保留外键约束别一上来为了图快就SET FOREIGN_KEY_CHECKS 0。外键这道防线很重要它能在数据刚进库的时候拦住孤儿记录总比导入完成后才发现脏数据要强。4.2 现象导入后中文全变成问号或乱码导入完成打开表一看原本应该显示“某省”的地方变成一列问号或者 SELECT 出来是一堆“å”开头的乱码字符串。这是中文本地化项目里最常见的翻车现场。原因通常不是表字段配错而是连接层字符集和表字符集不一致。比如建表用的是 utf8mb4但客户端连接的 character_set_results 被设置成 latin1数据在写入前就做了错误转码。还有一种情况是源 CSV 文件是 GBK 编码pandas 读取时没有指定 encoding导致内存里就已经是乱码状态后续不管怎么导入都救不回来。解决分三步。第一步确认库表字符集是 utf8mb4第二步在执行导入前跑一次SET NAMES utf8mb4;第三步导入后抽查用SELECT HEX(name_cn) FROM t_province LIMIT 5;看返回的是不是正常的 UTF-8 十六进制串。如果发现是乱码我的习惯是把表备份出来重新用正确的编码转换一遍再导而不是在乱码基础上做字符串替换越替换越脏。4.3 现象市级表导入中断提示外键约束失败导入城市表时报错 “Cannot add or update a child row: a foreign key constraint fails”并且日志里指到某个 province_id 不存在。这时候第一反应是去源文件里核对数据但往往查不出明显的非法值。实际原因是源数据里存在“孤儿行”——城市记录引用的省份在省级表里不存在。这通常来自行政区划调整旧省会代码被废弃但城市数据没有同步更新于是县级城市挂在了一个不存在的省份编码下面。解决这类问题我推荐在清洗阶段就做关联校验而不是等到导库报错才回来查。具体 SQL 写法是SELECT c.id, c.name_cn, c.province_id FROM t_city c LEFT JOIN t_province p ON c.province_id p.id WHERE p.id IS NULL;把查出来的这些行单独导出人工判断是补一份省份数据还是直接丢弃。如果只是个别历史遗留行建议先丢弃保留下一个干净的维表如果数量很大说明省级表本身有缺失需要回到源数据去补省份信息。4.4 现象唯一键冲突导致清洗脚本白跑清洗脚本跑完去重也做了结果导入时日志刷出几百行 Duplicate entry唯一键冲突到停不下来。原因在于去重逻辑只看了城市名没看“省份 城市”整体。同一个城市在不同源文件里可能出现两种拼写比如 “St. Paul” 和 “Saint Paul”如果用城市名做去重 subset系统会认为这是两条记录偏偏数据库中联合唯一键是 (province_id, city_code)于是第二次出现时直接撞唯一键。解决方法是严格按照表结构里的唯一键约束去设置去重字段。也就是 pandas 里写成subset[iso_alpha2, province_code, city_code]让重复检测的粒度跟数据库唯一键完全一致。如果城市代码缺失再用 name_en 和 name_cn 的组合做补充但依然要带上省份编码。唯一键设计成什么清洗逻辑就必须向它对齐这是数据入库前最重要的一条纪律。4.5 现象三级联动查询超过 1 秒下拉框卡到用户骂人场景是前端作国家、省、城市三类下拉联动后端写了一条 UNION 查询一次性拉出所有层级结果页面每次切换都等上 1 秒多。原因很典型UNION 把三张表的数据合并成大结果集之后又在外层做了过滤或分组。这种 SQL 很难利用上三张表各自的索引因为 MySQL 需要先把 UNION 的结果物化再执行外层条件等于每层结构都扫一遍全表。索引在这里根本发挥不出来纯属物理扫描。解决思路是把三张表拆开查询在接口层分别做三次 SQL第一次只查国家第二次根据 country_id 查省份第三次根据 province_id 查城市最后组一个嵌套 JSON 返回前端。每一层查询都可以走唯一索引响应时间能控制在几十毫秒。很多人觉得多查一次数据库不划算但在维表场景里三次索引命中的成本几乎可忽略而一条 UNION 全表扫描的代价会随着数据量增长快速放大。5. 进阶用法把数据变成能交付的下拉菜单与维度宽表5.1 三层联动下拉接口的一种简单写法落地到实际项目里最常见的使用方式就是提供三层联动接口。前端需要的数据结构一般长这样{ country_id: 1, country_name_cn: 某国家, provinces: [ { province_id: 10, province_name_cn: 某省, cities: [ { city_id: 100, city_name_cn: 某市 } ] } ] }后端可以在一次请求里做三次查询而不是用一条大 SQL 拼接。三次查询分别命中 t_country、t_province、t_city 的索引数据量在几十万行级别时响应时间基本无感# 伪代码三层联动数据聚合 def get_region_tree(country_idNone): countries db.query( SELECT id, name_cn FROM t_country WHERE is_active1 ) for c in countries: provinces db.query( SELECT id, name_cn FROM t_province WHERE country_id%s AND is_active1, c[id] ) for p in provinces: cities db.query( SELECT id, name_cn FROM t_city WHERE province_id%s AND is_active1, p[id] ) p[cities] cities c[provinces] provinces return countries这段伪代码展示了“逐层查、逐层挂”的模式。好处是每一层查询都走索引而且数据量增长时只需要给对应表加缓存不需要改 SQL。5.2 按需裁剪只保留中英字段并生成宽表如果项目中需要把三张维表合成一张宽表方便 BI 报表做 join可以用一次简单的 SQL insert 完成。把 country、province、city 三张表横向关联生成带完整路径的宽表CREATE TABLE region_wide AS SELECT c.id AS city_id, c.name_en AS city_name_en, c.name_cn AS city_name_cn, p.id AS province_id, p.name_en AS province_name_en, p.name_cn AS province_name_cn, ct.id AS country_id, ct.iso_alpha2, ct.name_en AS country_name_en, ct.name_cn AS country_name_cn FROM t_city c JOIN t_province p ON p.id c.province_id JOIN t_country ct ON ct.id p.country_id;如果你的数据量不大这张宽表可以直接给 BI 工具做热力图、区域报表省去每次报表都要跨三表 join 的操作。但要注意宽表只适合只读场景。一旦源表做增量更新宽表必须同步重建否则会出现“城市新增了但报表里看不到”的时效问题。5.3 我每次更新数据后强制执行的动作最后说一个我自己的习惯也是踩出来的经验每次拿到新版地区数据不管源文件看起来多干净我都强制自己走一遍“导入前自检 导入后抽样核对”的流程。具体就三步先跑唯一键去重再跑外键孤儿检查最后随便抽三个国家分别查一下国家—省—市三层是否都能闭合。这三步花不了五分钟但能拦住绝大多数后期的“黑匣子”问题。以前有一回我偷懒跳过自检结果某个地区的省代码新旧版本混用导致前端三级联动在某个省份下永远拉不出城市列表排查过程花了整整一下午。从那以后每次接地区数据我都先清洗再导入清洗完还要再抽查一遍宁可多做一道工序也不愿后期对着数据发呆。希望这个流程能帮你少走一段弯路。本文还有配套的精品资源点击获取