简介资源包内含一个手机号归属地数据库文件内容基于主流关系型数据库管理系统收录了号码段对应的省份、城市等区域信息适合开发人员、数据分析师以及需要快速定位客户区域的运营人员。压缩包共含一个文件类型为数据库脚本整体大小约二点二兆字节导入数据库后即可直接使用表格结构清晰便于后续查询与统计。这一数据集目前已获得两百六十八人次学习下载据称来自商业渠道号码覆盖较为全面可作为业务系统的基础数据。用户可以通过编写查询语句实现单个号码的归属地匹配也能够使用分组统计等功能按照省份、城市聚合用户数量为市场投放和区域决策提供参考依据。需要特别注意的是处理此类个人敏感信息时应当遵守个人信息保护相关法律法规采取必要的脱敏和访问控制措施确保数据使用安全合规。1. 手机号归属地查询一份 50 元买的 MySQL 数据怎么把它变成能用的服务做业务系统时经常碰到一个不起眼的需求用户填了手机号后端要在毫秒级返回“哪个省、哪个市、哪家运营商”。自己维护一套归属地数据麻烦点不在查而在数据从哪来。很多人会去某电商平台花几十元买一份号称“非常全”的手机号归属地数据卖家发来一个压缩包里面可能是 CSV、TXT 或者 XLSX通常附一句“导入 MySQL 就能用”。但真到了导入这一步才发现编码乱码、号段重复、新号段缺失、查询慢问题一个接一个。这篇文章不讨论这类数据商品的来源和授权归属只聚焦一件事把手里这份老数据变成 MySQL 里一张能扛住线上查询的表。我会按“看懂数据 → 建表 → 导入 → 查询 → 排坑 → 保鲜”的顺序讲全程给可复现的命令和 SQL。数据来源的合规性自己把握技术处理的部分照着做就行。2. 先看清号段数据长什么样字段设计决定后面顺不顺2.1 手机号归属地数据的基本结构与三种常见格式手机号本身是 11 位前 3 位是网络识别号前 7 位是号段也叫 HIR 码后 4 位是用户号。市面上卖的归属地数据最粗的粒度到“前 3 位”比如 139 属于某运营商好一点的数据到“前 4 位”能分出更多细分号段真正“非常全”的数据普遍到“前 7 位”一条记录对应一个号段能精确到省份和城市。买了数据之后第一件事不是建表而是解压后先看文件头和文件格式。常见格式大致有三种纯 CSV 逗号分隔、TXT 制表符分隔、XLSX 表格。前两种直接能用 LOAD DATAXLSX 要么另存为 CSV要么用脚本先转一遍。我用一个简单命令看文件前几行head -n 20 phone_data.csv | iconv -f gbk -t utf-8如果屏幕上是正常的中文说明文件本身是 UTF-8如果出现一堆乱码或问号说明源文件是 GBK 或 GB2312 编码后面导入时必须做编码转换。拿到文件后先用 Python 或者 awk 做一次“体检”重点看三件事总行数、每个字段的样本值、有没有明显脏数据。我一般会写个十几行的脚本把前 50 行和字段类型打印出来看清楚每一列到底存的是什么。这个动作看起来浪费几分钟实际上能帮你省掉后面反复改表结构的麻烦。字段设计上最常用的列就这么几个号段前 7 位、省份、城市、运营商类型、区号、邮编。区号和邮编是附加值不一定需要如果业务里不展示邮编表里完全可以不建这一列省空间也省索引。值得提醒的是不要买回来就直接把 Excel 里的列名当表字段名中文列名在 MySQL 里能用但后续写 SQL 很别扭统一转成英文小写加下划线。2.2 建表把号段表设计成能扛住千万级查询的样子核心设计原则一句话号段字段必须是等值查询不能靠 LIKE 139% 去匹配。手机号归属地查询的本质是“取前 7 位去表里查一行”所以号段字段要建唯一索引查询走点查而不是范围扫描。我常用的建表语句如下CREATE TABLE phone_location ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, segment CHAR(7) NOT NULL COMMENT 手机号前7位号段, province VARCHAR(32) NOT NULL DEFAULT COMMENT 省份, city VARCHAR(32) NOT NULL DEFAULT COMMENT 城市, isp VARCHAR(16) NOT NULL DEFAULT COMMENT 运营商, area_code VARCHAR(8) NOT NULL DEFAULT COMMENT 区号, postcode VARCHAR(8) NOT NULL DEFAULT COMMENT 邮编, PRIMARY KEY (id), UNIQUE KEY uk_segment (segment) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT手机号归属地号段表;几个参数单独说明一下。segment用 CHAR(7) 而不是 VARCHAR(7)因为号段长度固定CHAR 在等值查询上没差别但语义上更干净id自增主键是留给后续做增量更新用的业务查询不依赖它。uk_segment唯一索引是这张表的灵魂它同时承担两件事一是保证号段不重复二是让查询走索引点查。导入时如果遇到重复号段唯一索引会直接报错这正好能帮我们发现文件里的脏数据。字符集方面表用 utf8mb4别用 utf8。现在很多线上库默认就是 utf8mb4但如果是从老库导出的数据源文件里可能带 emoji 或者特殊字符utf8 存不下会直接报错。省份和城市字段加NOT NULL DEFAULT 是为了避免 NULL 值参与排序和分组时出现的各种意外实际导入时空值会自动填成空字符串。关于存储引擎有人会问能不能用 MyISAM 或者 MEMORY 来加速查询。我的建议是老老实实用 InnoDB。MEMORY 表重启丢数据不适合做持久化MyISAM 不支持事务而且表锁在高并发查询下是灾难。InnoDB 配上合适的索引千万级数据量的点查一样是毫秒级完全没必要牺牲可靠性去换那点性能。3. 把数据倒进 MySQLLOAD DATA 的完整命令与编码处理3.1 导入前的数据体检总行数、重复号段、非法记录数据文件拿到手别急着导。先统计行数、检查重复、确认编码这三步做完能避开 80% 的导入翻车现场。# 统计总行数减去表头行 wc -l phone_data.csv # 检查是否有重复号段假设文件是两列号段,省份城市运营商 awk -F, {print $1} phone_data.csv | sort | uniq -d | head -n 20 # 检测文件编码 file -i phone_data.csvawk那行命令专门用来列出重复的号段如果输出很多说明数据文件里有同一个号段对应多条记录的情况比如先出现 1390000 归属某省后面又出现 1390000 归属另一个城市。这种重复数据直接导入 MySQL 会被唯一索引拦住所以要先弄清楚是保留第一条、保留最后一条还是合并信息。我处理重复号段的常见做法是业务上以“最后一条为准”因为后出现的往往是对旧记录的修正。用 sort 和 uniq 去重会破坏顺序更好的方式是用 awk 按号段做覆盖保留最后一次出现的值awk -F, {a[$1]$0} END {for (k in a) print a[k]} phone_data.csv phone_data_dedup.csvfile -i用来确认文件编码。如果显示charsetgbk导入前要先转成 UTF-8。转码命令用 iconv 就行iconv -f gbk -t utf-8 phone_data.csv phone_data_utf8.csv。要特别注意如果文件是 GB18030 编码用 gbk 转会丢字符这时要用-f gb18030。体检的最后一步是看字段里有没有脏数据比如包含逗号、引号、换行符的字段。CSV 最怕带引号和换行典型现象是导完之后行数和源文件对不上。我一般用awk -F, {print NF}检查每行列数是否一致只要出现列数不等于预期值的行就说明文件里有转义没处理干净得先用 CSV 解析库重写一遍。3.2 LOAD DATA 导入字段映射与中文乱码的解法数据文件确认无误后用LOAD DATA LOCAL INFILE导入。这是 MySQL 导入文本文件最快的方式比逐行 INSERT 快一个数量级。以下是完整命令LOAD DATA LOCAL INFILE /tmp/phone_data_utf8.csv INTO TABLE phone_location CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (segment, province_city, isp, area_code, postcode) SET segment TRIM(segment), province SUBSTRING_INDEX(province_city, -, 1), city SUBSTRING_INDEX(province_city, -, -1), isp TRIM(isp), area_code TRIM(area_code), postcode TRIM(postcode);这句命令有几个关键点。CHARACTER SET utf8mb4告诉 MySQL 源文件是 UTF-8 编码如果文件没转码这里要填gbk否则中文会变成问号。FIELDS TERMINATED BY ,指定分隔符是英文逗号有些数据文件用制表符分隔改成\t即可。IGNORE 1 LINES跳过表头如果你的文件本来就没有表头这行删掉。最灵活的是SET部分。这个场景假设源文件里“省份城市”是同一列用横杠分隔比如“广东-深圳”于是用SUBSTRING_INDEX拆成两列。实际文件格式五花八门有的是三列省份、城市、运营商各一列有的是四列直接把变量对应上去就行。原则是源文件每列对应一个变量目标列的赋值写在SET里该拆就拆、该拼就拼。导入过程中先别急着看最终结果主要盯着两件事一是日志里有没有Duplicate entry报错二是导入行数和 wc -l 统计的是否接近。如果报错很多说明去重没做干净不要用IGNORE关键字强行吞掉错误先回头把数据文件清理好再导。3.3 校验导入结果用 SQL 验证完整性导入完成不等于导入正确。我会用三条 SQL 做校验分别检查总量、空值和抽样准确性-- 检查总数是否和源文件一致 SELECT COUNT(*) FROM phone_location; -- 检查关键字段是否有空值 SELECT COUNT(*) FROM phone_location WHERE province OR city OR isp ; -- 抽查几个已知号段验证归属地是否和常识一致 SELECT segment, province, city, isp FROM phone_location WHERE segment IN (1390000, 1880000, 1700000);总行数校验很直接导入完成后MySQL 返回的受影响行数应该和源文件去重后的行数一致。如果少了大概率是编码问题导致整行被跳过如果多了说明文件里有隐藏换行符一行被拆成了两行。空值检查抓的是字段映射错误比如SUBSTRING_INDEX写错方向导致省份和城市颠倒。抽样校验这里有个小技巧选号段时不要只挑常见的 139、138一定要带上虚拟运营商号段开头的 170、171 和新号段开头的 190、199。这些号段恰恰是数据文件最薄弱的地方如果源头数据“非常全”是吹的抽这些一查就露馅。4. 查询接口与索引优化从一条 SQL 到一个能用的服务4.1 封装查询逻辑函数、存储过程还是业务代码里算表建好了接下来是查询。查询逻辑很简单传入 11 位手机号截取前 7 位去表里等值查。但这段逻辑放在哪一层不同项目有不同的习惯。最蠢的做法是在业务代码里LIKE 139%去查——会让 MySQL 无法有效利用索引数据量一大就卡死。我一般会先在 MySQL 里写一个查询函数把“截号段 查询”的逻辑固化在数据库层业务方调用时只传手机号DELIMITER $$ CREATE FUNCTION get_phone_location(p_phone VARCHAR(11)) RETURNS VARCHAR(255) DETERMINISTIC READS SQL DATA BEGIN DECLARE v_segment CHAR(7); DECLARE v_result VARCHAR(255); SET v_segment LEFT(p_phone, 7); SELECT CONCAT(province, , city, , isp) INTO v_result FROM phone_location WHERE segment v_segment LIMIT 1; RETURN IFNULL(v_result, 未知归属地); END$$ DELIMITER ;调用就一行SELECT get_phone_location(13912345678)。这个函数的巧妙之处在于把“取前 7 位”的动作限定在 SQL 内部业务代码永远只传完整手机号不会出现有的地方截 3 位、有的地方截 7 位的不一致。不过要注意函数里用了LEFT(p_phone, 7)如果传入的手机号不是 11 位比如 10 位或者带86前缀返回值会变怪。调用前建议在业务侧统一格式只传纯 11 位数字。也可以顺手在里面加一个条件判断长度不为 11 时直接返回“参数错误”。4.2 索引策略与查询性能为什么等值查询比 LIKE 快这么多号码归属地查询慢十有八九是索引没用上。最容易踩的就是把查询写成WHERE segment LIKE 139%这里 % 通配符放在结尾MySQL 虽然可能走索引但本质上还是范围扫描更常见的是有人把手机号整段存了然后WHERE phone LIKE 139%这等于每次查询要扫全表里所有 139 开头的完整号码代价完全不可控。正确的姿势是“前 7 位等值查询”。我做一个简单的性能对比你就明白了-- 做法一错误示范全表扫描 SELECT * FROM phone_location WHERE segment LIKE 139%; -- 做法二正确示范索引点查 SELECT * FROM phone_location WHERE segment 1390000;后者走的是uk_segment唯一索引B 树定位到一行百万级数据量下耗时在 1 毫秒以内。前者如果只有 139 一个号段可能还会用索引但如果 LIKE 的是%3900这种通配符在开头的写法MySQL 直接放弃索引扫全表数据一多立刻卡住。还有一个很多人忽略的点id自增主键在这个业务里其实有点浪费。因为所有的查询都走segment唯一索引主键id只承担行定位功能。如果这张表只需要“按号段查归属地”这一种访问模式可以干脆把segment设为主键省掉id列表能小一圈。保留id的好处是后续做增量更新时方便记录批次取舍看实际业务。4.3 把查询服务化一个最小的 HTTP 接口封装业务系统一般不会直接让前端连 MySQL通常是后端查完再返回 JSON。这里给一个最小可用的 PHP 封装示例逻辑核心只有三步接收手机号、调用函数、返回结果。?php $phone $_GET[phone] ?? ; if (!preg_match(/^1[3-9]\d{9}$/, $phone)) { http_response_code(400); echo json_encode([error invalid phone]); exit; } $pdo new PDO(mysql:host127.0.0.1;dbnameapp, user, pass, [ PDO::ATTR_ERRMODE PDO::ERRMODE_EXCEPTION ]); $stmt $pdo-prepare(SELECT get_phone_location(:phone) AS location); $stmt-execute([:phone $phone]); $location $stmt-fetchColumn(); echo json_encode([phone $phone, location $location]);正则^1[3-9]\d{9}$用来拦截非 11 位数字的脏请求避免把垃圾参数送进数据库。PDO 预处理固定参数防止注入。这里刻意没有做 Redis 缓存原因是归属地数据本身变化极慢同一号段的查询结果完全一样缓存收益巨大后面第 6 章我会专门讲怎么给这个场景加缓存。有人会把这张表直接跨库 JOIN 到用户表比如SELECT u.*, pl.city FROM users u LEFT JOIN phone_location pl ON pl.segment LEFT(u.phone, 7)。这种做法在数据量不大时问题不大但一旦用户表到了几百万行每次全表 JOIN 都要对 LEFT 函数结果做关联索引完全失效。建议保持“先查用户、再查归属地”的两步查询或者把归属地冗余到用户表的一个字段里。5. 避坑清单数据“非常全”背后的五个真实边界5.1 查询慢到怀疑人生号段字段根本没走索引现象数据导完表里 60 万行但查一个号段要 300 毫秒以上并发一上来直接拖垮数据库。原因表里segment字段没有索引或者查询语句写成了LIKE %139%MySQL 只能全表扫描。还有一个隐蔽原因源文件里号段带了空格或者横杠比如存成139-0000导致 CHAR(7) 字段里存的内容根本对不上等值查询命中不了。解决先跑一遍EXPLAIN SELECT * FROM phone_location WHERE segment 1390000看type是不是const或ref。如果是ALL说明没走索引补上唯一索引如果是字段脏数据问题用UPDATE phone_location SET segment REPLACE(segment, -, )清洗后再查。5.2 导入后中文全是问号源文件编码和 MySQL 字符集打架现象LOAD DATA 执行成功行数也对但 province 列显示???或者一堆乱码。原因源文件是 GBK 编码命令里写了CHARACTER SET gbk但文件实际是 GB2312 或 GB18030部分字符映射失败或者命令里漏写了CHARACTER SET子句MySQL 用了表的默认字符集 utf8mb4 去解析 GBK 字节流自然全乱。解决先file -i phone_data.csv确认编码再在 LOAD DATA 命令里严格对应。如果是 GB18030就用CHARACTER SET gb18030。补救办法是删掉表数据重新导入不要试图用CONVERT函数在 SET 阶段转码转完还是脏的。5.3 新号段查不到归属地数据“全”是相对的不是实时的现象129、198、199 开头的手机号查询结果全是“未知归属地”而 139、138 这些老号段完全正常。原因卖家打包的数据是基于某个时间点的历史快照新号段要么当时还没放号要么没来得及整理进去。这是所有离线号码归属地数据的物理边界与数据质量无关。解决对接运营商公开号段公告定期手工补充新号段记录。具体做法是去三大运营商官网翻最近半年新增的号段整理成“号段-省份-城市-运营商”格式的 CSV按第 3 章的方式增量导入。注意新增记录和旧记录一样segment必须是前 7 位先查重再入库。5.4 携号转网后归属地不准离线数据的天然硬伤现象用户 188 开头的号码现在实际用的是另一家运营商但查询结果还是老运营商的归属信息。原因携号转网只变运营商、不变号码离线号段表记录的是“这个号段最初分配给谁”不是“这个号码现在属于谁”。任何纯离线方案都解决不了这个问题这是业务场景的物理限制不是代码 bug。解决如果业务强依赖准确运营商信息比如充话费、办套餐必须在页面文案里弱化展示或者接运营商的实时查询接口。如果只是展示省份城市影响很小直接接受这个误差即可。别在这上面花太多时间优化离线表做不到。5.5 导入中途报错主键冲突源数据文件有重复号段现象LOAD DATA 执行没多久就报Duplicate entry 1390000 for key uk_segment导入中断前功尽弃。原因源文件里同一个号段出现多次可能是一开始分配给了某省后来调整到另一个城市也可能是卖家打包时合并了多份表交叉部分没去重。解决导入前先用第 3 章的 awk 命令做一次按号段去重保留最后一条。或者干脆在 LOAD DATA 后面加IGNORE关键字让 MySQL 自动跳过重复行。但我不建议直接 IGNORE它会掩盖数据文件本身的质量问题后期查漏补缺时连“哪条被跳过了”都不知道还是先清理再导更靠谱。6. 让数据保鲜增量更新脚本与自我校验的实用技巧离线归属地数据最大的痛点不是导入而是会过期。号段在持续新增运营商归属在变化文件里那份“非常全”的数据过一年就只剩“非常旧”。我自己的习惯是建一个简单的保鲜机制每周跑一次增量检查和一次抽样比对。新号段检测可以做成一个 bash 脚本思路是定义几个“哨兵号码”——比如 199、190、192 开头的测试号每周期用外部查询接口随便一个能查归属地的公开服务就行拉一次结果和本地 MySQL 表对比。如果发现本地查不到或归属地和接口返回不一致就把这个号段标记出来人工确认后补录。手动补录的 SQL 就这么简单INSERT INTO phone_location (segment, province, city, isp) VALUES (1920000, 某省份, 某城市, 某运营商) ON DUPLICATE KEY UPDATE province VALUES(province), city VALUES(city), isp VALUES(isp);ON DUPLICATE KEY UPDATE是增量更新的核心它保证同一个号段只会存在一条记录新值直接覆盖旧值不用先 DELETE 再 INSERT。这个技巧我第一次用的时候觉得很省事后来才发现如果不带这个子句光写 INSERT 就会频繁撞唯一索引报错那才是真的浪费时间。至于缓存层我建议把“号段 → 归属地”的映射放到 Redis 里KEY 直接设计成phone:seg:1390000TTL 设 7 天。这样同一号段的重复查询根本不会打到 MySQL数据库压力小到可以忽略。缓存和 MySQL 的一致性不用担心TTL 过期后自然回源MySQL 里改了数据最多 7 天生效对归属地这种低频变动数据完全够用。这里有个我踩过的坑值得说一句早期我做增量更新时图省事直接 DROP 掉旧表重新导入新数据结果有一次新文件缺了整整一个号段段位线上查归属地直接大面积报“未知”用户投诉才反应过来。后来我养成了一个习惯所有涉及数据覆盖的操作先mysqldump导出旧表备份到本地再动导入出问题随时能回滚。数据这东西后悔药永远是自己提前准备的。这套方案做到最后你会发现它已经不只是一张表的事体检脚本、LOAD DATA 命令、查询函数、Redis 缓存、增量更新机制五块拼起来才是一个完整能用的归属地服务。刚开始可能觉得流程长但每一步都是被真实生产环境逼出来的。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/10/9 21:15:06 · 阅读 1.2k 手机号归属地查询：MySQL号段表导入与索引优化实战
简介资源包内含一个手机号归属地数据库文件内容基于主流关系型数据库管理系统收录了号码段对应的省份、城市等区域信息适合开发人员、数据分析师以及需要快速定位客户区域的运营人员。压缩包共含一个文件类型为数据库脚本整体大小约二点二兆字节导入数据库后即可直接使用表格结构清晰便于后续查询与统计。这一数据集目前已获得两百六十八人次学习下载据称来自商业渠道号码覆盖较为全面可作为业务系统的基础数据。用户可以通过编写查询语句实现单个号码的归属地匹配也能够使用分组统计等功能按照省份、城市聚合用户数量为市场投放和区域决策提供参考依据。需要特别注意的是处理此类个人敏感信息时应当遵守个人信息保护相关法律法规采取必要的脱敏和访问控制措施确保数据使用安全合规。1. 手机号归属地查询一份 50 元买的 MySQL 数据怎么把它变成能用的服务做业务系统时经常碰到一个不起眼的需求用户填了手机号后端要在毫秒级返回“哪个省、哪个市、哪家运营商”。自己维护一套归属地数据麻烦点不在查而在数据从哪来。很多人会去某电商平台花几十元买一份号称“非常全”的手机号归属地数据卖家发来一个压缩包里面可能是 CSV、TXT 或者 XLSX通常附一句“导入 MySQL 就能用”。但真到了导入这一步才发现编码乱码、号段重复、新号段缺失、查询慢问题一个接一个。这篇文章不讨论这类数据商品的来源和授权归属只聚焦一件事把手里这份老数据变成 MySQL 里一张能扛住线上查询的表。我会按“看懂数据 → 建表 → 导入 → 查询 → 排坑 → 保鲜”的顺序讲全程给可复现的命令和 SQL。数据来源的合规性自己把握技术处理的部分照着做就行。2. 先看清号段数据长什么样字段设计决定后面顺不顺2.1 手机号归属地数据的基本结构与三种常见格式手机号本身是 11 位前 3 位是网络识别号前 7 位是号段也叫 HIR 码后 4 位是用户号。市面上卖的归属地数据最粗的粒度到“前 3 位”比如 139 属于某运营商好一点的数据到“前 4 位”能分出更多细分号段真正“非常全”的数据普遍到“前 7 位”一条记录对应一个号段能精确到省份和城市。买了数据之后第一件事不是建表而是解压后先看文件头和文件格式。常见格式大致有三种纯 CSV 逗号分隔、TXT 制表符分隔、XLSX 表格。前两种直接能用 LOAD DATAXLSX 要么另存为 CSV要么用脚本先转一遍。我用一个简单命令看文件前几行head -n 20 phone_data.csv | iconv -f gbk -t utf-8如果屏幕上是正常的中文说明文件本身是 UTF-8如果出现一堆乱码或问号说明源文件是 GBK 或 GB2312 编码后面导入时必须做编码转换。拿到文件后先用 Python 或者 awk 做一次“体检”重点看三件事总行数、每个字段的样本值、有没有明显脏数据。我一般会写个十几行的脚本把前 50 行和字段类型打印出来看清楚每一列到底存的是什么。这个动作看起来浪费几分钟实际上能帮你省掉后面反复改表结构的麻烦。字段设计上最常用的列就这么几个号段前 7 位、省份、城市、运营商类型、区号、邮编。区号和邮编是附加值不一定需要如果业务里不展示邮编表里完全可以不建这一列省空间也省索引。值得提醒的是不要买回来就直接把 Excel 里的列名当表字段名中文列名在 MySQL 里能用但后续写 SQL 很别扭统一转成英文小写加下划线。2.2 建表把号段表设计成能扛住千万级查询的样子核心设计原则一句话号段字段必须是等值查询不能靠 LIKE 139% 去匹配。手机号归属地查询的本质是“取前 7 位去表里查一行”所以号段字段要建唯一索引查询走点查而不是范围扫描。我常用的建表语句如下CREATE TABLE phone_location ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, segment CHAR(7) NOT NULL COMMENT 手机号前7位号段, province VARCHAR(32) NOT NULL DEFAULT COMMENT 省份, city VARCHAR(32) NOT NULL DEFAULT COMMENT 城市, isp VARCHAR(16) NOT NULL DEFAULT COMMENT 运营商, area_code VARCHAR(8) NOT NULL DEFAULT COMMENT 区号, postcode VARCHAR(8) NOT NULL DEFAULT COMMENT 邮编, PRIMARY KEY (id), UNIQUE KEY uk_segment (segment) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT手机号归属地号段表;几个参数单独说明一下。segment用 CHAR(7) 而不是 VARCHAR(7)因为号段长度固定CHAR 在等值查询上没差别但语义上更干净id自增主键是留给后续做增量更新用的业务查询不依赖它。uk_segment唯一索引是这张表的灵魂它同时承担两件事一是保证号段不重复二是让查询走索引点查。导入时如果遇到重复号段唯一索引会直接报错这正好能帮我们发现文件里的脏数据。字符集方面表用 utf8mb4别用 utf8。现在很多线上库默认就是 utf8mb4但如果是从老库导出的数据源文件里可能带 emoji 或者特殊字符utf8 存不下会直接报错。省份和城市字段加NOT NULL DEFAULT 是为了避免 NULL 值参与排序和分组时出现的各种意外实际导入时空值会自动填成空字符串。关于存储引擎有人会问能不能用 MyISAM 或者 MEMORY 来加速查询。我的建议是老老实实用 InnoDB。MEMORY 表重启丢数据不适合做持久化MyISAM 不支持事务而且表锁在高并发查询下是灾难。InnoDB 配上合适的索引千万级数据量的点查一样是毫秒级完全没必要牺牲可靠性去换那点性能。3. 把数据倒进 MySQLLOAD DATA 的完整命令与编码处理3.1 导入前的数据体检总行数、重复号段、非法记录数据文件拿到手别急着导。先统计行数、检查重复、确认编码这三步做完能避开 80% 的导入翻车现场。# 统计总行数减去表头行 wc -l phone_data.csv # 检查是否有重复号段假设文件是两列号段,省份城市运营商 awk -F, {print $1} phone_data.csv | sort | uniq -d | head -n 20 # 检测文件编码 file -i phone_data.csvawk那行命令专门用来列出重复的号段如果输出很多说明数据文件里有同一个号段对应多条记录的情况比如先出现 1390000 归属某省后面又出现 1390000 归属另一个城市。这种重复数据直接导入 MySQL 会被唯一索引拦住所以要先弄清楚是保留第一条、保留最后一条还是合并信息。我处理重复号段的常见做法是业务上以“最后一条为准”因为后出现的往往是对旧记录的修正。用 sort 和 uniq 去重会破坏顺序更好的方式是用 awk 按号段做覆盖保留最后一次出现的值awk -F, {a[$1]$0} END {for (k in a) print a[k]} phone_data.csv phone_data_dedup.csvfile -i用来确认文件编码。如果显示charsetgbk导入前要先转成 UTF-8。转码命令用 iconv 就行iconv -f gbk -t utf-8 phone_data.csv phone_data_utf8.csv。要特别注意如果文件是 GB18030 编码用 gbk 转会丢字符这时要用-f gb18030。体检的最后一步是看字段里有没有脏数据比如包含逗号、引号、换行符的字段。CSV 最怕带引号和换行典型现象是导完之后行数和源文件对不上。我一般用awk -F, {print NF}检查每行列数是否一致只要出现列数不等于预期值的行就说明文件里有转义没处理干净得先用 CSV 解析库重写一遍。3.2 LOAD DATA 导入字段映射与中文乱码的解法数据文件确认无误后用LOAD DATA LOCAL INFILE导入。这是 MySQL 导入文本文件最快的方式比逐行 INSERT 快一个数量级。以下是完整命令LOAD DATA LOCAL INFILE /tmp/phone_data_utf8.csv INTO TABLE phone_location CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (segment, province_city, isp, area_code, postcode) SET segment TRIM(segment), province SUBSTRING_INDEX(province_city, -, 1), city SUBSTRING_INDEX(province_city, -, -1), isp TRIM(isp), area_code TRIM(area_code), postcode TRIM(postcode);这句命令有几个关键点。CHARACTER SET utf8mb4告诉 MySQL 源文件是 UTF-8 编码如果文件没转码这里要填gbk否则中文会变成问号。FIELDS TERMINATED BY ,指定分隔符是英文逗号有些数据文件用制表符分隔改成\t即可。IGNORE 1 LINES跳过表头如果你的文件本来就没有表头这行删掉。最灵活的是SET部分。这个场景假设源文件里“省份城市”是同一列用横杠分隔比如“广东-深圳”于是用SUBSTRING_INDEX拆成两列。实际文件格式五花八门有的是三列省份、城市、运营商各一列有的是四列直接把变量对应上去就行。原则是源文件每列对应一个变量目标列的赋值写在SET里该拆就拆、该拼就拼。导入过程中先别急着看最终结果主要盯着两件事一是日志里有没有Duplicate entry报错二是导入行数和 wc -l 统计的是否接近。如果报错很多说明去重没做干净不要用IGNORE关键字强行吞掉错误先回头把数据文件清理好再导。3.3 校验导入结果用 SQL 验证完整性导入完成不等于导入正确。我会用三条 SQL 做校验分别检查总量、空值和抽样准确性-- 检查总数是否和源文件一致 SELECT COUNT(*) FROM phone_location; -- 检查关键字段是否有空值 SELECT COUNT(*) FROM phone_location WHERE province OR city OR isp ; -- 抽查几个已知号段验证归属地是否和常识一致 SELECT segment, province, city, isp FROM phone_location WHERE segment IN (1390000, 1880000, 1700000);总行数校验很直接导入完成后MySQL 返回的受影响行数应该和源文件去重后的行数一致。如果少了大概率是编码问题导致整行被跳过如果多了说明文件里有隐藏换行符一行被拆成了两行。空值检查抓的是字段映射错误比如SUBSTRING_INDEX写错方向导致省份和城市颠倒。抽样校验这里有个小技巧选号段时不要只挑常见的 139、138一定要带上虚拟运营商号段开头的 170、171 和新号段开头的 190、199。这些号段恰恰是数据文件最薄弱的地方如果源头数据“非常全”是吹的抽这些一查就露馅。4. 查询接口与索引优化从一条 SQL 到一个能用的服务4.1 封装查询逻辑函数、存储过程还是业务代码里算表建好了接下来是查询。查询逻辑很简单传入 11 位手机号截取前 7 位去表里等值查。但这段逻辑放在哪一层不同项目有不同的习惯。最蠢的做法是在业务代码里LIKE 139%去查——会让 MySQL 无法有效利用索引数据量一大就卡死。我一般会先在 MySQL 里写一个查询函数把“截号段 查询”的逻辑固化在数据库层业务方调用时只传手机号DELIMITER $$ CREATE FUNCTION get_phone_location(p_phone VARCHAR(11)) RETURNS VARCHAR(255) DETERMINISTIC READS SQL DATA BEGIN DECLARE v_segment CHAR(7); DECLARE v_result VARCHAR(255); SET v_segment LEFT(p_phone, 7); SELECT CONCAT(province, , city, , isp) INTO v_result FROM phone_location WHERE segment v_segment LIMIT 1; RETURN IFNULL(v_result, 未知归属地); END$$ DELIMITER ;调用就一行SELECT get_phone_location(13912345678)。这个函数的巧妙之处在于把“取前 7 位”的动作限定在 SQL 内部业务代码永远只传完整手机号不会出现有的地方截 3 位、有的地方截 7 位的不一致。不过要注意函数里用了LEFT(p_phone, 7)如果传入的手机号不是 11 位比如 10 位或者带86前缀返回值会变怪。调用前建议在业务侧统一格式只传纯 11 位数字。也可以顺手在里面加一个条件判断长度不为 11 时直接返回“参数错误”。4.2 索引策略与查询性能为什么等值查询比 LIKE 快这么多号码归属地查询慢十有八九是索引没用上。最容易踩的就是把查询写成WHERE segment LIKE 139%这里 % 通配符放在结尾MySQL 虽然可能走索引但本质上还是范围扫描更常见的是有人把手机号整段存了然后WHERE phone LIKE 139%这等于每次查询要扫全表里所有 139 开头的完整号码代价完全不可控。正确的姿势是“前 7 位等值查询”。我做一个简单的性能对比你就明白了-- 做法一错误示范全表扫描 SELECT * FROM phone_location WHERE segment LIKE 139%; -- 做法二正确示范索引点查 SELECT * FROM phone_location WHERE segment 1390000;后者走的是uk_segment唯一索引B 树定位到一行百万级数据量下耗时在 1 毫秒以内。前者如果只有 139 一个号段可能还会用索引但如果 LIKE 的是%3900这种通配符在开头的写法MySQL 直接放弃索引扫全表数据一多立刻卡住。还有一个很多人忽略的点id自增主键在这个业务里其实有点浪费。因为所有的查询都走segment唯一索引主键id只承担行定位功能。如果这张表只需要“按号段查归属地”这一种访问模式可以干脆把segment设为主键省掉id列表能小一圈。保留id的好处是后续做增量更新时方便记录批次取舍看实际业务。4.3 把查询服务化一个最小的 HTTP 接口封装业务系统一般不会直接让前端连 MySQL通常是后端查完再返回 JSON。这里给一个最小可用的 PHP 封装示例逻辑核心只有三步接收手机号、调用函数、返回结果。?php $phone $_GET[phone] ?? ; if (!preg_match(/^1[3-9]\d{9}$/, $phone)) { http_response_code(400); echo json_encode([error invalid phone]); exit; } $pdo new PDO(mysql:host127.0.0.1;dbnameapp, user, pass, [ PDO::ATTR_ERRMODE PDO::ERRMODE_EXCEPTION ]); $stmt $pdo-prepare(SELECT get_phone_location(:phone) AS location); $stmt-execute([:phone $phone]); $location $stmt-fetchColumn(); echo json_encode([phone $phone, location $location]);正则^1[3-9]\d{9}$用来拦截非 11 位数字的脏请求避免把垃圾参数送进数据库。PDO 预处理固定参数防止注入。这里刻意没有做 Redis 缓存原因是归属地数据本身变化极慢同一号段的查询结果完全一样缓存收益巨大后面第 6 章我会专门讲怎么给这个场景加缓存。有人会把这张表直接跨库 JOIN 到用户表比如SELECT u.*, pl.city FROM users u LEFT JOIN phone_location pl ON pl.segment LEFT(u.phone, 7)。这种做法在数据量不大时问题不大但一旦用户表到了几百万行每次全表 JOIN 都要对 LEFT 函数结果做关联索引完全失效。建议保持“先查用户、再查归属地”的两步查询或者把归属地冗余到用户表的一个字段里。5. 避坑清单数据“非常全”背后的五个真实边界5.1 查询慢到怀疑人生号段字段根本没走索引现象数据导完表里 60 万行但查一个号段要 300 毫秒以上并发一上来直接拖垮数据库。原因表里segment字段没有索引或者查询语句写成了LIKE %139%MySQL 只能全表扫描。还有一个隐蔽原因源文件里号段带了空格或者横杠比如存成139-0000导致 CHAR(7) 字段里存的内容根本对不上等值查询命中不了。解决先跑一遍EXPLAIN SELECT * FROM phone_location WHERE segment 1390000看type是不是const或ref。如果是ALL说明没走索引补上唯一索引如果是字段脏数据问题用UPDATE phone_location SET segment REPLACE(segment, -, )清洗后再查。5.2 导入后中文全是问号源文件编码和 MySQL 字符集打架现象LOAD DATA 执行成功行数也对但 province 列显示???或者一堆乱码。原因源文件是 GBK 编码命令里写了CHARACTER SET gbk但文件实际是 GB2312 或 GB18030部分字符映射失败或者命令里漏写了CHARACTER SET子句MySQL 用了表的默认字符集 utf8mb4 去解析 GBK 字节流自然全乱。解决先file -i phone_data.csv确认编码再在 LOAD DATA 命令里严格对应。如果是 GB18030就用CHARACTER SET gb18030。补救办法是删掉表数据重新导入不要试图用CONVERT函数在 SET 阶段转码转完还是脏的。5.3 新号段查不到归属地数据“全”是相对的不是实时的现象129、198、199 开头的手机号查询结果全是“未知归属地”而 139、138 这些老号段完全正常。原因卖家打包的数据是基于某个时间点的历史快照新号段要么当时还没放号要么没来得及整理进去。这是所有离线号码归属地数据的物理边界与数据质量无关。解决对接运营商公开号段公告定期手工补充新号段记录。具体做法是去三大运营商官网翻最近半年新增的号段整理成“号段-省份-城市-运营商”格式的 CSV按第 3 章的方式增量导入。注意新增记录和旧记录一样segment必须是前 7 位先查重再入库。5.4 携号转网后归属地不准离线数据的天然硬伤现象用户 188 开头的号码现在实际用的是另一家运营商但查询结果还是老运营商的归属信息。原因携号转网只变运营商、不变号码离线号段表记录的是“这个号段最初分配给谁”不是“这个号码现在属于谁”。任何纯离线方案都解决不了这个问题这是业务场景的物理限制不是代码 bug。解决如果业务强依赖准确运营商信息比如充话费、办套餐必须在页面文案里弱化展示或者接运营商的实时查询接口。如果只是展示省份城市影响很小直接接受这个误差即可。别在这上面花太多时间优化离线表做不到。5.5 导入中途报错主键冲突源数据文件有重复号段现象LOAD DATA 执行没多久就报Duplicate entry 1390000 for key uk_segment导入中断前功尽弃。原因源文件里同一个号段出现多次可能是一开始分配给了某省后来调整到另一个城市也可能是卖家打包时合并了多份表交叉部分没去重。解决导入前先用第 3 章的 awk 命令做一次按号段去重保留最后一条。或者干脆在 LOAD DATA 后面加IGNORE关键字让 MySQL 自动跳过重复行。但我不建议直接 IGNORE它会掩盖数据文件本身的质量问题后期查漏补缺时连“哪条被跳过了”都不知道还是先清理再导更靠谱。6. 让数据保鲜增量更新脚本与自我校验的实用技巧离线归属地数据最大的痛点不是导入而是会过期。号段在持续新增运营商归属在变化文件里那份“非常全”的数据过一年就只剩“非常旧”。我自己的习惯是建一个简单的保鲜机制每周跑一次增量检查和一次抽样比对。新号段检测可以做成一个 bash 脚本思路是定义几个“哨兵号码”——比如 199、190、192 开头的测试号每周期用外部查询接口随便一个能查归属地的公开服务就行拉一次结果和本地 MySQL 表对比。如果发现本地查不到或归属地和接口返回不一致就把这个号段标记出来人工确认后补录。手动补录的 SQL 就这么简单INSERT INTO phone_location (segment, province, city, isp) VALUES (1920000, 某省份, 某城市, 某运营商) ON DUPLICATE KEY UPDATE province VALUES(province), city VALUES(city), isp VALUES(isp);ON DUPLICATE KEY UPDATE是增量更新的核心它保证同一个号段只会存在一条记录新值直接覆盖旧值不用先 DELETE 再 INSERT。这个技巧我第一次用的时候觉得很省事后来才发现如果不带这个子句光写 INSERT 就会频繁撞唯一索引报错那才是真的浪费时间。至于缓存层我建议把“号段 → 归属地”的映射放到 Redis 里KEY 直接设计成phone:seg:1390000TTL 设 7 天。这样同一号段的重复查询根本不会打到 MySQL数据库压力小到可以忽略。缓存和 MySQL 的一致性不用担心TTL 过期后自然回源MySQL 里改了数据最多 7 天生效对归属地这种低频变动数据完全够用。这里有个我踩过的坑值得说一句早期我做增量更新时图省事直接 DROP 掉旧表重新导入新数据结果有一次新文件缺了整整一个号段段位线上查归属地直接大面积报“未知”用户投诉才反应过来。后来我养成了一个习惯所有涉及数据覆盖的操作先mysqldump导出旧表备份到本地再动导入出问题随时能回滚。数据这东西后悔药永远是自己提前准备的。这套方案做到最后你会发现它已经不只是一张表的事体检脚本、LOAD DATA 命令、查询函数、Redis 缓存、增量更新机制五块拼起来才是一个完整能用的归属地服务。刚开始可能觉得流程长但每一步都是被真实生产环境逼出来的。希望帮到你。本文还有配套的精品资源点击获取