简介这份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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT手机号号段归属地字典;几个参数值得单独说。prefix用varchar(8)而不是char(8)因为库里 7 位和 8 位前缀混存char会在尾部补空格比较和GROUP BY时容易出现“看着一样但不相等”的玄学问题如果确定全部是 7 位那用char(7)更省空间。字符集必须utf8mb4部分城市名带生僻字早年用utf8实际是 utf8mb3建的表导进去会直接变成问号。isp字段的注释特意写了“初始分配运营商”这是后面第 4 章要展开的边界。字段类型用途常见误用prefixvarchar(8)号段前缀主键存完整 11 位手机号索引体积翻倍provincevarchar(32)省份带“省”后缀报表分组时对不齐cityvarchar(32)城市直辖市填成“市辖区”ispvarchar(16)初始运营商当成用户当前运营商card_typevarchar(16)卡类型把虚拟运营商和物联网卡混为一谈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-infile1 -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() SQLunique_checks0只在导大文件时开导完必须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, encodinggbk, errorsreplace) as fin, \ open(/data/phone_segment.csv, w, encodingutf-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)这段脚本的关键点在两个地方encodinggbk是因为这类二手号段文件大概率来自 Windows 环境errorsreplace保证遇到坏字节不会整个中断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里优化器没法把它当常量去走索引执行计划是typeALL几十万行全扫一遍。单次查询可能只要几十毫秒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;这条写法的执行计划是typerange、rows2、keyPRIMARY两次 B 树查找就结束。ORDER BY CHAR_LENGTH(prefix) DESC LIMIT 1保证 8 位记录优先于 7 位记录返回这是最长前缀匹配的落地点缺了这句遇到前缀重叠就会返回错误的城市。key_len大概是 34varchar(8)在 utf8mb4 下 8×42看到这个数就说明索引确实用上了。还有一种写法是把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 (fSELECT prefix, province, city, isp, card_type fFROM 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 resultchunk500是权衡出来的500 个号码会展开成 1000 个占位符SQL 文本约 4KB远低于max_allowed_packet默认的 4MB同时单次往返的解析开销也可控。调到 5000 也能跑但IN列表过长时优化器的 index dive 成本会上升收益不明显。如果量级到了百万级IN就不合适了改成临时表 JOINCREATE TEMPORARY TABLE tmp_phone ( phone char(11) NOT NULL PRIMARY KEY ) ENGINEMEMORY; -- 应用侧批量 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 编码而客户端连接默认latin1LOAD DATA里又没写CHARACTER SETMySQL 按连接字符集去解释文件字节中文直接被替换成问号而且是不可逆的。解决办法是导入前先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 → 归属地全部拉进本地字典查询走内存单次耗时在微秒级。加载代码要带版本号加载失败不能阻塞启动降级为直查 MySQLimport 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 延迟和缓存命中率。验证项方法通过标准数据完整性长度分布 空值率 SQL空值率 0.5%查询准确性500 条抽样与外部接口比对省市一致率 ≥ 99%性能1000 QPS 压测 10 分钟P99 1ms缓存命中率 95%降级手动停掉 MySQL 后发起查询有缓存时正常返回无缓存时返回“未知”不报错我印象最深的一次是压测指标全过上线第二天还是出了问题原因是号段表更新脚本把版本号写死了新数据加载进来但缓存 key 没变用户看到的还是旧归属地。从那以后我每次更新号段表都强制走一遍“换版本号 → 抽样比对 → 观察空值率”这三步一步都不能省。希望帮到你。本文还有配套的精品资源点击获取