77万条五级行政区划数据实战：表结构、查询、地址解析与版本更新

简介：面向地域信息化开发与数据分析场景的全国五级行政区划数据包，覆盖省、市、区县、乡镇及村委会层级，共收录近77万条记录，常被用作六级地区数据口径的基础地址库，适合地址选择、区域编码、地图服务、物流配送、本地化电商与统计分析的开发者、数据工程师及学生使用。压缩包内仅1个SQL文件，体积约7.97MB，该文件以建表与批量插入脚本的形式组织，可在MySQL中快速创建行政区划表并导入全量数据，便于按层级检索与关联业务表。该资源已有1074人学习，说明它在区域数据初始化场景中具有一定参考价值。读者可获得较完整的五级区划样本、可直接执行的导入脚本以及表结构设计思路，用于搭建地址选择器、区域维度统计、配送范围配置等基础能力，并能在此基础上按需更新行政区划变更，减少自行整理层级关系与编码对应的工作量。

1. 77万条五级区划数据能干什么：先看层级，再谈用法

做门店选址、CRM 客户地址、物流分单的人大概都遇到过同一种场面：表单里省市县是三级联动下拉，客户资料里却是一整句自由文本，乡镇和村两级干脆没有；等到要做区域汇总、算人均覆盖、按行政区划分派业务时，才发现地址字段根本没法对账。这份近 77 万条的全国五级行政区划数据，就是把这件麻烦事拉回可控范围的基础数据集——省、市、区县、乡镇街道、村（社区）五级齐全，每条记录挂一个可校验的行政区划代码。检索里经常出现的“六级地区数据”说法，其实说的是 6 位行政区划代码，层级本身是五级，两件事不是一回事，混淆了会导致表结构设计直接跑偏。适合数据清洗、表单级联、地址解析、区域统计这几类活。

2. 层级与代码：把“六级地区数据”这个说法拆开

2.1 五级层级和 6 位代码不是同一件事

先把最容易错的地方说清楚。行政区划代码是分段编码，不是流水号：前 2 位是省级，前 4 位是地市级，前 6 位是区县级，乡镇街道级扩展到 9 位，村（社区）级扩展到 12 位。也就是说，省、市、区县这三级共用一个 6 位长度，乡镇级 9 位，村级 12 位。所谓“六级”，大概率是有人把 6 位代码当成了 6 级层级，这类误解落到代码里就是灾难——我见过有人按“6 位=一级”去切分，结果把村级代码硬生生截成两段。

提示：表里的代码是为了讲清结构编的占位值，不是真实区划代码，照结构理解就行，别直接拿去当测试数据。

这里有个判断标准值得记住：父子关系以 parent_code 为准，代码前缀只能当快路径。绝大多数情况下村级代码确实以所属区县 6 位开头，但历史勘界调整、开发区代管、跨区划转这些情况会让前缀关系断掉。所以你需要的是一张显式的 parent_code 列，而不是每次查询都靠LEFT(code, 6)推父级。前缀适合用来做范围扫描和索引加速，权威关系还是得靠 parent_code 兜底。

2.2 一份最小可用的表结构设计

77 万行放在今天不算什么大表，但字段类型选错，后面每一条查询都要付利息。最典型的错是把 code 存成 INT：6 位区县代码看着像数字，可它带着业务含义，前导零、位数扩展都是坑，一旦变成整数，'010000'就静默成了10000。我一般用VARCHAR存代码，用TINYINT存层级，full_name 冗余一列，专门给模糊搜索和人工核对用。

CREATE TABLE region ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(12) NOT NULL COMMENT '行政区划代码，长度随层级变化', name VARCHAR(64) NOT NULL COMMENT '标准名称，如“某镇”', short_name VARCHAR(32) DEFAULT NULL COMMENT '去掉后缀的名称，供地址匹配用', level TINYINT NOT NULL COMMENT '1省 2市 3区县 4乡镇街道 5村社区', parent_code VARCHAR(12) NOT NULL DEFAULT '' COMMENT '顶级为空串，不用 NULL', full_name VARCHAR(160) DEFAULT NULL COMMENT '全路径，如 A省B市C区D街道E村', status TINYINT NOT NULL DEFAULT 1 COMMENT '1在用 0已撤销/合并', version VARCHAR(16) NOT NULL DEFAULT '' COMMENT '数据版本标记', PRIMARY KEY (id), UNIQUE KEY uk_code_version (code, version), KEY idx_parent (parent_code), KEY idx_level_name (level, name), KEY idx_fullname (full_name(48)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

几个参数的取舍说一下。parent_code用空串而不是 NULL，是为了避开!=比较时 NULL 的三值逻辑陷阱，顶级节点直接筛parent_code = ''就完事。唯一索引做成(code, version)的联合索引，而不是单独的 code——原因后面第 5 章会细讲，简单说就是多版本数据放一张表时，单列唯一会直接把导入顶回来。full_name上做前缀索引（48 字符）够用，中文名称很少有超过这个长度的。short_name是为了地址匹配阶段省掉一次去后缀操作，属于典型的用空间换 CPU。

2.3 用脚本做入库前的完整性体检

拿到数据别急着导库，先跑一遍体检。77 万条的规模，Python 读一遍也就几秒，但能提前拦下 90% 的返工。

import csv, collections # 层级 -> 该层代码应有的长度 REQUIRED_LEN = {1: 2, 2: 4, 3: 6, 4: 9, 5: 12} def load(path): rows = [] with open(path, encoding="utf-8-sig", newline="") as f: for r in csv.DictReader(f): r["level"] = int(r["level"]) r["code"] = (r["code"] or "").strip() r["parent_code"] = (r["parent_code"] or "").strip() rows.append(r) return rows def check(rows): report = collections.defaultdict(list) codes = {r["code"] for r in rows} parent_of = {r["code"]: r["parent_code"] for r in rows} for r in rows: code, lvl, parent = r["code"], r["level"], r["parent_code"] if len(code) not in (REQUIRED_LEN.get(lvl, -1), 6, 9, 12): report["code_len_mismatch"].append(code) if lvl > 1 and parent not in codes: report["orphan"].append(code) # 父级不存在，多半是数据被裁过 if lvl > 1 and not code.startswith(parent): report["prefix_broken"].append(code) # 前缀断链，需人工确认是历史调整还是脏数据 if lvl == 1 and parent: report["root_has_parent"].append(code) dup = [c for c, n in collections.Counter(r["code"] for r in rows).items() if n > 1] report["duplicate_code"] = dup[:50] # 同级同父重名：常见于“某某村”与“某某村村委会”并存 sib = collections.Counter( (r["parent_code"], r["level"], r["name"]) for r in rows if r["level"] >= 4 ) report["sibling_dup_name"] = [k for k, n in sib.items() if n > 1][:50] return report, parent_of if __name__ == "__main__": rep, _ = check(load("region_raw.csv")) for k, v in rep.items(): print(f"{k:>20}: {len(v)} 例, 前几条 -> {v[:5]}")

逻辑拆一下。第一段按 level 去比代码长度，比的是“该层级应该多长”，但村和乡镇这两级各地扩展位数不完全统一，所以给了(6, 9, 12)的容错集合，避免把合法的异构数据误判成脏数据。第二段查孤儿和断链，其中prefix_broken这个桶你会经常看到非空——它既是脏数据信号，也是历史区划调整的遗留痕迹，不要一看到就批量修，先抽样看几条。第三段查重复代码和同级重名，同级重名在村级特别普遍，两个村同名但分属不同乡镇是合法的，但同一个父级下出现两个同名村，基本就是“XX村”和“XX村村委会”两种写法混进来了。

阈值怎么定？我的习惯是：orphan和duplicate_code必须为 0 才允许导库，否则后续所有关联查询都不可信；prefix_broken和sibling_dup_name允许存在，但要把数量记进版本日志，下一次换版本时对比这两个数有没有暴涨。这个习惯帮我省过不少时间，曾经有一次换版本后orphan从 0 跳到几十条，一查是新版本裁掉了开发区代管的中间层，用脚本两分钟定位，人工翻表得看半天。

3. 落库与查询：77万行怎么在 MySQL 和 PostgreSQL 里跑得动

3.1 选型：三个方案各自的边界

77 万行对现代机器来说真不算大，全量装进内存也就几百兆。所以选型的关键不在数据量，而在查询模式：你是只做一次离线地址匹配，还是要支持在线表单级联和区域统计。

我一般这么选：一次性清洗任务用 SQLite，把 CSV 读进内存表，跑完导出结果就扔；要长期服务的业务库用 MySQL，够用且不用动运维；如果这个区划表是核心维表、天天要按“某市下所有村”做聚合，才值得上 PostgreSQL 的路径类型。别为了炫技上重型方案，最后维护的人不是你自己。

3.2 批量导入的三步走

导入这一步，血泪经验是：先导数据，后建索引。77 万行带着四五个索引逐行插入，比你想象中慢得多。

# 第一步：把源文件统一成 utf-8 无 BOM、LF 换行、字段用 \t 分隔 # 用 sed 清掉 BOM 和 Windows 换行，比在程序里处理省事 sed -i '1s/^\xEF\xBB\xBF//' region_raw.csv sed -i 's/\r$//' region_raw.csv # 第二步：走服务端批量导入，别用 Python 逐行 insert mysql --local-infile=1 -u user -p dbname -e " LOAD DATA LOCAL INFILE 'region_raw.csv' INTO TABLE region CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES (code, name, short_name, level, parent_code, full_name, status, version); " # 第三步：数据落表后再补索引（建表时可以先只留主键） mysql -u user -p dbname -e " ALTER TABLE region ADD UNIQUE KEY uk_code_version (code, version), ADD KEY idx_parent (parent_code), ADD KEY idx_level_name (level, name); "

参数逐个说。--local-infile=1是客户端侧开关，服务端也要local_infile=ON，两边缺一个就报The used command is not allowed，这个报错我第一次遇到时查了半小时。FIELDS TERMINATED BY要跟源文件实际分隔符一致，CSV 里如果字段值本身带逗号（比如某些全路径名称），必须加OPTIONALLY ENCLOSED BY。IGNORE 1 LINES跳过表头，但前提是源文件真有表头，没表头还加这句就会吃掉第一条真实数据——这种“少一条”的问题极难发现，因为总量 77 万，少一条你根本看不出来。CHARACTER SET utf8mb4显式指定，别指望客户端默认值，中文乱码十有八九出在这。

PostgreSQL 那边的等价写法是\copy region FROM 'region_raw.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')，注意是反斜杠\copy而不是COPY，前者走客户端文件权限，后者要求文件在服务端目录下且需要超级用户，权限不够时的报错信息一点也不友好。

导入之后先别急着庆祝，跑一遍行数核对：SELECT level, COUNT(*) FROM region GROUP BY level;，五个层级的数量和源文件的分层统计对上了，才算真的进去了。

3.3 上下级追溯与前缀取子树

日常查询基本就两类：往上追祖先链，往下取整棵子树。

往上追，用递归 CTE 最直观：

-- 从某个村往上追到省，输出完整路径 WITH RECURSIVE up AS ( SELECT code, name, level, parent_code, 1 AS depth FROM region WHERE code = '100101001001' AND version = 'v1' UNION ALL SELECT r.code, r.name, r.level, r.parent_code, u.depth + 1 FROM region r JOIN up u ON r.code = u.parent_code AND r.version = 'v1' WHERE u.depth < 6 ) SELECT * FROM up ORDER BY level;

depth < 6这个上限是必须的。递归 CTE 一旦遇上脏数据里的环（A 的父级是 B，B 的父级又是 A），没有上限就会一直转到cte_max_recursion_depth报错为止。加上限之后最坏情况是多转几圈然后停下，你能从结果里一眼看出环在哪。这个约束我在任何递归查询里都会加，属于肌肉记忆。

往下取子树，两种写法：

-- 写法一：递归展开，语义最准，深度可控 WITH RECURSIVE down AS ( SELECT code, name, level, parent_code FROM region WHERE code = '100101' AND version = 'v1' UNION ALL SELECT r.code, r.name, r.level, r.parent_code FROM region r JOIN down d ON r.parent_code = d.code AND r.version = 'v1' ) SELECT level, COUNT(*) AS cnt FROM down GROUP BY level; -- 写法二：前缀扫描，快但要求代码前缀严格连续 SELECT level, COUNT(*) FROM region WHERE version = 'v1' AND code LIKE '100101%' AND level > 3 GROUP BY level;

写法二在code有索引时能走范围扫描，速度明显更快，但它的前提是前缀关系成立。前面体检脚本里prefix_broken那个桶就是给这个查询兜底的：如果桶里非空，写法二会漏数据或者多捞数据，此时必须退回写法一。我的习惯是——统计口径用写法一，交互式下拉用写法二。下拉场景漏一两个边缘节点用户感知不到，但统计报表少一个村就是硬伤。

顺带说一句 PostgreSQL 的路径方案。给表加一列path ltree，值写成'1001.01.001.001'这种点分形式，取子树就是WHERE path <@ '1001.01'::ltree，配合 GiST 索引，比递归快一个量级。代价是写入时要维护这一列，层级调整时得同步更新，属于“查询爽、维护烦”的典型。

4. 地址解析实战：把一段脏地址补全成标准五级

4.1 为什么纯正则走不通

最朴素的想法是写正则：(.*?省)?(.*?市)?(.*?区|县)?(.*?镇|乡|街道)?(.*?村|社区)?，看着挺美，实际跑一遍就知道问题在哪。地址文本里省市区三级经常被省略——“A市C区某路 8 号”这种写法，市前面没有省；村名里带“乡”字的（某某乡愁村这种谐音梗在真实地址里真有）；还有“某某经济技术开发区”这类非标准行政单位。正则的贪婪/非贪婪只能处理位置，处理不了“这个名字必须是某个已知行政单位”这个语义约束。

合格的方案是分级词典 + 最长匹配 + 层级约束三件套。分级词典解决“知道这个名字属于哪一级”，最长匹配解决“A市B区和A市B区C街道哪个优先”，层级约束解决“匹配出来的层级必须递增，不能从区县跳回省”。

4.2 前缀词典与回溯匹配的实现

import re # 后缀归一：地址里“某某村村委会”“某某村委会”“某某村”指同一个地方 TAIL_PATTERN = re.compile(r"(村民委员会|居民委员会|村委会|居委会|社区服务中心|办事处|街道|社区|村|镇|乡)$") def normalize(text): text = text.strip().replace(" ", "") text = text.replace("(", "（").replace(")", "）") return re.sub(r"\s+", "", text) def build_index(rows): """name -> [(code, level, parent_code), ...]，同名不同父的都要留着""" idx = {} for r in rows: for key in {r["name"], r.get("short_name") or r["name"]}: if not key: continue idx.setdefault(key, []).append((r["code"], r["level"], r["parent_code"])) return idx def is_ancestor(code, maybe_ancestor, parent_of): cur, hops = code, 0 while cur and hops < 8: if cur == maybe_ancestor: return True cur = parent_of.get(cur, "") hops += 1 return False def parse_address(text, index, parent_of, max_window=12): """返回 [(level, code, matched_name), ...]，按层级递增顺序""" text = normalize(text) pos, out, last_level, last_code = 0, [], 0, None while pos < len(text): hit = None # 从长到短试窗口，保证最长匹配优先 for end in range(min(len(text), pos + max_window), pos, -1): seg = text[pos:end] for code, lvl, parent in index.get(seg, ()): if lvl <= last_level: continue # 层级必须递增 if last_code and lvl > 1 and parent != last_code \ and not is_ancestor(last_code, parent, parent_of): continue # 跨树不允许，只能跳级不能跳树 hit = (code, lvl, seg, end) break if hit: break # 窗口内没命中，试一次去后缀的写法 stripped = TAIL_PATTERN.sub("", seg) if stripped != seg: for code, lvl, parent in index.get(stripped, ()): if lvl > last_level and (not last_code or parent == last_code or is_ancestor(last_code, parent, parent_of)): hit = (code, lvl, stripped, end) break if hit: break if hit: out.append((hit[1], hit[0], hit[2])) last_level, last_code, pos = hit[1], hit[0], hit[3] else: pos += 1 # 未命中滑动一个字符 return out

关键逻辑在这几行。max_window=12是单级名称的最大长度窗口，村级全称最长也就十来个字，超过这个窗口还没命中的基本不是行政单位名。lvl <= last_level: continue是层级递增约束，它把“某市某街道”里街道名恰好也出现在某个区县名里的歧义挡掉了。is_ancestor那个判断是防跳树：地址里出现“A省…E村”，中间的市、区县被省略时允许跳级，但跳过去的节点必须仍在同一棵祖先链上，不能从 A 省的村跳到 B 省的镇。

参数上最值得调的是max_window和TAIL_PATTERN。前者遇到超长的开发区名称要放大到 16 左右；后者是最容易漏的——“某某社区居民委员会”这种全称，如果正则只收了“居委会”没写“居民委员会”，就会去尾不干净，导致命中失败。我的做法是把本地见过的所有后缀写法都收集到一个常量里，宁可多写几条。

4.3 命中率调优：别名表、缺级补全、置信度

上线跑一批真实地址，你会看到命中率卡在 60%～80% 上不去，剩下的长尾基本是三类问题。

第一类是同名歧义。同一个名字在不同父级下重复出现，词典扁平化之后没法判断该选哪个。解法是不要预先扁平化，build_index里保留(code, level, parent)三元组，匹配时用last_code做父级约束，命中率能立刻上一截。如果地址文本里连上级都没写，只能靠业务侧的上下文（比如客户所在仓库的归属省份）补一个初始last_code进去。

第二类是别名和历史名称。用户填的是老名字，数据里是新名字；或者填的是俗称，比如把“某某经济技术开发区”填成“某某开发区”。这类只能靠一张别名表硬扛：

第三类是缺级。地址只到区县，乡镇和村缺失。这种情况不该硬匹配，而应该输出“已匹配到第 3 级 + 置信度”，把补全交给下游（比如用该区县下的默认配送站兜底）。我给解析结果都带一个confidence字段：五级全命中记 1.0，每缺一级扣 0.15，出现跳级再扣 0.1。低于 0.5 的直接打回人工核对队列。这个分数看起来玄学，但实际跑起来非常有用——它把“看起来解析成功了但其实只匹配到省”这种假阳性挡在门外，否则下游拿到的就是一堆错误的标准地址，比没解析还糟糕。

5. 避坑与排查：五级数据最容易翻车的五个地方

5.1 数据本身的三类坑

现象：导入后代码位数不对，6 位区县代码变成 5 位甚至 4 位。原因几乎只有一个——中间某一环把 code 当数字处理了。Excel 打开 CSV 会自动把100101转成数字，前导零丢掉；Python 里用int(r["code"])排序再写回，同样丢；更隐蔽的是 pandas 的read_csv默认推断类型。解决办法是从源头到落库全程把 code 当字符串：pd.read_csv(path, dtype={"code": str, "parent_code": str})，SQL 里用VARCHAR，导出时用QUOTE_ALL强制加引号。这条我踩过一次就不敢大意，之后每张表的代码列都显式声明类型，绝不靠推断。

现象：同一个 code 在表里出现两条。原因通常是合并了不同批次或不同年份的数据，同一个行政区划在不同版本里代码相同但名称变了（撤乡并镇、改名）。解决办法是加version字段并把唯一索引做成(code, version)，查当前版本时统一带WHERE version = 'v1'。更优雅的做法是分表或分区，但加版本列是改动最小的方案。注意：加版本列之后，所有关联查询都必须带上版本条件，漏一个就会出笛卡尔积式的重复行，这类问题在 join 大表时特别难发现。

现象：村级数据量比预期少了一截，或者出现“某某村”和“某某村村委会”并存。原因是命名口径不统一——有的批次用全称（带“村民委员会”），有的用简称（只留“某某村”），混在一起就既重复又缺量。解决办法是入库时统一用简称存name，全称放另一个字段，short_name存去后缀版本，然后跑 2.3 节的sibling_dup_name检查。同一父级下出现同名，一律当脏数据处理，人工确认后合并或加区分后缀。

5.2 使用侧的两类坑

现象：按前缀查某省下的所有数据，越查越慢，最后超时。原因有两种。一是用了函数包裹列，比如WHERE LEFT(code, 2) = '10'，这会让索引彻底失效，77 万行全表扫一遍；二是前缀条件写得过宽，LIKE '1%'这种基本等于全表。解决办法是能用parent_code就别用前缀，WHERE parent_code = '100000'走的是idx_parent，快得多；非要用前缀就写成code LIKE '10%'让 B+ 树走范围扫描，同时确保没有函数包裹。这条属于典型的“写法差一点、性能差一个量级”。

现象：地址解析把“A市B区”匹配成了另一个同名的县，结果全错。原因是词典被扁平化了——只存 name 到 code 的映射，丢掉了 parent 关系，同名的区县、乡镇、村互相抢。解决办法是回退到 4.2 节的做法：词典保留三元组，匹配时用last_code做父级约束，跳级必须过is_ancestor校验。另外排查时可以加一行日志，把每个命中片段的(name, code, parent_code)打出来，一眼就能看出是哪个环节跳树了。我一般会在解析函数里留一个debug=True开关，出问题直接打开看中间过程，比闷头猜快。

现象：递归查询报Recursive query aborted after N iterations。原因是数据里存在环，或者递归没有深度上限。解决办法是先跑一个环检测（从每个节点往上走，超过层级数还没到顶就说明有环），再给所有递归查询加depth < 6的硬上限。环通常来自父子关系录反，一次导入错位就能造出一对互相指向的节点，检测脚本几行就能定位。

6. 版本比对与增量更新：换一版数据时只改变化的部分

区划数据不是一次性的。撤县设区、乡镇合并、村改社区，每年都有变动，拿到的下一版数据往库上一盖，很容易把业务方的历史归属关系冲掉——比如某个客户三个月前归属于 A 街道，现在这个街道并进了 B 街道，历史报表按当时的归属统计，不能跟着变。所以换版的时候只做增量，别做覆盖。

做法是给每行算一个内容指纹，只比字段不比代码，代码不变但名称变了也算变更：

import csv, hashlib def read_all(path): with open(path, encoding="utf-8-sig", newline="") as f: return {r["code"]: r for r in csv.DictReader(f)} def fingerprint(row): # 参与比对的字段：名称、层级、父级、状态；变更标记不参与 payload = "|".join([row["name"], str(row["level"]), row["parent_code"], row.get("status", "1")]) return hashlib.md5(payload.encode("utf-8")).hexdigest()[:12] def diff(old_path, new_path): old, new = read_all(old_path), read_all(new_path) added = sorted(new.keys() - old.keys()) removed = sorted(old.keys() - new.keys()) changed = sorted(c for c in new.keys() & old.keys() if fingerprint(old[c]) != fingerprint(new[c])) return {"added": added, "removed": removed, "changed": changed} if __name__ == "__main__": d = diff("region_v1.csv", "region_v2.csv") for k, v in d.items(): print(f"{k}: {len(v)} 条") # 变更明细落盘，供人工复核；removed 一律不物理删除，只置 status=0 with open("diff_detail.csv", "w", encoding="utf-8", newline="") as f: w = csv.writer(f) w.writerow(["change_type", "code"]) for k, v in d.items(): for code in v: w.writerow([k, code])

逻辑上有两个刻意的设计。一是用内容指纹而不是逐字段比较，原因很简单：五六个字段两两组合判断，代码写出来又长又容易漏，一个哈希串解决全部。哈希截断到 12 位对 77 万行的碰撞概率可以忽略。二是removed的处理策略——绝不物理删除，只把status置 0。历史订单、历史报表都还挂着这些代码，删掉之后 join 全变空值，这是最典型的后悔药场景：删的时候觉得清爽，对账的时候哭着补数据。

换版时我会跑一遍固定的校验清单，把结果贴进版本日志，下次出问题直接比对：

这套流程我最早是吃过亏才补上的：某次直接拿新版本覆盖了旧表，第二天报表里一批客户的乡镇归属全变成了空值，排查了一圈才发现是几个乡镇合并、代码被撤销，而历史数据里还挂着老代码。从那以后我每次换版都强制走一遍“先 diff、再校验、后导入、removed 只置状态位”这套流程，不管数据方怎么保证“只是小更新”。区划数据的变化从来不是小更新，一条记录背后可能挂着几万条业务数据。希望帮到你。

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