简介：这份资源是一份基于MySQL的手机号码归属地数据包，面向从事营销、客户服务、数据分析及数据库学习的开发者与运维人员，用于解决按手机号快速查询省份、城市归属地的实际需求。压缩包内共1个文件，为单个sql格式的数据脚本，整体约2.22MB，导入MySQL后即可通过结构化数据表完成号码与归属地的关联查询，并支持分组统计、多表关联等进阶分析。目前已有268人学习下载，数据完整度较高，适合作为练习SQL查询、批量处理与隐私合规管理的实战素材。读者可借此掌握按号码检索归属地、统计各省用户数量等典型操作，同时理解敏感数据的加密存储与访问权限控制思路，对初学者和有一定经验的开发者都具备参考价值。

1. 手机号归属地查询：从一条 SQL 说起

手里有一批用户注册数据，手机号字段是明文，运营要按地域做分层触达，第一反应是调第三方 API。但量一大，QPS 和费用都扛不住，而且很多接口对历史号段覆盖不全。这时候一张本地的手机号归属地表就显出价值了——一次导入，永久查询，没有网络依赖，没有调用次数限制。标题里说的“mysql，非常全”，核心就是这件事：把手机号前七位和运营商、归属地、区号、邮编的映射关系落到 MySQL 里，用一条SELECT换掉一次 HTTP 请求。适合谁？手里有几十万到上千万条手机号需要批量补全归属地、又不想被 API 限流卡脖子的后端和数据分析同学。下面把我自己建表、导数据、写查询、做批量更新的完整路径拆开讲。

2. 号段表怎么建：字段设计和索引选择

2.1 为什么用前七位而不是前三位

手机号归属地的判定精度取决于号段粒度。早期只看前三位（比如 138）就能定位到运营商，但归属地会粗到省份甚至多个省共用。真正能落到城市级的，是前七位——1381234这种粒度。中国移动、联通、电信的号段分配就是按前七位划块的，同一个前七位基本对应同一个地级市。所以表的主键或者唯一索引应该建在prefix（前七位）上，而不是phone全号。全号存进去既浪费空间又没必要，查询时用LEFT(phone, 7)截取即可。常见做法是单独存一列prefix CHAR(7)，查询时先截取再等值匹配，走索引，速度是毫秒级。

2.2 建表语句与字段含义

CREATE TABLE `phone_location` ( `prefix` char(7) NOT NULL COMMENT '号段前七位', `province` varchar(20) NOT NULL DEFAULT '' COMMENT '省份', `city` varchar(30) NOT NULL DEFAULT '' COMMENT '城市', `area_code` varchar(10) NOT NULL DEFAULT '' COMMENT '区号', `post_code` varchar(10) NOT NULL DEFAULT '' COMMENT '邮编', `carrier` varchar(20) NOT NULL DEFAULT '' COMMENT '运营商', PRIMARY KEY (`prefix`), KEY `idx_province` (`province`), KEY `idx_carrier` (`carrier`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='手机号归属地映射表';

prefix做主键，因为它是查询入口，等值匹配最快。province和carrier建普通索引是为了支持“按省统计用户数”“按运营商分组”这类运营查询。area_code和post_code用字符串而不是数字，因为区号有前导零（比如 010），存成整型会丢零。carrier字段存“移动”“联通”“电信”“虚拟运营商”这类值，不要存英文缩写，后续做报表时直接可读。字符集用utf8mb4，防止某些生僻地名乱码。

2.3 数据从哪来：导入前的清洗要点

标题说“淘宝50元买的”，本质就是一份 CSV 或 Excel 号段表。拿到手别急着LOAD DATA，先做三件事。第一，检查前七位是否有重复，重复的只保留一条，否则主键冲突会中断导入。第二，检查是否有空值或NULL，尤其是province和city，空值会导致后续查询结果为空但又不报错，排查起来很费时间。第三，统一运营商名称，比如“中国移动”“移动”“CMCC”统一成“移动”。清洗可以用 Python 脚本快速过一遍：

import pandas as pd df = pd.read_csv('raw_segments.csv', dtype=str) df['prefix'] = df['prefix'].str.strip().str[:7] df = df.drop_duplicates(subset='prefix', keep='first') df['province'] = df['province'].fillna('未知') df['city'] = df['city'].fillna('未知') df['carrier'] = df['carrier'].str.replace('中国', '').str.replace('CMCC', '移动') df.to_csv('clean_segments.csv', index=False)

这段脚本做了去重、空值填充和运营商名称归一化。dtype=str很关键，否则 pandas 会把前导零的区号读成数字。清洗完再导入，能避免 90% 的导入报错。

3. 把 CSV 灌进 MySQL：三种导入方式与速度对比

3.1 LOAD DATA INFILE 的用法和参数

数据量在十万行以上时，LOAD DATA是最快的，比逐条INSERT快一个数量级。前提是 CSV 格式规整，字段顺序和表结构一致。

LOAD DATA LOCAL INFILE '/path/to/clean_segments.csv' INTO TABLE phone_location FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (prefix, province, city, area_code, post_code, carrier);

FIELDS TERMINATED BY ','指定逗号分隔，OPTIONALLY ENCLOSED BY '"'处理字段里带逗号的情况，IGNORE 1 ROWS跳过表头。如果 CSV 是 Windows 换行，LINES TERMINATED BY '\r\n'要改一下，否则最后一行会带\r导致导入失败。LOCAL关键字允许从客户端读文件，如果 MySQL 服务端和客户端不在同一台机器，去掉LOCAL并把文件放到服务端secure_file_priv目录下。

3.2 用 Python 批量 INSERT 的兜底方案

有些环境禁用了LOAD DATA LOCAL，或者 CSV 里有脏数据导致整批失败。这时候用 Python 分批插入，每批 5000 条，配合executemany。

import pymysql import pandas as pd conn = pymysql.connect(host='127.0.0.1', user='root', password='yourpass', database='test', charset='utf8mb4') cursor = conn.cursor() df = pd.read_csv('clean_segments.csv', dtype=str) sql = "INSERT IGNORE INTO phone_location (prefix, province, city, area_code, post_code, carrier) VALUES (%s,%s,%s,%s,%s,%s)" batch_size = 5000 for i in range(0, len(df), batch_size): batch = df.iloc[i:i+batch_size].values.tolist() cursor.executemany(sql, batch) conn.commit() print(f"已插入 {i+len(batch)} 条") cursor.close() conn.close()

INSERT IGNORE遇到主键冲突会跳过而不是报错，适合清洗不彻底的情况。batch_size设 5000 是经验值，太大容易触发max_allowed_packet限制，太小则提交次数多、速度慢。每批commit一次，避免长事务锁表。

3.3 导入后的校验查询

导入完别急着用，先跑三条校验。第一条看总数：SELECT COUNT(*) FROM phone_location;。第二条看省份分布：SELECT province, COUNT(*) FROM phone_location GROUP BY province ORDER BY COUNT(*) DESC LIMIT 10;。第三条随机抽几个已知号段验证：SELECT * FROM phone_location WHERE prefix IN ('1381234','1590000','1770000');。如果总数和 CSV 行数对不上，大概率是主键冲突被IGNORE掉了，回去查重复项。

4. 查询与批量更新：一条 SQL 补全整列归属地

4.1 单条查询的写法

SELECT province, city, carrier FROM phone_location WHERE prefix = LEFT('13812345678', 7);

LEFT函数截取前七位，走主键索引，执行计划是const级别，基本不耗时。如果手机号字段有空格或+86前缀，先用REPLACE和SUBSTRING清洗再截取。

4.2 批量更新用户表的 JOIN 写法

假设用户表users有phone和province两列，要把归属地批量刷进去：

UPDATE users u JOIN phone_location p ON LEFT(u.phone, 7) = p.prefix SET u.province = p.province, u.city = p.city, u.carrier = p.carrier WHERE u.province IS NULL OR u.province = '';

这个JOIN会让users全表扫描一次，但phone_location走主键，整体速度取决于users的行数。百万级用户表大概几十秒到几分钟。注意WHERE条件只更新未补全的行，避免重复更新。如果users表很大，建议分批：每次LIMIT 10000，循环执行，减少锁持有时间。

4.3 查询性能的边界在哪

单条查询和批量 JOIN 的性能瓶颈不在phone_location表，而在users表的扫描和LEFT函数的计算。LEFT(u.phone, 7)是函数运算，无法走users表上phone的索引。如果users表有phone索引，可以改成u.phone LIKE CONCAT(p.prefix, '%')，但这样phone_location又没法走索引了。常见做法是给users加一列prefix，导入时就算好，然后两列直接等值 JOIN，两边都走索引。这是用空间换时间的典型操作。

5. 避坑与排查：号段表落地时最容易翻车的五件事

5.1 导入报错 “Data too long for column”

现象：LOAD DATA或INSERT时报字段超长。原因：CSV 里某些城市名或运营商名超过了varchar(30)或varchar(20)。解决：先查SELECT MAX(CHAR_LENGTH(city)), MAX(CHAR_LENGTH(carrier)) FROM ...，或者直接把字段改成varchar(50)。别小看这个，有些县级市名字很长，加上“自治县”三个字就超了。

5.2 查询结果为空但手机号明明存在

现象：SELECT返回空集。原因：手机号字段带了空格、+86、或者存成了科学计数法。解决：查询前先SELECT phone FROM users WHERE phone LIKE '% %' LIMIT 5;检查脏数据，用TRIM和REPLACE清洗。另外确认LEFT截取的是数字，如果手机号存成13812345678.0，截取前七位还是对的，但JOIN时可能因为类型不一致匹配不上。

5.3 主键冲突导致导入中断

现象：LOAD DATA报Duplicate entry。原因：CSV 里有重复前七位。解决：导入前用GROUP BY prefix HAVING COUNT(*) > 1查重复，或者用INSERT IGNORE跳过。如果重复项的归属地不一致，需要人工判断保留哪条，通常保留更新时间较晚的。

5.4 虚拟运营商号段覆盖不全

现象：170、171、165 等号段查不到归属地。原因：很多低价号段表只覆盖三大运营商，虚拟运营商（移动转售、联通转售）的号段缺失。解决：单独补一份虚拟运营商号段表，或者把carrier字段留空但province和city尽量补全。虚拟运营商的归属地通常跟随基础运营商，但城市级信息可能不准，业务上要接受这个精度损失。

5.5 批量 UPDATE 锁表导致线上告警

现象：执行UPDATE users JOIN phone_location时，线上写入变慢甚至超时。原因：大事务持有行锁和间隙锁，阻塞了其他写入。解决：分批更新，每批LIMIT 5000，批间sleep 0.5秒。或者用pt-online-schema-change这类工具的思路，先建影子表再切换。血泪经验是：任何超过一万行的UPDATE都不要在业务高峰期裸跑。

6. 进阶技巧：把归属地查询做成可复用的存储过程

6.1 封装一个按手机号返回归属地的函数

如果多个业务线都要查归属地，与其到处写JOIN，不如封装一个FUNCTION。

DELIMITER $$ CREATE FUNCTION get_phone_location(p_phone VARCHAR(20)) RETURNS VARCHAR(100) READS SQL DATA BEGIN DECLARE v_result VARCHAR(100); SELECT CONCAT(province, '|', city, '|', carrier) INTO v_result FROM phone_location WHERE prefix = LEFT(p_phone, 7) LIMIT 1; RETURN IFNULL(v_result, '未知|未知|未知'); END$$ DELIMITER ;

调用方式：SELECT get_phone_location('13812345678');返回省份|城市|运营商。READS SQL DATA声明只读，LIMIT 1防止意外多行。这个函数在单条查询场景下很方便，但批量更新时不要用，因为逐行调用函数比JOIN慢很多。

6.2 用定时事件自动更新号段表

号段会随着运营商放号而变化，建议每月更新一次。MySQL 的EVENT可以定时执行导入，但更稳妥的做法是用外部脚本 + 定时任务，因为LOAD DATA需要文件路径权限。如果坚持用EVENT，可以建一个staging表，先把新 CSV 导入staging，再用INSERT ... ON DUPLICATE KEY UPDATE合并到主表。

INSERT INTO phone_location (prefix, province, city, area_code, post_code, carrier) SELECT prefix, province, city, area_code, post_code, carrier FROM phone_location_staging ON DUPLICATE KEY UPDATE province = VALUES(province), city = VALUES(city), carrier = VALUES(carrier);

ON DUPLICATE KEY UPDATE保证新号段插入、旧号段更新，不会丢数据。VALUES()函数引用的是SELECT子句里的值，MySQL 8.0 之后推荐用别名写法，但VALUES()仍然兼容。

6.3 验证数据新鲜度的两个查询

怎么知道手里的号段表是不是过时了？跑两个查询。第一，看最新号段：SELECT MAX(prefix) FROM phone_location;，如果最大值还停留在 199 开头，说明 200 号段之后的新号段没覆盖。第二，抽查已知新号段：SELECT * FROM phone_location WHERE prefix LIKE '192%' LIMIT 5;，192 是广电号段，如果查不到，说明数据需要更新。我一般会在表里加一列update_time，每次导入时打上时间戳，查询时顺便看一眼。

6.4 我踩过的一个坑：字符集不一致导致 JOIN 失效

有次帮同事排查一个“明明有数据却 JOIN 不上”的问题，查了半天发现users表是utf8，phone_location是utf8mb4，JOIN时 MySQL 做隐式转换，索引直接失效，全表扫描导致查询超时。统一字符集之后问题消失。这个坑不常见，但一旦遇到很难往字符集方向想。习惯是建表时显式指定CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci，两边保持一致。希望帮到你。

本文还有配套的精品资源，点击获取