五级行政区划数据实战：树形建模、清洗导入与地址选择器

简介面向地域分析、地图服务与本地化业务开发者的全国五级行政区划数据集合覆盖省、市、区县、乡镇及村委会层级可解决应用中的标准行政区划编码与层级关联需求。压缩包为rar格式共1个文件主体是可在MySQL中创建并填充行政区划表的SQL脚本包体约7.97MB内含近77万条记录字段通常涉及省、市、区县、乡镇、村等层级信息便于直接导入后做关联查询。已有1074人学习下载适合需要快速搭建地域基础表的中初级开发者参考。数据可用于地图位置检索、按区域展示天气、人口与经济统计、物流配送范围划分以及电商附近商家推荐等场景配合建表语句和数据插入语句读者能较快得到结构统一、层级完整的行政区划底表并在此基础上叠加业务数据做筛选、统计与本地化服务开发。1. 别急着建表一套五级区划数据真正难的是“最后一公里”地址录入页做到乡镇和村委会这一层最常见的卡点不是下拉框样式而是库里的层级断档省、市、区县都有再往下就只剩自由输入。全国5级行政区划数据把省、市、区县、乡镇、村委会将近77万条串成树做地址选择器、配送范围、门店覆盖和客户地址清洗时六级地区数据的口径也常在这里对齐。它适合后端、前端和做数据清洗的开发者但别急着建表先看清层级、编码和查询方式后面才不会被“最后一公里”拖住。2. 数据结构拆解从省级到村委会的层级模型与字段设计这套数据的第一价值不是“多”而是“能挂成树”。77万条放在一张表里不算大真正决定后面好不好用的是 parent_id 怎么落、区划代码怎么保真、层级怎么判断。我见过不少实现把省市区县做成三张表到了乡镇和村委会又临时加一张结果前端每选一级就要换一个接口后端每加一层都要改代码。更稳的做法是一张主表加 level 字段所有层级共用一套查询前端只按 parent_id 懒加载。2.1 五级层级与“六级地区数据”的口径对齐先对齐口径。省、市、区县、乡镇、村委会是五级资源标题写的是5级行政区划数据。有人嘴里说的“六级地区数据”通常是把村委会下面的村民小组、网格、片区也算一级也有人把“省、市、区县、街道/乡镇、社区/村、小区/村组”叫六级。口径不一致表结构就会打架。我的处理方式是主表只承载资源包里真实存在的五级第六级用扩展表挂上去不硬塞进主表也不假装原始数据已经覆盖。level含义parent_id 指向常见用途1省级0地址选择器第一屏2地市级省级 id城市筛选、配送范围3区县级地市级 id门店覆盖、行政区统计4乡镇/街道区县级 id最后一公里配送5村委会/社区乡镇 id地址补全、村级归属这张表里没有放“第六级”因为资源包的主干到村委会为止。要做六级地区数据单独建扩展表更干净扩展表只存村小组、网格、片区parent_id 指向主表的村委会 id。这样主表保持稳定后续换数据源也不会把五级结构搅乱。2.2 核心表字段与层级编码设计主表我一般会按下面这样建。字段看着多但每个都有明确用途id 给内部关联用region_code 保住原始编码parent_id 建树path 做子树查询full_name 给前端展示pinyin 和 initial 给搜索兜底。原始包不一定带拼音和全称可以在导入后补别在业务查询时临时拼。CREATE TABLE region ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, parent_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 父级id省级为0, region_code VARCHAR(20) NOT NULL COMMENT 原始区划编码按字符串保存, region_name VARCHAR(100) NOT NULL COMMENT 当前层级名称, short_name VARCHAR(50) DEFAULT NULL COMMENT 简称没有就留空, level TINYINT UNSIGNED NOT NULL COMMENT 1省 2市 3区县 4乡镇 5村委会, path VARCHAR(255) NOT NULL DEFAULT COMMENT 祖先id路径如/1/23/456/, full_name VARCHAR(255) DEFAULT NULL COMMENT 全路径名称便于展示和搜索, pinyin VARCHAR(100) DEFAULT NULL COMMENT 拼音可选, initial CHAR(1) DEFAULT NULL COMMENT 首字母可选, sort_no INT NOT NULL DEFAULT 0 COMMENT 同级排序, status TINYINT NOT NULL DEFAULT 1 COMMENT 1有效 0停用, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_code (region_code), KEY idx_parent (parent_id, status, sort_no), KEY idx_level (level), KEY idx_path (path(64)), KEY idx_name (region_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;这里有个关键选择parent_id 存内部 id不存 region_code。原因是有些数据包的上级编码列并不完整甚至同一批数据里编码长度不统一内部 id 由导入程序生成父子关系一旦建好就稳定。region_code 加唯一索引用来做增量更新和去重。path 存成/1/23/456/这种形式查某个省下面所有节点时直接path LIKE /1/%比递归查询省事。编码长度要按字符串处理不要用 INT。区划代码常见做法是分级补位省级2位、地市4位、区县6位、乡镇9位、村级12位但不同数据源会有差异以资源包实际字段为准。用 INT 会丢前导零Excel 双击打开也会把长编码变成科学计数法这个坑后面还会单独讲。2.3 树形结构落库邻接表、路径表与闭包表怎么选树形结构常见的三种落库方式放到77万条这个量级要分开看。邻接表就是 parent_id优点是写入简单、移动节点方便缺点是查整棵子树要递归。路径表是在邻接表上多加 path查子树快代价是移动父级时要批量更新子节点 path。闭包表单独存 ancestor/descendant 关系查询最灵活但77万条全量展开关系表会膨胀得很快除非业务经常做任意层级祖先查询否则没必要一上来就上闭包表。我的选择是邻接表加 path。地址选择器是典型懒加载点省查市点市查区县每次只查直接子节点走idx_parent足够。运营后台要导出某个省下面所有村走 path 前缀匹配也比递归 CTE 稳。移动节点的场景在区划数据里很少真遇到行政区划调整走增量脚本重算 path比为了偶尔的移动把整张表做成闭包表划算。提示path 里存 id 不存名称名称会变id 不变。full_name 可以重算path 是结构别混在一起。3. 跑起来第一步把原始数据清洗并导入数据库拿到数据包后不要直接往业务库里灌。先做一份中间清洗表把字段名、层级、父级编码、前导零这些问题处理掉再进正式表。77万条不算海量但字段错一列、层级错一级后面排查成本会翻倍。我一般分三步原始文件转统一 CSV生成 id 和 parent_id导入 MySQL 后跑完整性校验。3.1 原始字段识别与清洗脚本不同来源的列名不一样有的叫“区划代码”有的叫 code有的直接给“名称/上级代码”。先做一个字段别名映射把原始行转成统一结构。下面这段脚本只做清洗和层级归一不直接连库方便你先在本地看结果。import csv from pathlib import Path RAW Path(raw.csv) OUT Path(region_clean.csv) # 常见列名别名拿到新数据包时优先改这里 FIELD_ALIAS { code: [code, 区划代码, 行政区划代码, 编码], name: [name, 名称, 区划名称, 地名], parent_code: [parent_code, 父级代码, 上级代码, pcode], level: [level, 层级, 级别], } LEVEL_MAP { 省: 1, 省级: 1, 市: 2, 地市: 2, 地级市: 2, 区县: 3, 县: 3, 区: 3, 乡镇: 4, 街道: 4, 镇: 4, 乡: 4, 村: 5, 村委会: 5, 社区: 5, } def pick(row, names): for n in names: if n in row and row[n].strip(): return row[n].strip() return def main(): with RAW.open(r, encodingutf-8-sig, newline) as fin, \ OUT.open(w, encodingutf-8, newline) as fout: reader csv.DictReader(fin) writer csv.DictWriter( fout, fieldnames[code, name, parent_code, level] ) writer.writeheader() for row in reader: code pick(row, FIELD_ALIAS[code]) name pick(row, FIELD_ALIAS[name]) parent pick(row, FIELD_ALIAS[parent_code]) level_raw pick(row, FIELD_ALIAS[level]) if not code or not name: continue level LEVEL_MAP.get(level_raw) if level is None: # 没有层级列时用编码长度兜底实际以数据包为准 level {2: 1, 4: 2, 6: 3, 9: 4, 12: 5}.get(len(code)) writer.writerow({ code: code, name: name, parent_code: parent, level: level, }) if __name__ __main__: main()这段脚本的重点有三个。第一encodingutf-8-sig处理带 BOM 的 CSV不然第一列列名可能带不可见字符后面取不到值。第二pick按别名顺序取第一个非空值兼容不同列名。第三LEVEL_MAP只做常见映射遇到“街道”“镇”“乡”都归到第4级“村委会”“社区”归到第5级如果数据包有更细的分类另加category字段不要硬改 level。清洗完先看两件事code有没有重复parent_code能不能在code集合里找到。前者决定唯一索引能不能建后者决定树会不会断。可以在脚本里加一段统计也可以在 Excel 里做透视但别用 Excel 直接保存长编码容易丢前导零。3.2 MySQL 建表与批量导入导入前把local_infile打开CSV 列顺序和表字段对齐。下面这条LOAD DATA先灌基础字段path 和 full_name 留到导入后统一回填避免一边导入一边算路径。LOAD DATA LOCAL INFILE /tmp/region_import.csv INTO TABLE region CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (id, parent_id, region_code, region_name, level, sort_no, status) SET level level, sort_no IFNULL(sort_no, 0), status IFNULL(status, 1), path , full_name region_name;LOAD DATA比一条条 INSERT 快很多77万条在普通开发机上几分钟能进完具体看磁盘和索引。导入前可以先把非唯一索引删掉灌完再建能省不少时间。id和parent_id要在 CSV 里提前生成不要指望数据库按名称自动关联重名乡镇和重名村会让关联直接翻车。生成 id 和 parent_id 的脚本可以接在清洗后面先读全部行建code - id字典再回写parent_id。import csv rows list(csv.DictReader(open(region_clean.csv, encodingutf-8))) code_to_id {r[code]: i for i, r in enumerate(rows, start1)} for i, r in enumerate(rows, start1): r[id] i r[parent_id] code_to_id.get(r[parent_code], 0) with open(region_import.csv, w, encodingutf-8, newline) as f: writer csv.DictWriter( f, fieldnames[id, parent_id, code, name, level] ) writer.writeheader() for r in rows: writer.writerow({ id: r[id], parent_id: r[parent_id], code: r[code], name: r[name], level: r[level], })这里parent_id0表示找不到父级省级节点也应该是0。导入后一定要查parent_id0且 level1 的记录数量不为0就说明有孤儿节点。孤儿节点不一定是数据错也可能是原始包把某个层级拆到了另一个文件先定位再决定补父级还是剔除。path 和 full_name 的回填我一般用 Python 按 level 从小到大跑一遍。父节点先处理子节点才能拼出完整路径。77万条一次性executemany可能占内存分批5000条更新更稳。import pymysql conn pymysql.connect( host127.0.0.1, userdemo, passworddemo, databaseregion_db, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor ) with conn.cursor() as cur: cur.execute(SELECT id, parent_id, region_name FROM region ORDER BY level, id) rows cur.fetchall() path {0: } full {0: } updates [] for r in rows: pid r[parent_id] path[r[id]] path.get(pid, /) f{r[id]}/ full[r[id]] full.get(pid, ) r[region_name] updates.append((path[r[id]], full[r[id]], r[id])) if len(updates) 5000: cur.executemany( UPDATE region SET path%s, full_name%s WHERE id%s, updates ) updates.clear() if updates: cur.executemany( UPDATE region SET path%s, full_name%s WHERE id%s, updates ) conn.commit() conn.close()这段代码按level, id排序保证父节点先出现在结果里。path用 id 拼接full_name用名称拼接两者分开维护。updates分批提交避免一次攒77万条把内存顶上去。跑完后抽查一条村级记录看path的斜杠数量是不是5段full_name是不是从省到村的完整名称。3.3 导入后的完整性与层级校验导入完成别急着接前端先跑四类校验层级数量、孤儿节点、重复编码、path 与 level 是否匹配。下面几条 SQL 可以直接抄。-- 1. 各层级数量先看分布是否合理 SELECT level, COUNT(*) AS cnt FROM region GROUP BY level ORDER BY level; -- 2. 孤儿节点非省级但父级不存在 SELECT COUNT(*) AS orphan_cnt FROM region c LEFT JOIN region p ON c.parent_id p.id WHERE c.parent_id 0 AND p.id IS NULL; -- 3. 重复编码 SELECT region_code, COUNT(*) AS cnt FROM region GROUP BY region_code HAVING cnt 1; -- 4. path 段数与 level 粗校验 SELECT COUNT(*) AS bad_path_cnt FROM region WHERE level 5 AND (LENGTH(path) - LENGTH(REPLACE(path, /, ))) 5;第2条查出孤儿节点先按region_code回原始 CSV 找父级编码再看父级是不是被过滤掉了。第3条查出重复编码通常是前导零丢失或同一编码在多个文件重复出现。第4条是粗校验村级 path 应该有5段斜杠如果对不上回填脚本的排序或 parent_id 有问题。校验通过后再建业务索引顺序别反。4. 查询与接口层地址选择器、模糊搜索和按层级懒加载数据导入只是底座前端真正要的是“点一下省返回市点一下市返回区县输入关键词返回带全路径的候选”。接口设计要围绕 parent_id 做不要每个层级写一个/province、/city接口否则第六级扩展时又要加一组。一个/regions加 parent_id 和 level 参数前后端都省事。4.1 地址选择器的懒加载接口下面用 FastAPI 写一个最小接口返回直接子节点并带has_children给前端判断要不要显示下一级箭头。SQL 里用 EXISTS 子查询走idx_parent每次只查一层77万条也不会慢。from fastapi import FastAPI, Query from typing import Optional import pymysql app FastAPI() def get_conn(): return pymysql.connect( host127.0.0.1, userdemo, passworddemo, databaseregion_db, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor ) app.get(/regions) def list_regions( parent_id: int Query(0, ge0), level: Optional[int] Query(None, ge1, le5), ): sql SELECT r.id, r.parent_id, r.region_name, r.level, EXISTS( SELECT 1 FROM region c WHERE c.parent_id r.id AND c.status 1 ) AS has_children FROM region r WHERE r.status 1 AND r.parent_id %s args [parent_id] if level is not None: sql AND r.level %s args.append(level) sql ORDER BY r.sort_no, r.id conn get_conn() try: with conn.cursor() as cur: cur.execute(sql, args) return cur.fetchall() finally: conn.close()parent_id0返回省级前端第一屏直接调这个。点某个省时把该省 id 传进来返回地市再点地市返回区县。has_children用来控制是否显示“继续选择”按钮避免前端为了判断有没有下一级再发一次请求。level是可选参数正常懒加载不需要传做后台按层级导出时才用。注意不要让前端保存区划名称当外键。名称会重名也会随区划调整变化业务表存region_code或主表id展示时再关联full_name。4.2 模糊搜索前缀、拼音与“六级地区数据”的扩展搜索框是另一个高频入口。用户可能输“某镇”“某村”也可能只输首字母。最稳的 SQL 是前缀匹配加层级排序别一上来就LIKE %关键词%77万条全表扫描在并发下很难看。SELECT id, region_name, full_name, level FROM region WHERE status 1 AND ( region_name LIKE CONCAT(%s, %) OR pinyin LIKE CONCAT(%s, %) ) ORDER BY level DESC, CHAR_LENGTH(region_name), id LIMIT 20;LIKE CONCAT(%s, %)是前缀匹配能走idx_namepinyin字段如果建了索引也能走。排序把层级高的放前面用户搜“某村”时更容易看到村级结果再用full_name区分同名。真要做“包含搜索”可以加 MySQL 的 ngram 全文索引或者把搜索请求打到独立搜索引擎但那是第二步不要一开始就把简单查询复杂化。六级地区数据扩展也在这层处理。主表只到村委会扩展表region_ext挂村小组、网格、片区接口多一个sourceext参数即可。下面这张扩展表只存第六级parent_id 指向主表村委会 id。CREATE TABLE region_ext ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, parent_id BIGINT UNSIGNED NOT NULL COMMENT 指向region.id的村委会, ext_name VARCHAR(100) NOT NULL, ext_code VARCHAR(20) DEFAULT NULL, sort_no INT NOT NULL DEFAULT 0, status TINYINT NOT NULL DEFAULT 1, PRIMARY KEY (id), KEY idx_parent (parent_id, status, sort_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这样主表数据包升级时不用动扩展表扩展表也不会污染五级结构。前端地址选择器在第五级后面多渲染一个“村组/网格”列有数据就显示没有就隐藏。4.3 缓存、分页与热点数据地址选择器的访问有明显热点省级、地市、区县这几层被反复查村级搜索相对分散。我的做法是给直接子节点加 Redis 缓存key 按region:children:{parent_id}:{level}拼TTL 设1小时数据更新时主动删相关 key。根节点和第一、二级缓存命中率很高能挡掉大部分重复查询。import json import redis r redis.Redis(host127.0.0.1, port6379, db0, decode_responsesTrue) def cache_children(parent_id: int, level: int | None, rows: list): key fregion:children:{parent_id}:{level or 0} r.setex(key, 3600, json.dumps(rows, ensure_asciiFalse)) def read_children(parent_id: int, level: int | None): key fregion:children:{parent_id}:{level or 0} val r.get(key) return json.loads(val) if val else None缓存里存接口返回的 JSON读出来直接返回不用再查库。数据更新脚本跑完后按 parent_id 删region:children:{parent_id}:*或者干脆按前缀扫一遍删掉。分页在区划接口里一般不需要单层子节点数量有限搜索接口要限制LIMIT不要一次返回几百条。5. 避坑与排查77万条区划数据最容易翻车的5个地方区划数据的坑很集中层级、编码、重名、搜索、更新。下面这5条是我在地址类项目里反复见到的翻车现场每条按“现象、原因、解决”拆开照着排查能少走很多弯路。5.1 层级断档省下面直接挂区县或村级找不到父级现象是前端选完省市的下拉框空着或者后台导出某个市下面所有村结果少了一大片。原因通常有两个原始数据的parent_code列和code列不是同一套编码或者导入时按名称关联父子重名乡镇把关系挂错了。还有一种情况是清洗脚本把parent_code当数字读前导零丢了code_to_id字典里找不到父级parent_id全变成0。解决方式是先建code - id的字典再按parent_code回填parent_id全程按字符串处理。导入后跑孤儿检查SELECT c.id, c.region_code, c.region_name, c.parent_id FROM region c LEFT JOIN region p ON c.parent_id p.id WHERE c.parent_id 0 AND p.id IS NULL LIMIT 50;查出来的记录回原始 CSV 找父级编码看是父级缺失还是编码格式不一致。如果是层级列本身缺失用编码长度兜底只适合结构规整的数据包最好还是以原始层级列为准。5.2 重名导致选错搜索“某村”出现几十条一模一样的结果现象是用户搜一个村名下拉框里几十条同名记录前端只显示region_name根本分不清是哪个乡镇下面的。原因不是数据重复而是展示字段太少。区划数据里同名乡镇、同名村很常见只存名称、只展示名称一定会选错。解决方式是接口返回full_name和path前端展示全路径业务表存region_code或主表 id。搜索时加层级过滤比如用户已经选了市就在该市path前缀下搜别在全表搜。SQL 可以这样收紧SELECT id, region_name, full_name, level FROM region WHERE status 1 AND path LIKE CONCAT(%s, %) AND region_name LIKE CONCAT(%s, %) ORDER BY level DESC, id LIMIT 20;path前缀把范围限定在已选父级下面重名概率立刻降下来。5.3 编码前导零丢失导入后唯一键冲突或代码对不上现象是导入时报Duplicate entry或者导完后发现某些区划代码少了一位。原因多半是 CSV 被 Excel 打开后另存长编码被当成数字前导零没了也可能是 Python 读 CSV 时没指定字符串类型pandas 自动推断成 int。区划代码是标识不是数值不能参与加减乘除。解决方式是建表用VARCHAR读文件时全部按字符串处理。用 pandas 的话显式写dtypestrimport pandas as pd df pd.read_csv(raw.csv, dtypestr, keep_default_naFalse) df[code] df[code].str.strip() df[parent_code] df[parent_code].str.strip()keep_default_naFalse避免空值变成NaN后被写成字符串nan。导入前抽查几条长编码确认位数和原始文件一致再跑唯一索引。5.4 搜索接口慢LIKE %关键词%把77万条扫了一遍现象是地址搜索框输入两个字接口要一两秒并发一上来直接超时。原因很直接前置%让 B-Tree 索引失效77万条全表扫描再加上排序和full_name拼接耗时更高。地址搜索是高频操作不能靠加机器硬扛。解决方式是优先做前缀搜索把LIKE %关键词%改成LIKE 关键词%让idx_name生效。拼音搜索也按前缀走pinyin LIKE mou%。如果产品一定要包含搜索再加 ngram 全文索引或独立搜索服务别在主库上硬扫。用EXPLAIN看执行计划重点看type是不是ALLrows是不是接近全表。EXPLAIN SELECT id, region_name, full_name FROM region WHERE region_name LIKE 某% LIMIT 20;执行计划里出现range或ref才算走上索引。搜索接口还要限制返回条数前端做防抖输入停顿300毫秒再请求能挡掉大量无效查询。5.5 更新数据没有后悔药truncate 重建导致业务 id 全变现象是区划数据升级一次历史订单里的地址关联全乱了或者缓存里还是旧名称。原因是每次更新都TRUNCATE region再全量导入自增 id 重新生成业务表存的老 id 全对不上。区划数据不是日志不能这么干。解决方式是用region_code做 upsert保留已有 id新增的插入删除的标停用而不是物理删除。更新前把旧表备份一份更新后跑孤儿和 path 校验再按 parent_id 清缓存。下面这条 upsert 按唯一键更新名称、父级和层级INSERT INTO region (id, parent_id, region_code, region_name, level, path, full_name, status) VALUES (%s, %s, %s, %s, %s, , %s, 1) ON DUPLICATE KEY UPDATE parent_id VALUES(parent_id), region_name VALUES(region_name), level VALUES(level), full_name VALUES(full_name), status 1;父级变了要重算该节点及子节点的path不能只更新当前行。更新脚本跑完抽查几个有历史订单的region_code确认 id 没变、名称已更新、path 还对得上。缓存删除按region:children:*前缀处理别只删根节点。6. 二次开发把静态区划表改成可维护的地址中台这份数据拿来做地址选择器只是第一步真要长期用得把它当成“可维护的地址中台”来改。我的改造顺序是先加变更日志再做增量导入最后把查询接口收口。变更日志表不用复杂记录region_code、变更类型、旧父级、新父级、变更时间即可出问题时能回放。改造点做法验证方式增量更新按 region_code upsert保留 id对比更新前后 count 和抽样 id变更日志记录新增、停用、父级变化回放一条区划调整接口收口统一/regions和/regions/search前端不再按层级写死接口第六级扩展单独建 region_ext村委会下能查出村组增量导入时先把新数据清洗成同样结构跑一遍code去重和孤儿检查再进临时表。临时表和主表按region_code做 diff新增的插入消失的标status0父级变化的记日志并重算 path。不要直接覆盖主表也不要每次重建自增 id。INSERT INTO region_change_log (region_code, change_type, old_parent_id, new_parent_id, changed_at) SELECT n.region_code, parent_changed, o.parent_id, n.parent_id, NOW() FROM region_stage n JOIN region o ON o.region_code n.region_code WHERE o.parent_id n.parent_id;验证方法很土但有效更新后跑一遍第3章的孤儿检查、重复编码检查和 path 段数检查再抽三个有历史订单的地址确认region_code能关联到新名称id 没漂。缓存按 parent_id 清一遍前端重新点一轮省市区县乡镇村。从那以后我每次导区划数据都先把编码按字符串读、先跑孤儿检查、再让业务接接口顺序反一次就得花半天找 id。希望帮到你。本文还有配套的精品资源点击获取