简介这份身份证前六位地区对照表面向开发、数据分析与测试人员用于处理用户注册、订单归属地、风控校验等需要由身份证号反查行政区划的场景。数据量超过五千条并特别收录了已被撤销的行政区划代码能有效避免因历史数据缺失导致的匹配失败。资源包共3个文件包含2个xlsx表格和1个sql脚本压缩后约534KBExcel版覆盖全部行政区划代码SQL版表名为bj_addr字段为id、num、addr可直接导入数据库使用。目前已有10378人学习下载说明其在实际项目中的认可度较高。读者可获得一份可直接落地的地区码表既能用于快速查询与数据清洗也能作为系统初始化脚本减少自行整理与核对的工作量。1. 身份证前六位地区对照表为什么你的数据清洗总在“籍贯”字段翻车做用户画像、风控评分、物流分单甚至只是给运营拉一张“各省用户分布”的报表只要原始数据里有一列身份证号你迟早会撞上同一个问题前六位到底对应哪个地区很多人第一反应是写个正则截取前六位然后拿一张网上随手搜到的“行政区划代码表”去 join结果发现要么对不上、要么对上了但名字是旧的、要么直辖市和地级市混在一起统计口径全乱。身份证前六位是 GB/T 2260 行政区划代码的“历史快照”它记录的是首次签发时的区划不是当前区划。这意味着你手里那张 2022 年整理的对照表本质上是一份“时间切片”而不是一份永远正确的字典。这篇笔记就围绕“2022年最新最全身份证前六位地区对照表excel和sql双版”这个具体产物讲清楚它从哪来、怎么落到 Excel 和 SQL 两种载体、参数怎么设、以及我在实际清洗里踩过的坑。适合每天要和脏数据打交道的数据开发、数据分析以及需要把地区维度接进业务系统的后端同学。2. 先搞懂前六位的编码逻辑再谈对照表怎么建2.1 六位数字不是随机分配而是三级嵌套身份证前六位遵循“省 2 位 市 2 位 区县 2 位”的结构。前两位是省级代码中间两位是市级代码最后两位是区县级代码。比如110101拆开就是11省级、01市级、01区县。这个嵌套关系决定了你在建表时不能只存一张平表否则一旦要做“按省汇总”就得反复substr既慢又容易在边界值上出错。常见做法是建一张带层级字段的维表把省、市、区县三列都显式存下来同时保留完整的六位码作为主键。这样无论业务方要按哪一级聚合都能直接 group by不用再解析字符串。另一个容易被忽略的点是省级代码里11、12、31、50这些是直辖市和特殊区域它们的“市级”代码往往和省级代码重复或固定比如1101代表“市辖区”。如果你直接拿六位码去匹配一张只有“省-市-区”三级名称的表直辖市的区县很容易匹配不上因为中间那层在业务语义上是虚的。我一般会在维表里额外加一列level_type标记这条记录是省级、市级还是区县级清洗时先判断层级再决定用哪一列做关联。2.2 为什么必须是“2022 年”这个切片行政区划代码每年都可能调整撤县设区、合并乡镇、新设开发区都会导致同一串六位码在不同年份指向不同名称甚至同一名称对应不同代码。2022 年这个时间点的价值在于它覆盖了第七次全国人口普查之后的一轮区划调整结果很多 2020 年前后新设的区在这版里才有稳定代码。如果你拿 2015 年的表去匹配 2022 年签发的身份证会出现“代码存在但名称对不上”或者“代码已废弃”的情况。所以这份对照表的定位不是“永久字典”而是“2022 年签发身份证的区划快照”。建表时我建议加一列valid_year哪怕当前只填 2022也为后续叠加多年份留好扩展位。2.3 Excel 版和 SQL 版不是简单导出关系很多人以为 Excel 版就是 SQL 版导出的 csv其实两者的字段设计目标不同。Excel 版面向人工查阅和 VLOOKUP需要把省、市、区县名称拆成独立列并且名称要尽量口语化、带“省/市/区”后缀方便运营直接看。SQL 版面向程序 join主键必须是定长字符串的六位码名称列要保留官方全称同时额外加拼音首字母或简称列用于模糊匹配。如果直接把 Excel 的列结构搬进 SQL会出现前导零丢失、名称带空格、后缀不统一等问题。我一般会维护一份“主数据”在 SQL 里再用脚本导出 Excel 版导出时做一次格式清洗而不是反过来。3. 把对照表落进 Excel字段设计、VLOOKUP 和三个必调格式3.1 字段清单与命名规范一份能直接用的 Excel 对照表至少要有这几列code6六位码文本格式、province、city、district、level_type、full_name、valid_year。code6必须设成文本否则110101会被 Excel 当成数字显示为110101但前导零的码比如010101这种理论存在的情况会丢零。full_name是把省市区拼起来的全称用于快速人工核对。level_type用枚举值province/city/district方便筛选。命名上不要用中文列名避免在不同系统间导入导出时编码出问题。# 用 pandas 生成 Excel 版对照表的骨架 import pandas as pd # 假设 raw 是从主数据读出的 DataFrame包含 code6, province, city, district raw pd.read_sql(SELECT code6, province, city, district FROM dim_region_2022, conn) # 补层级类型根据 code6 后四位是否全为 0 判断 def judge_level(code): if code[2:] 0000: return province elif code[4:] 00: return city else: return district raw[level_type] raw[code6].apply(judge_level) raw[full_name] raw[province].fillna() raw[city].fillna() raw[district].fillna() raw[valid_year] 2022 # 关键code6 强制转字符串并补零到 6 位 raw[code6] raw[code6].astype(str).str.zfill(6) raw.to_excel(region_2022.xlsx, indexFalse)这段代码的逻辑是先把层级判断做掉再拼全称最后强制code6为六位字符串。参数上唯一要留意的是zfill(6)它保证即使源数据里存的是整数110101导出后也是文本110101。如果跳过这一步Excel 打开后列会变成常规数字格式后续 VLOOKUP 用文本去匹配就会全部返回#N/A。3.2 VLOOKUP 匹配时的文本陷阱运营同学最常用的操作是拿身份证前六位去 VLOOKUP 这张表。这里有一个高频翻车点身份证号在 Excel 里如果存成数字超过 15 位会变成科学计数法截取前六位时用LEFT函数得到的是文本而对照表里的code6如果没设成文本两边类型不一致VLOOKUP 会直接失败。解决办法有两个一是把身份证列和code6列都显式设成文本二是在公式里用TEXT(LEFT(A2,6),000000)强制对齐格式。我一般会在对照表旁边加一个“使用说明”sheet把这两个公式写进去减少反复解释的成本。3.3 三个必调的 Excel 格式参数第一个是列宽和冻结首行六位码和全称列要足够宽否则看起来像被截断。第二个是code6列设置“文本”格式后再粘贴数据不要先粘贴再改格式那样已经丢的零补不回来。第三个是给level_type加数据验证下拉限制只能选三个枚举值防止人工维护时写进错别字。这三个参数看起来琐碎但直接决定这张表是“能长期用”还是“用一次就废”。4. 把对照表落进 SQL建表、索引和 join 性能4.1 建表语句与字段类型选择SQL 版的核心是把六位码作为定长字符串主键并且给省、市、区县分别建索引因为业务查询往往只按其中一级过滤。字段类型上code6用CHAR(6)而不是VARCHAR(6)定长在 join 时比较效率更稳。名称列用VARCHAR(64)足够valid_year用SMALLINT。下面是一份可直接执行的建表语句。CREATE TABLE dim_region_2022 ( code6 CHAR(6) NOT NULL COMMENT 六位行政区划代码, province VARCHAR(32) DEFAULT NULL COMMENT 省级名称, city VARCHAR(32) DEFAULT NULL COMMENT 市级名称, district VARCHAR(32) DEFAULT NULL COMMENT 区县名称, level_type VARCHAR(16) NOT NULL COMMENT 层级province/city/district, full_name VARCHAR(96) DEFAULT NULL COMMENT 省市区全称, valid_year SMALLINT NOT NULL DEFAULT 2022 COMMENT 适用年份, PRIMARY KEY (code6), KEY idx_province (province), KEY idx_city (city), KEY idx_level (level_type) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT2022年身份证前六位地区对照表;建表时把code6设为主键天然去重也防止导入时重复插入。idx_level这个索引容易被省掉但当你要统计“省级记录有多少条”时没有它就得全表扫。utf8mb4是为了兼容极少数生僻字区县名虽然少见但一旦遇到就是乱码事故。4.2 导入数据的两种方式与参数差异如果数据源是 csv用LOAD DATA最快但要注意code6列在 csv 里如果是数字导入后会丢前导零。稳妥做法是在 csv 里就把该列写成带引号的文本或者在LOAD DATA时用SET code6 LPAD(code6, 6, 0)做一次补零。如果数据源是另一张表用INSERT INTO ... SELECT时同样要LPAD。我一般会在导入后跑一次校验SELECT COUNT(*) FROM dim_region_2022 WHERE CHAR_LENGTH(code6) 6;返回 0 才算过关。-- 从临时表导入并补零的写法 INSERT INTO dim_region_2022 (code6, province, city, district, level_type, full_name, valid_year) SELECT LPAD(tmp.code, 6, 0) AS code6, tmp.province, tmp.city, tmp.district, CASE WHEN RIGHT(LPAD(tmp.code, 6, 0), 4) 0000 THEN province WHEN RIGHT(LPAD(tmp.code, 6, 0), 2) 00 THEN city ELSE district END AS level_type, CONCAT(IFNULL(tmp.province,), IFNULL(tmp.city,), IFNULL(tmp.district,)) AS full_name, 2022 FROM tmp_region_import tmp;这段 SQL 的关键在LPAD和CASE的配合先补零再判断层级顺序不能反。CONCAT里用IFNULL是因为省级记录的city和district可能为空直接拼会得到 NULL。4.3 join 时的 collation 与性能注意点业务表里的身份证前六位如果是用SUBSTRING(id_card, 1, 6)现算的join 时无法走索引数据量上百万就会明显变慢。常见优化是提前在业务表里加一列region_code并建索引写入时就算好。另外如果业务库和维表的字符集 collation 不一致join 会触发隐式转换同样拖慢查询。我一般会统一用utf8mb4_general_ci并在 join 前用EXPLAIN确认走的是ref而不是ALL。5. 避坑与排查五条血泪经验5.1 现象join 结果大量为空原因前导零丢失解决全链路强制文本这是最高频的问题。Excel 里code6被识别成数字SQL 里CHAR(6)插入时源数据是整数都会导致110101变成110101看似正常但010101这类码直接变成10101。解决方式是在数据进入对照表的每一个环节都做LPAD或zfill并且在最终校验里加一条长度检查。5.2 现象直辖市数据统计重复原因层级判断把“市辖区”当独立市解决用 level_type 过滤直辖市的1101这类代码在语义上是“市辖区”不是独立城市。如果按city分组统计会把“市辖区”当成一个城市导致北京、上海的数据出现一个奇怪的“市辖区”条目。正确做法是统计省级时用level_typeprovince统计区县时用level_typedistrict跳过中间的虚级。5.3 现象同一六位码匹配出两个名称原因多年份数据混入解决加 valid_year 过滤如果后续叠加了 2023 或更早年份的数据同一code6可能对应不同名称。join 时必须带上AND valid_year 2022否则会返回多行业务方拿到重复数据还以为是 bug。建表时把valid_year放进联合主键或至少加索引能避免这类问题。5.4 现象Excel 打开后中文乱码原因导出编码不是 UTF-8 with BOM解决导出时指定编码用 pandas 的to_excel一般不会乱码但如果先导 csv 再让用户用 Excel 打开必须用utf-8-sig编码。否则 Excel 会按本地编码解析区县名里的生僻字直接变问号。我一般直接给 xlsx绕开 csv 的编码坑。5.5 现象查询慢到超时原因join 列类型不一致触发隐式转换解决统一 CHAR(6) 并 EXPLAIN 验证业务表里region_code如果是VARCHAR(6)维表是CHAR(6)join 时可能触发转换。统一成CHAR(6)后再用EXPLAIN看 type 是否为ref。如果还是ALL检查是不是函数包了列比如SUBSTRING写在 join 条件里。6. 进阶用法把对照表变成可校验的数据资产6.1 用校验 SQL 做每日巡检对照表一旦被业务依赖就需要有巡检机制。我一般会写一条校验 SQL每天跑一次检查四件事六位码长度是否都为 6、level_type是否有非法值、省级记录的city是否为空、是否存在重复code6。任何一项异常就告警。这条 SQL 不复杂但能挡住 90% 的维护事故。SELECT SUM(CHAR_LENGTH(code6) 6) AS bad_length, SUM(level_type NOT IN (province,city,district)) AS bad_level, SUM(level_type province AND city IS NOT NULL) AS bad_province_city, COUNT(*) - COUNT(DISTINCT code6) AS dup_code FROM dim_region_2022 WHERE valid_year 2022;四个指标都应为 0。bad_province_city这一项容易被忽略省级记录的city理论上应为空如果被填了值说明导入时层级判断有误。6.2 用 Python 做 Excel 与 SQL 的一致性比对维护双版本最大的风险是两边不一致。我习惯在每次更新后跑一段 Python把 SQL 里的数据读出来和 Excel 版做一次全量比对按code6对齐后检查名称列是否完全一致。不一致就打印差异行人工确认哪边是对的。这段脚本不长但能避免“运营用 Excel 查出来一个结果系统用 SQL 查出来另一个结果”这种最难排查的问题。import pandas as pd sql_df pd.read_sql(SELECT code6, province, city, district FROM dim_region_2022 WHERE valid_year2022, conn) xls_df pd.read_excel(region_2022.xlsx, dtype{code6: str}) sql_df[code6] sql_df[code6].str.zfill(6) xls_df[code6] xls_df[code6].str.zfill(6) merged sql_df.merge(xls_df, oncode6, howouter, suffixes(_sql, _xls), indicatorTrue) diff merged[merged[_merge] ! both] print(diff[[code6, _merge, province_sql, province_xls]])indicatorTrue会标出每行是两边都有、只在 SQL 还是只在 Excelsuffixes区分同名列。跑完看diff是否为空不为空就逐行核对。6.3 一个具体技巧用前缀树加速模糊匹配如果业务方经常输入“朝阳区”这种不带省市的名称来反查代码直接LIKE %朝阳%会全表扫。一个实用技巧是把区县名称按拼音首字母建一列再用前缀索引。比如“朝阳”对应CY查询时先按pinyin_prefix过滤再精确匹配速度能提升一个量级。这个列在导出 Excel 版时也可以保留方便运营用拼音搜索。最后说个我自己的习惯每次更新对照表先在一个临时 schema 里跑完整套校验和比对确认无误再切到生产。行政区划代码这种数据错一位就是整批用户归属错误没有后悔药可吃。希望帮到你。本文还有配套的精品资源点击获取