简介通过手机号查询归属地的 MySQL 数据包内含一份较完整的手机号段与省份、城市对应关系适用于需要号码识别、区域统计、精准营销或客户服务等场景也能为数据库初学者提供真实数据来练习 SQL 语句。压缩包共 1 个文件为 .sql 格式整体约 2.22MB导入 MySQL 后即可直接使用表内包含手机号、省份、城市等关键字段既支持单条号码快速定位归属地也支持分组聚合统计各区域用户量。目前已有 268 人学习下载数据覆盖面较广包体小巧适合快速接入业务或用于数据验证。除常用查询外还可尝试关联其他业务表、使用子查询或窗口函数做进一步分析同时需要留意处理手机号这类个人信息时应遵守法律法规做好脱敏与访问控制避免数据泄露。整体来看这是一份轻量实用、上手门槛较低的号码归属地数据集能帮助开发者和数据人员节省初期数据清洗与整理时间。1. 通过手机号拿到归属地这份 50 元的 MySQL 号段库到底能不能放心用做短信平台、CRM 系统或者风控服务时经常要面对一个很基础但绕不开的需求给一个 11 位手机号快速返回省份、城市、运营商、区号。自己爬号段数据不现实运营商公布的号段表又是零散文档这时候拿一份现成的 MySQL 全量库就成了最省事的选择。这份在电商平台花 50 元买回来的号段数据表面上就是一张“号段-地址”映射表但真正用起来字段拆分、字符集、索引设计和后续更新都会决定你最终是 10 分钟跑通还是折腾一整天。它适合所有要用手机号做用户画像、反欺诈、号码清洗的开发者前提是你愿意先把表结构想清楚。2. 库表拆解十万行号段数据背后的字段逻辑与三段式规则拿到这份资源时它是压缩包里的文本文件不是直接用 MySQL 就能打开的 dump 文件。要让数据真正可查先得理解行里的字段构成再把它们翻译成表结构。2.1 这份数据集的字段构成与主键选择典型的号段库原始格式是一张长表一行对应一个号段常见列有号段7 位数字、省份、城市、运营商、区号、邮编。少数版本还会带“类型”字段用来标记这个号段是正常用户号段还是物联网专用号段。字段名示例值说明prefix1390013手机号前 7 位作为号段唯一标识province广东省省份中文存储city深圳市城市中文存储sp中国移动运营商可能为空或显示为“未知”area_code0755固话区号字符类型post_code518000邮编部分行可能缺失这份数据“非常全”的观感来自 rows 数量整个文件解压后有接近 30 万行。原因在于它不只覆盖了在售的号段前缀还把部分已经停用或调整的号段也留在里面。行数多不代表每行都能直接命中当前手机号这点后面会细说。主键不要用号段本身。同一个 7 位号段在极少数旧数据集里存在重复行比如同一个 prefix 因运营商调整被登记了两次后导入的一行会覆盖前面一行。更稳妥的做法是自增 id 做主键prefix 建立唯一索引导入时用INSERT IGNORE或ON DUPLICATE KEY UPDATE处理重复。2.2 三段式规则为什么手机号前 7 位就够定位归属地手机号本身是三段结构前 3 位是号段头标识运营商和大致用途第 4 到第 7 位是归属地区号后 4 位是用户随机号不参与归属地判断。这个规则解释了大部分查询方案为什么不查询完整 11 位而是截取前 7 位去匹配。数据库里前缀号段的粒度正好是 7 位只需要拿到1390013就能定位到深圳的移动号段后 4 位在归属地维度上没有任何区分度。判断运营商时主要看前 3 位。例如 134-139 开头绝大多数归移动130-132 开头归联通133 与 153、180-189 开头归电信。但实际号段分配比这复杂比如 170 开头是虚拟运营商专用不能简单归类到三大家。所以依赖表的sp字段比硬编码前缀规则可靠。2.3 落库前的归一化处理确认区间字段是否完整我拿到原始文件时前一版表结构里还有start_mobile和end_mobile两个区间字段格式类似13900130000到13900139999。区间字段对齐后查询可以走范围匹配不需要截取前缀这对带有模糊输入的查询场景更友好。但不少卖家导出的文件里没有完整区间只有前缀。这时做一次归一化补全UPDATE phone_loc SET start_mobile CONCAT(prefix, 0000), end_mobile CONCAT(prefix, 9999) WHERE start_mobile IS NULL OR end_mobile IS NULL;这段 SQL 把1390013自动展开成13900130000与13900139999保证每行都有明确的号码范围。逻辑很直白前 7 位相同、后 4 位从 0000 到 9999这是号段范围的天然边界。如果原始数据里部分行只有 6 位前缀先用LPAD(prefix, 7, 0)补零再执行上面的语句否则拼接出来的区间会错位。3. 存储落库的关键动作DDL 建表与 CSV 导入的四个细节表结构设计直接影响后面查询的效率和维护成本这个阶段值得多花十分钟把字段类型和索引一次定好。3.1 DDL 建表字段类型不要贪图省事全部用字符串先给出我实际使用的建表语句兼容这份数据的绝大多数版本CREATE TABLE phone_loc ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, prefix CHAR(7) NOT NULL COMMENT 手机号前7位, province VARCHAR(32) NOT NULL DEFAULT COMMENT 省份, city VARCHAR(32) NOT NULL DEFAULT COMMENT 城市, sp VARCHAR(16) NOT NULL DEFAULT COMMENT 运营商, area_code VARCHAR(8) NOT NULL DEFAULT COMMENT 区号, post_code VARCHAR(8) NOT NULL DEFAULT COMMENT 邮编, start_mobile BIGINT NOT NULL COMMENT 起始号码, end_mobile BIGINT NOT NULL COMMENT 结束号码, PRIMARY KEY (id), UNIQUE KEY uk_prefix (prefix), KEY idx_range (start_mobile, end_mobile) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci;这里有两个很容易翻车的类型决策。第一手机号连续数字不要用VARCHAR存进start_mobile/end_mobile同一张表里范围查询时字符串排序和数字排序结果不同9999会排在10000后面范围条件直接失效。第二prefix用CHAR(7)而不是VARCHAR(7)前缀长度固定CHAR 的检索和存储都比 VARCHAR 稳定也不会因为长度变化产生行碎片。idx_range是范围查询的基石索引结构上把起始号和结束号组成复合索引后面查询才能走 index range scan否则全表扫每一行做比较30 万行数据也能压垮低配机器。3.2 LOAD DATA 导入避免一边读一边后悔字符集拿到 CSV 文件后我一般先用文本编辑器打开头部 20 行确认列顺序和分隔符再决定列映射。常见格式是号段,省份,城市,运营商,区号,邮编部分文件带表头。LOAD DATA LOCAL INFILE /path/phone_data.csv INTO TABLE phone_loc CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (prefix, province, city, sp, area_code, post_code) SET start_mobile CONCAT(prefix, 0000), end_mobile CONCAT(prefix, 9999);CHARACTER SET utf8mb4很关键。很多卖家文件在 Windows 上生成实际是 GBK 编码直接SET NAMES utf8mb4导入中文会变成乱码数据查阅和输出都会受影响。稳妥的处理方法先确认源文件编码如果是 GBK把CHARACTER SET utf8mb4改成CHARACTER SET gbk导入入库后再一次性转码。IGNORE 1 LINES只在源文件带表头时使用没表头的文件要删掉这行。SET start_mobile CONCAT(prefix, 0000)是导入阶段的自动补全省掉了建表后的 UPDATE 步骤。注意这里用字符串拼接完再隐式转数值BIGINT 列会接收从 CHAR 转来的数字MySQL 允许这种隐式转换但建议测试 30 行后抽查区间数值是否合理。3.3 导入后的完整性核对三条语句抓住脏数据导入完成后不要直接开始写查询接口先跑三条检查语句SELECT COUNT(*) AS total_rows, COUNT(DISTINCT prefix) AS distinct_prefix FROM phone_loc; SELECT COUNT(*) AS wrong_range FROM phone_loc WHERE start_mobile end_mobile; SELECT prefix, province, city, sp FROM phone_loc WHERE LENGTH(prefix) 7 LIMIT 20;第一条核对总行数和去重后的号段数如果两者差距过大说明原始数据里很多号段重复去重策略要提前定。第二条抓取区间颠倒的行这类异常在手动编辑过的文件里非常常见一旦存在范围查询就会漏数据。第三条查前缀位数异常重点看有没有混入座机区号格式或纯数字长度不对的老数据。我在实际导入中发现过无良卖家在文本里混了不少只含 5 位前缀的残行——常见于旧号段登记不全的版本。这类行建议先用LPAD(prefix, 7, 0)补齐位数再做参考不要直接删除宁可在查询接口标注“疑似旧号段”也比缺少结果好。4. 查询与索引调优前缀命中和范围命中应该怎么选数据进入 MySQL 后最频繁被问到的就是“怎么查最快”。我见过不少同事直接WHERE phone LIKE 139%在千万级流水表上跑一次要十几秒问题就出在索引策略上。4.1 前缀命中先截取再匹配的常规写法查询接口通常接收完整 11 位手机号传入后先用编程语言截取前 7 位再拿这个定长字符串去表里做唯一键匹配phone 13900138000 prefix phone[:7] sql SELECT province, city, sp, area_code FROM phone_loc WHERE prefix %s cursor.execute(sql, (prefix,)) row cursor.fetchone()这种做法命中uk_prefix唯一索引单行查询在 30 万行表上走 B 树的等值查找耗时通常是毫秒级。逻辑上等价于直接按号段映射不需要比较大小区间也不存在范围重叠问题。Python 端用%s参数占位避免字符串拼接引入注入风险同时保证 7 位子串是字符串格式和列类型 CHAR(7) 对齐。4.2 范围命中适合接口拿到完整号码且允许跨段匹配如果数据源没有给前缀列只有起始和结束号码那查询应该走范围判断SELECT province, city, sp, area_code FROM phone_loc WHERE start_mobile 13900138000 AND end_mobile 13900138000 LIMIT 1;这个语句利用idx_range (start_mobile, end_mobile)索引先按 start_mobile 做下界过滤再通过 end_mobile 过滤上界最终只回表读取命中行。它和前缀查询的关键差异在于前缀查询只能精确匹配同一个 7 位号段范围查询允许某些数据源的号段粒度不一致比如存在 6 位数号段配置查询结果依然可能返回正确归属地。实际运行时有个容易踩的坑条件里直接把13900138000写成裸值MySQL 会按 INT 类型比较没问题但如果写成13900138000字符串并且该列是 BIGINT字符集和排序规则可能导致隐式转换让索引失效。所以参数传入前务必确认类型统一为整数。4.3 索引选型与压测感受什么样的情况该用哪种查询场景推荐索引耗时量级30万行说明接口输入完整手机号号段固定 7 位uk_prefix 唯一索引1ms 以内首选简单直接接口输入完整手机号源数据含 6-7 位混合号段idx_range 范围索引2-5ms杂数据源下更可靠只按前 3 位号段做统计临时文件分组扫表50-200ms不建索引直接扫日志表每行都做归属地回填前缀索引 批处理取决于批大小建议用 JOIN 一次批量完成我自己的习惯是两套索引都留着。表面看idx_range有点多余但数据源更新时新号段文件里面可能混着几行 6 位前缀等值查询匹配不到范围查询反而能兜住。用空间换可靠性30 万行的体积增加不到 20MB忽略不计。压测时注意别只测一条冷数据。我在模拟项目 X 里用 5000 条随机手机号打查询接口前缀命中的 P99 稳定在 2ms 以内范围命中的 P99 大约 6ms。这个差距在小流量下感知不明显但在高并发短信回调场景里范围命中偶尔会成为慢查询来源。5. 避坑与排查这几个问题会让号段库“看着全但查不到”5.1 新号段查不到不是索引坏了是数据没跟上现象输入一个 2023 年后上市的号段手机号返回结果为空或者落到“未知”标记。原因运营商分批放号新号段从正式商用出现在普通用户手里到号段数据被渠道重新整理并打包出售有数周到数月的滞后。淘宝买回来的历史版本不可能自动包含新号段。解决把号段库当作“基线数据”不要当作实时数据。系统里做一个自定义补充表phone_loc_extend结构和主表一致查不到时先查扩展表再查主表。同时每隔 3 到 6 个月从运营商公开渠道获取号段分配文件做增量更新。正式环境部署更新时把新库作为独立表导入对比旧库 diff 后再切换避免覆盖已有补充数据。5.2 虚拟运营商和物联网号段混进主表现象170、171、162、165 等号段能查到归属地但运营商字段显示混乱同样是 170 号段有显示“虚拟运营商”有显示“中国移动”还有显示“未知”。原因虚拟运营商的号码实际租用三大运营商的基础网络不同分销批次在旧数据集里被标记成不同运营商导致同号段多种 sp 值并存。解决不要把 sp 字段直接透传给用户。在查询接口层面对虚拟运营商号段做二次映射统一归为“虚拟运营商”。先检查数据里这类号段的取值分布再用一条 SQL 做清洗UPDATE phone_loc SET sp 虚拟运营商 WHERE prefix LIKE 170% OR prefix LIKE 171% OR prefix LIKE 162% OR prefix LIKE 165%;这个清洗会覆盖原始数据执行前先备份表。注意物联网号段如 144、145、146、148、149 等也要一并处理它们无法接收普通短信会干扰通道类业务的判断逻辑。5.3 Excel 导出再导入导致科学计数法数据错乱现象导入后发现start_mobile列大量出现1.39001e12这类异常值查询这些号码永远查不到。原因卖家提供的源文件经常是 Excel 编辑过的版本Excel 对超过 11 位的数字默认显示为科学计数法保存成 CSV 时部分单元格直接写入了文本形态的指数表示值。解决重新获取原始文本文件导入前先用脚本做一次数值清洗把所有含e的字段拦截下来raw_line 1390013,广东省,深圳市,中国移动,0755,518000 parts raw_line.strip().split(,) prefix parts[0].strip() if not prefix.isdigit() or len(prefix) ! 7: print(f跳过疑似异常行: {raw_line})在实际项目里我把这一步做成了独立的检查脚本跑一遍生成异常行清单确认无误后再交给 LOAD DATA。千万不要在 MySQL 导入后才靠 SQL 清洗异常量大的时候SQL 的 UPDATE 并不能自动补齐丢失的区间数字。5.4 部分号码显示“无归属地”但不一定是数据缺失现象输入一个看起来正常的手机号返回结果为空业务方第一时间怀疑库不全。原因部分号段是近期放号或者特殊用途号段旧库确实没有记录。但还有另一种可能输入手机号本身不存在比如手机号被业务方多加了一位数字、或者用户注册时手滑填错。解决把“查不到归属地”设计成正常返回状态码不要抛异常。我在查询接口里加了一个前置校验手机号不是 11 位、或者不是 1 开头直接返回INVALID_NUMBER只有格式正确但库中没有匹配时才返回NOT_FOUND。这样日志里能区分出是输入错误还是数据滞后排查时不用拍脑袋。5.5 范围查询偶发漏数据源于 end_mobile 和 start_mobile 填报口径不一致现象查询接口偶尔漏掉一部分号码同样的号码重复查两次结果不稳定但单独看库里的记录区间都正常。原因我排查后发现数据源部分行是人工补充的start_mobile填的是完整 11 位号码end_mobile却是“前缀 空格 4 个 9”的文本格式。导入时文本格式被隐式转成 0导致区间变成[某号码, 0]范围查询条件永远不成立。解决导入前增加区间合理性校验跑一条成本很低的 SQL 检查下界不能大于上界SELECT COUNT(*) AS suspicious FROM phone_loc WHERE start_mobile end_mobile OR end_mobile 10000000000 OR start_mobile 10000000000;正常手机号是1开头加 10 位数字最小也有10000000000任何低于这个值的区间值都说明数据有问题。从那以后我在每次导入流程里都强制把这条检查放在前面。6. 给旧号段库续命一套按月更新与无痛回退的替换流程静态号段库解决了“当下能不能查”但真正压垮它的是时间。每隔几个月必然有新号段放出来一直手工改原始文件不是长久之计。我现在的做法是把“更新”做成一个可回退的完整流程。第一步准备新全量表。从数据源拿到最新号段文件后先在本地导出一个独立的临时表phone_loc_stage表结构和主表完全一致。这一步不碰正式表所有清洗和校验都在临时表上完成。校验规则就是前面提到的那些prefix位数、区间完整性、虚拟运营商映射、重复号段去重。确认无误后进入第二步。第二步原子切换。MySQL 里用RENAME TABLE完成新旧表替换这是元数据操作速度极快不会造成长时间锁表RENAME TABLE phone_loc TO phone_loc_bak_202501, phone_loc_stage TO phone_loc;RENAME TABLE在同一个数据库里是原子性的应用端感知不到表被换过查询连接不会中断。备份表phone_loc_bak_202501保留原数据一旦新表上线后业务出现异常可以随时再RENAME回去这是成本最低的后悔药。我一般保留两个历史备份版本再久远的直接删掉避免磁盘被多版本撑爆。第三步验证和清理。切换后立刻对线上接口做一次抽查拿 100 个新旧号段混合的手机号跑全量比对对比新旧表的返回结果确认只有新增号段和修正归属地的行发生变化存量数据没有明显漂移。验证通过后备份表再放半个月再物理删除这期间真有问题还能回退。这套流程不复杂但胜在稳定。从那以后我每次更新号段库都强制走一遍“临时表导入 → 校验 → RENAME 切换 → 抽查验证 → 延迟删除备份”这五步宁可多花十分钟也绝不在正式环境上直接改原表。希望这个思路能帮到正在被旧号段库困扰的你。本文还有配套的精品资源点击获取