全球行政区划四级数据建模：层级血缘、质量校验与ETL落地

简介：这是一份覆盖全球范围的国家、省份与城市四级行政区划的公开关系型数据，数据结构清晰、层级对应明确，适合需要地图展示、区域统计、多级联动选择或自建业务系统的开发人员直接导入使用。资源包为单个rar压缩包，内部包含1个sql文件，总大小约115KB，可通过SQL快速完成建库与数据导入，便于在MySQL等常见关系型数据库中使用。目前已逾900人学习下载，受到地理信息、后端开发及数据分析方向用户的关注。数据中整理了国家、省份、城市之间的归属关系，并保留四级字段结构，使用者可直接基于该数据集扩展出区域筛选、按级聚合统计、物流配送范围匹配等业务功能，节省从零收集和清洗地理信息的成本。整体字段规范、关联明确，适合作为中小型项目中的基础维度数据，也可供教学演示或数据分析练习参考使用。

1. 全球国家-省份-城市四级数据：先看清“关系”再谈“体量”

拿到这份全球国家-省份-城市四级数据时，我的第一反应是“这不就是几张字典表嘛，导入就能用”。真等到接业务侧地址归属统计才发现，麻烦全在“关系”上：一个城市的上级省份能不能正确指到国家，一个区县是否挂到了它该挂的城市下面，同名不同地的区域怎么区分。这份资源解决的就是这套行政区划归属关系的标准化问题，覆盖层级、中英文名称、国家代码、中心点经纬度这些常用字段，适合后端开发、BI 工程师和数据产品直接拿来做地址维度建模。它的核心价值并不在体量，而在层级模型和字段设计的完整程度——关系理顺了，下游才能少返工。

体量上，几万到十几万行的数据量其实很轻，但越轻的数据越考验字段设计。这篇笔记会把这份资源的层级模型、质量检查、入库流程和几个常见翻车点拆开讲，看完你至少能判断它能不能用、怎么接。

2. 先看层级模型：国家-省份-城市的编码与父子关系约束

2.1 四级结构的本质：每条记录都要能回答“我从哪里来”

行政区划数据最基本也最容易出错的地方，是层级关系的存储方式。绝大多数同类资源采用的是一种邻接表模型：每一行记录保存自己的 code，同时挂一个 parent_code 指向父级。查询“某省下有哪几个市”，实际就是按 parent_code 过滤一次；要回溯“某区县属于哪个国家”，则需要沿着 parent_code 一层层往上找。

这种设计直观、兼容性好，四级的粒度也正好对应业务里最常见的地址表达：国家 → 省份/州 → 城市 → 区县。第四级在中文语境里是“区/县”，海外数据里可能对应 district、county 或者 commune，命名不一致但层级位置是一致的。建表时把 level 字段严格控制在这四个取值，比用字符串区分“国/省/市/区”要稳得多。

CREATE TABLE geo_region ( id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '内部主键，仅用于关联和索引', code VARCHAR(64) NOT NULL COMMENT '业务侧行政区划代码，全局唯一，对接用', parent_code VARCHAR(64) DEFAULT NULL COMMENT '父级行政区划代码，国家级的父级为空', level TINYINT NOT NULL COMMENT '层级：1=国家，2=省份/州，3=城市，4=区县', name_zh VARCHAR(128) NOT NULL COMMENT '简体中文名', name_en VARCHAR(128) DEFAULT NULL COMMENT '英文名，海外节点必填', name_local VARCHAR(128) DEFAULT NULL COMMENT '当地语言名，部分小语种地区才有', iso_country_code CHAR(2) DEFAULT NULL COMMENT 'ISO 3166-1 两位国家代码', longitude DECIMAL(9,6) DEFAULT NULL COMMENT '中心点经度，用于地图标注', latitude DECIMAL(9,6) DEFAULT NULL COMMENT '中心点纬度', timezone VARCHAR(64) DEFAULT NULL COMMENT 'IANA 时区标识，如 Asia/Shanghai', updated_at DATETIME DEFAULT NULL COMMENT '本条记录最近一次更新时间', UNIQUE KEY uk_code (code), KEY idx_parent_code (parent_code), KEY idx_level (level) ) COMMENT '全球四级行政区划关系表';

这段 DDL 里最值得关注的不是主键，而是 uk_code 和 idx_parent_code 两个索引。code 承担业务对接，必须唯一；parent_code 是查询父子关系的高频条件，不加索引的话，几万行数据用子查询勉强能跑，但一旦做联级联查就会明显变慢。id 自增只是为了让表关联时有稳定的内部主键，真正对外传输、做缓存键的永远应该是 code。

再仔细说一下 parent_code 为空的情况。只有 level=1 的国家级节点允许为空，其他层级出现空父级，基本可以判定为脏数据。后边第 3 章的孤岛检测就是用这个规则来筛问题的。

2.2 编码设计：id 与 code 分开，业务对接才不返工

很多开发拿到这类数据，第一件事是把 code 直接删掉，只留 id。这个习惯在纯内部系统里没问题，但行政区划数据几乎一定会被消息队列、Excel 导入、日志系统反复引用，一旦内部 id 因为重建表而变化，所有下游存储的历史值就全对不上了。

资源里对 code 的约定一般会写在 README 里，常见的是国家码拼省级码拼市级码，用短横线隔开，例如：

L1-AA -> 某个国家 L1-AA-L2-01 -> 该国家下某个省级单位 L1-AA-L2-01-L3-05 -> 该省下某个城市 L1-AA-L2-01-L3-05-L4-02 -> 该市下某个区县

真正落地时不要自己去猜 code 的组成部分，直接把它当成一个不透明字符串来用，查询和比较都整体匹配。因为部分国家的行政区划编码并不是严格递增的，中间可能跳号，按前缀解析编码很容易踩到隐藏格式的坑。

2.3 多语言与别名：一个 name 字段永远不够

只存一个 name_zh 的行政区划表，在中文站或许够用，但只要业务涉及海外节点或者后续要做国际化，就会立刻发现缺字段。同一层级里，name_en 是英文搜索和前台展示的默认值，name_local 用于小语种地区的本地展示，而中文名在东南亚部分国家可能不是官方名称，而是华人圈的通用叫法。

另一个高频需求是别名。地址解析场景里，用户输入的可能不是标准名而是简称，例如“某市”和“某地区”指向同一个地方；历史场景里，某些地方改名后旧名称仍然出现在存量订单里。资源本身一般不会单独提供别名表，但会在字段里保留部分备注，我拿到后通常会自己拆一层单独维护。

CREATE TABLE geo_region_alias ( alias_id BIGINT PRIMARY KEY AUTO_INCREMENT, region_code VARCHAR(64) NOT NULL COMMENT '指向 geo_region.code', alias_name VARCHAR(128) NOT NULL COMMENT '别名、简称、曾用名', alias_type VARCHAR(16) COMMENT 'SHORT/HISTORIC/LOCAL', KEY idx_region_code (region_code) ) COMMENT '行政区划别名表';

这张表的使用场景很明确：地址归一化时先用原词匹配 geo_region.name_zh，匹配不到再去 alias 表里反查。注意 alias_type 能帮你区分“现在是正式名”还是“历史上用过”，对按时间统计的 BI 查询非常重要，否则旧数据的省份名很容易被当成未知节点。

3. 先解决“能不能用”的质量问题：覆盖度、孤岛与行政区划变更

3.1 孤岛数据检测：父级不存在的记录是最大的隐性风险

拿到资源先别急着导入业务库，先干一件事：查孤岛。所谓孤岛，就是子级记录存在，但它指向的父级 code 在表里根本找不到。这种数据在单条查看时毫无破绽，只有做归属汇总或层级下钻时才会暴露——某城市的订单永远汇总不到省份维度，而且没人知道为什么。

SELECT 'level2' AS check_level, COUNT(*) AS orphan_count FROM geo_region ch LEFT JOIN geo_region p ON ch.parent_code = p.code WHERE ch.level = 2 AND p.code IS NULL UNION ALL SELECT 'level3', COUNT(*) FROM geo_region ch LEFT JOIN geo_region p ON ch.parent_code = p.code WHERE ch.level = 3 AND p.code IS NULL UNION ALL SELECT 'level4', COUNT(*) FROM geo_region ch LEFT JOIN geo_region p ON ch.parent_code = p.code WHERE ch.level = 4 AND p.code IS NULL;

这个 SQL 干了三件事：分别统计第二、三、四级节点中找不到父级的行数。左侧 ch 是子表，右侧 p 是父表，连接条件 ch.parent_code = p.code，只要 p.code 为 NULL 就说明父级缺失。实际使用中，我把阈值定在 0，任何一条孤岛记录都要拉出来看原因，常见原因有两种：一是源数据在整理时把重复的父节点去重掉了，二是行政区划变更后父级被停用，但子级没有同步迁移。前者属于数据质量问题，后者需要靠版本机制处理，下一节会讲到。

3.2 覆盖度校验：国家数、节点数与第四级缺失情况

孤岛检测解决的是“关系对不对”，覆盖度校验解决的是“全不全”。我一般先跑一个分组统计，看每个层级的节点总数是否在合理范围，再看每个国家下的省级、市级、区县级节点数量是否符合预期。

SELECT level, COUNT(*) AS node_count FROM geo_region GROUP BY level; SELECT iso_country_code, COUNT(*) AS total_nodes, SUM(CASE WHEN level = 2 THEN 1 ELSE 0 END) AS province_count, SUM(CASE WHEN level = 3 THEN 1 ELSE 0 END) AS city_count, SUM(CASE WHEN level = 4 THEN 1 ELSE 0 END) AS district_count FROM geo_region GROUP BY iso_country_code ORDER BY total_nodes DESC;

第一条 SQL 看整体分布是否合理：正常资源里，第三级城市数通常比第二级省份数多一个数量级，第四级区县数又会更多。如果某层级的数量明显偏少，比如全部国家都只有两三个省级单位，就说明这份资源可能是简化版，只保留了主要行政区。第二条 SQL 把视角切到国家维度，重点看第四级缺失情况。有些国家对区县级别的数据没有完整收录，district_count 会为零，这不算错，但接入业务前必须明确知道哪些国家缺第四级，否则做城市级下钻时会出现大片空白。

3.3 行政区划变更：合并、更名与代码废弃

行政区划不是静态数据。近几年各国都有过省份合并、城市更名、县级单位调整的例子，老的五级/四级编码体系会因此失效。资源里如果只有一张最新快照，去年存储的地址映射到今年再看可能就已经偏了。

处理这类问题的标准做法是在原表上增加有效时间窗口字段。下载资源后我一般会先确认文件里有没有 valid_from、valid_to 或者 discontinued 标记；如果没有，后续更新时要自己补。实际建模时，可以保留原始记录并新增替代记录，两条记录共用一个逻辑节点ID，但物理 code 不同：

{ "old_code": "L1-AA-L2-01-L3-05", "new_code": "L1-AA-L2-02-L3-11", "reason": "行政区划合并", "effective_date": "2024-07-01" }

这份对应关系是后续数据迁移的后悔药。订单表里如果还存着 old_code，通过这张映射表就能把它归到新区划下，而不是直接丢弃。很多团队忽略这一步，等到年报审计时发现历史省份归并不了档，才回来补映射，成本高得多。

4. 从原始CSV到业务表：ETL入库与联级查询的落地流程

4.1 宽表与血缘表：两张表结构怎么配合

上一章建的是标准的三范式关联表，适合数据治理，但业务查询并不总是方便。比如 BI 侧要看“某个区县所属的国家”，就得连着 join 两次，查询引擎压力大不说，SQL 也绕。所以我建表时通常再落一张扁平宽表，把层级路径直接铺开，让下游拿着单表就能 join。

CREATE TABLE dim_area_flatten ( full_code VARCHAR(255) PRIMARY KEY COMMENT '当前节点的完整编码路径', path_zh VARCHAR(512) COMMENT '中文路径，如 国家/省份/城市/区县', country_code VARCHAR(10) COMMENT '国家代码', province_code VARCHAR(64) COMMENT '省级代码', city_code VARCHAR(64) COMMENT '市级代码', district_code VARCHAR(64) COMMENT '区县级代码', level TINYINT COMMENT '本节点层级', name_zh VARCHAR(128) COMMENT '本节点中文名' ) COMMENT '行政区划扁平宽表'; CREATE TABLE dim_area_lineage ( self_code VARCHAR(64) COMMENT '节点自身的 code', parent_code VARCHAR(64) COMMENT '父级 code', level TINYINT COMMENT '层级', self_name_zh VARCHAR(128) COMMENT '节点中文名', KEY idx_self_code (self_code), KEY idx_parent_code (parent_code) ) COMMENT '行政区划血缘表';

两张表的分工很清晰：宽表给 BI 报表和标签体系用，血缘表给程序递归和关系修复用。宽表的 full_code 是把各级 code 用短横线拼起来的结果，path_zh 则是给前端展示用的完整中文路径。两张表必须由同一套 ETL 脚本生成，避免两边数据不一致。

4.2 入库与更新：全量重建还是增量合并

CSV 导入数据库这一步，最常见的选择有两种：全量 DELETE 后 INSERT，或者按主键做 ON DUPLICATE KEY UPDATE。对于几万行的行政区划数据，两种方式性能上都没压力，选哪个主要取决于下游是否有人依赖旧编码的引用关系。

import csv import mysql.connector def load_geo_data(csv_path: str, conn) -> int: """从原始CSV加载行政区划数据，写入 geo_region 表。""" rows = [] with open(csv_path, encoding="utf-8-sig") as f: for row in csv.DictReader(f): rows.append(( row["code"].strip(), row["parent_code"].strip() or None, int(row["level"]), row["name_zh"].strip(), row.get("name_en", "").strip(), row.get("iso_country_code", "").strip(), )) sql = """ INSERT INTO geo_region (code, parent_code, level, name_zh, name_en, iso_country_code, updated_at) VALUES (%s, %s, %s, %s, %s, %s, NOW()) ON DUPLICATE KEY UPDATE name_zh = VALUES(name_zh), name_en = VALUES(name_en), updated_at = NOW() """ cursor = conn.cursor() cursor.executemany(sql, rows) conn.commit() return len(rows)

脚本里的关键点在两点：一是编码必须用 utf-8-sig，很多原始 CSV 是带 BOM 头的，直接按 utf-8 读会把“code”读成“\ufeffcode”，第一个字段名就对不上；二是 executemany 批量写入时，参数顺序要和 SQL 里的 %s 占位符完全对应，我经常看到有人把 level 和 name_zh 写反，导入后数据整体错位。这个脚本只处理了插入，没有处理历史失效节点，所以更适合首次导入或全量重建，日常增量更新需要再加一层变更日志。

4.3 联级下拉与地址归一的SQL写法

后端最常见的需求是省市级联下拉：选中一个国家，把该国家下的省份列出来，再选一个省份，列出该省份下的城市。这种查询对应的是最基础的按父级过滤，实际写起来比很多人以为的复杂一点，因为要额外过滤掉停用节点。

SELECT code, name_zh FROM geo_region WHERE parent_code = %s AND level = 2 AND (valid_to IS NULL OR valid_to > NOW()) ORDER BY name_zh;

这里 %s 是传入的国家 code。加 level=2 的原因是因为部分资源没有严格遵守“parent_code 一定是上一级”，偶尔会出现错挂，显式指定层级可以避免这种情况。ORDER BY name_zh 用中文排序，实际项目里如果涉及多语言，建议改成按 name_en 或单独的 sort_order 字段排序，否则海外节点的展示顺序会很乱。

地址归一化的场景则可以直接利用宽表的 path_zh：用户输入一串地址，先做分词和行政区划名称匹配，匹配到节点 code 后，再通过 full_code 把省、市、区县一次性取出来，不需要多次递归查询。

5. 避坑与常见问题：四级数据落地的五个典型翻车点

5.1 城市ID按省独立编号，拼接全路径时越界

现象：两个不同省份下的城市，在原始文件里都叫“01”，直接拿一个自增 id 当全局关联键，导致省份 A 的城市数据串到省份 B 下面。 原因：部分数据源为了压缩存储，市级节点采用省内的顺序编号，而不是全球唯一编号。它本身没错，错在使用者把局部编号当成了全局唯一标识。 解决：永远使用 code 或者“省级 code + 市级 code”拼接后的完整字符串作为关联键，不要依赖短编号。我在 ETL 脚本里额外加了一步校验：拼接后的 full_code 如果出现重复，立即报错停止入库，不让脏数据流到下游。

5.2 同名不同地：名称字段不能作为关联键

现象：地址匹配时用 name_zh 去 join 行政区划表，命中率很高，但结果里混入了大量无关数据，统计口径直接失真。 原因：不同省份甚至不同国家下存在同名城市，中文名也一样。名称是给人看的，不是给机器做关联用的。 解决：匹配逻辑必须用 code，名称只能作为展示字段。但用户输入的是文本，所以要先通过别名表和地理上下文把文本解析成候选 code 列表，再结合省市级联关系做消歧。

5.3 第四级定义不一致：有的国家根本没有区县级

现象：做全球数据看板时，部分国家下钻到第四级后是空白，看起来像是数据缺失。 原因：不是缺数据，而是这些国家的行政区划根本不设区县级，第四级天然为空，资源只是如实反映。 解决：接入前先统计每个国家下 level=4 的节点数，把为零的国家清单记录下来。做可视化时，碰到第四级为空的记录，降级展示到城市层级，而不是展示空白页。

5.4 行政区划更名，旧代码指向新区域

现象：一批老订单在做省份聚合时，有大量省份标签匹配不上，这些订单被归到“未知区域”。 原因：地区更名后，新节点占用了原名称，旧订单里的名称仍然指向历史节点，而历史节点在新版本里被标记停用，代码匹配自然失败。 解决：维护一张旧码到新码的映射表，ETL 执行时做一层翻译。如果资源里没提供这张表，就把旧版本表直接归档保留，并建立 code 与 archived_code 的对应关系。

5.5 全量覆盖更新导致历史数据丢失

现象：数据源发布了新版文件，你按老办法 DELETE 旧表再导入新表，结果所有历史订单里的行政区划 code 全部失效。 原因：不是新版数据错了，而是城区划变更被处理后，旧节点的引用被物理删除，使得历史数据失去了归属支撑。 解决：不要物理删除旧记录。表里保留 valid_from、valid_to 字段，每次更新时把要淘汰的节点标记 valid_to=NOW()，而不是 DELETE。查询维度表时按当前生效时间过滤，查询历史订单时则按订单时间找回当时有效的节点。这个习惯养成后，基本不会再被数据更新搞得措手不及。

6. 数据自检与版本管理：拿到资源后两小时内跑完四步验证

6.1 三条校验SQL替代人工抽检

项目页面提供了原始文件的下载通道，但拿到手后第一件事不是导入，而是先跑一遍我的老规矩——三条校验 SQL。第一步是孤岛检查，第二步是层级统计，第三步是编码唯一性检查，除了第 3.1 节的孤岛查询外，再加一条重复编码排查：

SELECT code, COUNT(*) AS dup_count FROM geo_region GROUP BY code HAVING dup_count > 1;

这条 SQL 跑出来如果有重复结果，说明源文件里可能存在一码多节点的问题，直接拿去入库必然会导致关联错乱。把这三条 SQL 固化成一个 shell 脚本，每次下载新版本先执行，确认通过再谈后续。

6.2 与业务历史表抽样比对

数据自检通过不代表业务能直接接。我一般会从订单表里随机抽最近三个月的地址归属数据，和维度表做一次匹配率比对：

SELECT COUNT(*) AS total_orders, SUM(CASE WHEN dim.code IS NOT NULL THEN 1 ELSE 0 END) AS matched_orders FROM orders o LEFT JOIN geo_region dim ON o.region_code = dim.code WHERE o.order_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH);

匹配率低于 95% 就要回头查是业务侧存了旧码，还是维度表本身有缺口。抽样只抽三个月，是因为行政区划变更的影响在短期内最明显，时间跨度太长反而会被历史数据干扰判断。

6.3 版本快照与回滚：给重建留后悔药

行政区划数据不会频繁更新，但每次更新都伴随风险。我的习惯是在每次入库前先对当前生效版本做一次全量快照，存成带时间戳的文件，入库任务里固定带一个 version_id 字段。这样一旦新版数据被发现有重大质量问题，可以在分钟级把维表回滚到上一个版本，而不是用备份文件手工恢复。

从那以后，我每次拿到这类基础数据资源，都会强制自己先跑一遍孤岛、覆盖度和编码唯一性检查，再谈导入。关系对了，数据才真正能用。希望帮到你。

本文还有配套的精品资源，点击获取