身份证前六位地区对照表：超五千条含撤销代码的数据清洗与SQL导入指南

简介这份身份证前六位地区对照表面向开发、数据分析与测试人员用于解决行政区划代码查询、地址解析与历史数据兼容等问题。数据量超过五千条特别收录了已被撤销的行政区划代码适合需要处理历史身份证数据或做地区校验的场景。资源包共3个文件包含2个xlsx表格和1个sql脚本压缩后约534KBExcel版覆盖全部行政区划代码SQL版表名为bj_addr字段为id、num、addr可直接导入数据库使用。目前已有10378人学习下载说明其在实际开发中具有较高参考价值。读者可获得一份可直接落地的对照数据既能用于快速查询身份证归属地也能作为地址库基础数据减少自行整理与校验的成本尤其适合需要兼顾现行与历史区划代码的项目。1. 五千条行政区划代码里藏着多少数据清洗的坑做用户信息入库、订单地址校验、风控实名比对的时候很多人第一反应是拿身份证前六位去查地区。真到动手才发现网上随手搜到的对照表要么只有三千多条、要么缺了撤销代码、要么格式乱得没法直接 join。我手上这份「身份证前六位地区对照表」资源包一共超过五千条记录同时给了 Excel 和 SQL 两个版本Excel 里还专门保留了历史上被撤销的行政区划代码。这意味着什么意味着你拿到的不是一份「当前有效地区清单」而是一份带时间维度的行政区划快照。适合谁用做数据清洗的、写地址解析服务的、搞实名认证风控的以及需要把历史脏数据回填地区名称的。如果你只是想要一份「最新的省市列表」这份资源可能比你预期的要重但恰恰是那些被撤销的代码才是真实业务里最容易翻车的地方。2. 拆开资源包四个文件分别对应什么场景2.1 文件清单与字段结构资源包解压后是四个文件命名很直白但用途差别不小。先看清单文件名格式主要用途是否含撤销代码身份证地区对照表包含已撤销地区.xlsxExcel人工查阅、全量比对是身份证地区对照表合并整理.xlsxExcel快速筛选当前有效地区否已合并身份证对照.sqlSQL直接导入数据库做 join是身份证地区对照.zip压缩包原始归档视内部文件而定SQL 版本的表结构在摘要里写得很清楚表名bj_addr字段为id、num、addr。num就是身份证前六位addr是对应地区名称。注意这里没有省市区层级字段也没有生效/失效时间戳所以它是一张扁平对照表不是行政区划树。这一点决定了你后面怎么用它——它适合做「代码到名称」的单点映射不适合直接拿来构建级联下拉框。2.2 为什么要有「包含已撤销」和「合并整理」两个 Excel很多人会问既然 SQL 里已经全量了为什么还要两个 Excel。实际用起来就明白了合并整理版是给业务同学看的他们不需要知道某个代码在 2015 年就被撤销了只需要知道现在填这个代码对应哪个地区。而包含已撤销版是给数据工程师做历史数据回溯的。比如你库里有一批 2010 年注册的用户地址字段里存的是当年的行政区划代码现在用最新表去查查不到就会变成空值。这时候就得用全量表去匹配匹配上之后再决定是保留历史名称还是映射到新名称。两个版本并存本质上是把「查询效率」和「历史完整性」拆开了。2.3 SQL 导入前的字符集检查SQL 文件直接 source 进去之前有一件事必须先做确认addr字段的字符集。行政区划名称里会有生僻字和多音字比如「盱眙」「婺源」「歙县」这类如果数据库默认字符集是 latin1 或者 utf8 的非完整实现导入后会出现乱码或者截断。常见做法是建表时显式指定utf8mb4排序规则用utf8mb4_general_ci就够了不需要unicode_ci因为这里不涉及多语言排序。-- 建表时显式指定字符集避免生僻字乱码 CREATE TABLE bj_addr ( id INT PRIMARY KEY AUTO_INCREMENT, num CHAR(6) NOT NULL COMMENT 身份证前六位地区代码, addr VARCHAR(100) NOT NULL COMMENT 地区名称 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci; -- 导入后立刻验证条数和关键样本 SELECT COUNT(*) FROM bj_addr; SELECT * FROM bj_addr WHERE num IN (110101,310101,440305);上面这段代码里num用CHAR(6)而不是VARCHAR(6)因为身份证地区代码长度固定CHAR在等值查询时效率更稳。addr给到VARCHAR(100)是留了余量实际最长的地区名称一般不超过 30 个字符但考虑到有些历史名称带括号备注100 足够。导入后先跑COUNT(*)确认条数是否在五千以上再用几个已知代码抽样验证比如110101应该是北京东城310101是上海黄浦440305是深圳南山。如果这几个查出来是空或者乱码说明导入环节有问题先别往下做 join。3. 把对照表接进业务三种典型用法与参数调优3.1 用 SQL 做身份证前六位批量回填最常见的场景是用户表里存了身份证号但地区字段是空的现在要批量回填。假设用户表叫user_info身份证字段叫id_card地区字段叫region_name。核心 SQL 就是拿LEFT(id_card, 6)去 joinbj_addr。-- 批量回填地区名称只更新地区为空的记录 UPDATE user_info u JOIN bj_addr b ON LEFT(u.id_card, 6) b.num SET u.region_name b.addr WHERE u.region_name IS NULL AND u.id_card IS NOT NULL AND CHAR_LENGTH(u.id_card) 18;这段 SQL 有三个参数点值得注意。第一LEFT(u.id_card, 6)在 join 时会对每一行做函数计算如果user_info数据量在百万级以上建议先在id_card上建前缀索引或者干脆加一个冗余字段region_code存前六位避免每次查询都算。第二CHAR_LENGTH(u.id_card) 18这个条件是为了过滤掉 15 位老身份证和脏数据15 位身份证的前六位含义和 18 位不完全一致直接混用会出错。第三WHERE u.region_name IS NULL保证只填空值不覆盖已有数据这是血泪经验——曾经有人没加这个条件把已经人工修正过的地区名全刷成了对照表里的旧名称。3.2 用 Python 做 Excel 版比对与差异输出有时候你不方便连数据库或者需要把对照表跟另一份业务数据做差异比对这时候用 Python 读 Excel 更灵活。下面这段代码演示怎么加载「包含已撤销地区」的 Excel并找出业务数据里存在但对照表里没有的代码。import pandas as pd # 读取包含已撤销地区的全量对照表 df_ref pd.read_excel(身份证地区对照表包含已撤销地区.xlsx, dtype{num: str}) # 统一列名防止 Excel 里列名有空格 df_ref.columns [num, addr] # 去重防止同一代码出现多条 df_ref df_ref.drop_duplicates(subsetnum) # 读取业务数据里的地区代码 df_biz pd.read_csv(biz_region_codes.csv, dtype{region_code: str}) # 找出业务数据里存在、但对照表里没有的代码 missing set(df_biz[region_code]) - set(df_ref[num]) print(f对照表缺失代码数量: {len(missing)}) for code in sorted(missing)[:20]: print(code)这里的关键参数是dtype{num: str}。身份证地区代码前六位里有些以0开头比如010开头的旧代码如果 pandas 按默认推断成整数前导零就丢了join 的时候永远匹配不上。这是 Excel 和 CSV 处理里最隐蔽的坑之一。另外drop_duplicates(subsetnum)是为了防止对照表里同一代码有多条记录比如合并整理时产生的重复保留第一条即可。输出缺失代码后你可以拿这些代码去查历史档案或者标记为「待人工确认」而不是直接丢弃。3.3 用对照表做地址解析服务的缓存层如果你在写一个地址解析 API每次请求都查数据库不现实。常见做法是把bj_addr全量加载到内存里用字典做 O(1) 查询。五千多条记录占不了多少内存一个dict大概几百 KB。下面是一个简单的加载和查询示例。import pymysql # 从数据库加载全量对照表到内存字典 conn pymysql.connect(hostlocalhost, userroot, password, databasetest, charsetutf8mb4) cursor conn.cursor() cursor.execute(SELECT num, addr FROM bj_addr) addr_map {row[0]: row[1] for row in cursor.fetchall()} cursor.close() conn.close() def get_region(id_card: str) - str: 根据身份证号返回地区名称查不到返回空字符串 if not id_card or len(id_card) 6: return return addr_map.get(id_card[:6], ) # 测试 print(get_region(110101199001011234)) # 应输出北京东城 print(get_region(440305199001011234)) # 应输出深圳南山这段代码里charsetutf8mb4必须和建表时保持一致否则读出来的中文可能是乱码。addr_map.get(id_card[:6], )用get而不是直接索引是为了避免 KeyError 导致服务崩溃。如果查不到返回空字符串由上层决定是报错还是降级。这个缓存层适合读多写少的场景如果对照表需要更新重启服务或者加一个定时刷新机制即可。注意不要每次请求都重新加载那样反而比查数据库还慢。4. 避坑与排查撤销代码、前导零和字符集的三重门4.1 现象join 之后大量记录地区为空原因业务数据里存的是历史行政区划代码而对照表用的是「合并整理」版已经把这些撤销代码删掉了。比如某地 2010 年撤销后并入相邻区旧代码在新表里查不到。解决换用「包含已撤销地区」的 Excel 或 SQL 全量表。如果全量表里也查不到说明这个代码可能不是行政区划代码而是身份证顺序码或校验位被误截取了需要检查数据来源。4.2 现象Excel 打开后代码列的前导零消失原因Excel 默认把数字列推断为数值类型010101会变成10101。这是 Excel 的默认行为不是文件损坏。解决用 pandas 读取时显式指定dtype{num: str}如果必须用 Excel 人工看先把该列格式设为「文本」再粘贴。SQL 导入时num字段用CHAR(6)也能避免这个问题因为数据库不会自动去零。4.3 现象导入 SQL 后中文显示为问号或乱码原因数据库、表、连接三处的字符集不一致。常见的是数据库默认latin1表建成了utf8连接又用了gbk。解决统一用utf8mb4。建库时CREATE DATABASE test DEFAULT CHARSET utf8mb4;建表时显式指定连接字符串里加charsetutf8mb4。导入前可以用SHOW VARIABLES LIKE character%;检查当前会话字符集。4.4 现象同一代码对应多个地区名称原因行政区划变更过程中同一代码在不同年份可能对应不同名称或者合并整理时产生了重复记录。解决先SELECT num, COUNT(*) FROM bj_addr GROUP BY num HAVING COUNT(*) 1;找出重复代码。如果重复不多人工确认保留哪条如果重复量大说明导入时没有去重需要重新清洗。业务上建议以最新名称为准历史名称可以另存一个备注字段。4.5 现象15 位身份证查出来的地区不对原因15 位身份证的前六位地区代码规则和 18 位不完全一致而且 15 位身份证没有校验位本身可能是脏数据。解决在查询条件里加CHAR_LENGTH(id_card) 18把 15 位数据单独处理。如果业务上必须支持 15 位需要先做升位转换再取前六位。5. 进阶技巧用对照表做行政区划变更追踪这份资源最被低估的地方是它保留了撤销代码。这意味着你可以拿它做一件更有价值的事追踪行政区划变更对业务数据的影响。具体做法是把「包含已撤销」和「合并整理」两个版本都加载进来做一次差集找出所有被撤销的代码然后去业务库里统计这些代码的出现频次。import pandas as pd # 加载两个版本 df_full pd.read_excel(身份证地区对照表包含已撤销地区.xlsx, dtype{num: str}) df_merged pd.read_excel(身份证地区对照表合并整理.xlsx, dtype{num: str}) df_full.columns [num, addr] df_merged.columns [num, addr] # 找出被撤销的代码在全量里但不在合并版里 revoked set(df_full[num]) - set(df_merged[num]) print(f被撤销代码数量: {len(revoked)}) # 假设业务数据里有一个地区代码字段 df_biz pd.read_csv(biz_region_codes.csv, dtype{region_code: str}) affected df_biz[df_biz[region_code].isin(revoked)] print(f受影响的业务记录数: {len(affected)}) print(affected[region_code].value_counts().head(10))这段代码的输出能直接告诉你有多少业务数据还在用已经撤销的行政区划代码。如果数量很大说明你的数据清洗策略需要调整——是保留历史名称还是统一映射到新名称取决于业务对历史准确性的要求。我一般会建议保留历史名称同时加一个「当前名称」字段做映射这样既不影响历史报表也能支持新业务查询。另一个技巧是给bj_addr加一个「数据版本」字段。虽然原始资源里没有但你可以自己在导入时加一列version记录这份对照表的整理时间。以后每次更新对照表旧版本不删新版本追加用version区分。这样当业务方问「为什么这个代码去年查得到今年查不到」的时候你可以直接定位到版本差异而不是靠记忆去猜。这个习惯我坚持了三年每次导入外部对照表都强制走一遍版本标记省了无数次扯皮。希望帮到你。本文还有配套的精品资源点击获取