简介这份资源是一份基于MySQL的手机号码归属地数据库面向需要处理用户地域信息的开发者、数据分析人员及数据库学习者可用于营销分群、客户服务、数据统计等场景。压缩包内共1个文件为phone_msg.sql格式的SQL脚本整体约2.22MB导入后即可获得手机号与省份、城市字段的关联数据表方便直接编写查询语句定位号码归属地也能结合GROUP BY、JOIN等操作完成批量统计与多表分析。目前已有268人学习下载说明该数据集在实际工作中具有一定参考价值。通过它读者可以练习MySQL查询、数据聚合与结果验证同时接触敏感信息处理时的合规与安全思路适合作为数据库入门到进阶的实战素材。1. 手机号归属地查询一份 MySQL 数据包能省掉多少接口调用做用户注册风控的时候经常遇到一个需求拿到手机号先判断归属地再决定要不要发短信、走不走人工审核。早期我图省事直接调第三方 API结果 QPS 一上来就被限流账单也跟着涨。后来换成本地 MySQL 查表单机几万 QPS 毫无压力延迟从 80ms 降到 2ms 以内。这份资源就是干这个的——一个手机号段归属地数据库导入 MySQL 后直接 SQL 查询覆盖号段非常全连 170、171、162 这些虚拟运营商号段都有。适合做风控、用户画像、短信路由的开发者也适合不想被第三方接口卡脖子的团队。下面从表结构设计讲到批量查询优化把踩过的坑一并说清。2. 号段表怎么建字段类型和索引选错查询慢十倍2.1 先搞清楚数据长什么样这份数据包解压后通常是一个 SQL 文件或 CSV核心字段就几个号段前缀、省份、城市、运营商、区号、邮编。号段前缀一般是 7 位比如1381234代表 138 号段下 1234 这个子段。有些版本会拆成号段前3位 号段后4位两列存储查询时用BETWEEN匹配这种设计对索引不友好我一般会合并成一列。先看导入前的原始结构用head扫一眼# 查看 CSV 前 5 行确认分隔符和字段顺序 head -5 phone_segment.csv # 输出示例 # 1381234,北京,北京,移动,010,100000 # 1381235,北京,北京,移动,010,100000如果拿到的是 SQL 文件直接看CREATE TABLE语句grep -A 20 CREATE TABLE phone_db.sql | head -30这一步别跳过。我见过有人拿到文件直接source导入结果字段类型全是varchar(255)一张千万级表占了几百 MB查询还得全表扫。常见做法是先把结构看清楚再决定要不要改字段类型。2.2 建表语句与字段类型选择号段前缀用char(7)还是int我选char(7)。原因有两个一是号段可能以 0 开头虽然手机号不会但数据包里有些固话区号会int会丢前导零二是char(7)定长索引效率比varchar高。省份、城市、运营商用varchar(20)足够别用text否则索引体积翻倍。CREATE TABLE phone_attribution ( id int(11) NOT NULL AUTO_INCREMENT, prefix char(7) NOT NULL COMMENT 号段前缀7位, province varchar(20) NOT NULL DEFAULT COMMENT 省份, city varchar(20) NOT NULL DEFAULT COMMENT 城市, isp varchar(20) NOT NULL DEFAULT COMMENT 运营商, area_code varchar(10) NOT NULL DEFAULT COMMENT 区号, post_code varchar(10) NOT NULL DEFAULT COMMENT 邮编, PRIMARY KEY (id), UNIQUE KEY uk_prefix (prefix), KEY idx_isp (isp) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT手机号归属地号段表;逻辑说明prefix加唯一索引保证一个号段只有一条记录避免数据包重复导入导致查询结果翻倍。isp加普通索引因为按运营商筛选是高频操作比如只查移动号段做短信通道分流。area_code和post_code用varchar而不是int因为区号可能带0前缀邮编也可能有字母极少见但别赌。参数说明utf8mb4是为了兼容城市名里的生僻字有些数据包城市字段带少数民族文字utf8会报错。ENGINEInnoDB不用多说MyISAM 不支持事务导入中断后表就废了。2.3 导入数据的两种方式与速度对比数据量通常在 30 万到 50 万行之间看号段细分程度。导入方式有两种LOAD DATA INFILE和INSERT批量插入。前者快一个数量级但需要secure_file_priv权限。-- 方式一LOAD DATA适合 CSV 文件 LOAD DATA LOCAL INFILE /tmp/phone_segment.csv INTO TABLE phone_attribution FIELDS TERMINATED BY , LINES TERMINATED BY \n (prefix, province, city, isp, area_code, post_code);-- 方式二批量 INSERT适合 SQL 文件里已经是 INSERT 语句 -- 先关闭自动提交每 1000 条提交一次 SET autocommit 0; -- 执行数据包里的 INSERT 语句 SOURCE /tmp/phone_db.sql; COMMIT;逻辑说明LOAD DATA直接读文件跳过 SQL 解析层50 万行大概 3 到 5 秒。INSERT批量插入如果没关autocommit每行都写一次 redo log50 万行能跑十几分钟。我一般先用LOAD DATA如果报权限错误再退回INSERT。参数说明FIELDS TERMINATED BY ,要和 CSV 实际分隔符一致有些数据包用\t或|导入前用head确认。LINES TERMINATED BY \n在 Windows 换行符下要改成\r\n否则最后一行会带\r查询时匹配不上。提示导入前先SELECT COUNT(*)确认行数导入后再查一次两边对不上就是有行被截断了常见原因是字段长度不够或特殊字符转义问题。3. 查询怎么写从 LIKE 到前缀索引的优化路径3.1 手机号截取与匹配逻辑拿到一个 11 位手机号13812345678要匹配号段表里的1381234。思路是取前 7 位去表里精确查。别用LIKE 1381234%那样索引会失效变成全表扫。-- 正确写法截取前 7 位精确匹配 SELECT province, city, isp FROM phone_attribution WHERE prefix LEFT(13812345678, 7);-- 错误写法LIKE 前缀匹配索引失效 SELECT province, city, isp FROM phone_attribution WHERE prefix LIKE CONCAT(LEFT(13812345678, 7), %);逻辑说明LEFT函数在 MySQL 里是确定性的配合prefix上的唯一索引走的是const或eq_ref访问类型单行查询微秒级。LIKE虽然也是前缀匹配但优化器有时会误判尤其当prefix列有函数包裹时直接放弃索引。参数说明LEFT(str, 7)第二个参数是号段长度如果数据包里号段是 8 位这里要改成 8。导入前确认清楚否则查不到结果。3.2 批量查询的两种写法与性能差异单个查询快没用实际场景是批量。比如一次导入 1000 个用户手机号要批量补全归属地。两种写法循环单查和IN批量查。# 方式一循环单查1000 次数据库往返 import pymysql conn pymysql.connect(hostlocalhost, userroot, password, dbphone_db) cursor conn.cursor() phones [13812345678, 13987654321, ...] # 1000 个 results {} for phone in phones: prefix phone[:7] cursor.execute(SELECT province, city, isp FROM phone_attribution WHERE prefix %s, (prefix,)) row cursor.fetchone() if row: results[phone] row# 方式二IN 批量查一次往返 prefixes list(set([p[:7] for p in phones])) placeholders ,.join([%s] * len(prefixes)) sql fSELECT prefix, province, city, isp FROM phone_attribution WHERE prefix IN ({placeholders}) cursor.execute(sql, prefixes) rows cursor.fetchall() prefix_map {row[0]: row[1:] for row in rows} results {p: prefix_map.get(p[:7]) for p in phones}逻辑说明方式一 1000 次网络往返即使每次 0.1ms累计也要 100ms 以上加上 Python 循环开销实际 300ms 起步。方式二一次往返IN列表去重后可能只有 800 个前缀MySQL 用索引扫描总耗时 5ms 以内。差距是几十倍。参数说明IN列表别超过 1000 个否则 SQL 解析时间会超过查询时间。超过 1000 就分批每批 500 个。另外placeholders用%s占位别用字符串拼接防止 SQL 注入。3.3 覆盖索引减少回表如果查询只需要省份和城市不需要运营商可以建联合索引(prefix, province, city)这样查询直接走索引不用回表读数据行。ALTER TABLE phone_attribution ADD INDEX idx_prefix_province_city (prefix, province, city); -- 查询只取索引列走覆盖索引 SELECT province, city FROM phone_attribution WHERE prefix LEFT(13812345678, 7);逻辑说明EXPLAIN看Extra列出现Using index就是覆盖索引没出现就是回表了。回表意味着每行都要去主键索引里捞数据批量查询时差距明显。参数说明联合索引字段顺序很重要prefix必须放最左因为查询条件只用了prefix。如果把province放最左这个索引就废了。4. 避坑与排查导入和查询中最容易翻车的五个点4.1 导入后中文乱码现象查询结果里省份显示??或å京。原因数据包编码是gbk建表用了utf8mb4导入时没指定字符集。解决导入前加SET NAMES gbk;或者用iconv转码后再导入。# 转码后再导入 iconv -f gbk -t utf-8 phone_segment.csv phone_segment_utf8.csv4.2 号段匹配不到现象明明手机号是 138 开头查出来是空。原因数据包里号段是 8 位代码里截了 7 位。解决先SELECT MAX(LENGTH(prefix))确认号段长度再调整截取位数。SELECT LENGTH(prefix) AS len, COUNT(*) FROM phone_attribution GROUP BY len;4.3 虚拟运营商号段缺失现象170、171、162 号段查不到归属地。原因数据包版本较老虚拟运营商号段是后来才放号的。解决手动补几条或者找更新版数据包。常见做法是建一张isp_mapping表把虚拟运营商统一映射到基础运营商。4.4 批量 IN 查询超时现象IN列表 5000 个前缀查询跑了 3 秒还没返回。原因SQL 解析器处理超长IN列表时开销大而且可能放弃索引走全表扫。解决分批查询每批 500 个用UNION ALL合并结果。-- 分批查询示例 (SELECT prefix, province, city FROM phone_attribution WHERE prefix IN (...500个...)) UNION ALL (SELECT prefix, province, city FROM phone_attribution WHERE prefix IN (...500个...));4.5 主从同步延迟导致查不到新号段现象刚导入的新号段从库查不到。原因主从复制有延迟批量导入时延迟可能到分钟级。解决导入后SHOW SLAVE STATUS确认Seconds_Behind_Master为 0 再切流量或者强制走主库查询。5. 进阶用法把归属地查询做成缓存服务5.1 用 Redis 缓存热点号段MySQL 查询再快也有网络开销如果 QPS 上万单机 MySQL 也会吃力。我一般会在前面加一层 Redis缓存号段到归属地的映射。号段数据是静态的不会变缓存可以设永不过期导入新数据时手动刷新。import redis import pymysql r redis.Redis(hostlocalhost, port6379, db0) def get_attribution(phone): prefix phone[:7] # 先查 Redis cached r.hgetall(fphone:{prefix}) if cached: return cached # Redis 没有查 MySQL conn pymysql.connect(hostlocalhost, userroot, password, dbphone_db) cursor conn.cursor() cursor.execute(SELECT province, city, isp FROM phone_attribution WHERE prefix %s, (prefix,)) row cursor.fetchone() if row: data {province: row[0], city: row[1], isp: row[2]} r.hmset(fphone:{prefix}, data) r.expire(fphone:{prefix}, 86400 * 30) # 30 天过期兜底 return data return None逻辑说明Redis 用 Hash 结构存一个号段一个 key字段是省份、城市、运营商。hgetall一次拿全比get反序列化 JSON 快。过期时间设 30 天是兜底防止数据更新后缓存一直不刷新。参数说明expire时间根据数据更新频率定如果数据包一年更新一次设 30 天足够。Redis 内存占用估算50 万个号段每个 Hash 大概 100 字节总共 50MB随便一台机器都扛得住。5.2 验证数据完整性的两个 SQL导入完成后别急着上线先跑两个校验 SQL。第一个查号段总数和运营商分布SELECT isp, COUNT(*) AS cnt FROM phone_attribution GROUP BY isp ORDER BY cnt DESC;第二个查有没有重复号段SELECT prefix, COUNT(*) AS cnt FROM phone_attribution GROUP BY prefix HAVING cnt 1;如果第二个查询有结果说明数据包有重复需要去重。去重 SQLDELETE t1 FROM phone_attribution t1 INNER JOIN phone_attribution t2 WHERE t1.id t2.id AND t1.prefix t2.prefix;逻辑说明t1.id t2.id保留 id 小的那条删掉重复的。执行前先SELECT确认影响行数别直接DELETE。5.3 一个我踩过的坑有次导入数据后没校验直接上线结果用户反馈部分号码归属地显示成隔壁省。排查半天发现是数据包里有一批号段前缀重复但省份不同查询时LIMIT 1随机返回了一条。从那以后我每次导入完都强制跑一遍重复检查确认HAVING cnt 1返回空结果才继续。这个习惯帮我省掉了至少三次线上事故。希望帮到你。本文还有配套的精品资源点击获取