手机号归属地查询：用MySQL号段表实现批量落地与维护全攻略

简介通过手机号查询归属地是运营、风控与客户服务中的常见需求这份 MySQL 数据库文件提供了覆盖面较完整的手机号段与省份、城市对应数据适合开发者在本地建表后直接用于号码归属地匹配、批量统计分析或二次开发。压缩包整体仅 2.22MB内含 1 个 SQL 文件导入 MySQL 后即可生成结构化数据表查询时可用类似 SELECT Province, City FROM phone_data WHERE PhoneNumber ? 的语句快速取回归属地若需按省份统计用户量也可通过 GROUP BY 与 COUNT 完成。数据源自淘宝购买据称完整性与准确性较高可作为生产环境或学习项目的参考数据源。目前已有 268 人在 CSDN 学习下载对于需要快速补充号码归属地数据的后端工程师、数据分析师而言是一份实用且轻量的现成资源。1. 通过手机号得到归属地为什么落到最后都在折腾一张 MySQL 表“通过手机号得到归属地”放到从业者的盘子里通常不是“查一个号码”而是给几万行用户表批量打上省份、城市、运营商标签。在线查询接口按次计费、有并发限制跑一次批量要等很久更常见的落地方式是维护一份本地 MySQL 号段归属表——这份表几十块就能买到卖家描述往往只有四个字“非常全”。“非常全”是导火索买回来能不能用全看接下来几小时你的清洗动作。下面只聊号段文件到手后的落地路径拆解号段表结构、给建表和批量导入的最小 SQL、点出五条高频踩坑再补增量更新与抽样回验的维护方案。适合做用户分群、短信分流、风控地域校验的后端开发者如果你只是偶尔查一个号用在线接口就行没必要自己维护一张表。这份库落地时真正的成本不在导入而在清洗和持续维护。卖家不会告诉你文件里有多少重复前缀、多少脏字段、虚拟号段和转网用户会让结果看起来多不靠谱。这些坑下面逐个拆开讲。2. 先看懂号段表的真实结构为什么归属地查询等于前缀匹配一张字典直接说结论手机号 11 位但归属地信息不在整串号码里而在前 7 位。按码号资源规划前 3 位标识网络前 7 位原则上标识一个号段发放给哪个运营商、哪个地区。渠道商卖给你的“完整号段表”本质就是一张前缀字典从 prefix 映射到 province、city、carrier。所谓“非常全”全在前缀覆盖的颗粒度——好的表能覆盖现网大部分号段差的表只有最常见的几十个前缀换个新号段就查不到。导入 MySQL 之前先对这张表的真实结构有个预判。它不大但脏而且口径混乱。下面从号段拆分、选型理由、体检三步三个角度把结构讲透。2.1 手机号前 7 位决定了号段归属从 11 位号码里拆出可检索的 prefix国内手机号 11 位可以拆成三段3 位网络识别号 4 位地区或 HLR 编码 4 位用户号。严格说前 7 位对应一个“号段”。同一个城市、同一个运营商的号段经常连号比如 1380000 和 1380001 差一个数字查出来可能归属不同城市——这正是号段表必须用 prefix 做唯一键的原因。渠道文件里一条典型记录长这样字段类型说明prefix7 位数字手机号前 7 位理论上唯一province省份名号段发放时的省份city城市名号段发放时的城市carrier运营商标识基础运营商或虚拟运营商代码area_code区号原始文件带的字段新号段不一定有7 位前缀的理论组合有 1000 万个但实际发放的号段通常只有几万条。我一般拿到文件先跑 COUNT 看量级几万行是常态哪怕十万行在 MySQL 里也只是热身。也正因数据量小查询完全不需要上 Redis 之类的缓存主键等值查询就是最快的路。有一点必须提前说清楚号段归属是“发放时”的归属不是“当前实时”的归属。历史区划调整、号码重新分配都会让查询结果看起来“很旧”这是数据本身的固有问题不是查询代码的 bug。这个口径后面第四章会详细展开。2.2 为什么本地 MySQL 比在线 API 更适合批量归属地查询选型要分场景。如果你只是登录页展示一个归属地每天几百次调用在线接口的开发成本更低服务方还帮你维护数据。但做用户画像、短信通道按省份分流、订单风控按地域校验数据往往要批量和其他表 JOIN这时候把用户表拉到本地一条 SQL 关联号段表就完成在线接口做不到。两类方案的差异集中在四个维度对比项在线归属地接口本地 MySQL 号段表单条查询延迟网络往返几十到几百毫秒主键等值零点几毫秒批量成本按条计费几万条费用可观一次导入后免费查并发限制多数有 QPS 上限取决于 MySQL 配置内网无瓶颈离线可用依赖外网完全离线批量场景下本地表还有个额外优势你可以直接用 SQL 做统计分析。比如统计某省用户量、某运营商号段覆盖了多少这些在在线接口上得一条条拉回来再算在本地表里一条 GROUP BY 就结束了。这也是为什么“MySQL 非常全”这个方向能成立核心价值在于把数据变成可 JOIN、可统计的资产而不是一个只能单查的黑匣子。2.3 买来的“非常全”先别急着导入编码、去重与脏数据体检我见过太多人把 CSV 直接 LOAD DATA然后被报错和脏数据折磨一下午。这里只讲导入前必须做的三步体检每一步都有明确目的。第一步看编码和行尾。渠道文件大量来自 Windows 导出常见 GB18030 编码 CRLF 行尾直接进 MySQL 会乱码或把 \r 带进字段。# 检查文件编码和行尾类型 file phone_data.csv # 看前 3 行有没有乱码cat -A 能看到行尾的 ^M$ cat -A phone_data.csv | head -n 3file 输出会告诉你编码是 UTF-8 还是 GB18030以及行尾是 CRLF 还是 LF。看到 ^M 就说明是 Windows 行尾MySQL 侧统一按 UTF-8 导入前先转码# GB18030 能兼容 GBK 和大部分中文场景比单独指定 GBK 更稳 iconv -f GB18030 -t UTF-8 phone_raw.csv phone_utf8.csv如果文件本身就是 UTF-8跳过转码重复转码反而会把正常的文件转出一堆问号。另外留意第一列是否带 BOM 头head 看到第一行第一个字段前有 \xEF\xBB\xBF 的话用 sed -i 1s/^\xEF\xBB\xBF// 去掉否则第一条前缀会多一个隐藏字符。第二步看结构和总行数。# 看表头和字段列数是否对齐 head -n 5 phone_utf8.csv # 看总行数用于和导入后的 COUNT 对账 wc -l phone_utf8.csvhead 能暴露两类问题文件里混了注释行或者字段列数不一致。wc 给的总行数是后面核对导入行数的基准很多 CSV 有表头、有空行直接 LOAD 会导致行数对不上。第三步看前缀重复度。# 第一列是 prefix统计重复次数排序列出重复最多的前 20 个 cut -d, -f1 phone_utf8.csv | sort | uniq -c | sort -rn | head -n 20如果重复不多导入时可以直接 IGNORE如果大量前缀重复说明渠道商把多个版本的号段文件拼接了同一条前缀可能对应两个不同城市这种必须人工介入。常规的清洗脚本我会用 Python 的 csv 模块而不是简单 split 逗号原因后面避坑章会讲# 用 csv 模块规整号段文件避免字段内逗号导致列错位 import csv src, dst phone_raw.csv, phone_clean.csv with open(src, encodingutf-8, errorsreplace) as fin, \ open(dst, w, encodingutf-8, newline) as fout: reader csv.reader(fin) writer csv.writer(fout) for row in reader: if len(row) 5: continue # 行数不够说明是空行或残缺行直接丢 prefix row[0].strip() if not prefix or not prefix.isdigit(): continue # 前缀不是纯数字大概率是表头/注释/脏行 # 强制截成 7 位保证后续主键长度可控 writer.writerow([prefix[:7], *row[1:5]])errorsreplace 的作用是遇到 GBK 乱码时替换成占位符而不是让脚本中断len(row) 5 判断列数防止空行混入prefix.isdigit() 过滤掉表头和注释行。清洗完的文件就是可以放心 LOAD 的干净输入。3. 用 MySQL 落地号段库建表、批量导入与索引核对体检做完开始落地。这里给的是“最小可行版”表结构没有冗余字段、不分区、不加缓存表因为几万行数据根本不需要这些花活。你要关注的是主键怎么选、导入怎么跳过脏行、查询怎么确认走了索引。3.1 建表prefix 做主键别让自增 id 挡路建表 SQL 如下CREATE TABLE phone_location ( prefix CHAR(7) NOT NULL COMMENT 手机号前7位, province VARCHAR(32) NOT NULL COMMENT 归属省份, city VARCHAR(32) NOT NULL COMMENT 归属城市, carrier VARCHAR(16) NOT NULL COMMENT 运营商标识, area_code CHAR(4) NOT NULL DEFAULT COMMENT 区号, updated_at DATE DEFAULT NULL COMMENT 数据批次日期, PRIMARY KEY (prefix), KEY idx_city (city) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT手机号段归属地;prefix 选 CHAR(7) 而不是 INT 或 VARCHARINT 需要转换语义VARCHAR 会引入长度字节CHAR 定长在这个量级下既好读又能避免尾部空格比较问题。主键直接选 prefix因为查询永远是等值匹配前缀主键就是天然索引额外加自增 id 或 UNIQUE KEY 都是浪费 B 树空间。idx_city 这个索引不是必须的。它只在你要按城市统计号段覆盖时才用得上比如分析用户号段分布。如果没这个需求建议删掉减少写入开销。utf8mb4 是为了给脏数据兜底城市名乱码也不至于导入失败。updated_at 字段强烈建议保留它是排查旧数据的锚点后面增量更新要靠它。注意不要用 MyISAM。几万行表 MyISAM 查询也快但后续要在线补录、并发更新InnoDB 的行锁和事务能力才是长期维护的保障。3.2 批量导入LOAD DATA 的本地文件开关与重复行处理LOAD DATA 是 MySQL 批量导入最快的方式但多数人第一次跑都会在 local_infile 开关上报错。MySQL 8.0 起客户端默认不开启 LOCAL 加载要先开两个开关# 客户端侧参数带 local-infile 启动 mysql --local-infile1 -uroot -p-- 服务端侧开关8.0 默认 OFF SET GLOBAL local_infile 1;只开一边就会报 3948 或 2068 这类错误。生产环境开着 local_infile 有安全面我一般导入完就 SET GLOBAL local_infile 0 关掉需要时再临时开。导入语句LOAD DATA LOCAL INFILE /data/phone_clean.csv IGNORE INTO TABLE phone_location FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (prefix, province, city, carrier, area_code) SET updated_at CURDATE();逐个参数说明IGNORE 表示遇到主键重复时跳过该行不报错不中断适用于渠道文件里少量重复的情况如果你希望重复行用新值覆盖旧值把 IGNORE 换成 REPLACE。OPTIONALLY ENCLOSED BY 是兼容 Python csv 模块默认带引号的输出。IGNORE 1 LINES 跳过表头。SET updated_at CURDATE() 把导入当天日期写进表作为这一批数据的时间锚点。导入后立刻做两个验证-- 核对总行数是否和清洗文件一致 SELECT COUNT(*) FROM phone_location; -- 按运营商看分布数量为 0 说明字段映射错位 SELECT carrier, COUNT(*) FROM phone_location GROUP BY carrier;如果文件里同一个 prefix 重复较多LOAD DATA 默认遇到主键冲突会报错中断加上 IGNORE 后则静默跳过所以导入前后行数对不上通常是正常现象不用慌但要能解释差异。注意如果遇到 secure-file-priv 相关报错优先改用 LOCAL 关键字走客户端上传而不是去改 my.cnf后者会影响所有文件导入操作风险更大。3.3 查询与索引核对EXPLAIN 里 typeconst 才算合格单条查询就一条 SQLSELECT province, city, carrier FROM phone_location WHERE prefix LEFT(13800001234, 7);LEFT 函数在这里只是演示“从手机号取前 7 位”实际代码里建议先算好前缀再传参。LEFT 作用在常量字符串上MySQL 优化器会在准备阶段把它折叠成 1380000不会影响走主键索引。批量给用户表打标签是更常见的场景SELECT u.id, u.phone, COALESCE(p.province, 未知) AS province, COALESCE(p.city, 未知) AS city FROM users u LEFT JOIN phone_location p ON p.prefix LEFT(u.phone, 7);LEFT JOIN 保留查不到归属地的用户COALESCE 把 NULL 归一成“未知”这样后续统计口径统一。需要留神的是 LEFT(u.phone, 7) 作用在列上如果 users 表数据量大这里可能不走索引但号段表只有几万行实际执行时会变成全表扫一遍压力不大。想较真的话程序端先把每行手机号截成 prefix 再传进来用下面的批量 IN 查询SELECT prefix, province, city, carrier FROM phone_location WHERE prefix IN (1380000, 1390000, 1370000);几万个前缀也能毫秒级返回。最后用 EXPLAIN 确认查询计划EXPLAIN SELECT * FROM phone_location WHERE prefix 1380000;看 type 列理想结果是 const说明走的是主键等值查询。如果看到 ref 或 ALL先检查 WHERE 条件里是不是对列做了函数包裹那是索引失效最常见的翻车点。4. 号段库上线前要避开的五个坑旧数据、虚拟号段与转网误判“非常全”只是卖家的话术数据导入不代表上线成功。下面五条来自实际维护中的高频现场每一条按“现象、原因、解决”的顺序讲。4.1 新号段查不到返回结果全是“未知”现象线上查询日志里出现一批连续前缀查不到集中在 19x、192 这类批得晚的号段每次查询都返回空或“未知”。原因号段文件发布早于新号段批文文件里根本没有这些前缀单纯靠查询优化解决不了这是纯数据缺口。解决定期拿码号资源公告把新号段整理成增量文件补进表里。补录用 INSERT 加 ON DUPLICATE KEY UPDATE保留已有记录的同时更新新字段INSERT INTO phone_location (prefix, province, city, carrier, updated_at) VALUES (1920000, 某省, 某市, 某运营商, CURDATE()) AS new ON DUPLICATE KEY UPDATE province new.province, city new.city, carrier new.carrier, updated_at new.updated_at;注意 MySQL 8.0.20 之后 VALUES() 写法已废弃用上面这种别名写法更稳。更省事的做法是给查询接口的“未知”分支加日志每周收一次未知前缀列表对比码号公告就能发现该补哪些段。4.2 虚拟运营商号段被归到错误归属现象一组 170、171、16x 开头的号段查询出的省份和运营商经常变同一份文件内部都不自洽。原因虚拟运营商租用基础网络号段发放时不绑定单一归属地渠道商清洗时按“卖号时登记的网络”填换一批货就换一套结果。解决把虚拟号段单独拆一张表字段只放 prefix 段和虚拟运营商标识查询时先走虚拟表再走基础表不要在前缀上做一刀切。展示文案明确写“号段归属”而不是“当前运营商”避免业务方把虚拟号段的归属当成准确属性。4.3 携号转网让归属地变成“历史归属”现象用户实际已经转网但查询返回的还是原号段的运营商信息甚至用户投诉“我在某省却显示某省”归属地和实际所在地对不上。原因号码段固定携号转网不改变号码本身任何号段表都无法表达“当前实时归属”。在线接口同样有这个盲区只是部分服务方接入了转网状态源看起来更准。解决架构上把结果字段命名成“号段归属地”不要叫“用户归属地”接口文档里写清数据口径。业务侧不要拿 carrier 做短信通道的硬分流转网用户会被送错通道正确做法是用号码前 3 位做粗分流发送失败后二次路由兜底。4.4 city 字段脏数据省直辖县、市辖区全挤在一个字段现象前端展示直接崩city 返回“省直辖县级行政区划”这类超长名称有的返回“市辖区”没法直接展示。原因原始文件做行政区划合并时把上级行政区名直接写进了 city 列没有按展示粒度归并。一些不设区县的地级市city 列填“市辖区”会让查询语义混乱。解决建一张映射表把长名称归一成展示名CREATE TABLE city_alias ( raw_name VARCHAR(64) PRIMARY KEY, display_name VARCHAR(32) NOT NULL );清洗时一次性 JOIN 更新UPDATE phone_location p JOIN city_alias a ON p.city a.raw_name SET p.city a.display_name;不要在查询 SQL 里做一串 REPLACE 嵌套多张表 JOIN 映射一次比写一堆字符串函数干净得多也方便后续维护映射关系。4.5 导入行数与文件行数对不上还没有报错现象文件统计 10 万行LOAD DATA 后 COUNT 只有 7 万过程没报错。原因同一 prefix 重复主键冲突被 IGNORE 静默吞掉或者文件里本身有空行、注释行被 wc 计入LOAD DATA 的 IGNORE 1 LINES 又跳了表头两边统计口径不一致。解决导入前先跑 2.3 节的 cut uniq 命令把重复量算出来。重复多的话在清洗阶段去重保留最新一条记录。导入后也跑一遍查重SELECT prefix, COUNT(*) AS cnt FROM phone_location GROUP BY prefix HAVING cnt 1 LIMIT 5;千万别为了让行数对上就把主键改成允许重复那会让查询结果出现多条记录取哪条全靠运气属于给自己埋雷。5. 让号段库长期可用增量更新、抽样回验与查询封装号段数据不是一次导入就永久有效的资产。新号段持续在批行政区划也在调整维护工作要固化下来。下面三件事建议放进自己的值班清单。5.1 增量更新新号段的补录流程补录流程四步拿增量文件用 2.3 节同一套清洗脚本跑一遍INSERT ... ON DUPLICATE KEY UPDATE 进表更新后跑一次分布统计。前两步和首次导入完全一样不用额外写逻辑。更新后做两个查询SELECT COUNT(*), MAX(updated_at) FROM phone_location;总条数和最近数据日期两条信息就够了。如果 MAX(updated_at) 停留在上次日期说明 UPDATE 没执行成功优先查增量文件路径和表结构是否匹配。更新脚本里日期不要写死用 CURDATE() 自动取当天省得每次改脚本。5.2 抽样回验别全信卖家信随机号码每次拿到新版数据或者大版本更新后我会抽样和在线接口对拍确认这版数据没有整体偏移。def verify(sample_count50): phones random.sample(active_phone_pool, sample_count) mismatch 0 for phone in phones: local get_local_prefix(phone[:7]) remote get_online_location(phone) if local.province ! remote.province: mismatch 1 log.warning(diff: %s local%s remote%s, phone, local, remote) return mismatch差异集中在某几个前缀说明那批数据旧差异散乱分布说明整表口径有问题。我自己的标准是差异超过样例的 20% 就直接回滚数据更新不再继续调。在线接口单查有成本验证集控制在 50 到 100 个就够。5.3 查询封装参数化与空结果策略查询接口最小的封装长这样def query_location(phone: str): if not phone or len(phone) ! 11: raise ValueError(手机号长度不合法) prefix phone[:7] sql SELECT province, city, carrier FROM phone_location WHERE prefix %s row db.query_one(sql, prefix) if row is None: return {province: 未知, city: 未知, carrier: 未知} return {province: row[province], city: row[city], carrier: row[carrier]}参数化查询防止把手机号拼进 SQL空结果归一成“未知”而不是返回 None避免上层拼 JSON 时报错。如果 QPS 超过每秒几百可以在服务启动时把全表加载到内存几万行也就几 MB查询变成字典取值更新表后重启或加版本号判断即可这是后续优化不是必选。我维护过几个号码属地库最大的教训是数据入手时再“全”上线前也必须把“旧数据、虚拟号段、转网口径”三件事跟业务方对齐否则查询上线两小时就会收到反馈。另一个教训是更新脚本必须留日志不然三个月后没人记得上次更新是哪天。这两个习惯帮我挡掉了不少麻烦希望帮到你。本文还有配套的精品资源点击获取