MySQL手机号号段归属地查询：建表导入、索引优化与批量查询实战

简介：这份MySQL手机号归属地数据集面向数据库初学者、数据分析人员及需要按号码定位省份城市的开发者，提供一份较完整的号码与省份、城市映射表，可用于归属地查询练习、客户地域分布统计等场景。压缩包内共1个sql文件，整体约2.22MB，导入MySQL后即可围绕号码字段、省份字段和城市字段编写查询，完成精确匹配、批量筛选、分组计数及多表关联等操作。已有269人学习，说明其在数据库练习与小型分析任务中具备一定参考价值。读者可借此熟悉SELECT与WHERE条件查询、GROUP BY聚合统计和JOIN合并数据的写法，也能观察一张实用数据表的字段组织方式，为营销分析、客户服务中的地域维度处理积累素材。使用时需注意个人信息保护，仅在合法合规前提下存储和访问敏感数据。

1. 号段库不是“查表”那么简单：先看清这份 MySQL 手机号归属地数据装了什么

上个月帮一个做注册风控的团队排查线上问题，日志里一条请求的归属地显示成了隔壁省，追下去发现他们用的号段表只覆盖 7 位前缀，而那个号码落在新放的 8 位号段里。手机号归属地查询看着只是“拿前几位去 MySQL 里 SELECT 一下”，真落到生产上，号段长度、虚拟运营商、携号转网、索引写法、批量查询，每一项都能让结果偏掉。手里这份 MySQL 号段库，本质是一张把“号段前缀 → 省 / 市 / 运营商 / 卡类型”映射起来的字典表，覆盖面比网上随手爬的版本全不少。它适合注册风控、订单归属地展示、运营地域报表这类需要离线可查、不想被第三方接口卡脖子限流和计费的场景，也适合只想在自己的 MySQL 里加一张小表、不想引入额外服务的团队。

2. 建表与导入：让几十万行号段数据落地的四个决策

原始 SQL 文件拿到手，第一反应往往是直接source进去，然后发现表结构是别人拍脑袋定的：前缀存成varchar(11)存完整手机号、没有主键、城市字段用 GBK 存的。这类库能查，但查询路径和存储成本都不对。号段表是一张典型的“读多写少、等值查询为主、总量可控”的字典表，结构设计要围绕这三条来。

2.1 表结构：前缀做主键，别用自增 ID

我一般会把号段单独存一列，长度 7 到 8 位，直接做主键。原因是 InnoDB 的主键是聚簇索引，叶子节点上就是整行数据，按前缀等值查一次 B+ 树就能拿到省市区，连回表都省了。如果再加一个自增id做主键、前缀建二级索引，每次查询就要多一次“二级索引 → 主键 → 聚簇索引”的跳转，纯属给自己加活。

CREATE TABLE `phone_segment` ( `prefix` varchar(8) 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 '号段初始分配运营商，非当前运营商', `card_type` varchar(16) NOT NULL DEFAULT '' COMMENT '普通/虚拟/物联网', `area_code` varchar(8) NOT NULL DEFAULT '' COMMENT '区号', `post_code` varchar(8) NOT NULL DEFAULT '' COMMENT '邮编', `updated_at` date NOT NULL DEFAULT '1970-01-01' COMMENT '数据版本日期', PRIMARY KEY (`prefix`), KEY `idx_province_city` (`province`, `city`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='手机号号段归属地字典';

几个参数值得单独说。prefix用varchar(8)而不是char(8)，因为库里 7 位和 8 位前缀混存，char会在尾部补空格，比较和GROUP BY时容易出现“看着一样但不相等”的玄学问题；如果确定全部是 7 位，那用char(7)更省空间。字符集必须utf8mb4，部分城市名带生僻字，早年用utf8（实际是 utf8mb3）建的表导进去会直接变成问号。isp字段的注释特意写了“初始分配运营商”，这是后面第 4 章要展开的边界。

2.2 导入：LOAD DATA 的开关和字符集要一次配对

几十万行的 CSV，用客户端工具一行行 INSERT 是最慢的做法，我一般直接上LOAD DATA LOCAL INFILE，但 MySQL 8 默认把local_infile关了，得先开。

# 1) 服务端打开本地文件导入开关（会话级不够，LOAD DATA LOCAL 需要全局开） mysql -uroot -p -e "SET GLOBAL local_infile = 1;" # 2) 导入时显式指定字符集和分隔符，避免中文变问号 mysql --local-infile=1 -uroot -p phone_db <<'SQL' SET NAMES utf8mb4; SET SESSION unique_checks = 0; -- 导入期间跳过唯一性校验，导完记得恢复 SET SESSION foreign_key_checks = 0; -- 字典表没有外键，这行是习惯性保险 LOAD DATA LOCAL INFILE '/data/phone_segment.csv' INTO TABLE phone_segment CHARACTER SET utf8mb4 -- 关键：和文件真实编码一致 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES -- 跳过表头 (prefix, province, city, isp, card_type, area_code, post_code) SET updated_at = '2024-06-01'; -- 用导入日期做数据版本，别用 NOW() SQL

unique_checks=0只在导大文件时开，导完必须SET SESSION unique_checks = 1还原，否则后续业务写入的唯一性约束形同虚设。updated_at写死成导入批次日期而不是NOW()，是为了让同一次导入的所有行共享一个版本号，后面做增量对比时能直接按版本筛。另外注意不要在主库上随手加sql_log_bin = 0，几十万行的字典表导入量不大，为省这点 binlog 破坏复制不划算。

如果源文件是别人从各种渠道拼出来的，格式往往不干净：前缀带空格、带+86、城市名前后有全角空格。我一般先用 Python 过一遍清洗再入库：

import csv, re SEG_RE = re.compile(r'^1[3-9]\d{5,6}$') # 7~8 位号段 CLEAN_RE = re.compile(r'[\s\u3000\-]') # 半角/全角空格、横线 def clean_row(row: dict) -> dict | None: prefix = CLEAN_RE.sub('', row.get('prefix', '')) if not SEG_RE.match(prefix): return None # 明显脏数据直接丢，别硬塞 out = {'prefix': prefix} for k in ('province', 'city', 'isp', 'card_type', 'area_code', 'post_code'): out[k] = CLEAN_RE.sub('', row.get(k, '')).strip() return out with open('/data/raw.csv', encoding='gbk', errors='replace') as fin, \ open('/data/phone_segment.csv', 'w', encoding='utf-8', newline='') as fout: writer = csv.DictWriter(fout, fieldnames=['prefix','province','city','isp', 'card_type','area_code','post_code']) writer.writeheader() for row in csv.DictReader(fin): if cleaned := clean_row(row): writer.writerow(cleaned)

这段脚本的关键点在两个地方：encoding='gbk'是因为这类二手号段文件大概率来自 Windows 环境，errors='replace'保证遇到坏字节不会整个中断；SEG_RE只放行1[3-9]开头的 7 到 8 位前缀，把混进来的固话区号、邮编、身份证前几位全部挡在库外。清洗完再导出成 UTF-8，和LOAD DATA里的CHARACTER SET utf8mb4对上，中文就不会翻车。

2.3 校验：导入后必须跑的四条 SQL

导完不校验，等于没导。我固定跑四条，每条都有明确的判读标准。

-- 1. 长度分布：7 位和 8 位各占多少 SELECT CHAR_LENGTH(prefix) AS len, COUNT(*) AS cnt FROM phone_segment GROUP BY len ORDER BY len; -- 2. 重复检查：主键本身不会重复，但清洗环节出问题时会在这里暴露 SELECT prefix, COUNT(*) AS c FROM phone_segment GROUP BY prefix HAVING c > 1; -- 3. 空值率：省市同时为空的记录占比 SELECT SUM(province = '' OR city = '') / COUNT(*) AS empty_ratio FROM phone_segment; -- 4. 抽样：拿几个自己熟悉的号段对一下 SELECT * FROM phone_segment WHERE prefix IN ('1381234', '1991234', '1651234');

第 1 条看长度分布，如果 8 位记录占比突然从个位数冲到三成以上，八成是源文件里混了别的表，得回退重导。第 3 条的空值率我一般要求低于 0.5%，超了就说明上游把“已分配未启用”的号段也塞进来了，得在应用层加“未知”兜底，而不是直接把空字符串展示给用户。第 4 条是血泪经验，抽样一定要选自己待过的城市，界面上显示得对不对一眼就能看出来，比看任何统计数字都直接。

3. 查询写法决定响应时间：前缀匹配、索引与批量查询的三种写法

表建好了，接下来是查询。号段表的查询有一个特殊性：匹配长度不固定。号码是 11 位，但库里前缀是 7 位还是 8 位不确定，这就引出了“最长前缀匹配”的问题，也直接决定了 SQL 该怎么写。

3.1 三种写法的执行计划对比

先看一个常见但很糟的写法：

-- 反例：把号码当左值去 LIKE 前缀，列没被函数包裹，但依然全表扫 SELECT province, city FROM phone_segment WHERE '13812345678' LIKE CONCAT(prefix, '%');

这条 SQL 语义上是对的，'13812345678' LIKE '1381234%'确实能匹配上。但prefix出现在CONCAT里，优化器没法把它当常量去走索引，执行计划是type=ALL，几十万行全扫一遍。单次查询可能只要几十毫秒，QPS 上到几百就直接把 CPU 打满。

-- 正例：把号码裁成候选前缀，走主键等值匹配，取最长的那条 SELECT prefix, province, city, isp, card_type FROM phone_segment WHERE prefix IN ('1381234', '13812345') ORDER BY CHAR_LENGTH(prefix) DESC LIMIT 1;

这条写法的执行计划是type=range、rows=2、key=PRIMARY，两次 B+ 树查找就结束。ORDER BY CHAR_LENGTH(prefix) DESC LIMIT 1保证 8 位记录优先于 7 位记录返回，这是最长前缀匹配的落地点，缺了这句，遇到前缀重叠就会返回错误的城市。key_len大概是 34（varchar(8)在 utf8mb4 下 8×4+2），看到这个数就说明索引确实用上了。

还有一种写法是把LEFT(phone, 7)放在等号左边：

-- 反例：函数包裹列，索引失效 SELECT city FROM phone_segment WHERE prefix = LEFT('13812345678', 7);

这里LEFT()作用在常量上，其实优化器能常量折叠，性能没问题，但它只裁了 7 位，遇到 8 位号段就查不到。真正要避免的是WHERE LEFT(prefix, 7) = ...这种把函数套在列上的写法，那是百分之百的索引失效。

3.2 前缀索引和覆盖索引：什么时候该建，什么时候是浪费

有人会问，要不要给prefix建个前缀索引KEY idx_prefix (prefix(7))。答案是不用，prefix已经是主键，聚簇索引的叶子节点就是整行，再建前缀索引只会多占空间。前缀索引真正该用的场景是另一张表——比如订单表里存了完整手机号，你想按号段做统计：

-- 订单表按号段聚合，先算 7 位前缀的选择性 SELECT COUNT(DISTINCT LEFT(phone, 7)) / COUNT(*) AS sel7, COUNT(DISTINCT LEFT(phone, 8)) / COUNT(*) AS sel8 FROM order_main;

手机号前 7 位的选择性通常能到 0.99 以上，也就是说 7 位前缀足以区分绝大多数号码，KEY idx_phone (phone(7))完全够用，没必要索引完整 11 位。覆盖索引在这个场景里意义也不大：prefix是主键，聚簇索引叶子已经是整行，除非这张字典表宽到单行超过 200 字节，否则拆表做覆盖索引属于过度设计。

3.3 批量查询：1000 个号码怎么一次查完

单条查询再快，也扛不住一次请求要查 1000 个号码时的网络往返。我一般用IN批量查，把 7 位和 8 位候选前缀一次性展开。

def batch_lookup(conn, phones: list[str], chunk: int = 500) -> dict: """批量查归属地，按最长前缀优先返回，chunk 控制单条 SQL 的参数个数""" result = {} for i in range(0, len(phones), chunk): part = phones[i:i + chunk] args = [] for p in part: args.append(p[:7]) # 7 位候选 args.append(p[:8]) # 8 位候选，长的优先 holders = ','.join(['%s'] * len(args)) sql = (f"SELECT prefix, province, city, isp, card_type " f"FROM phone_segment WHERE prefix IN ({holders})") with conn.cursor() as cur: cur.execute(sql, args) rows = {r[0]: r for r in cur.fetchall()} for p in part: # 先取 8 位，再回落 7 位，都取不到返回 None result[p] = rows.get(p[:8]) or rows.get(p[:7]) return result

chunk=500是权衡出来的：500 个号码会展开成 1000 个占位符，SQL 文本约 4KB，远低于max_allowed_packet默认的 4MB，同时单次往返的解析开销也可控。调到 5000 也能跑，但IN列表过长时优化器的 index dive 成本会上升，收益不明显。如果量级到了百万级，IN就不合适了，改成临时表 JOIN：

CREATE TEMPORARY TABLE tmp_phone ( phone char(11) NOT NULL PRIMARY KEY ) ENGINE=MEMORY; -- 应用侧批量 INSERT 进来之后： SELECT t.phone, s.province, s.city, s.isp FROM tmp_phone t LEFT JOIN phone_segment s ON s.prefix = LEFT(t.phone, 7);

注意这个 JOIN 只匹配 7 位前缀，遇到 8 位号段会漏，正确写法是ON s.prefix IN (LEFT(t.phone,7), LEFT(t.phone,8))，但那样 JOIN 条件里带函数，优化器只能走嵌套循环。所以百万级场景我通常先按 7 位 JOIN 出结果，再对未命中的那部分单独按 8 位补查一轮，两轮都走等值匹配，比一条复杂 JOIN 稳。

4. 数据准确性：虚拟运营商、携号转网与新号段怎么兜

号段库最大的价值不是“能查”，而是“查得准”。但准确性这件事有天然的边界，很多线上事故不是查询写错了，是业务方误以为库里的字段等于事实。

4.1 号段长度不统一：7 位、8 位和 13 位物联网卡

普通 11 位手机号的前 7 位基本能定位到城市，但有三类例外。第一类是部分虚拟运营商号段，前 4 位只能定到“虚拟运营商”这个大类，要到 7 位才能落到城市。第二类是新放号段，分配批次晚，7 位还不足以区分相邻城市，库里会存成 8 位。第三类是 13 位物联网卡，141、144、146这些开头，长度就不是 11 位，拿普通手机号规则去套必然出错。

import re RE_MOBILE = re.compile(r'^1[3-9]\d{9}$') # 标准 11 位手机号 RE_IOT = re.compile(r'^1[3-9]\d{11}$') # 13 位物联网卡 STRIP_RE = re.compile(r'[\s\-]') def normalize(phone: str) -> tuple[str, str]: """返回 (清洗后号码, 类型)，类型取 mobile/iot/invalid""" p = STRIP_RE.sub('', phone or '') if p.startswith('+86'): p = p[3:] elif p.startswith('86') and len(p) == 13: p = p[2:] if RE_IOT.match(p): return p, 'iot' if RE_MOBILE.match(p): return p, 'mobile' return p, 'invalid'

清洗顺序是这里最容易翻车的地方：必须先去+86再做长度判断，否则8613812345678会被RE_IOT当成 13 位物联网卡放过去。物联网卡在业务上一般直接标记为iot并跳过归属地查询，硬查只会得到一个错误的城市。

4.2 运营商字段不等于当前运营商：携号转网的边界

号码的省、市归属在分配时就定了，基本不随用户迁移变化；但运营商字段记录的是号段初始分配方。用户办了携号转网之后，实际运营商变了，号段表不会跟着变，这时候拿isp字段去判断“这是移动用户”就会错。这个问题没法靠号段库本身解决，只能在表结构和业务语义上做区分。

我一般把字段名写成isp，但注释明确标“初始分配运营商”，对外接口返回时字段名叫isp_original。如果业务确实需要当前运营商，单独建一张补充表，只存发生过转网的号码：

CREATE TABLE phone_portability ( phone char(11) NOT NULL PRIMARY KEY, current_isp varchar(16) NOT NULL COMMENT '携号转网后的当前运营商', updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ) COMMENT='携号转网补充表，只存已发生转网的号码，量级很小';

查询时先查这张补充表，命中就用current_isp，未命中再回落号段表的isp。这张表的数据来源只能是业务侧自己积累或者用户主动上报，别指望号段库能覆盖。

4.3 增量更新：新号段怎么进表，旧数据怎么留痕

号段是按批次放号的，新批次启用后旧库就查不到。增量更新的常见做法是每月或每季度拿一份新文件，和新旧版本做 diff，只处理变化的部分。写入用幂等 upsert，保证重复执行结果一致：

INSERT INTO phone_segment (prefix, province, city, isp, card_type, area_code, post_code, updated_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) AS new ON DUPLICATE KEY UPDATE province = new.province, city = new.city, isp = new.isp, card_type = new.card_type, area_code = new.area_code, post_code = new.post_code, updated_at = new.updated_at;

VALUES(...) AS new这个别名语法是 MySQL 8.0.20 之后推荐的写法，老写法VALUES(col)已经被标记废弃，新环境上跑会出 warning。如果还在 5.7，就退回VALUES(col)。

更新之前一定要留痕，这是后悔药。我会在每次全量更新前把受影响的行捞出来写进变更表：

CREATE TABLE phone_segment_change ( id bigint AUTO_INCREMENT PRIMARY KEY, prefix varchar(8) NOT NULL, old_city varchar(32) NOT NULL, new_city varchar(32) NOT NULL, batch_date date NOT NULL, KEY idx_batch (batch_date) ) COMMENT='号段变更留痕，用于回滚和排查';

有一回上游把某个地级市的号段整体划到了邻省，业务侧报表当天就炸了，靠这张变更表半小时内定位到是数据源的问题，而不是代码逻辑。没有留痕，就只能全量回滚再重新对账。

5. 避坑：五条号段库上线时踩过的坑

这一章每条都是线上真实遇到过的，按“现象 → 原因 → 解决”写，方便对照排查。

5.1 数据层的三个坑

第一条：导入后城市名全是问号。

现象是SELECT city FROM phone_segment LIMIT 10出来一堆???。原因是源 CSV 是 GBK 编码，而客户端连接默认latin1，LOAD DATA里又没写CHARACTER SET，MySQL 按连接字符集去解释文件字节，中文直接被替换成问号，而且是不可逆的。解决办法是导入前先SHOW VARIABLES LIKE 'character_set_%'确认character_set_client、character_set_connection、character_set_database三者一致，LOAD DATA语句里显式写CHARACTER SET utf8mb4，源文件先用编辑器或 Python 转成 UTF-8。已经导坏的只能清表重导。

第二条：同一个号码查出两个不同城市。

现象是测试同学拿同一个号码在订单页和风控页看到不同归属地。原因是库里同时存在 7 位的1381234和 8 位的13812345两条记录，两个页面的 SQL 一个只查 7 位、一个只查 8 位，谁先命中谁说了算。这类重叠在新放号段里很常见。解决办法是统一走最长前缀匹配，SQL 里用ORDER BY CHAR_LENGTH(prefix) DESC LIMIT 1，或者在应用层封装成一个lookup()函数，所有调用方只认这个函数，不允许自己拼 SQL。

第三条：19x 新号段查出来省市为空。

现象是部分192、196开头的号码返回空字符串，前端直接展示成空白。原因是上游把“已分配未启用”的号段也放进了文件，这些号段还没实际放号，自然没有城市信息。解决办法分两层：查询层对province = ''的记录统一返回“未知”而不是空串；导入层加一个空值率监控，超过 0.5% 就中止导入并告警，别让脏数据静默进库。

5.2 查询与接入层的两个坑

第四条：QPS 一上来 CPU 打满，慢查询日志里全是 LIKE。

现象是压测到 300 QPS 时数据库 CPU 冲到 90%，慢查询日志里清一色WHERE 'xxx' LIKE CONCAT(prefix, '%')。原因是这种写法让优化器无法使用主键索引，只能全表扫描，几十万行扫一遍看着快，并发一上来就雪崩。解决办法是把 SQL 改成前缀等值IN查询，改完必须用EXPLAIN确认type是const或range、rows是个位数，没达到这个标准就不算改完。顺手在代码评审里加一条规则：号段表相关 SQL 不允许出现LIKE。

第五条：缓存把错误结果固化了。

现象是某个号码第一次查的时候库里还没有数据，返回“未知”，后来补了数据，这个号码还是“未知”，持续了好几天。原因是缓存把空结果也写进去了，而且没设过期时间。解决办法有两条：空结果的 TTL 单独设短，比如 5 分钟，正常结果可以设 24 小时；缓存 key 带上数据版本号，比如seg:v20240601:1381234，每次更新号段表就换版本号，旧 key 自然失效，不需要手动删缓存。这个技巧在号段这种“低频变更、高频读取”的字典表上特别好用。

6. 上生产前的验证：把单次查询压到 1ms 的三个动作

号段表总量就几十万行，单行几十字节，全量加载进内存大概几十 MB，这个体量决定了它最适合的部署形态不是“每次请求都打 MySQL”，而是“启动时全量加载 + 变更时刷新”。我一般做三个动作来验证。

第一个动作是全量预热。应用启动时把prefix → 归属地全部拉进本地字典，查询走内存，单次耗时在微秒级。加载代码要带版本号，加载失败不能阻塞启动，降级为直查 MySQL：

import time def warm_up(conn, cache: dict, timeout: float = 30.0): """启动预热，超时或异常时降级，不阻塞主流程""" start = time.time() try: with conn.cursor() as cur: cur.execute("SELECT prefix, province, city, isp, card_type FROM phone_segment") for prefix, prov, city, isp, ctype in cur.fetchall(): cache[prefix] = (prov, city, isp, ctype) except Exception as e: print(f"[warn] 号段预热失败，降级直查: {e}") finally: print(f"[info] 预热完成 {len(cache)} 条，耗时 {time.time()-start:.2f}s") def lookup(phone: str, cache: dict) -> tuple: """先 8 位后 7 位，内存字典同样遵守最长前缀优先""" return cache.get(phone[:8]) or cache.get(phone[:7]) or ('未知', '未知', '未知', '未知')

timeout参数是防止预热把启动拖死，实际用的时候配合线程池或者future做超时控制，超时就走降级路径，等后台异步补加载。

第二个动作是对照验证。抽 500 个真实号码，同时用本地库和外部接口各查一遍，逐条比对省、市两个字段，准确率低于 99% 就不上线。这个动作看起来笨，但能一次性抓出号段重叠、编码错误、脏数据三类问题。

第三个动作是压测。用随机生成的号码跑 1000 QPS，观察 P99 延迟和缓存命中率。

我印象最深的一次是压测指标全过，上线第二天还是出了问题，原因是号段表更新脚本把版本号写死了，新数据加载进来但缓存 key 没变，用户看到的还是旧归属地。从那以后我每次更新号段表都强制走一遍“换版本号 → 抽样比对 → 观察空值率”这三步，一步都不能省。希望帮到你。

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