行政区划代码SQL脚本：GB/T 2260维度表设计与查询实践

简介依据GB/T 2260标准整理的全国行政区划代码资源内含截至2017年的四级行政区划数据共46462条覆盖省/直辖市、市、县与乡/镇/街道/兵团适合开发者、数据分析师及GIS项目人员用于建表存储、地址匹配与区域维度分析。压缩包共4个文件大小5.01MB分别提供MySQL版SQL脚本、Access数据库mdb、TXT文本和XLSX电子表格SQL脚本可直接导入MySQL快速搭建地区信息表mdb文件适合无SQL环境下的直接查阅TXT与XLSX便于用Excel等工具做数据清洗、筛选和二次分析。所有文件围绕同一套标准编码设计省去手动整理编码表的麻烦能直接支撑人口统计、物流规划、市场分析等按地域拆分的业务场景。目前已有1102人学习下载适合需要快速获得规范化行政区划字典的入门与进阶用户。1. 行政区划代码不是一串数字这份SQL脚本能省下你一下午做数据清洗的时候最烦的不是SQL写不出来而是“地址字段没法聚合”。用户填的省市区五花八门想要按区域统计第一步就得把每个地址映射到标准行政区划代码上。GB/T 2260就是这套国标六位数字代表省、市、县三级。网上能找到的数据很多但大多是Excel或者网页拷贝格式乱、带“省/市/区”后缀、还有废弃代码。这份SQL资源直接把行政区划代码整理成了可导入的脚本省去了自己写正则清洗的步骤。适合做数据仓库、报表系统、订单地址归一化的开发者和数据分析师。它能解决的痛点是把“地址文本”变成“可关联、可下钻的行政区划维度表”。2. 读懂GB/T 2260六位编码的位段规则与表结构设计2.1 六位编码的位段含义与校验规则GB/T 2260的核心是六位数字编码但很多人只把它当成普通ID忽略了位段里的规律。前两位是省级代码中间两位是地级代码后两位是区县级代码。比如“110000”前两位“11”代表某直辖市后面四位用零补齐表示省级单位本身。再比如“110100”前两位“11”还是该直辖市“01”表示市辖区最后两位“00”补齐。真正到区县一级“110101”就代表该直辖市下某个具体市辖区。这个规律的价值在于你可以只凭数字判断级别。如果后四位都是0这是省级如果后两位是0但中间两位不是0这是地级如果后两位不是0基本就是区县级。实际使用时我会在脚本里加一个级别字段而不是每次去判断字符串尾部。校验时最容易踩的坑是“省直辖县级市”和“地级市代管县级市”。有些县级市的代码在行政区划上不归属某个地级市而是由省直辖编码上后两位不是00但中间两位也不是常规的地级市编码。如果只按位段规则判断很容易把这类代码归错级别。所以我一般还会保留原始数据里的“级别”字段不纯粹依赖位置推导。另外代码不是连续的。有些代码段被废弃有些新代码是后补的。比如某地区撤地设市后整个地级代码会变但下辖区县的代码会保留一部分。如果你拿旧数据做历史查询就得保留“生效日期”和“失效日期”字段否则统计口径对不上。2.2 表结构设计与字段选型为什么我建议用三张表而不是一张拿到这份SQL脚本第一件事不是直接导入而是看一眼表结构。常见做法是一张全量表字段包括代码、名称、级别、上级代码。这种设计对简单查询够用但遇到“某市下所有区县”这种递归查询就得写自连接。如果业务表已经存了省市区三个冗余字段那直接用三个代码列关联就行。我实际用的是三张表省表、市表、区县表。每张表只存本级代码和名称。这样做有三个好处第一数据量小索引效率高。区县表也就三千行左右省级表才三十几行关联查询走索引后都是毫秒级。第二业务表可以冗余存三个代码列不用每次去递归查父级。第三做数据字典时前端下拉框直接查两张表就行省得用DISTINCT去重。当然如果你的业务表只有一个行政区划代码字段那用一张包含“上级代码”的表更方便。这份SQL脚本里两种模式都有说明。我建议至少保留以下字段字段名类型说明codeCHAR(6)六位行政区划代码nameVARCHAR(30)行政区划名称不带“省/市/区”后缀levelTINYINT1省级2地级3区县级parent_codeCHAR(6)上级代码省级为000000statusTINYINT是否现行有效1有效0废弃注意name字段不带后缀这是我在实际清洗时最在意的点。Excel里的数据通常叫“北京市”但代码表里应该叫“北京”。如果带着后缀业务表里用户填的地址就匹配不上还得再处理一次。2.3 脚本里的数据组织方式省市区县的冗余与关联这份SQL脚本在数据组织上做了一个很实用的处理除了原始的三级代码表还生成了一张“全量维度表”把每个区县代码对应的省代码、省名称、市代码、市名称都冗余进去了。这样业务表只要存一个区县代码就能直接关联出省市区三个维度不需要连三张表。这种做法在OLAP场景下非常友好。比如你要统计某省所有订单的金额如果业务表只存了区县代码那SQL就是SELECT o.province_code, SUM(o.amount) FROM orders o JOIN dim_district d ON o.district_code d.district_code WHERE d.province_code 某省代码 GROUP BY o.province_code;逻辑说明这里dim_district就是冗余维度表district_code是业务表里的区县代码province_code是维度表里冗余的省级代码。这个查询不需要做多级递归直接等值关联就能过滤出某个省的所有订单。参数说明province_code是CHAR(6)查询时字符串常量要用引号。如果业务表里district_code存的是数字类型join时会有隐式转换会导致索引失效这个在第3章会细讲。我一般会把这张冗余表建成持久化视图或者直接实体化。因为数据不常变更实体化之后查询性能更好。注意冗余表要加唯一索引district_code是主键province_code、city_code分别建普通索引避免后续做分组汇总时全表扫描。3. 把脚本跑起来建库、导数据与常用查询模板3.1 MySQL 导入步骤与编码陷阱拿到脚本后先用命令行导入别直接在图形化工具里粘贴。脚本文件通常包含建表语句和INSERT语句文件编码可能是UTF-8或GBK。导入前先确认你的MySQL默认字符集否则会出现中文乱码。推荐在库上指定字符集CREATE DATABASE IF NOT EXISTS region DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;逻辑说明utf8mb4与utf8的区别在于它能存储emoji在内的四字节字符。行政区划名称虽然用不到emoji但万一有生僻字用三个字节的utf8可能存不下直接用utf8mb4最稳妥。参数说明utf8mb4_general_ci是MySQL默认的校对规则对中文排序不友好但行政区划代码一般按code排序所以不需要刻意改成utf8mb4_unicode_ci。导入命令mysql -u root -p --default-character-setutf8mb4 region region.sql逻辑说明--default-character-setutf8mb4强制客户端把脚本文件内容按utf8mb4解析配合库的字符集一致能避免“???”或“乱码”的问题。如果脚本里有SET NAMES语句以脚本为准。参数说明region是数据库名region.sql是脚本文件路径。注意shell里输入密码会明文显示在历史记录中我一般用-p后回车再输入密码。导入之后先查一下总行数SELECT level, COUNT(*) FROM dim_district GROUP BY level;正常的话应该是省级三十几行地级三百多行区县三千行左右。如果区县行数明显少于三千就要检查是不是脚本没导入完整或者脚本里包含了废弃代码被过滤掉了。3.2 常用查询按省份汇总、查某市下所有区县、反查代码有了维度表最常见的三个查询操作分别对应三个业务场景。第一个场景按省份汇总订单金额。假设业务表orders里冗余了province_code字段SELECT province_code, SUM(amount) AS total_amount FROM orders GROUP BY province_code ORDER BY total_amount DESC;逻辑说明这里province_code直接在业务表里不需要join维度表。如果你的业务表只存了district_code那就得join dim_district拿province_code。参数说明注意GROUP BY的字段顺序如果还要按省名称显示需要join dim_district取name。别直接GROUP BY province_name因为名称可能重复虽然实际不会但按代码分组更严谨。第二个场景查某市下所有区县。这个如果只有一张自关联表写起来稍麻烦但用冗余维度表就很简单SELECT district_code, district_name FROM dim_district WHERE city_code 某市代码;逻辑说明dim_district表里已经有city_code字段直接过滤即可。注意city_code是省级下一位的代码不是区县代码别拿province_code去过滤。参数说明如果要包含市本级也就是代码末尾两位为00的那个“市辖区”要确认表里是否插入了“某某市辖区”这种记录。部分脚本会把这行去掉导致城市下没有“市辖区”这一项业务端下拉框可能缺数据。第三个场景用户输入了一个六位代码想反查出是哪个省市区。这个直接用OR条件或者UNIONSELECT province_name, city_name, district_name FROM dim_district WHERE district_code 某区县代码;逻辑说明冗余表的好处体现出来了一次查询就能拿到三级名称不用分三次。参数说明如果代码是废弃的结果集为空这时要提示用户代码不存在。有的业务需要显示“已撤销”那就要在status字段上做过滤或者保留废弃数据行但标记status0。3.3 与业务表关联时的索引建议导入完成后别急着写业务SQL先把索引建好。行政区划表的行数很少但业务表可能有几千万行。关联时最关键的是业务表上的存储字段类型。我见到最多的翻车点是业务表里district_code用VARCHAR存但维度表里是CHAR(6)。虽然MySQL对CHAR和VARCHAR的比较整体上不敏感但如果你在维度表字段上用了函数索引就废了。比如SELECT * FROM orders o LEFT JOIN dim_district d ON TRIM(o.district_code) d.district_code;逻辑说明TRIM函数作用在JOIN条件上MySQL无法使用dim_district表上的索引导致维度表全扫描。虽然维度表小但几千万订单关联时每行都要做函数运算查询会慢不少。参数说明正确做法是保证两端类型一致或者不要在索引列上做函数操作。如果业务表已经是VARCHAR维度表也用VARCHAR(6)加索引。如果业务表是INT那维度表code列就别用CHAR建一个INT列存纯数字。另外多表关联时如果业务表同时冗余了province_code、city_code、district_code三个字段我给这三个字段建一个联合索引ALTER TABLE orders ADD INDEX idx_region (province_code, city_code, district_code);逻辑说明联合索引能让“按省过滤再按市分组”这类查询走索引下推避免回表。参数说明如果业务表里这三个字段经常是单独查询比如只按district_code查那联合索引的最左前缀原则会导致district_code单独查询用不到索引。这种情况拆成三个单列索引反而更灵活。4. 避坑与排查行政区划数据导入与使用的四个高频翻车点4.1 现象导入后中文乱码或者省份名称变成“???”原因脚本文件的编码与数据库字符集不一致。最常见的是脚本文件是GBK编码但库是utf8mb4导入时客户端又没指定字符集MySQL会把GBK字节流当成utf8解析出现乱码。解决先查文件的真实编码。Linux下用file命令Windows下用记事本打开另存为UTF-8。导入时加--default-character-setgbk或者在脚本开头加SET NAMES gbk。如果数据已经导乱了就把表DROP掉重新导别指望UPDATE能救回来。4.2 现象查询“某市下所有区县”时结果缺少“市辖区”那一行原因部分脚本把市辖区后缀为“市辖区”的虚拟节点过滤掉了因为它的parent_code指向下一个省级单位但在实际业务里用户选择地址时往往需要“XX市市辖区”这个选项尤其是直辖市的市辖区比较特殊。解决检查维度表city_code等于某市代码的所有行看是否包含district_code类似“某市辖区”的虚拟代码。如果没有从脚本原始数据里找到该行手动插入。或者写一条SQL把该市代码本身作为district_code补一行。4.3 现象业务表关联维度表后结果行数变多出现重复数据原因维度表里同一个district_code对应多行通常是因为脚本里保留了历史废弃代码并且status字段没做过滤。业务表关联时没加WHERE d.status 1导致一个业务记录匹配了多条历史维度。解决关联维度表时默认加上d.status 1条件。如果历史统计必须用旧代码那就用AND d.code o.district_code先保证代码完全匹配再处理status。更严谨的做法是在维度表上建唯一索引索引列包含code和status但这样废代码和现行代码只能保留一个。4.4 现象按省分组统计时某省的数据算到了其他省头上原因业务表中的province_code存的是行政区划代码但在某些系统中“直辖市”的province_code是“110000”而“市辖区”代码是“110100”如果业务字段存错层级就会把市辖区代码当成省代码去group by于是同一个省的多个市辖区被拆成多行。解决统计前先校验业务表字段的业务含义。如果业务表存的是“用户下单时选择的省份ID”要确认这个ID到底是省级代码还是县级代码。很多前端选省市区时后端会存三个字段但某些组件库只存了最后一个区县代码导致后面统计时拿区县代码去匹配省的维度。我一般会在清洗脚本里加一个断言检查如果district_code对应的province_code不在省级表里就报警。5. 进阶玩法用代码变更记录做版本对比与增量更新脚本行政区划代码不是一成不变的每年的国标都有调整比如某县撤县设区、某县级市升格地级市。如果业务跨度几年维度表就需要版本管理。我的做法是在维度表上增加valid_from和valid_to两个日期字段。导入脚本时不直接UPDATE现有记录而是把当前有效的记录失效valid_to设为昨天再插入新记录。UPDATE dim_district SET valid_to DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) WHERE valid_to IS NULL; INSERT INTO dim_district (code, name, level, parent_code, status, valid_from, valid_to) SELECT * FROM temp_new_data;逻辑说明第一条语句把所有“现行有效”的记录关闭第二条语句把新数据插入。这样同一时刻某一条记录在历史上被标记为已失效新记录标记为有效。查询业务表时可以按业务日期去关联对应版本的行政区划。参数说明valid_to IS NULL表示当前有效。对于新插入的记录valid_from设为今天valid_to为NULL。注意INSERT语句里的字段顺序要跟SELECT一致否则会错位。有了版本字段还能写个对比脚本看看两个版本之间有哪些代码新增、撤销、变更名称SELECT v1.code, v1.name, v2.name FROM dim_district v1 LEFT JOIN dim_district v2 ON v1.code v2.code AND v2.valid_to IS NULL WHERE v1.valid_to IS NOT NULL AND v2.code IS NULL;逻辑说明这个查询找出了v1版本中存在但当前版本中已经消失的代码也就是被撤销的行政区划。参数说明如果要看名称变更把v2.code IS NULL改成v1.name v2.name即可。最后的状态检查我每次更新完维度表都会跑一遍完整性校验确认每个区县代码都能唯一匹配一条有效记录SELECT COUNT(*) AS dup_count FROM dim_district WHERE valid_to IS NULL GROUP BY code HAVING COUNT(*) 1;如果dup_count大于0说明版本管理没处理好有两条有效记录重叠。从那以后我每次更新行政区划都强制走这三步备份旧表、执行失效插入、跑唯一性校验。这套流程虽然看起来多花两分钟但比以后排查重复数据省几个小时。希望帮到你。本文还有配套的精品资源点击获取