简介这是一份面向数据库初学者与数据分析人员的MySQL手机号归属地查询数据集适合用于练习SQL查询、批量统计与隐私合规处理。压缩包内共1个文件为phone_msg.sql格式的SQL脚本整体约2.22MB导入MySQL后即可获得手机号码与省份、城市字段的关联表支持单条号码归属地查询、按省份分组统计用户数量等操作也可结合JOIN与聚合函数做进一步分析。资源标签为mysql内容侧重结构化数据表与查询实践能帮助读者熟悉SELECT、WHERE、GROUP BY等常用语句的写法与执行效果。目前已有267人学习下载适合需要真实数据练手、准备数据分析或客户服务类项目的开发者参考同时提醒使用者注意个人信息保护在合法合规前提下加密存储并限制访问权限。1. 手机号归属地查询这件事为什么最后都落到一张 MySQL 表上做用户系统、风控、运营后台的人几乎都会碰到一个需求拿到一串 11 位手机号想知道它属于哪个省、哪个市、哪家运营商。最省事的做法是调第三方接口但接口有 QPS 限制、有费用、断网就废而且很多场景下你只是要一个「大概归属地」用于展示或粗筛根本不值得为每次查询付一次调用费。于是绝大多数团队最后都会走同一条路把一份手机号归属地数据落到本地 MySQL用号段前缀做匹配查询一次导入、长期使用。这个标题里的「非常全」和「淘宝 50 元买的」说的其实就是这类号段库的典型来源——一份覆盖三大运营商、包含号段、省份、城市、区号、邮编、卡类型的表。它不是什么高深技术但真正落地时会卡在几个地方数据怎么建表、号段怎么匹配前 3 位还是前 7 位、导入时编码和换行怎么处理、查询怎么走索引才不慢。这篇就按「建表 → 导数据 → 写查询 → 排坑 → 进阶」的顺序把这条链路讲透新手能照着跑通熟手能对照自己的实现看边界。2. 号段表怎么设计字段、主键和匹配粒度的取舍2.1 先想清楚匹配粒度再决定表结构手机号归属地查询的核心是「前缀匹配」。中国大陆手机号是 11 位前 3 位是号段如 138、159、199但同一个前 3 位号段会被分配给不同省份甚至不同运营商所以只靠前 3 位是不够的。真正能唯一定位到「省 市 运营商」的通常是前 7 位号段 HLR 段。这就是为什么你买到的数据里很多表的 key 是 7 位数字而不是 3 位。常见做法是建一张以 7 位前缀为主键的表字段大致如下字段名类型说明prefixchar(7)号段前 7 位主键provincevarchar(20)省份如「广东」cityvarchar(30)城市如「深圳」operatorvarchar(20)运营商如「移动」「联通」「电信」area_codevarchar(10)区号如 0755zip_codevarchar(10)邮编card_typevarchar(20)卡类型如「普通卡」「虚拟运营商」用 char(7) 而不是 int是因为号段可能以 0 开头虚拟运营商号段里存在int 会丢前导零。主键用 prefix 而不是自增 id是因为查询永远是等值匹配前缀主键即索引省掉一次回表。2.2 建表语句与字符集选择CREATE TABLE phone_attribution ( prefix char(7) NOT NULL COMMENT 号段前7位, province varchar(20) NOT NULL DEFAULT COMMENT 省份, city varchar(30) NOT NULL DEFAULT COMMENT 城市, operator varchar(20) NOT NULL DEFAULT COMMENT 运营商, area_code varchar(10) NOT NULL DEFAULT COMMENT 区号, zip_code varchar(10) NOT NULL DEFAULT COMMENT 邮编, card_type varchar(20) NOT NULL DEFAULT COMMENT 卡类型, PRIMARY KEY (prefix) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT手机号归属地号段表;这里几个参数值得说清楚。字符集用 utf8mb4是因为城市名里可能出现生僻字utf8 三字节版本会插入失败。排序规则用 general_ci 而不是 unicode_ci是因为这张表只做等值查询不涉及排序和比较general_ci 更快、占用更小。存储引擎用 InnoDB虽然这张表读多写少、MyISAM 也能用但 InnoDB 支持事务和崩溃恢复导入中途失败不会留下半张表。提示如果你的数据里号段是 3 位和 7 位混在一起不要塞进同一张表用同一个主键会冲突。正确做法是拆两张表或者统一补全到 7 位不足的用 0 补齐查询时也按 7 位查。2.3 数据从哪来、怎么清洗淘宝买的这类数据常见格式是 CSV 或 Excel字段顺序各家不一样有的还带 BOM 头。导入前一定要先看一眼编码和分隔符。用 Python 做一次清洗是最稳的import csv # 读取原始 CSV处理 BOM 和字段顺序 with open(raw_phone.csv, r, encodingutf-8-sig) as f: reader csv.reader(f) header next(reader) # 跳过表头 rows [] for line in reader: if len(line) 4: continue # 跳过残缺行 prefix line[0].strip().zfill(7) # 不足7位补0 if not prefix.isdigit(): continue # 非数字号段直接丢弃 rows.append((prefix, line[1].strip(), line[2].strip(), line[3].strip())) # 写出清洗后的数据供 LOAD DATA 使用 with open(clean_phone.csv, w, encodingutf-8, newline) as f: writer csv.writer(f) writer.writerows(rows) print(f清洗完成有效记录 {len(rows)} 条)这段代码做了三件事用 utf-8-sig 吃掉 BOM避免第一列字段名带不可见字符用 zfill(7) 把短号段补齐保证主键长度一致过滤掉非数字和残缺行避免导入时报错中断。参数上zfill 的 7 要和建表的 char(7) 对齐改了一边另一边也要改。3. 把数据灌进 MySQLLOAD DATA 与批量插入的取舍3.1 用 LOAD DATA LOCAL INFILE 快速导入数据量通常在几万到几十万行用 INSERT 一条条插会非常慢。MySQL 自带的 LOAD DATA 是最快的方式LOAD DATA LOCAL INFILE /path/to/clean_phone.csv INTO TABLE phone_attribution FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n (prefix, province, city, operator);几个关键参数FIELDS TERMINATED BY 要和 CSV 实际分隔符一致如果是制表符就写 \tENCLOSED BY 处理字段里带逗号的情况LINES TERMINATED BY \n 在 Windows 导出的文件里可能是 \r\n写错会导致最后一行带 \r 插进去。如果导入后发现有字段末尾多了乱码八成就是换行符没对上。注意LOAD DATA LOCAL INFILE 需要服务端开启 local_infile。用SHOW VARIABLES LIKE local_infile;查看如果是 OFF用SET GLOBAL local_infile 1;打开。生产环境开这个参数要评估安全影响导入完可以关掉。3.2 导入后必须做的两件事校验行数和抽样导入不是插完就完事。先对一下行数SELECT COUNT(*) FROM phone_attribution;再抽样看几条确认字段没有错位SELECT * FROM phone_attribution WHERE prefix IN (1380013, 1591234, 1990000);如果行数比源文件少常见原因是主键冲突重复号段被跳过或某行字段数不对被截断。这时候去看 MySQL 的 warningSHOW WARNINGS;它会告诉你哪一行、哪个字段出了问题。这一步很多人跳过结果线上查询时才发现某些号段查不到回头再查就很痛苦。3.3 批量插入的备选方案如果环境不允许用 LOAD DATA退而求其次用批量 INSERTimport pymysql conn pymysql.connect(host127.0.0.1, userroot, passwordxxx, databasetest, charsetutf8mb4) cursor conn.cursor() batch [] with open(clean_phone.csv, r, encodingutf-8) as f: for line in f: parts line.strip().split(,) if len(parts) 4: continue batch.append(tuple(parts[:4])) if len(batch) 1000: # 每1000条提交一次 cursor.executemany( INSERT IGNORE INTO phone_attribution (prefix, province, city, operator) VALUES (%s,%s,%s,%s), batch) conn.commit() batch [] if batch: cursor.executemany( INSERT IGNORE INTO phone_attribution (prefix, province, city, operator) VALUES (%s,%s,%s,%s), batch) conn.commit() conn.close()用 INSERT IGNORE 而不是普通 INSERT是为了遇到重复号段时跳过而不是报错中断。每 1000 条提交一次是吞吐和内存的折中太小会频繁刷盘太大失败时回滚代价高。这个方案比 LOAD DATA 慢一个数量级但胜在不依赖服务端参数适合云数据库这类不给开 local_infile 的环境。4. 查询怎么写才不慢前缀匹配、索引和常见误用4.1 用 LEFT 取前 7 位做等值查询表建好了查询本身很简单关键是别写成全表扫描SELECT province, city, operator FROM phone_attribution WHERE prefix LEFT(13812345678, 7);LEFT 取前 7 位和主键做等值匹配走的是主键索引一次定位。不要写成WHERE 13812345678 LIKE CONCAT(prefix, %)这种写法虽然逻辑上对但会让索引失效变成逐行比较几万行还能忍几十万行就明显卡了。4.2 在应用层做前缀截取而不是在 SQL 里做函数运算更稳的做法是在代码里截好再传参def get_attribution(phone: str): if len(phone) ! 11 or not phone.isdigit(): return None prefix phone[:7] cursor.execute( SELECT province, city, operator FROM phone_attribution WHERE prefix %s, (prefix,)) return cursor.fetchone()这样做的好处是 SQL 里没有函数执行计划稳定走索引而且参数化查询能防注入。参数说明phone[:7] 取前 7 位和表里的 prefix 长度严格对应如果哪天数据源改成 8 位前缀这里和建表都要同步改否则查不到。4.3 用 EXPLAIN 确认走的是主键写完查询用 EXPLAIN 看一眼EXPLAIN SELECT province, city, operator FROM phone_attribution WHERE prefix 1381234;关注 type 列应该是 const 或 eq_refkey 列应该是 PRIMARY。如果 type 是 ALL说明在扫全表八成是前缀长度不对或者字段类型不匹配比如拿 int 去比 char。这一步是排查慢查询的第一现场别等线上报警了才想起来看。5. 避坑与排查导入和查询里最容易翻车的 5 个点5.1 导入后中文变问号现象查出来的 province 和 city 全是???。原因客户端连接字符集不是 utf8mb4或者 CSV 文件本身是 GBK 编码。解决连接串里显式指定 charsetutf8mb4导入前用file -i clean_phone.csv确认文件编码GBK 的先转成 UTF-8 再导。5.2 号段查不到但数据里明明有现象某个号段在表里能 SELECT 到但用 LEFT 截取后查不到。原因表里存的是 7 位但数据源里这个号段只有 3 位导入时没补齐或者补齐时用了空格而不是 0。解决导入前统一 zfill(7)导入后用SELECT * FROM phone_attribution WHERE CHAR_LENGTH(prefix) ! 7;检查有没有长度不对的行。5.3 LOAD DATA 报错 ERROR 1148现象执行 LOAD DATA LOCAL INFILE 时报ERROR 1148 (42000): The used command is not allowed with this MySQL version。原因服务端没开 local_infile或者客户端连接时没带 local_infile 参数。解决服务端SET GLOBAL local_infile 1;客户端连接时加--local-infile1Python 的 pymysql 要在 connect 里传local_infileTrue。5.4 查询偶尔慢但 EXPLAIN 又是走索引现象大部分查询很快偶尔有几条特别慢。原因号段表被其他大查询挤占了 buffer pool或者表长期没更新统计信息优化器选错计划。解决ANALYZE TABLE phone_attribution;刷新统计信息如果这张表很小几十万行以内可以考虑SELECT ... INTO OUTFILE导成内存缓存应用启动时加载到本地字典彻底绕开数据库。5.5 虚拟运营商号段归属地不准现象170、171、162 这类号段查出来的城市和实际不符。原因虚拟运营商号段是租用三大运营商网络的归属地数据本身就有多种口径有的按发卡地、有的按网络归属。解决在业务层对虚拟运营商号段做特殊标记展示时写「虚拟运营商」而不是具体城市避免用户投诉。这不是数据错是口径问题提前和产品对齐。6. 进阶把号段表变成内存字典以及数据更新的自动化6.1 小表常驻内存查询降到微秒级号段表通常不超过 50 万行完全放得进内存。应用启动时一次性加载成 dict查询就是一次哈希查找import pymysql def load_attribution(): conn pymysql.connect(host127.0.0.1, userroot, passwordxxx, databasetest, charsetutf8mb4) cursor conn.cursor() cursor.execute(SELECT prefix, province, city, operator FROM phone_attribution) table {row[0]: (row[1], row[2], row[3]) for row in cursor.fetchall()} conn.close() return table ATTRIBUTION load_attribution() def query(phone: str): return ATTRIBUTION.get(phone[:7])这样做的代价是内存占用50 万行大约几十 MB对现代服务可以忽略。收益是查询从毫秒级降到微秒级而且完全不占数据库连接。适合号段表更新频率低几个月一次的场景。如果数据更新频繁可以加一个定时任务每天凌晨重新加载一次。6.2 用版本号管理数据更新避免「不知道线上是哪版」号段数据是会变的新号段放号、老号段回收一年总得更新几次。血泪经验是一定要在表里加一个版本字段或者单独建一张 meta 表记录导入时间和数据来源。否则线上出问题时你连「现在跑的是哪一版数据」都说不清排查全靠猜。CREATE TABLE phone_attribution_meta ( id int NOT NULL AUTO_INCREMENT, version varchar(20) NOT NULL, imported_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, row_count int NOT NULL DEFAULT 0, PRIMARY KEY (id) );每次导入完插一条记录查询出问题时先看这张表确认版本和行数。这个习惯花不了几分钟但能省掉大量「到底哪版数据有问题」的扯皮。6.3 一个验证数据质量的小技巧拿到一份新数据别急着全量替换。先抽样 100 个已知号码用新旧两份数据分别查对比差异。差异超过 5% 就要警惕可能是数据源口径变了也可能是清洗脚本出了问题。我一般会写一个对比脚本把差异行输出成 CSV人工扫一眼再决定要不要上线。这个习惯帮我拦下过好几次「新数据反而更旧」的翻车。数据这东西导入只是开始能说清楚它从哪来、什么时候更新的、和上一版差在哪才算真正落地。希望帮到你。本文还有配套的精品资源点击获取