MySQL离线手机号码归属地数据库：导入、查询与避坑指南

MySQL离线手机号码归属地数据库：导入、查询与避坑指南

发布时间：2026/10/14 7:03:19
MySQL离线手机号码归属地数据库：导入、查询与避坑指南 简介手机号码归属地数据库资源收录38万条以上国内手机号段数据涵盖省份、城市、运营商等字段基于MySQL存储可支撑号码归属查询、区域分布统计、通信运营分析等需求适合开发人员、数据分析师及需要离线查询的项目使用。资源包为zip压缩包共2个文件包含可导入MySQL的SQL脚本文件和Access格式的MDB数据库文件大小8.27MBSQL文件便于快速建表导数据MDB文件便于Windows端预览和迁移。包内数据按手机号段组织字段清晰既可按号码精确查询归属地也可按省份、城市或运营商聚合统计适合前端展示、订单风控、客服系统等场景。资源自发布以来已有730人学习下载包内数据相对完整可直接导入现有系统省去自行采集和整理手机号段的时间成本。1. 手机号码归属地数据库一份能离线查询的 MySQL 资源包很多人一提到查手机号归属地下意识就想去接在线 API仿佛没有网络请求就做不了这件事。实际上我在做某跨平台系统的用户画像和注册风控时把一套基于 MySQL 的手机号码归属地数据库直接导入本地用一条 SQL 就能返回省份、城市、区号和运营商既没有网络开销也没有按次计费的压力。这份资源的核心是一组号段表、区号表和城市表外加一个封装好的查询函数导入后可以直接当工具用。适合需要批量处理号码、对数据隐私和调用成本敏感或者想在内部系统里快速做位置分析的开发者和数据分析人员。下面我按拆包、建表、查询、避坑和更新这条线完整讲一遍。2. 拆包看结构这份 MySQL 资源包里实际有哪三件套2.1 为什么离线号段库仍然可靠它的有效期边界在哪很多人会问运营商号段不是公开数据吗为什么还需要专门下载一份库这里有个现实约束从 2017 年开始官方渠道不再按月开放完整的号段归属数据市面可见的离线归属性数据库基本都来自早期号段档案的整理再叠加各渠道搜集到的新号段增量。换句话说任何离线库都有“数据截止日”这一点在拿到资源时就要接受而不是等查询结果对不上时才怀疑。所以一份合格的离线库通常不是一张“大宽表”而是拆成三个相互关联的小表号段表记录号码前缀和归属区号区号表记录区号对应的省份城市城市表记录更细的行政区和邮政编码。为什么要拆三层因为一个号段只对应一个区号一个区号又对应多个城市拆开后数据冗余少后续维护某个城市的改名、邮编调整时不需要动号段主表。这个模型也是业内做号码归属地存储最常见的做法跟你自己攒一套 API 代理缓存是两码事。了解了边界之后再看这套资源本身。它至少包含四类文件号段表初始化脚本、区号与城市字典脚本、查询函数脚本、数据字典说明文档。实际使用前建议先打开数据字典文档看一眼号段覆盖的年份范围再决定是否要补增量数据。如果库里最新号段只到某一年的某个月而你手头业务又大量出现新 8 位号段就得提前规划补充方案而不是等上线后被人反馈“查不到”。2.2 资源包文件清单与导入顺序我整理了一份这类离线库最常见的文件构成方便你拿到手后对照检查文件/内容作用建议导入顺序number_segments.sql号段表结构 号段种子数据1area_codes.sql区号、省份、城市、邮编字典2phone_query_helper.sql存储函数、查询示例3data_dictionary.md字段字典、号段版本说明文档参考导入顺序不是随便定的。多数离线库的号段表不设物理外键但逻辑上依赖区号表的存在先导入号段表、再导入区号表能避免在后续校验时看到一堆“区号找不到对应城市”的告警。查询函数脚本放最后因为它依赖前两张表的结构和部分种子数据才能编译通过。如果你拿到的包把这三段 SQL 揉在一起也不影响使用但调试时定位问题会麻烦一点我一般会先拆成独立文件再导。另外注意一点脚本里如果有CREATE DATABASE语句我建议先注释掉把建库动作自己来控制。这样能避免脚本内默认的库名和字符集跟你的生产环境不一致也防止误建了一个不想要的库。2.3 建库与导入命令按顺序执行下面这段命令是我在自己环境里验证过的标准导入流程可复制到服务器上执行# 1. 创建独立数据库避免和业务表混在同一个库里 mysql -uroot -p -e CREATE DATABASE IF NOT EXISTS phone_area \ DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; # 2. 导入号段表如果脚本里自带 USE 语句可以省略 -D 参数 mysql -uroot -p -D phone_area number_segments.sql # 3. 导入区号和城市字典 mysql -uroot -p -D phone_area area_codes.sql # 4. 导入查询函数和示例脚本 mysql -uroot -p -D phone_area phone_query_helper.sql # 5. 快速验证表和行数是否正常 mysql -uroot -p -D phone_area -e SHOW TABLES; \ SELECT COUNT(*) AS seg_cnt FROM number_segments; \ SELECT COUNT(*) AS area_cnt FROM area_codes;这段命令的逻辑是先建库并指定字符集再按依赖顺序导表最后用COUNT(*)确认数据落库。-D phone_area的意思是直接使用目标数据库省去脚本里反复写库名的麻烦。这里我特意把字符集定成utf8mb4而不是utf8原因是有相当一部分号段备注字段会包含生僻地名和特殊字符utf8在 MySQL 里最多只支持 3 字节遇到生僻字会变成问号utf8mb4是完整版 UTF-8这也是处理中文数据的基本底线。注意如果脚本内部已经包含USE phone_area;命令行里的-D可以省略否则两个库上下文冲突时可能报 “No database selected” 或含义不明的语法错。2.4 导入完先做三件事行数、抽样、查版本导入成功不等于数据可用。我先跑行数然后抽样几个已知号段验证归属地最后看一眼号段表里的 release_year 分布确认数据新鲜度。这一步是排查类动作看似基础但特别容易跳过跳过的后果往往在后面的查询阶段集中爆雷。-- 1. 看年份分布确认数据覆盖范围 SELECT release_year, COUNT(*) AS cnt FROM number_segments GROUP BY release_year ORDER BY release_year DESC; -- 2. 抽样验证查两个已知号段 SELECT seg_prefix, area_code FROM number_segments WHERE seg_prefix IN (1390000, 1880000);第一句能直观看出号段在哪些年份集中投放如果最新年份离当前时间超过一年说明你需要准备增量更新。第二句是抽查把结果和公开已知的归属地对照一下能快速识别资源包是否存在导入错位。我见过有人导完直接跑业务结果发现号段表和区号表 JOIN 不上究其原因就是导入时没检查区号表是否有完整数据。3. 表结构设计与索引优化为什么是三层模型而不是一张大表3.1 号段、区号、城市三张表的建表语句大多数离线号段库的表结构是高度相似的。我按实际使用体验把核心建表语句整理成这样-- 号段表核心表seg_prefix 是主键 CREATE TABLE number_segments ( seg_prefix VARCHAR(8) NOT NULL COMMENT 号段前缀7位或8位, operator_id TINYINT NOT NULL COMMENT 运营商编码: 1移动 2联通 3电信, area_code VARCHAR(4) NOT NULL COMMENT 区号保留前导零, release_year SMALLINT DEFAULT NULL COMMENT 号段投放年份, update_date DATE DEFAULT NULL COMMENT 入库更新日期, PRIMARY KEY (seg_prefix) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT手机号段表; -- 区号表区号到省份城市的映射 CREATE TABLE area_codes ( area_code VARCHAR(4) NOT NULL COMMENT 区号比如 010、021, province VARCHAR(32) NOT NULL COMMENT 省份, city VARCHAR(64) NOT NULL COMMENT 城市, postcode VARCHAR(6) DEFAULT NULL COMMENT 邮政编码, PRIMARY KEY (area_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT区号字典表;这里有两个容易忽略的设计细节。第一seg_prefix用VARCHAR(8)不用INT或BIGINT。原因是号段前缀可能以 0 开头虽然手机号本身不以 0 开头但后续如果要兼容固话区号查询VARCHAR能保住前导零。第二area_code也必须是VARCHAR(4)我在第 5 章会专门讲前导零丢失的坑这里先把类型定对后面省一大堆麻烦。为什么不用一张大表把所有字段塞进去一张大表在数据量小的时候看不出问题但号段数据一旦超过几十万行再频繁按城市分组、按省份统计冗余字段会让表体积膨胀索引也难建。拆成三层后号段表只存“前缀到区号的映射”区号表存“区号到城市的映射”职责清楚更新时也能单独刷新某张表。3.2 索引该建在哪几列别让查询拖垮全表号段表的主键就是seg_prefixInnoDB 引擎下它本身就是聚簇索引所以最常见的等值查询WHERE seg_prefix 1390000走的是主键索引速度没问题。真正容易忽略的是区号表和城市表的 JOIN 条件。-- 区号表主键已覆盖 JOIN 场景无需额外索引 -- 如果号段表有“区间号段”的替代设计则需要额外索引 CREATE TABLE number_segments_range ( start_seg VARCHAR(8) NOT NULL, end_seg VARCHAR(8) NOT NULL, area_code VARCHAR(4) NOT NULL, PRIMARY KEY (start_seg, end_seg), INDEX idx_end_seg (end_seg) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT连续号段区间表; -- 验证等值查询是否走主键 EXPLAIN SELECT seg_prefix, area_code FROM number_segments WHERE seg_prefix 1390000;EXPLAIN输出里type是const或ref说明命中了主键如果出现ALL表示全表扫描就要回头查字段类型或字符集。区间号段表适合运营商一次性投放一大段连续号码的场景查询方式从等值变成范围匹配SELECT area_code FROM number_segments_range WHERE start_seg 1391234 AND end_seg 1391234 LIMIT 1;这种范围查询依赖idx_end_seg或者联合主键做区间扫描。但我要提醒一句如果你的离线库同时包含离散前缀表和区间表查询时最好先走离散前缀表匹配不到再查区间表。因为多数据源合并后区间表里可能存在重叠区间范围查询返回的第一条不一定是最准确的那条。3.3 数据装载顺序、编码与合法性校验拿到配套 CSV 文件时导入命令一般是这样的LOAD DATA LOCAL INFILE /data/number_segments.csv INTO TABLE number_segments CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY IGNORE 1 LINES (seg_prefix, operator_id, area_code, release_year, update_date);这段命令的参数含义拆开讲LOCAL表示文件在客户端机器上不加这个参数会去服务器端找文件这是最常见的“File not found”原因CHARACTER SET utf8mb4直接把文件字节按 utf8mb4 解释避免中文注释乱码IGNORE 1 LINES跳过 CSV 表头。如果 CSV 里每列都用双引号包着就需要加OPTIONALLY ENCLOSED BY 不加会导致每行多出引号最终导入的数据带着引号入库查询时永远匹配不上。导入顺序上建议先导区号表再导号段表。虽然有外键的表可以靠SET FOREIGN_KEY_CHECKS0临时绕过检查但真正生产环境里我不推荐关外键检查。我更愿意让导入过程暴露依赖问题而不是把问题藏到查询阶段。4. 查询实现用存储函数把归属地判断锁在一个入口4.1 为什么封装成函数而不是每次都写 JOIN业务系统里查号码归属地的地方不止一处注册页、风控审核、用户列表、运营报表。如果每个场景各写一套 JOIN很容易出现口径不一致比如有的地方取 7 位有的地方取 8 位有的地方匹配不到就返回空字符串。把这些逻辑收拢到一个 MySQL 存储函数里等于把“归属地判断”变成一条单一规则后续要调整匹配优先级只改函数不动业务代码。另外一个理由是性能。存储函数在 MySQL 8.0 里可以被缓存加上DETERMINISTIC声明后优化器知道相同输入必然得到相同输出可以更放心地复用结果。虽然单次查询成本不高但在批量处理场景里这个声明能减少重复计算。4.2 函数代码与调用方式下面这段函数是整套资源的灵魂我把它做了注释DELIMITER $$ CREATE FUNCTION f_get_phone_info(p_phone VARCHAR(11)) RETURNS VARCHAR(255) DETERMINISTIC READS SQL DATA BEGIN DECLARE v_seg VARCHAR(8); DECLARE v_result VARCHAR(255); -- 非 11 位号码直接返回“非法号码” IF CHAR_LENGTH(p_phone) 11 THEN RETURN 非法号码; END IF; -- 优先用前 8 位匹配匹配不到再退回前 7 位 IF EXISTS (SELECT 1 FROM number_segments WHERE seg_prefix LEFT(p_phone, 8)) THEN SET v_seg LEFT(p_phone, 8); ELSE SET v_seg LEFT(p_phone, 7); END IF; SELECT CONCAT(ac.province, , ac.city, , op.operator_name) INTO v_result FROM number_segments ns LEFT JOIN area_codes ac ON ns.area_code ac.area_code LEFT JOIN operators op ON ns.operator_id op.operator_id WHERE ns.seg_prefix v_seg LIMIT 1; RETURN IFNULL(v_result, 未知); END$$ DELIMITER ;逻辑分三段。第一段校验号码长度把脏数据挡在门外第二段决定取 7 位还是 8 位前缀这是为了兼容新旧号段差异第三段做三段 JOIN拼出“省份 城市 运营商”的可读结果。参数p_phone是输入号码函数返回值是字符串。DETERMINISTIC告诉优化器函数是确定性的相同输入必有相同输出READS SQL DATA声明函数里包含 SELECT 查询。调用方式极其简单SELECT f_get_phone_info(13912345678);这里有个关键设计我先把v_seg用IF EXISTS查一次再做正式的 JOIN 查询。这样看起来多了一次查询实际上第一次命中的是主键索引成本极低换来的是“8 位优先”的匹配策略避免老号段把新号段挡住。4.3 前 7 位还是前 8 位为什么必须“先精确再泛化”早期号段按 7 位规划后来 8 位号段逐渐出现很多离线库为了兼容把 7 位和 8 位前缀混在同一张表里。如果直接按LEFT(p_phone, 7)匹配一个 8 位新号段会被它的 7 位祖先前缀抢先命中返回错误归属地。“先 8 后 7”的策略能解决大部分问题但要注意如果号段表里根本没有 8 位前缀数据IF EXISTS每次都会落空等于白白多查一次。对于这种情况我一般把号段表设计成seg_prefix列统一定长为 87 位号段右侧用0补齐查询时统一取前 8 位。这样虽然牺牲了一点存储但代码分支少逻辑更稳定。4.4 批量回填一次 JOIN 处理几万条号码有些需求不是查单号而是给一张用户表批量补省份和城市。逐行调用函数会非常慢循环几万次即使每次 1 毫秒也要几十秒。更稳妥的做法是一条 UPDATE JOIN 完成回填UPDATE user_profile u LEFT JOIN number_segments ns ON LEFT(u.mobile, 8) ns.seg_prefix LEFT JOIN area_codes ac ON ns.area_code ac.area_code SET u.province COALESCE(ac.province, 未知), u.city COALESCE(ac.city, 未知) WHERE u.mobile REGEXP ^1[3-9][0-9]{9}$;这段 SQL 先把用户表里合法的手机号筛出来再和号段表关联。LEFT JOIN是刻意用的因为允许部分号码匹配不到匹配不到的省份城市就置成‘未知’。这里唯一的隐患是LEFT(u.mobile, 8)在字段上用了函数导致 user_profile 表的索引派不上用场所以此类语句只适合一次性回填不适合高频线上查询。高频查询请走存储函数它只扫小号段表成本低得多。5. 避坑排查导入和查询阶段最容易翻车的五个问题5.1 区号前导零被抹掉010 变成 10现象导入后查询北京号码返回的区号是 10不是 010用于拼接 400 电话或展示时数据对不上。原因区号字段在导入前被建成了INT或BIGINTMySQL 对数值类型不会保留前导零。很多现成的离线库脚本里确实是这么写的因为建库的人图省事。解决把area_code字段改成VARCHAR(4)再刷一遍数据-- 找出长度不够的区号 SELECT area_code FROM area_codes WHERE LENGTH(area_code) 4; -- 用 LPAD 左侧补零 UPDATE area_codes SET area_code LPAD(area_code, 4, 0) WHERE LENGTH(area_code) 4;这个问题最坑的地方在于部分号码查出来是对的比如区号原本就不带 0部分号码是错的表面看像“数据不准”实际上根因是字段类型。从那以后我拿到任何库的第一件事就是看建表语句里的区号字段类型。5.2 新旧号段混用只按 7 位前缀匹配出现误判现象同一个 7 位前缀下挂了两个城市的记录查询结果时对时错尤其集中在近几年的新号段。原因新投放的 8 位号段和旧 7 位号段的前缀前几位相同但实际归属地已经变化。如果查询只管 7 位等于无条件相信老库。解决匹配逻辑改成“先 8 后 7”并且号段表要能区分前缀长度SELECT seg_prefix, area_code FROM number_segments WHERE seg_prefix LEFT(18812345678, 8) OR seg_prefix LEFT(18812345678, 7) ORDER BY CHAR_LENGTH(seg_prefix) DESC LIMIT 1;这条 SQL 的核心是ORDER BY CHAR_LENGTH(seg_prefix) DESC让 8 位号段排前面优先被选中。如果你用存储函数等价逻辑就是 4.2 里的IF EXISTS分支。5.3 迁移到新服务器后函数报 “definer does not exist”现象使用mysqldump把库从旧机器导出导入新机器后调用存储函数报ERROR 1449 (HY000): The user specified as a definer ... does not exist。原因导出文件里存储函数头部带了原库的 definer 账号新库没有这个 MySQL 账号函数就编译不了。解决导出时加--skip-definer如果已经导进来了就查看函数定义并修正SELECT routine_schema, routine_name, definer FROM information_schema.routines WHERE routine_name f_get_phone_info;查到 definer 后用 root 在目标库创建同名账号并授权或者把函数删掉重建重建时去掉DEFINER关键字。这个坑和手机号数据本身无关但只要资源包带存储函数十有八九会遇到提前知道能省半天排查时间。5.4 JOIN 字段字符集不一致索引悄悄失效现象号段表只有几十万行单条查询却要几十毫秒EXPLAIN显示全表扫描。原因号段表的seg_prefix是utf8mb4_general_ci区号表的area_code却是utf8mb4_unicode_ci两张表 JOIN 时 MySQL 必须做字符集转换索引无法直接用于比较。解决统一字符集和排序规则然后在 JOIN 条件两侧字段上确认类型完全一致ALTER TABLE area_codes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; EXPLAIN SELECT ns.seg_prefix, ac.city FROM number_segments ns LEFT JOIN area_codes ac ON ns.area_code ac.area_code WHERE ns.seg_prefix 1390000;字符集不一致导致的隐式转换比字段类型不一致更难发现因为数据看起来一切正常只是慢。经验是导入任何外部库之前先把所有表的字符集统一成一套。5.5 导入 CSV 多出一行表头中文全变问号现象COUNT(*)比预期多一行且那行的seg_prefix是seg_prefix这个单词本身中文地名变成???。原因LOAD DATA没有加IGNORE 1 LINES文件的实际编码也不是 utf8。解决显式声明编码并跳过首行LOAD DATA LOCAL INFILE /data/number_segments.csv INTO TABLE number_segments CHARACTER SET utf8mb4 FIELDS TERMINATED BY , IGNORE 1 LINES (seg_prefix, operator_id, area_code, release_year, update_date);如果文件本身是 GBK 编码先把文件转成 utf8 再导入不要试图在 SQL 里用SET NAMES gbk来糊弄那会留下更多隐患。6. 把离线库用活增量更新与命中率自检6.1 每月增量合并按主键去重离线库的时效性靠增量维护。拿到新号段 CSV 后我会先备份旧表再用INSERT ... ON DUPLICATE KEY UPDATE合并INSERT INTO number_segments (seg_prefix, operator_id, area_code, release_year, update_date) VALUES (1981234, 3, 010, 2024, 2024-06-01) ON DUPLICATE KEY UPDATE operator_id VALUES(operator_id), area_code VALUES(area_code);seg_prefix是主键重复时自动走更新分支。整个过程用事务包起来出错可以一键回滚。6.2 抽样验证命中率更新完不能直接上线先抽样自检SELECT COUNT(*) AS total, SUM(CASE WHEN ns.seg_prefix IS NOT NULL THEN 1 ELSE 0 END) AS hit_cnt, ROUND(SUM(CASE WHEN ns.seg_prefix IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*), 3) AS hit_rate FROM ( SELECT mobile FROM user_profile WHERE mobile REGEXP ^1[3-9][0-9]{9}$ ORDER BY RAND() LIMIT 2000 ) tmp LEFT JOIN number_segments ns ON LEFT(tmp.mobile, 8) ns.seg_prefix;命中率低于 95% 说明库需要补数据高于 98% 基本够用。残留的未命中大多是虚拟运营商和物联网卡号段这类号段本来就不在传统归属地库里不要因此怀疑整个资源包坏了。我在某模拟项目 X 里吃过一次亏上线后用户反馈归属地不准查到最后原因是 7 位号段和 8 位号段混用加上区号字段被建成 INT前导零全丢。从那以后我每次导入号段库都会强制走一遍字段类型检查、前导零补齐、先 8 后 7 匹配、抽样命中率自检这四步。希望帮到你。本文还有配套的精品资源点击获取