全球国家省州城市数据库中英双语设计与导入实战

简介这是一份面向地理信息系统开发、跨境电商、物流配送及数据可视化从业者的全球行政区划基础数据包用于解决多语言场景下国家、省/州、城市三级联动与地址库搭建问题。资源以XML结构化格式组织包含中英文两个独立文件便于按语言环境分别加载或做双语对照。压缩包共2个文件均为xml类型整体约99KB体积轻量适合直接嵌入项目或作为数据库初始化脚本使用。目前已有1526人学习下载说明其在同类基础数据中具备一定参考价值。读者可获得覆盖全球的国家、省/州、城市层级关系中英文名称一一对应能快速用于下拉选择、地址解析、区域统计等场景XML结构清晰字段规整便于解析为JSON、导入MySQL或转换为前端树形组件所需格式减少自行采集与校对成本适合需要快速搭建地区数据底座的开发者与数据分析人员。1. 全球国家、省/州、城市数据库一份中英双语地理底表能省掉多少脏活做跨境业务、物流计费、用户画像或者多语言表单时绕不开一个基础问题地址里的国家、省/州、城市到底怎么存、怎么显示、怎么校验。很多团队一开始觉得这事简单随手在代码里写几个字符串等到要做中英双语切换、要做下拉联动、要做数据统计时才发现省市区数据是一张需要长期维护的底表不是几个常量。全球国家、省/州、城市数据库中英版解决的正是这件事它把国家、一级行政区、城市三层结构连同中文名、英文名、编码、层级关系一起固化下来让前端下拉、后端校验、报表聚合都有统一来源。这篇文章面向需要落地这套数据的后端、前端和数据工程师讲清楚数据从哪来、表怎么设计、中英怎么对齐、导入怎么不翻车。适合正在做国际化产品、地址中台或数据仓库的人照着复现。2. 全球行政区划数据的结构设计与中英对齐2.1 三层结构为什么不能拍脑袋定成两张表行政区划天然是树形结构国家下面是一级行政区省、州、邦、大区一级行政区下面是城市或县。很多人第一反应是建两张表一张国家表、一张城市表城市表里塞一个省名字段。这个设计在单语言、单国家场景下能跑一旦要支持全球就会崩。原因有三个第一不同国家层级深度不一样有的国家省下面是市有的省下面还有郡硬压成两层会丢信息第二中英双语需要每个层级都能独立切换不能靠拼接第三编码体系不统一ISO 3166 管国家ISO 3166-2 管一级行政区城市级没有全球统一标准必须自己维护稳定主键。常见做法是建三张核心表加一张映射表。国家表存 ISO 两位码、三位码、数字码、英文名、中文名一级行政区表存所属国家码、行政区编码、英文名、中文名、类型省/州/邦城市表存所属行政区编码、城市编码、英文名、中文名、经纬度可选。映射表用来处理别名和历史名称比如同一个城市在不同语言里的旧称。这样设计的好处是每一层都能单独查询、单独缓存前端做三级联动时按层级请求不用一次拉全量。提示城市级数据没有全球统一编码不要试图用某个第三方编码当主键自己生成稳定自增 ID 或 UUID把外部编码当普通字段存方便以后换数据源。2.2 中英双语字段的存储方式分列还是分表中英对齐是这套数据最容易踩坑的地方。两种主流方案一是同一张表里用 name_en、name_zh 两个字段二是拆成主表和翻译表主表存编码和结构翻译表存 language_code、name。小规模数据用分列最简单查询快前端直接按语言取字段。但如果以后要加日语、西班牙语分列就要不断加字段扩展性差。翻译表方案更规范代价是每次查询要 join。我一般会这样取舍如果确定只做中英双语分列足够代码里写一个 get_name(row, lang) 函数统一取值如果产品路线图里有多语言计划直接上翻译表主表只存编码和层级关系。翻译表结构大概是 entity_typecountry/region/city、entity_id、lang、name 四个核心字段加一个唯一索引防止重复。这样加语言只是插数据不用改表结构。-- 主表国家 CREATE TABLE country ( id INT PRIMARY KEY AUTO_INCREMENT, iso2 CHAR(2) NOT NULL UNIQUE, -- ISO 3166-1 alpha-2 iso3 CHAR(3) NOT NULL UNIQUE, -- ISO 3166-1 alpha-3 numeric_code CHAR(3), -- ISO 3166-1 numeric name_en VARCHAR(128) NOT NULL, name_zh VARCHAR(128) NOT NULL, INDEX idx_name_en (name_en), INDEX idx_name_zh (name_zh) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 主表一级行政区 CREATE TABLE region ( id INT PRIMARY KEY AUTO_INCREMENT, country_id INT NOT NULL, code VARCHAR(16) NOT NULL, -- ISO 3166-2 或自定义 name_en VARCHAR(128) NOT NULL, name_zh VARCHAR(128) NOT NULL, type VARCHAR(32), -- province/state/region UNIQUE KEY uk_country_code (country_id, code), INDEX idx_country (country_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 主表城市 CREATE TABLE city ( id INT PRIMARY KEY AUTO_INCREMENT, region_id INT NOT NULL, code VARCHAR(32), name_en VARCHAR(128) NOT NULL, name_zh VARCHAR(128) NOT NULL, latitude DECIMAL(9,6), longitude DECIMAL(9,6), INDEX idx_region (region_id), INDEX idx_name_zh (name_zh) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;上面这段建表语句的关键点字符集必须用 utf8mb4否则中文和某些特殊字符会截断country_id、region_id 用整型外键而不是直接存字符串编码查询和 join 更快name_en 和 name_zh 都建索引因为按名字搜索是高频操作。latitude、longitude 用 DECIMAL 而不是 FLOAT避免精度漂移。如果走翻译表方案把 name_en、name_zh 从主表拿掉换成 entity_type、entity_id、lang、name 的翻译表即可主表结构不变。2.3 数据来源与清洗别直接拿一份 CSV 就入库全球行政区划数据没有一份官方全量免费源能覆盖所有层级的中英双语。常见做法是组合几个来源国家层用 ISO 3166 官方列表一级行政区用 ISO 3166-2城市层用公开的地理数据集再补中文翻译。清洗阶段要做四件事去重、补全、对齐、校验。去重是按编码去重同一个国家不能出现两条补全是把缺失的中文名或英文名标出来不要留空字符串对齐是确保 region 的 country_id 能对上 country 表校验是检查编码格式比如 ISO2 必须两位大写字母。import csv import re def clean_country_row(row): 清洗单条国家记录返回标准化字典或 None iso2 (row.get(iso2) or ).strip().upper() iso3 (row.get(iso3) or ).strip().upper() name_en (row.get(name_en) or ).strip() name_zh (row.get(name_zh) or ).strip() # 编码格式校验ISO2 两位字母ISO3 三位字母 if not re.fullmatch(r[A-Z]{2}, iso2): return None if not re.fullmatch(r[A-Z]{3}, iso3): return None # 名称不能为空缺失的单独记录待补 if not name_en or not name_zh: return None return { iso2: iso2, iso3: iso3, name_en: name_en, name_zh: name_zh, } def load_countries(path): seen set() result [] with open(path, encodingutf-8) as f: for row in csv.DictReader(f): cleaned clean_country_row(row) if not cleaned: continue if cleaned[iso2] in seen: continue seen.add(cleaned[iso2]) result.append(cleaned) return result这段清洗脚本的逻辑说明clean_country_row 负责单条记录的格式校验和标准化返回 None 表示这条记录不合格调用方直接跳过load_countries 用 seen 集合做去重保证 iso2 唯一。参数方面iso2、iso3 的校验正则可以根据实际数据源调整如果数据源里混了小写或带空格strip 和 upper 先处理掉。缺失中文名的记录不要硬编一个翻译单独输出到待补文件人工或翻译接口补齐后再入库否则后面做中文搜索时会查不到。3. 把中英双语地理数据导入数据库并跑通三级联动查询3.1 批量导入的两种方式与事务控制数据清洗完就是入库。数据量小的时候几千到几万条可以用脚本逐条 insert简单直接数据量大或者要反复导入时用批量 insert 或数据库自带的 load data 工具更快。不管哪种方式都要用事务包起来导入失败能回滚不会留下半张表的数据。下面是一个批量导入的 Python 示例用 executemany 减少网络往返。import pymysql def batch_insert_countries(conn, countries): sql INSERT INTO country (iso2, iso3, name_en, name_zh) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE name_en VALUES(name_en), name_zh VALUES(name_zh) rows [(c[iso2], c[iso3], c[name_en], c[name_zh]) for c in countries] with conn.cursor() as cur: cur.executemany(sql, rows) conn.commit() def import_all(conn, countries, regions, cities): try: conn.begin() batch_insert_countries(conn, countries) # regions、cities 同理注意先插上级再插下级 conn.commit() except Exception as e: conn.rollback() raise e逻辑说明ON DUPLICATE KEY UPDATE 让脚本可以重复执行已存在的记录更新名称而不是报错适合数据源会定期更新的场景。executemany 把多条 insert 合并发送比循环单条快很多。事务控制上先插国家、再插行政区、最后插城市保证外键能对上。参数方面batch size 不要一次塞几十万条按 1000 到 5000 条一批避免单条 SQL 过大或内存暴涨。如果用的是 PostgreSQL把 ON DUPLICATE KEY UPDATE 换成 ON CONFLICT DO UPDATE。3.2 三级联动查询的 SQL 与接口设计前端三级联动是这套数据最高频的用法用户选国家省/州列表刷新选省/州城市列表刷新。接口设计上不要一次返回全量树按层级懒加载。三个查询分别是按国家查行政区、按行政区查城市、按名称搜索。下面给出核心 SQL。-- 1. 查所有国家按英文名排序 SELECT id, iso2, name_en, name_zh FROM country ORDER BY name_en; -- 2. 按国家查一级行政区 SELECT id, code, name_en, name_zh, type FROM region WHERE country_id ? ORDER BY name_en; -- 3. 按行政区查城市 SELECT id, code, name_en, name_zh, latitude, longitude FROM city WHERE region_id ? ORDER BY name_en; -- 4. 中英文模糊搜索城市用于输入联想 SELECT c.id, c.name_en, c.name_zh, r.name_zh AS region_name, co.name_zh AS country_name FROM city c JOIN region r ON c.region_id r.id JOIN country co ON r.country_id co.id WHERE c.name_zh LIKE CONCAT(%, ?, %) OR c.name_en LIKE CONCAT(%, ?, %) LIMIT 20;参数说明country_id、region_id 是上一级查询返回的主键前端缓存起来传参。模糊搜索的 LIKE 前后加通配符会导致索引失效数据量大时建议改用全文索引或搜索引擎。LIMIT 20 是防止联想结果过多拖慢响应。如果要做中英切换接口返回两个名称字段前端按当前语言取不要在 SQL 里写死语言。3.3 缓存策略哪些数据该进 Redis哪些不该国家数据变化极少适合全量缓存到 Rediskey 用 geo:countriesvalue 存 JSON 数组前端请求直接读缓存。一级行政区按国家缓存key 用 geo:regions:{country_id}。城市数据量大全量缓存占内存建议只缓存热门国家或按需缓存设置合理过期时间。缓存更新策略上数据导入完成后主动删除相关 key下次请求回源重建不要等过期。import json import redis r redis.Redis(hostlocalhost, port6379, db0) def get_countries(conn): cache_key geo:countries cached r.get(cache_key) if cached: return json.loads(cached) with conn.cursor() as cur: cur.execute(SELECT id, iso2, name_en, name_zh FROM country ORDER BY name_en) rows cur.fetchall() data [ {id: row[0], iso2: row[1], name_en: row[2], name_zh: row[3]} for row in rows ] r.set(cache_key, json.dumps(data, ensure_asciiFalse), ex86400) return data逻辑说明先查缓存命中直接返回未命中查库并写回缓存过期时间 86400 秒。ensure_asciiFalse 保证中文正常存储而不是转成 Unicode 转义。参数方面过期时间根据数据更新频率调整国家数据可以设长一点城市数据设短一点。注意缓存和数据库的一致性导入脚本跑完后要主动删 key否则用户会看到旧数据。4. 中英双语地理数据落地时的避坑与排查4.1 中文名乱码或显示成问号现象导入后查询中文名返回的是问号或者乱码。原因通常是数据库、表、连接三处字符集不一致。数据库默认可能是 latin1表建的时候没指定 utf8mb4或者客户端连接没设置 charset。解决建库建表统一用 utf8mb4连接串里显式指定 charsetutf8mb4Python 读文件时用 encodingutf-8。三处都对齐后重新导入。4.2 同一城市中英文对不上号现象切换语言后城市名变了但对应的行政区没变或者中英文指向了不同城市。原因是对齐时用了名称而不是编码做关联中文名和英文名各自匹配遇到重名就错位。解决所有层级关联一律用编码或主键名称只用于显示和搜索。导入前做一次校验确保每条 region 的 country_id 能查到每条 city 的 region_id 能查到。4.3 三级联动接口慢首屏加载超过两秒现象国家下拉能出来但选完国家后省/州列表要等很久。原因是每次请求都全表扫描或者没建索引。解决region 表的 country_id 建索引city 表的 region_id 建索引查询走索引而不是全表。另外把国家列表缓存到 Redis减少数据库压力。如果城市数据量特别大考虑分页或按首字母过滤。4.4 数据源更新后旧数据没删干净现象某个行政区调整了新数据导入了但旧记录还在查询出现重复。原因是导入脚本只做 insert 不做清理。解决导入前按编码比对标记或删除已不存在的记录或者用全量替换策略先清空再导入但要注意外键依赖顺序先删城市再删行政区再删国家。生产环境建议用软删除加 is_active 字段避免误删。4.5 英文名里的特殊字符导致 SQL 报错现象导入时遇到带单引号的英文名比如某些地名SQL 直接报语法错误。原因是字符串拼接没有转义。解决一律用参数化查询不要用字符串拼接 SQL。如果必须拼接对单引号做转义。参数化查询不仅解决转义问题还能防注入。5. 用编码做关联、用名称做展示一套可长期维护的地理数据习惯这套数据做完之后真正决定它能不能长期用的是使用习惯而不是表结构。我踩过最深的一个坑是早期图省事前端下拉直接把国家名当 value 传给后端后端再按名字反查。结果遇到重名地区、遇到用户切换语言、遇到名称里有空格和特殊字符查询就翻车。后来统一改成所有接口传参和关联一律用编码或主键名称只用于展示和搜索。这个习惯看起来多写了几行代码但省掉了后面无数次的排查。验证数据是否可用我一般跑三个检查。第一层级完整性检查统计每个国家的行政区数量、每个行政区的城市数量数量为 0 的单独列出来确认是数据源缺失还是导入漏了。第二中英对齐检查随机抽 100 条记录确认 name_en 和 name_zh 都不为空且指向同一实体。第三编码唯一性检查country 表的 iso2、region 表的 (country_id, code)、city 表的 (region_id, code) 都不能重复。这三个检查写成脚本每次导入后自动跑一遍比人工抽查靠谱。-- 层级完整性找出没有城市的行政区 SELECT r.id, r.name_zh, r.name_en FROM region r LEFT JOIN city c ON c.region_id r.id WHERE c.id IS NULL; -- 中英对齐找出名称为空的记录 SELECT country AS tbl, id FROM country WHERE name_en OR name_zh UNION ALL SELECT region, id FROM region WHERE name_en OR name_zh UNION ALL SELECT city, id FROM city WHERE name_en OR name_zh ; -- 编码唯一性检查重复 SELECT iso2, COUNT(*) FROM country GROUP BY iso2 HAVING COUNT(*) 1;进阶用法上如果产品需要按经纬度做距离计算或地图打点city 表里的 latitude、longitude 就派上用场。注意坐标系要统一常见的是 WGS84如果数据源混了 GCJ02 或 BD09打点会偏移。另一个技巧是给城市表加一个 search_key 字段把中英文名、拼音、别名拼在一起搜索时只查这一个字段比多字段 OR 快。拼音可以用现成库生成导入时算好存进去不要每次查询时算。最后说一个我自己的习惯地理数据永远保留一份原始文件在版本控制里数据库只是它的投影。每次更新先改原始文件再跑导入脚本这样任何时候都能追溯某条记录是什么时候、从哪个源进来的。地理数据不像业务数据天天变但它一旦错了影响的是所有依赖地址的功能后悔药很贵。希望帮到你。本文还有配套的精品资源点击获取