全国行政区划代码数据集：SQL/JSON/CSV多格式维度表设计指南

简介一份面向开发者与数据分析师的全国行政区划代码数据集涵盖国家至县区级行政单位适用于GIS地图定位、统计分析、邮政编码匹配及地址标准化等场景。资源共4个文件压缩包大小201KB提供SQL、JSON、CSV、XLS四种格式可直接导入MySQL等数据库、供Web接口调用或在Excel中筛选查阅满足不同工具链的使用习惯。数据集共收录3050条记录字段包含行政代码、级别与名称便于快速关联区域信息。由于数据源自网络整理使用时建议与官方区划信息交叉校验行政区划变更频繁可按最新公告自行更新维护。已有2013人学习浏览对需要搭建基础地理数据模块或处理地址数据的开发者而言是一份实用且轻量的基础数据集。1. 行政区划代码不是一串数字是一个维度行政区划代码表像是一份为全国省、市、县三级区域统一发放的“身份编号”。做订单后台时遇到过一个问题同一地址一侧存成“北京市朝阳区”另一侧落库成了“北京朝阳”字符串匹配完全对不上最后靠 6 位行政代码做维度关联才把两批数据洗干净。这份数据集包含 3050 条记录覆盖省、市、县三级并提供 SQL、JSON、CSV、XLS 四种格式既能直接灌进 MySQL 做维度表也能丢给 Python 做地址清洗与统计分析。如果你在维护 GIS 系统、物流计价、行政区划联动下拉框或者正在做数据库课程设计这套数据足够把项目里最容易糊弄过去的“区域维度”撑起来。2. SQL 文件里的字段设计code 用 char 还是 int解压后建议先别急着执行administrative_division.sql把文件开头读一遍。很多人在导入后才发现中文乱码或主键重复问题往往出在没看建表语句直接当普通文本导了。这份 SQL 里的核心表结构大致如下CREATE TABLE administrative_division ( code CHAR(6) NOT NULL COMMENT 行政区划代码, level VARCHAR(10) NOT NULL COMMENT 级别省级/市级/县级, name VARCHAR(50) NOT NULL COMMENT 行政区名称, PRIMARY KEY (code), KEY idx_level (level) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT全国行政区划代码表;code是 6 位定长字符串level标记省级、市级、县级name是中文名称。主键直接落在code上这符合行政区划代码的定位每条记录在全国范围内唯一且代码本身具有业务语义不适合用自增 INT 代替。idx_level索引则是为了应对按级别过滤的查询场景。字段类型说明codeCHAR(6)6 位行政区划代码主键levelVARCHAR(10)省级 / 市级 / 县级nameVARCHAR(50)行政区名称如 北京市、朝阳区2.1 为什么 code 不适合用 INT很多从业务表转过来的开发者看到 6 位数字第一反应是用 INT 或 BIGINT。这里有一个容易忽略的细节行政区划代码要求定长虽然大部分代码不以 0 开头但一旦用数值类型存储代码的“编码位”含义就丢了。比如石家庄市是 130100后两位 00 是市级区的汇总标记如果用 INT 存做LIKE前缀匹配、按位截取、与外部接口下发的字符串代码关联时都要额外做一次类型转换。CHAR(6) 在 InnoDB 底层的对比开销并不比 INT 高多少但语义清晰得多。2.2 3050 条记录里的编码规律整套代码遵循“省、市、县”三位一体的截断规则级别代码特征示例省级前两位有效后四位为 0110000 北京市市级前四位有效后两位为 0130100 石家庄市县级六位全有效130102 长安区表中 3050 条记录看起来比很多人预期多是因为省直辖县级行政区划也被算作县级记录例如湖北的 429004 序列。此外直辖市的市辖区直接挂在省级代码下中间没有独立的市级记录处理前端三级联动时要把这种情况单独考虑。数据库基础知识里有一句话始终适用代码表不是业务表不能只看名称去重要以code为唯一键。2.3 level 字段的取舍中文可读TINYINT 更省原始数据里level很可能直接存中文这对临时查询很友好。但如果这张表要在大数据量 JOIN 场景中长期使用我一般会把它改成 TINYINTALTER TABLE administrative_division MODIFY COLUMN level TINYINT NOT NULL COMMENT 1省级 2市级 3县级;改完别忘了同步更新关联代码里的过滤条件例如WHERE level 2。中文枚举在报表里可读但作为维度表的过滤条件TINYINT 的存储和索引效率都更好如果只是做数据库课程设计或本地练手保持原样也没有问题。3. 导入 MySQL 与日常查询写法3.1 命令行导入与可视化工具拿到 SQL 文件后先确认文件里是否包含CREATE DATABASE。如果包含直接执行即可如果不包含需要先建库再导入mysql -u root -p -e CREATE DATABASE IF NOT EXISTS division DEFAULT CHARSET utf8mb4; mysql -u root -p division administrative_division.sql第一行建库指定 utf8mb4 防止中文乱码第二行把 SQL 导入到指定库中。如果文件里已经写了USE语句第二行的库名可以省略但显式指定库名更保险。日常使用 Navicat 或 DBeaver 时也可以用导入向导选 SQL 文件执行效果相同。提示SQL 文件若自带DROP TABLE导入前确认不会覆盖已有数据。3.2 基础查询筛省、筛市、查全称导入完成后最常用的三类查询是查所有省级、查某省的下辖市、根据名称关键词反查代码。-- 查所有省份 SELECT code, name FROM administrative_division WHERE code LIKE __0000 AND code 000000; -- 查河北省代码前缀 13下的所有地级市 SELECT code, name FROM administrative_division WHERE LEFT(code, 2) 13 AND level 市级; -- 根据名称关键字反查行政区划常用于地址清洗 SELECT code, level, name FROM administrative_division WHERE name LIKE %朝阳% LIMIT 20;第一条用两个下划线__0000匹配任意两位省级代码 000000排除空记录第二条用LEFT(code, 2) 13定位河北省再叠加level 市级第三条适合处理用户输入的地址碎片先查代码再做关联。要注意LEFT(code, 2)这类写法在 MySQL 里会触发全表扫描对于 3050 行的维度表无所谓如果未来拆成千万级分区表建议额外冗余一个province_code字段。3.3 用自连接拼省市区全路径报表里经常需要把区县和省市拼成完整路径比如“河北省石家庄市长安区”。用自连接可以基于代码截断关系实现SELECT p.name AS province_name, c.name AS city_name, d.name AS district_name, d.code FROM administrative_division d JOIN administrative_division c ON LEFT(d.code, 4) LEFT(c.code, 4) AND c.level 市级 JOIN administrative_division p ON LEFT(d.code, 2) LEFT(p.code, 2) AND p.level 省级 WHERE d.level 县级 LIMIT 10;逻辑上分为两步县级记录d的前四位对应市级c的前四位前两位对应省级p的前两位。这个写法在普通省份下没有毛病但有两个坑直辖市的区县没有中间市级记录比如 110101 东城区直接挂在 110000 北京市下用c.level 市级会 JOIN 不到省直辖县级行政区划同理如 429004 上边的 4290 并不是完整的地级市代码。常见做法是在应用层把直辖市拉平成“省 - 区县”两级或者预先在表里补一条虚拟市级记录具体取决于你的展示需求。3.4 与业务表关联的写法和防坑地址相关的业务表通常只存 6 位 code比如user_address.area_code。关联维度表时用左连接避免因为代码表缺记录把业务数据过滤掉SELECT ua.user_id, ua.area_code, ad.name AS area_name FROM user_address ua LEFT JOIN administrative_division ad ON ua.area_code ad.code WHERE ua.updated_at 2024-01-01;这里给area_code加普通 B-tree 索引即可因为维度表只有 3050 行驱动表是业务表MySQL 会选择先扫业务表、再逐行查维度表。如果发现有些行关联不上优先检查业务表里是否混入了旧版代码或手工录入的异常值比如已经撤销的区划代码、前四位缩略码等。4. CSV 与 JSON两个高频使用场景4.1 CSV 进 Excel 或 pandasCSV 版最常见的用法是给数据分析师或临时脚本使用。用 pandas 读取时要注意编码问题import pandas as pd # 如果 CSV 第一行是字段名 df pd.read_csv(administrative_division.csv, encodingutf-8-sig) # 如果第一行就是数据需要手动指定字段名 df pd.read_csv( administrative_division.csv, names[code, level, name], encodingutf-8-sig ) print(df.head()) print(df[level].value_counts())utf-8-sig会在读取时自动处理 BOM 头。Excel 直接打开 UTF-8 编码的 CSV 时经常出现中文乱码用带 BOM 的utf-8-sig写出的文件则能被 Excel 正确识别如果你只是用 CSV 来做手工筛选建议直接另存为 XLS 格式更省心。如果想把这批数据落进 MySQL不需要中转 Excel直接用 SQLAlchemy 写更顺from sqlalchemy import create_engine engine create_engine(mysqlpymysql://root:密码localhost/division?charsetutf8mb4) df.to_sql(administrative_division, engine, if_existsappend, indexFalse)if_existsappend表示追加如果重复执行建议先DROP TABLE或者把if_exists改成replace避免主键冲突。4.2 JSON 直接喂前端或 APIJSON 适合做行政区划联动下拉框或开放接口的数据源。先打开文件确认结构常见的有两种平铺数组和嵌套 children 树。如果是平铺数组用 jq 就能快速提取# 提取所有省级记录 jq .[] | select(.level 省级) | {code, name} administrative_division.json这段命令把 JSON 数组逐条流式处理筛出level为“省级”的对象再只输出code和name两个字段。如果文件是嵌套结构把.[]换成.. | objects再叠加select即可。Windows 的 PowerShell 里 jq 的单引号会被误解析建议改成双引号并转义内部引号或者直接在 Node.js/Python 里处理避免折腾转义规则。4.3 四种格式选型表格式适用场景注意事项SQL直接导入 MySQL 做维度表先检查是否含 CREATE DATABASECSVExcel 筛选、pandas 清洗、Excel 导入数据库用 utf-8-sig 防止中文乱码JSONWeb API、前端下拉联动、小程序先确认平铺还是嵌套结构XLS非技术同事查阅、手工维护排序过滤方便适合人工核对CSV 和 XLS 区别在于CSV 是纯文本适合程序批量处理XLS 自带格式和筛选功能适合人肉核对。实际项目中我常把 SQL 和 CSV 各备一份SQL 进测试环境CSV 丢给数据同事做一次性分析JSON 只在需要给前端实时下发时才单独维护。5. 给代码表做差异校验用快照找出新增和撤销的区划5.1 用 Python 比对两个版本行政区划代码并不是永久不变的部分区县会合并、更名或升级而网络整理的 3050 条版本难免滞后。我的习惯是每隔一段时间从权威渠道拉一份最新代码公告和历史版本做差异比对把结果发给业务方确认。下面这个脚本以code为唯一键比较新旧两份 CSVimport csv def load_codes(path): rows {} with open(path, encodingutf-8-sig) as f: for r in csv.DictReader(f): rows[r[code]] r[name] return rows old load_codes(administrative_division_2020.csv) new load_codes(administrative_division_2023.csv) print( 新增区划 ) for code in sorted(set(new) - set(old)): print(code, new[code]) print( 撤销/停用区划 ) for code in sorted(set(old) - set(new)): print(code, old[code]) print( 名称变更 ) for code in sorted(set(old) set(new)): if old[code] ! new[code]: print(code, old[code], -, new[code])脚本先把每个版本的code映射到name然后利用集合差集找出新增、撤销的代码最后求交集比较名称是否变化。这里的核心思路是用快照对比而不是原地更新数据旧文件保留下来就是一条历史审计记录。5.2 把快照收进 SQLite 做历史查询如果想把多次快照都留下来不必每次都维护 CSV 文件可以直接落进 SQLite给每次导入的版本打一个version字段。下次拿到新数据时按版本号分组做 JOIN就能还原出任意两个时间点之间的区划变更记录。实际操作时我会把脚本挂成季度任务跑完直接输出工单内容给下游系统让他们确认是否需要刷新地址映射表。这样一跑新增区划第二天就能同步到业务字典不用等用户来投诉地址落不了库。本文还有配套的精品资源点击获取