★ FEATURED ARTICLE 做业务系统的这些年我有个很深的感触越是看起来不起眼的基础数据越容易在关键时刻给你一记闷棍。行政区划数据就是典型例子——电商下单要省市县三级联动物流分单要按区域做路由统计报表要按省市区汇总连填个注册地址都要校验一遍省市区是否匹配得上。这个项目提供的正是这样一份刚需数据覆盖省级、地级、县级、乡级、村级五个层级同时给出 SQL 和 CSV 两套文件无论你是做后端开发、数据分析还是搭建可视化大屏都能直接用起来不用再到处找残缺不全的旧数据。这篇文章我会从数据结构设计讲起把 12 位区划代码的规律、表字段怎么定、SQL 和 CSV 各自适合什么场景、级联查询怎么做、行政区划变动时怎么维护以及我在实际项目中踩过的几个坑一次性说清楚。适合理想上需要一份离线行政区划数据的开发者、数据产品和运维同学参考。1. 行政区划数据到底难在哪先说说这个项目的立足点1.1 一个再常见不过的业务痛点我和不少做系统的朋友聊过大家手里多多少少都有一份“祖传”的行政区划数据要么是前端项目里写死的一个 JSON 文件要么是从某个技术博客复制来的 SQL 脚本注释里写着“更新于某年”之后就一直没动过。平时开发环境里跑着没毛病一上线就露馅——用户选的区和街道对不上物流系统解析地址失败统计报表里出现“未知区域”。这种问题本质上不是因为大家不认真而是行政区划数据确实难维护。它的变化频率远比一般人想象的高撤县设区、撤地设市、乡镇合并、村居更名每年都会有一批变动。而且网络上流传的数据来源杂乱有的只有省市区三级没有乡镇和村级有的字段命名五花八门有的还用拼音缩写整合起来非常费劲。所以当我看到这个项目把五级数据整理成规范的两套文件时第一反应是“终于有人把这事干利索了”。它解决的不是某个具体功能问题而是一类基础设施问题让业务系统在起步阶段就有一份可靠的、可追溯的行政区划底表。1.2 五级覆盖带来的新可能性市面上常见的行政区划数据集大多是三级也就是省、市、区县。三级数据应付电商收货地址、注册表单这类场景基本够了但一旦业务触角往下探问题就出来了。物流末端配送快递分拨到区县之后还得知道街道乡镇的归属才能安排具体网点。乡村业务做农村电商、农资配送、冷链调度必须精确到乡镇甚至行政村。政府服务类系统社区治理、网格化管理、基层普查数据粒度天然就是村和社区。数据报表与可视化大屏想展示某个区下面的乡镇分布没有乡级、村级数据图表就只能停在区县这一层。这个项目补上的正是这一段链条。省级、地级对应宏观管理县级承接常规业务乡级和村级支撑末端场景。五级齐了从省到村我就能用同一套编码体系做完整的下钻和聚合不需要再为末端数据去单独采购或爬取。1.3 双格式设计为什么实用只看数据内容还不够交付格式往往决定了一个数据集能不能被顺利用起来。这个项目同时提供 SQL 和 CSV 两种文件这个细节很加分。SQL 文件是给要落库的人准备的建表语句、插入语句一气呵成MySQL 或 PostgreSQL 导进去就能跑CSV 文件是给数据分析场景准备的Python 里 pandas 直接读Excel 里也能打开查看和筛选。两种格式不是重复造轮子而是对应了两类完全不同的使用者后端工程师要的是“开箱即用”数据分析师要的是“灵活可读”。下面我会分别把两套文件的使用方式拆开讲。2. 五级模型与区划代码表结构这样设计才不乱2.1 先搞懂 12 位区划代码的规律拿到数据之前我强烈建议先花十分钟理解区划代码本身的编码规则。这是整个数据的骨架理解了它后面所有查询、统计、维护都顺理成章。标准区划代码通常是 12 位数字可以拆成三段来看前 6 位是县级及以上区域的编码本身又可以拆成三段每段 2 位前 2 位代表省级中间 2 位代表地级后 2 位代表县级。这也正是身份证号码前 6 位的含义。第 7 到第 9 位对应乡级街道、镇、乡。第 10 到第 12 位对应村级村委会、居委会。举例来说假如我用“接着编一个不存在的 A 省”做演示A 省的省级代码是990000000000它下面的 B 市地级代码是990100000000B 市下的 C 县县级代码是990102000000C 县下面的示范镇代码是990102001000示范镇下面的示例村代码是990102001001。注意一个规律每一级代码末尾补零补齐 12 位。所以从代码本身就能直接判断它的层级也能通过前缀快速找到父子关系。这个特性在实际查询时非常有用。2.2 单表搞定五级建表 SQL 长这样这个项目把五级数据放在同一张表里用level字段区分层级、用parent_code维护父级关系。个人认为这是很合理的设计五级数据的量级在几十万条左右单表完全可以承载而且比拆成五张表更灵活。下面是参考建表语句保留了最核心的字段CREATE TABLE region ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 自增主键, code CHAR(12) NOT NULL COMMENT 12位行政区划代码, name VARCHAR(50) NOT NULL COMMENT 行政区划名称, level TINYINT NOT NULL COMMENT 层级1省级 2地级 3县级 4乡级 5村级, parent_code CHAR(12) DEFAULT NULL COMMENT 父级区划代码省级为NULL, short_name VARCHAR(50) DEFAULT NULL COMMENT 简称, pinyin VARCHAR(100) DEFAULT NULL COMMENT 拼音用于搜索联想, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1启用 0停用, sort_order INT NOT NULL DEFAULT 0 COMMENT 排序权重, PRIMARY KEY (id), UNIQUE KEY uk_code (code), KEY idx_parent_code (parent_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT行政区划五级表;这里有几个设计点值得展开说一下。主键用自增 id业务查询用 code 唯一键。行政区划代码才是真正的业务主键但它可能随区划调整而变化所以保留一个自增 id 做数据库内部标识更稳妥。level 字段必须加。虽然按 code 前缀可以判断层级但直接存一个 level 字段查询和索引都更直观。parent_code 建索引。级联查询是高频操作父级编码的索引直接影响性能。status 字段强烈建议保留。行政区划会有“停用”的情况用 status 标记比直接删除安全得多这一点我在第 5 节会详细说。CSV 文件的结构和这张表是镜像的每条记录对应一行表头包括code,name,level,parent_code,pinyin,status。这样你在 Excel 里查一个城市的时间和直接在数据库里查的时间成本差不多。2.3 为什么编码比名称更适合做主键有些人刚接手时会想直接用名称做关联不就行了为什么非要搞一整套代码我拿实际踩过的坑来回答你。第一名称不稳定。同一个县级市可能从“县”改成“区”乡镇合并后名字直接消失而代码一旦分配在同一轮区划调整中通常尽量保持不变名称变了但 code 还能对上这对历史数据的追溯意义重大。第二名称不唯一。全国叫“城关镇”“中心街道”的地方数不胜数单靠名称做关联十有八九会串数据。加上“省份城市区县”的全路径去匹配文本匹配的复杂度立刻上去了而用代码做关联一次精确匹配就结束。第三代码天然带层级。LEFT(code, 2)就是省级LEFT(code, 4)就是地级LEFT(code, 6)就是县级。这种结构让跨级统计变得极其简单名称字段做不到这一点。理解了这三点你就明白为什么所有规范的行政区划数据集都把 code 作为核心字段了。3. SQL和CSV两套文件的定位差异与实际导入操作3.1 SQL 文件适合快速落库SQL 文件里面通常包含两部分建表语句和数据插入语句。拿到之后你只需要创建一个数据库然后直接导入即可。以 MySQL 为例基本流程是这样mysql -uroot -p CREATE DATABASE IF NOT EXISTS region DEFAULT CHARACTER SET utf8mb4; USE region; SOURCE /path/to/region.sql;PostgreSQL 这边我用的是 psql 的\i命令psql -U postgres -d region -f /path/to/region.sql导入完成后先跑两条验证 SQLSELECT level, COUNT(*) FROM region GROUP BY level; SELECT * FROM region WHERE parent_code IS NULL;第一条看每个层级的数据量是否符合预期第二条抽查省级记录是否完整。验证这一步千万别省早发现问题早处理等业务跑起来才发现数据少了才叫难受。导入 SQL 文件时最容易遇到两个问题。一是字符集不对建库时没有指定utf8mb4导入后中文变乱码二是max_allowed_packet设置太小插入记录多的 SQL 语句可能被 MySQL 拒绝报Packet for query is too large。解决方法是在 MySQL 配置文件里调大参数或者把大 SQL 拆开分批执行。3.2 CSV 文件适合数据分析与跨语言使用CSV 的价值在于通用性。你没法保证每个项目都恰好用 MySQL但 CSV 在任何语言里都能按标准方式读取。Python 这边最常用的姿势是 pandasimport pandas as pd df pd.read_csv(region.csv, dtype{code: str, parent_code: str}) print(df.head()) print(df[level].value_counts())注意dtype里强制把 code 和 parent_code 转成字符串。如果不加这一步pandas 会把001这种前导零的代码当成整数前导零丢失后代码就废了。这个问题我见过不止一次凡是用非字符串类型读区划代码的迟早要吃大亏。如果 CSV 在 Excel 里打开出现乱码大概率是文件编码与 Excel 默认编码不一致。处理办法有两个一个是用带 BOM 的 UTF-8 格式Excel 能自动识别另一个是用utf-8-sig读取df pd.read_csv(region.csv, dtype{code: str}, encodingutf-8-sig)数据量不大时用 Excel 直接筛选和手工核对也完全可行CSV 在纯手工场景下比 SQL 友好太多。3.3 两种格式怎么选我给一个参考表很多朋友会纠结到底用哪个文件我把两者的差异整理成一个对照表对比维度SQL 文件CSV 文件核心用途快速建库、后端落库数据分析、跨语言读取、人工核对依赖条件需要 MySQL/PostgreSQL 等数据库任意文本工具即可打开导入成本命令行导入适合批量pandas/Excel 读取灵活轻量类型风险建表时需注意字符集读取时需注意编码与前导零适用人群后端开发、DBA数据分析师、测试、产品我的建议是正式环境一定以 SQL 落库为准CSV 作为分发和校验的副产物。两者内容一致但 SQL 能保证数据类型和约束在数据库层面被强制执行CSV 更自由也就更容易被“自由地”读错。4. 从省到村的下拉联动与关联查询落地实操4.1 级联菜单接口可以这样设计拿到了五级数据最常见的落地场景就是行政区划级联选择。以前的做法是在前端写死一个 JSON后端只存一个完整编码。这种做法在五级数据面前会非常臃肿更合理的做法是后端提供下级查询接口SELECT code, name, level FROM region WHERE parent_code :parentCode AND status 1 ORDER BY sort_order, code;对应的接口语义是GET /regions/children?parent990000000000查询“A 省”下的地级单位返回列表。前端拿到第一级后每次用户选中一项就再携带该项的 code 查询下一级直到第 5 级。这套方案的关键是给parent_code建索引。五级数据量虽然不算大但级联接口是高频调用没有索引很容易在并发稍高时拖垮数据库。上面建表语句里已经包含了idx_parent_code注意别在建表时顺手删了。如果某些场景希望一次请求拿到省到村的完整路径可以用递归 CTEWITH RECURSIVE region_path AS ( SELECT code, name, level, parent_code FROM region WHERE code :targetCode UNION ALL SELECT r.code, r.name, r.level, r.parent_code FROM region r INNER JOIN region_path rp ON r.code rp.parent_code ) SELECT * FROM region_path ORDER BY level;这个写法在 PostgreSQL 和 MySQL 8.0 里都支持适合给“地址回显”场景使用——用户保存过的地址只有一个村级编码打开详情页时需要反推出省、市、区的完整名称。4.2 用区划代码做跨级聚合统计展开一个区域维度做统计报表区划代码的前缀规律能派上大用场。假设业务表order里存了收货地址的 12 位区划代码region_code我只想统计各省的订单量SELECT LEFT(region_code, 2) AS province_code, COUNT(*) FROM order WHERE region_code IS NOT NULL GROUP BY LEFT(region_code, 2);想下钻到地级改成LEFT(region_code, 4)下钻到县级改成LEFT(region_code, 6)。不用 JOIN 任何区域表就能先拿到一个编码维度的聚合结果。要和名称关联时再 JOINregion表SELECT r.name, COUNT(*) FROM order o INNER JOIN region r ON LEFT(o.region_code, 6) r.code WHERE r.level 3 GROUP BY r.code, r.name;这套写法的核心思想是区划代码本身就是一个携带层级信息的维度字段不要在业务表里只存名称不存代码否则后续所有统计分析都会变成一场文本匹配的灾难。4.3 历史数据没有代码怎么办兜底匹配的通用思路现实项目里没有那么理想。存量用户数据很多只存了文本地址没有区划代码这时候就需要做一次地址补全给老数据“回填”代码。我的做法是优先做三级匹配把地址文本按“省”“市”“区/县”等关键词做切分先尝试提取省级和地级。用region表按名称做匹配先匹配县级再根据parent_code反查地级和省级。匹配不上时进入人工核对队列宁可标记为“待确认”也不要随便给一个错误代码。有一个细节特别值得提醒名称里带“市”不代表它一定是地级。县级市的名字里也带“市”直辖市下属的区里也可能出现“市辖区”这类特殊名称。绝对不要按“名称里是否包含某个字”来推断层级一切以 level 字段和 parent_code 为准。这是新手最容易错的一环。5. 数据维护与更新行政区划变更时的处理思路5.1 行政区划为什么老在变我最早也天真地以为行政区划是“一次维护、终身使用”的数据。后来发现完全不是。现实中触发数据变更的情形很多比如县改为区、县级市升级为地级市新设立开发区、新区乡镇合并、街道拆分村居合并、更名城中村改造后居委会撤销。这些变动对做业务的人来说直接意味着昨天还在用的区划代码明天可能就查不到对应的名称了。所以在设计阶段就把“数据会变”作为一个基本假设而不是等到线上碰壁才补救。5.2 一套可落地的更新流程我不建议拿到新数据后直接清空旧表重新导入那样会把所有历史关联关系一并“清空掉”。更稳妥的更新流程是三步走。第一步比对。把新数据与库内现有数据按 code 做对比找出三类差异新出现的 code、消失的 code、名称或层级发生变化的 code。-- 以导入一张新表 region_new 为例找出新增的 code SELECT n.code, n.name FROM region_new n LEFT JOIN region r ON n.code r.code WHERE r.code IS NULL;第二步停用旧数据。对“消失”的旧 code不要 DELETE而是更新 status 字段UPDATE region r LEFT JOIN region_new n ON r.code n.code SET r.status 0, r.remark 区划调整停用 WHERE n.code IS NULL AND r.status 1;第三步写入新增。将新增的 code 一次性插入正式表并保留parent_code关系。这套流程的关键价值在于业务表里的历史数据不会因为区划代码停用而失效你只是给旧代码打了“失效标记”查询时默认status 1需要追溯历史时可以放开条件看到旧名称。5.3 让历史数据可追溯的更远一步如果你的系统对区划有效性有强诉求比如要做审计、对账、历史快照我建议在区域表扩展两个字段valid_from和valid_to分别标识该记录的有效起始和结束时间。日常查询统一加上WHERE valid_from NOW() AND (valid_to IS NULL OR valid_to NOW())就能随时看到“当前应该生效的区划”。要做历史还原时把时间参数带进去即可。再进一步可以建一张region_mapping表记录“旧 code - 新 code”的迁移关系CREATE TABLE region_mapping ( old_code CHAR(12) NOT NULL, new_code CHAR(12) NOT NULL, change_type VARCHAR(20) COMMENT 变更类型撤并/升格/更名等, changed_at DATE NULL, PRIMARY KEY (old_code) );这样就算某个乡镇被合并到隔壁镇原有的村级数据也能通过 mapping 表映射到新区域不至于在统计时永久“失联”。这套设计在交易系统、账务系统里非常实用。6. 我在实际使用中踩过的坑和几条避坑建议6.1 编码问题比想象中频繁一组数据文件最容易被忽略的就是编码。我见过同事在 Windows 下用记事本打开 CSV另存为 ANSI 编码后直接扔给后端读取结果所有中文全部变“锟斤拷”。解决办法就两条数据库侧统一utf8mb4CSV 文件保持 UTF-8并且读取时优先用带utf-8-sig的处理方式兼容 BOM 头。另外导入前先看几行原始数据确认中文显示正常。这个动作十秒钟能帮你省掉一次“全表乱码重导”的返工。6.2 “市”这个字最容易坑人前面提过一次这里再单独说透。当你从接口或数据中拿到一个叫“某某市”的名称时先别急着认定它的层级名称示例实际层级判断依据某直辖市省级level 字段为 1某地级市地级level 字段为 2某县级市省直辖县级level 字段为 3parent_code 直接挂省级某县级市属地级市代管县级level 字段为 3parent_code 挂地级判断层级唯一可靠的手段是 level 字段和 parent_code 关系名称只是一个展示维度。我曾经在一个项目里为了省事用“名称以市结尾”推断层级结果省直辖县级市的统计全部错挂到地级排查了两天才找到根因。从那以后凡是涉及层级判断我规定代码里只能查字段不能猜名称。6.3 村级数据质量要提前打预防针五级数据里村级的数据量最大变动也最频繁。很多村在乡镇合并后名称随之调整城中村改造后原来的村委会改成居委会编码也随之变化。这意味着两件事。第一不要假设村级代码永恒不变做缓存时要有失效策略定期触发全量刷新第二历史订单、历史地址不要在前端展示时硬查村级名称查不到就降级显示上一级名称这样用户体验不会断。如果业务允许我建议村级数据做成“次日生效”的异步更新任务而不是在线上交易链路里同步读取并解析。村级数据的实时性要求通常没高到需要在毫秒级链路里处理。6.4 长期维护的两条建议第一确立唯一数据源。数据库里只有一张region表是权威数据CSV、Excel、JSON 都只是它的导出产物。所有对区划数据的修改必须走这张表不允许谁手里攥着一份自己的副本各改各的。第二每次更新前先做备份。导入新数据或执行批量 UPDATE 前导出当前表的完整快照。这个习惯救过我很多次尤其是批量更新语句里漏了 WHERE 条件的时候。更新完成后再跑一次五级数据量的汇总对比确认没有异常丢失。数据维护这种事多一分谨慎线上就少一分事故。区划数据看着简单但它牵连的系统面太广了前期把结构和流程想清楚后面会轻松很多。
阅读完成 · 觉得有帮助？
