简介这份MySQL手机号归属地数据包面向数据库学习者、数据分析初学者以及需要做号码地域映射与离线测试的开发者主要用于根据手机号快速查询对应省份、城市并支持按地域做批量统计也适合搭建演示环境或补充报表地域维度。压缩包约2.22MB内含1个SQL文件导入MySQL后即可建表检索省去自行收集整理基础映射数据的环节结构简单便于直接验证查询逻辑。已有270人学习下载可作为课程练习、练手项目或数据分析样例的参考素材。数据以手机号、省份、城市等字段组织读者可借它熟悉SELECT筛选、WHERE条件、GROUP BY聚合及JOIN关联等写法也能用于营销分析、客户服务等场景的地域分布验证实际使用时应关注个人信息保护与合规边界。1. 手机号归属地查询一张 MySQL 号段表就能顶掉一次接口调用一个做后台的同学上周拉我改注册页产品要求用户在填完手机号、光标离开输入框的瞬间就显示出归属地。他第一反应是去接第三方接口问了两家都是按次计费、有并发上限、超时还得自己重试为一个辅助字段挂上外部依赖实在不划算。我让他先别加钱加依赖盯着那 11 位数字看三秒前 7 位本来就是运营商放号时按号段切好的号段落到哪个地区是静态的、几乎不变的数据根本不需要每次实时算一遍。最后落地的方案就是一张 MySQL 号段表几十万行一次点查走唯一索引耗时不到一毫秒。这份号段表是当年花钱买回来的覆盖了常规号段、虚拟运营商号段和一部分物联网卡号段清洗一遍就能直接导入。它适合谁做后台管理系统、CRM、注册风控、订单归属统计的后端开发尤其是那些不想为了一个展示字段被第三方服务绑定和计费的人。2. 数据落地号段表结构设计与全量导入拿到的原始数据大概率是一份纯文本或者 CSV一堆行每行是「号段 地区 运营商」分隔符可能是逗号、竖线或者制表符编码可能是 UTF-8 也可能是 GBK中间还夹着空行和表头。真正花时间的不是建表而是把这份脏数据规整成能直接查的东西。这一章先把表结构钉死再走一遍导入。2.1 一张表存什么从 11 位号码里拆出的 6 个字段先明确一件事这张表的主键不是手机号而是号段。11 位手机号里前 3 位是网络识别号第 4 到第 7 位是号段分配标识最后 4 位是用户号。归属地实际绑定在号段这一层所以表里存的是号段到归属地的映射手机号进来的时候截前 7 位去比。字段设计我一般按下面这个来字段名类型是否必填说明idINT UNSIGNED AUTO_INCREMENT是自增主键InnoDB 聚簇索引顺序写入prefixCHAR(8)是号段前缀以 7 位为主兼容 8 位的细分号段provinceVARCHAR(32)是归属地省份无数据时存空串cityVARCHAR(32)是归属地城市直辖市与省份同名ispVARCHAR(16)是运营商归属area_codeCHAR(4)否长途区号非必填做外呼场景才用得上post_codeCHAR(6)否邮政编码很多数据集里缺失允许空这里有两个容易吵起来的点。第一个是prefix为什么用CHAR(8)而不是VARCHAR号段长度是固定的 7 或 8 位定长类型不会在行里额外存长度前缀索引键更紧凑扫描时也少一次长度解析。代价是CHAR存不满 8 位时会在右侧补空格取值时 MySQL 会自动去掉尾部空格等值比较也忽略尾部空格所以 7 位号段写进去、读出来仍然是干净的 7 位LIKE 1380000%也照样命中。第二个是prefix的字符集。它只可能是纯数字没必要跟着业务表一起用utf8mb4。单独把它声明成ascii唯一索引的键长度就是实打实的 8 字节索引更小、比较更快一百万行的表上这个差异是能感觉到的。提示原始数据里如果出现「1380000 」这种带尾部空格的脏值导入前一定要TRIM否则CHAR(8)会把它当成 8 位的号段和真正的 8 位号段撞车。2.2 建表 SQL字符集、引擎与字段类型的选择建表语句我通常写成下面这样一次到位后面导入脚本直接跑CREATE TABLE phone_location ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 自增主键, prefix CHAR(8) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL COMMENT 号段前缀7 位为主兼容 8 位, province VARCHAR(32) NOT NULL DEFAULT COMMENT 归属地省份, city VARCHAR(32) NOT NULL DEFAULT COMMENT 归属地城市, isp VARCHAR(16) NOT NULL DEFAULT COMMENT 运营商, area_code CHAR(4) NOT NULL DEFAULT COMMENT 长途区号, post_code CHAR(6) NOT NULL DEFAULT COMMENT 邮政编码, PRIMARY KEY (id), UNIQUE KEY uk_prefix (prefix) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT手机号段归属地表;逐项说一下为什么这么定引擎用 InnoDB不是因为要事务而是它支持崩溃恢复和行级锁导入中途被 kill 掉之后表不会变成一坨需要REPAIR的东西补救起来省心。主键用自增INT UNSIGNED插入时顺序写页尾不会引起聚簇索引的页分裂。有人图省事拿prefix当主键理论上也成立但号段是字符串聚簇索引键会跟着二级索引一起变大没必要。uk_prefix必须是唯一索引它的作用不只是加速查询更重要的是挡住重复导入。第二次导入同一份文件时唯一约束会直接报冲突或者被ON DUPLICATE KEY吃掉表里的行数不会翻倍。表字符集给utf8mb4是因为省份城市里可能出现生僻字utf8三字节那个会截断甚至报错。utf8mb4_general_ci对这类表足够不需要上unicode_ci比较规则更复杂反而慢。province/city/isp都给了DEFAULT 而不是允许NULL。这在排查时很关键——NULL会把COUNT、SUM、比较全搅乱空串永远是空串行为可预测。2.3 批量导入分片 INSERT 与 LOAD DATA 的取舍数据集到手后第一步永远是先看长什么样。用编辑器打开确认分隔符、有没有表头、编码是 UTF-8 还是 GBK、有没有混进全角字符。这一步别省后面所有报错都从这里来。导入两条路。LOAD DATA LOCAL INFILE最快几十万行几秒钟搞定但它依赖服务端和客户端都打开local_infile很多托管数据库默认关着普通账号也没权限改所以它能用就用、用不上就退回批量 INSERT。批量 INSERT 的通用写法如下import csv import pymysql BATCH_SIZE 2000 # 单次 executemany 的行数太大容易撞 max_allowed_packet def iter_rows(path): 逐行产出 (prefix, province, city, isp)脏行直接丢掉。 with open(path, newline, encodingutf-8) as f: reader csv.reader(f) for row in reader: if not row or not row[0].strip(): continue prefix row[0].strip().replace(\ufeff, )[:8] # 只接受纯数字号段表头和分隔线在这一步被过滤掉 if not prefix.isdigit(): continue yield ( prefix, row[1].strip() if len(row) 1 else , row[2].strip() if len(row) 2 else , row[3].strip() if len(row) 3 else , ) def load(path, conn): sql (INSERT INTO phone_location(prefix, province, city, isp) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE provinceVALUES(province), cityVALUES(city), ispVALUES(isp)) batch, total [], 0 with conn.cursor() as cur: for item in iter_rows(path): batch.append(item) if len(batch) BATCH_SIZE: cur.executemany(sql, batch) conn.commit() total len(batch) batch.clear() if batch: cur.executemany(sql, batch) conn.commit() total len(batch) return total这段逻辑里有几个决定成败的细节prefix.replace(\ufeff, )处理的是 UTF-8 BOMWindows 下导出的 CSV 头一行经常带这个不可见字符不清掉的话第一个号段永远导不进去而且报错信息完全看不出原因。prefix.isdigit()顺便把表头行、空行、注释行全过滤了比预先写一堆正则判断干净。[:8]截断是为了防止某些数据集在号段后面多跟了空格或制表符。ON DUPLICATE KEY UPDATE是我强烈建议保留的。它把「重复导入」从一次报错变成一次幂等覆盖同一个号段第二次出现时更新地区字段而不是插入新行。这样做增量更新号段的时候可以直接拿全量文件覆盖一遍不用先算差集。BATCH_SIZE 2000是个经验值。太小则提交次数太多每个事务都要写 redo 和刷盘太大则拼出来的 SQL 字符串会撞上max_allowed_packet默认 4MB一条 2000 行四列的 INSERT 大概几百 KB留足了余量。如果local_infile是开着的可以直接用服务端导入速度比上面快一个数量级LOAD DATA LOCAL INFILE /tmp/phone_segment.csv INTO TABLE phone_location CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n (prefix, province, city, isp) SET prefix TRIM(LEFT(prefix, 8)), province TRIM(province), city TRIM(city), isp TRIM(isp);SET子句是这里的关键它让导入过程本身带上了清洗能力——TRIM去掉首尾空白LEFT(..., 8)强制截断。中间变量prefix这种写法可以让你先读进临时变量处理完再落列比导入完再UPDATE全表一遍省事得多。导完必须校验别信「脚本跑完没报错就没事」SELECT COUNT(*) AS total_rows, COUNT(DISTINCT prefix) AS distinct_prefix, SUM(prefix REGEXP ^[0-9]{7,8}$) AS valid_prefix, SUM(province ) AS empty_province FROM phone_location;total_rows和distinct_prefix应该相等不相等说明唯一索引没生效或者prefix字段被截断了。valid_prefix应该等于总行数少了的行就是脏数据。empty_province是允许存在的但如果占比超过两成说明分隔符判断错了某些列整体错位。3. 查询链路从手机号到归属地的完整实现表建好、数据进去了接下来是最核心的一步给一个 11 位手机号怎么在一次索引查找里拿到归属地。这件事听起来简单翻车点却集中在「号段到底截几位」和「参数传进来是什么类型」这两处。3.1 号段截取规则7 位为主兼容 8 位与虚拟运营商前面说过常规号段的归属地粒度是前 7 位。但现实里有两类例外一类是虚拟运营商号段同一批 7 位前缀下面可能拆给了不同地区另一类是物联网卡和部分新放号段粒度要到 8 位才能区分。如果表里只存 7 位这些号段就会查出一个错误的地区而且是那种「看起来有结果、其实不对」的错误比查不到更难发现。所以我的处理方式是表里prefix允许 7 位和 8 位共存查询时同时用 7 位和 8 位去匹配命中 8 位优先。SQL 里就是SELECT province, city, isp, area_code FROM phone_location WHERE prefix IN (LEFT(?, 7), LEFT(?, 8)) ORDER BY CHAR_LENGTH(prefix) DESC LIMIT 1;IN里给了两个候选键MySQL 会对唯一索引做两次等值查找代价是常数级的。ORDER BY CHAR_LENGTH(prefix) DESC保证 8 位的结果排在前面LIMIT 1只取一条。CHAR_LENGTH用在CHAR列上返回的是去掉尾部空格后的实际长度所以 7 位号段算出来是 7不会被补位空格骗到。注意别写成ORDER BY prefix DESC。字符串排序下13800001和1380000的先后关系跟长度无关靠字典序猜优先级迟早出问题。如果你的数据里 8 位号段极少也可以退化成两步查先查 8 位查不到再查 7 位。两次往返换来的可读性提升有限我更倾向上面这一条 SQL 搞定。3.2 精确匹配与 EXPLAIN 验证SQL 写完别急着接进业务先EXPLAIN看一眼执行计划。这一步在开发机上成本几乎为零却能提前发现绝大多数「上线后才慢」的查询。EXPLAIN SELECT province, city, isp FROM phone_location WHERE prefix IN (LEFT(13800001234, 7), LEFT(13800001234, 8));健康的输出长这样列期望值含义typerange 或 ref走了索引范围扫描或等值查找possible_keysuk_prefix优化器识别到唯一索引可用keyuk_prefix实际选中的索引rows1 ~ 2预估扫描行数不该是几十万ExtraUsing index condition没有出现 Using filesort / Using temporarykey是NULL或者type是ALL说明索引没被用上往下查两个方向一是WHERE左边是不是被包了函数比如写成WHERE LEFT(phone, 7) prefix左边是计算表达式索引直接废掉二是prefix列和传入值之间发生了隐式类型转换——传进去的是数字1380000而不是字符串1380000MySQL 会把列转成数字再比索引同样失效。rows出现几十万也是信号通常意味着唯一索引没建成功或者建成了普通索引。用SHOW INDEX FROM phone_location确认一下Non_unique是不是 0。3.3 应用层封装Python 与 Java 两种写法把这段查询封成一个小类业务侧只调一个方法参数清洗和兜底都收在类里后面换数据源或者加缓存都不用动调用方。Python 版本import pymysql class PhoneLocator: def __init__(self, conn): self.conn conn def lookup(self, phone: str): # 去掉空格、横线、86 前缀只留数字 digits .join(ch for ch in str(phone) if ch.isdigit()) if digits.startswith(86) and len(digits) 13: digits digits[2:] if len(digits) ! 11 or not digits.startswith(1): return None sql (SELECT province, city, isp, area_code FROM phone_location WHERE prefix IN (LEFT(%s, 7), LEFT(%s, 8)) ORDER BY CHAR_LENGTH(prefix) DESC LIMIT 1) with self.conn.cursor(pymysql.cursors.DictCursor) as cur: cur.execute(sql, (digits, digits)) row cur.fetchone() return row # 查不到返回 None由调用方决定怎么展示Java 版本用PreparedStatement思路一致public class PhoneLocator { private final DataSource ds; public PhoneLocator(DataSource ds) { this.ds ds; } public String[] lookup(String rawPhone) throws SQLException { if (rawPhone null) return null; String phone rawPhone.replaceAll([^0-9], ); if (phone.length() ! 11 || phone.charAt(0) ! 1) return null; String sql SELECT province, city, isp FROM phone_location WHERE prefix IN (LEFT(?, 7), LEFT(?, 8)) ORDER BY CHAR_LENGTH(prefix) DESC LIMIT 1; try (Connection c ds.getConnection(); PreparedStatement ps c.prepareStatement(sql)) { ps.setString(1, phone); // 必须 setString不能 setLong ps.setString(2, phone); try (ResultSet rs ps.executeQuery()) { if (!rs.next()) return null; return new String[]{ rs.getString(province), rs.getString(city), rs.getString(isp) }; } } } }两段代码的共同点是先清洗再查询。用户从通讯录复制过来的号码可能带86、带空格、带-前端表单也可能把号码当数字传一旦带进 SQLLEFT()截出来的就不是你想的那 7 位。Java 里ps.setString(1, phone)这一行尤其重要改成setLong就会触发前面说的隐式类型转换索引悄悄失效而且在本机小表上完全测不出来。返回值统一用null表示「查不到」而不是抛异常。号段表不可能覆盖所有号码新放号、测试号、境外号都会查不到业务侧处理成「未知」就行抛异常只会让注册流程凭空多一个失败分支。4. 性能与容量几十万到百万行号段表的索引与缓存策略号段表的特点是「读极多、写极少、数据量有限」。这三点决定了它的性能优化方向和常规业务表完全不一样——不需要读写分离不需要分库分表真正值得做的是把索引做对、把重复查询挡在数据库外面。4.1 索引设计为什么前缀列用 CHAR(8) 而不是 VARCHAR唯一索引uk_prefix是这张表的命脉它的键长度直接决定每次查找要读多少页。CHAR(8)配ascii字符集键长是固定的 8 字节如果换成VARCHAR(8)加utf8mb4最坏情况键长能到 32 字节索引体积翻几倍同样的数据量需要更多的 B 树层数多一层就多一次随机 IO。还有一个容易被忽略的点prefix设成唯一索引之后它本身就是一个可用的「覆盖索引」候选。如果查询只取prefixMySQL 可以只扫索引不回表。但业务上我们总要拿province、city、isp所以这条用不上真正起作用的是「唯一性带来的等值查找最多命中一行」——优化器知道结果集最多一行LIMIT 1之后的排序和返回开销可以忽略。要不要再给province、city建索引看场景。如果业务里有「统计某省号码数量」这类聚合查询那确实需要但那是离线统计跑在从库或者干脆导一份到分析库里做别在主库上加索引拖慢导入。在线查询永远是按prefix点查一个索引足够。4.2 本地缓存与批量查询一次查 500 条的做法单个查已经够快了真正拖慢系统的是「列表页里给 200 条订单逐条查归属地」这种 N1 场景。200 次网络往返的开销远大于 200 次索引查找。正确做法是攒一批、一次查回来。def lookup_batch(self, phones): result {} uniq list({p for p in phones if p and len(p) 11}) for i in range(0, len(uniq), 500): chunk uniq[i:i 500] # 每条号码贡献 7 位和 8 位两个候选键先去重再拼 IN keys set() for p in chunk: keys.add(p[:7]) keys.add(p[:8]) placeholders ,.join([%s] * len(keys)) sql (SELECT prefix, province, city, isp FROM phone_location WHERE prefix IN (%s) % placeholders) with self.conn.cursor(pymysql.cursors.DictCursor) as cur: cur.execute(sql, tuple(keys)) seg {r[prefix].strip(): r for r in cur.fetchall()} for p in chunk: hit seg.get(p[:8]) or seg.get(p[:7]) if hit: result[p] hit return result参数说明分片大小选 500是因为每条号码贡献两个键一批最多 1000 个占位符拼出来的 SQL 长度在几十 KB 量级离max_allowed_packet还有很大余量同时单次往返的收益已经吃满再加大边际收益很低。seg这个字典用strip()过的prefix做键是因为CHAR列在个别驱动下取出来可能带尾部空格不strip就取不到。回填时先试 8 位再退 7 位和单条查询的优先级保持一致。再往上就是进程内缓存。号段数据基本不变最适合用带 TTL 的 LRUfrom functools import lru_cache lru_cache(maxsize100_000) def cached_lookup(prefix7: str) - tuple: # 这里放单条查询返回值必须是可哈希的 tuple ...maxsize100_000大约占几十 MB 内存对一台正常配置的应用服务器完全可接受。命中率通常能到 95% 以上因为真实流量里号段分布高度集中头部几十个号段就能覆盖大半请求。缓存的失效策略很简单增量更新完号段表之后直接重启进程或者清一次缓存不需要做细粒度失效。4.3 什么时候该上 Redis什么时候不必经常有人问要不要把整张号段表塞进 Redis。我的判断标准是实例数单实例部署进程内 LRU 就够多一跳网络反而更慢多实例部署且各实例缓存不一致会造成困扰这时候 Redis 才有意义因为它是共享的、更新一次全部生效。但即便如此也要算一下收益。几十万行的号段表每行只取省份城市运营商序列化后单行不到 100 字节整表也就几十 MBRedis 完全装得下。问题在于——你真的需要每秒几十万次的查询能力吗一个注册页、一个订单列表QPS 撑死几百MySQL 点查加本地缓存绰绰有余。上 Redis 之前先想清楚是在解决真实瓶颈还是在给自己加一个需要监控、需要容灾、需要处理缓存击穿的新组件。至于分库分表这张表可以直接排除。数据量上限就在百万级单表点查延迟稳定在亚毫秒分片只会让「一次查询要定位到哪个库」变成新的复杂度来源。5. 避坑与排查导入和查询阶段最容易翻车的 5 个地方这套东西跑通不难难的是跑通之后各种「明明有数据却查不出来」。下面五条是我自己踩过、也见过别人踩的按出现频率排序。5.1 导入环节的三个坑现象一导入显示成功但COUNT(*)比文件行数少了一截。原因是唯一索引uk_prefix在起作用文件里本来就有重复号段ON DUPLICATE KEY UPDATE把后来的覆盖掉了。这不算 bug但如果你的预期是「一行不少地进去」就会误判成导入失败。解决办法是导入前先跑一遍文件级去重统计distinct数量再和入库后的COUNT(*)对账对不上就说明确实有重复而不是丢数据。现象二省市字段全变成问号或者乱码。原因是文件编码和连接字符集不一致。拿到手的原始文件很多是 GBK用encodingutf-8去读第一行就可能抛UnicodeDecodeError有些人图快加了errorsignore结果字符被吞掉插进库里就是空的或者问号。正确处理是先用编辑器确认编码读文件时指定对应编码同时在连接上显式执行SET NAMES utf8mb4两头都对齐。导入完成后再跑一次SELECT province FROM phone_location WHERE province REGEXP [?] LIMIT 10抽查能立刻发现问题。现象三LOAD DATA LOCAL INFILE报The used command is not allowed with this MySQL version。原因是local_infile被禁用托管数据库上很常见普通账号也没有权限打开。这时候不用死磕直接切回批量 INSERT 方案几十万行也就多花一两分钟。硬要开的话需要服务端参数和客户端连接参数同时打开还得有相应权限在别人的数据库上折腾这个不划算。5.2 查询环节的两个坑现象四号码明明在表里就是查不到。八成是传参没清洗。用户输入138-0000-1234、86 13800001234、138 0000 1234LEFT(?, 7)截出来的是138-000或者86 138自然匹配不上。还有一种更隐蔽的前端把手机号当数字传13800001234这种 11 位数在 JS 里没超安全整数范围但如果中间经过某些序列化环节变成浮点或者科学计数法截出来的前缀就完全错了。解决办法只有一个——在PhoneLocator入口处强制转成字符串再剔除非数字字符别指望上游给你干净数据。现象五单条查询很快列表页却慢得离谱。前面提过的 N1。200 条记录逐条查200 次往返每次哪怕只有 0.5 毫秒加上连接池借还和网络开销一秒就过去了。用EXPLAIN看不出问题因为单条 SQL 确实走了索引。排查方法是打开慢查询日志看是不是同一张表在极短时间内被查了几百次相同结构的语句——这是 N1 的典型特征。改成批量查询或者本地缓存。还有一种变体是「在循环里开事务」每次查询都自带一次BEGIN/COMMIT开销翻倍这个用连接池的autocommit配置就能规避。6. 二次开发号段增量、兜底查询与一个上线前的校验习惯这套东西交付之后真正的工作才刚开始——号段会变运营商隔一段时间就放新号段老号段偶尔也会因为行政区划调整改归属地。我的做法是保留「全量覆盖 唯一索引」这个组合每次拿到新数据就直接跑一遍导入脚本靠ON DUPLICATE KEY UPDATE让重复号段原地更新、新号段自动插入。这样不需要写复杂的 diff 逻辑也不会出现「增量脚本漏跑一次导致数据错位」的问题。增量验证我一般用一个极简的对账查询-- 找出本次更新后地区字段发生变化的号段人工抽查前 50 条 SELECT prefix, province, city, isp, updated_at FROM phone_location WHERE updated_at CURDATE() ORDER BY updated_at DESC LIMIT 50;前提是表上有个updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP字段建表时顺手加上排查时能省很多事。真实业务里一定会有查不到的号码尤其是新放号段和测试号。我的处理原则是表里查不到就返回空绝不抛异常业务侧统一展示为「未知地区」并且把这个手机号的前 7 位打进一条日志。攒一段时间之后这些日志就是一份天然的「缺失号段清单」下次更新数据时优先补上比漫无目的地找新数据源高效得多。还有一个我强制自己执行的习惯每次更新完号段表随机抽 1000 个手机号分别用本地表和外部数据源各查一遍把结果不一致的记录下来。不一致的原因通常是三种——本地表缺号段、外部源本身错了、行政区划刚调整。前两种能直接指导下一步动作第三种则提醒你这类数据本来就有滞后性业务上不能把它当成强一致的事实来用。最后一句血泪经验号段表这种「看起来永远不会变」的静态数据最容易被当成一次性工程导完就忘。等半年后有人反馈某个新号段查出来是空的你翻遍代码也想不到问题出在数据本身。从那以后我每次上线这类字典表都会在监控里挂一个「查不到归属地的号码占比」指标超过阈值就报警。希望帮到你。本文还有配套的精品资源点击获取