★ FEATURED ARTICLE 简介面向数据库查询与数据分析场景的手机号归属地数据包适合MySQL初学者、数据分析人员及需要做号码地域统计的开发者参考。压缩包共1个文件主体为SQL数据文件体积约2.22MB导入后可围绕手机号、省份、城市等字段进行单条查询、批量匹配和分组统计例如按省份汇总用户数量、结合业务表做关联分析。内容侧重提供较完整的号码与归属地映射关系可用于营销分析、客户服务、区域分布研究等练习与原型验证。已有269人学习能帮助读者快速搭建可查询的本地数据表理解结构化数据的组织方式并练习SELECT、WHERE、JOIN、COUNT、GROUP BY等常用语句。使用时需注意个人信息保护与合规边界适合在合法授权范围内进行学习、测试和数据分析。1. 手机号归属地离线库为什么我把在线API换成了MySQL号段表手机号归属地查询这件事我一开始也是调在线接口直到一次大促前接口限流才把一份离线号段库导进 MySQL。那份库从二手渠道收来号段覆盖比较全核心就是一张前 7 位号段表加一张行政区划表。它解决的不是“查不到”的问题而是“查询量一上来就被外部依赖卡住”的问题单号查询走本地索引批量查询走临时表 JOIN延迟稳定隐私数据也不用出库。适合订单风控、客服工作台、用户画像补全这类既要查得勤、又不想把手机号往外发的场景。新手能照着建表导入熟手更关心号段冲突、字符集和索引顺序后面几章按这个顺序拆。2. 拆开这个MySQL库三张表、前7位号段与数据粒度拿到一个 SQL 导出文件别急着往生产库灌。先看表结构和字段含义判断它能不能接进现有业务。这份手机号归属地库的骨架并不复杂常见做法是拆成号段表、行政区划表、运营商编码表三张表号段表只存前 7 位不存完整手机号。这样做的好处是数据量可控查询时用LEFT(phone, 7)命中索引后 4 位用户号不参与归属判断也不会把用户明文散落在多张表里。2.1 号段表、归属地表、运营商表怎么分工号段表负责“前 7 位映射到哪个行政区划、哪个运营商编码、什么卡类型”。行政区划表负责“编码对应哪个省、哪个市”把名称统一收在一张表里号段表只存编码后续地名调整时不用改几百万行号段。运营商编码表可选如果业务只需要展示归属地可以把运营商编码直接放号段表省一次 JOIN如果客服工作台要按运营商筛单独一张小表更干净。我一般会这样建号段表CREATE TABLE t_phone_prefix ( prefix CHAR(7) NOT NULL COMMENT 手机号前7位, region_code CHAR(6) NOT NULL COMMENT 行政区划编码, carrier_code TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 运营商编码0未知, card_type TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 0普通 1转售 2物联网, area_code VARCHAR(8) DEFAULT NULL COMMENT 区号, post_code VARCHAR(8) DEFAULT NULL COMMENT 邮编, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (prefix, carrier_code), KEY idx_prefix_region (prefix, region_code), KEY idx_region (region_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这段建表语句里有几个取舍。prefix用CHAR(7)而不是INT因为号段可能以 0 开头转成整数会丢前导零后面查询对不上。主键用(prefix, carrier_code)而不是单独prefix是给“同一号段多运营商编码”留余地导入去重时也不容易把正常数据顶掉。card_type单独留字段是因为转售号段和物联网号段在城市粒度上经常和普通号段不一样业务侧需要能过滤。updated_at不是装饰后面做增量更新和冲突排查要靠它判断哪条更新。行政区划表我一般会保持很薄CREATE TABLE t_region ( region_code CHAR(6) NOT NULL COMMENT 行政区划编码, province_name VARCHAR(32) NOT NULL COMMENT 省份名称, city_name VARCHAR(32) NOT NULL COMMENT 城市名称, PRIMARY KEY (region_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 实际数据里是行政区划名称这里用占位符演示字段关系 INSERT INTO t_region (region_code, province_name, city_name) VALUES (P00001, A省, B市), (P00002, C省, D市);号段表里只存region_code展示时再 JOINt_region。这样资源更新时如果只是地名展示调整改t_region就行如果号段归属变了才动号段表。很多二手库把省名、市名直接冗余在号段表里导入快但后面做增量对比时会出现“同一编码两种写法”清理起来很烦。我吃过这个亏后来宁可多一次 JOIN。2.2 为什么匹配前7位号段分配与11位手机号的边界11 位手机号可以粗分成三段前 3 位是网络识别号前 7 位是号段识别码后 4 位是用户编号。前 3 位只能粗到运营商级别往城市落不够前 7 位才能覆盖到本地网级别所以归属地库普遍按前 7 位建索引。查询时不要拿完整手机号去LIKE %xxx%也不要把后 4 位带进条件那样索引直接废掉。标准写法是先规范化手机号再取前 7 位等值匹配。SET phone 13800001234; SELECT p.prefix, r.province_name, r.city_name, p.carrier_code, p.card_type FROM t_phone_prefix p JOIN t_region r ON r.region_code p.region_code WHERE p.prefix LEFT(phone, 7);这里的LEFT(phone, 7)要求传入的是纯数字 11 位手机号。如果上游传进来带86、空格、短横线先清洗再进 SQL别在 SQL 里套多层REPLACE那样每次查询都要算一遍表达式批量查询时很吃亏。参数上prefix是定长 7 位LEFT返回的也是 7 位字符串类型一致才能走索引如果表里是CHAR(7)查询参数是整数MySQL 会做隐式转换索引可能失效这个后面第 4 章会展开。还有一个边界不是所有 11 位号码都能查到。新放号段、回收号段、转售号段、物联网号段二手库不一定同步得那么快。所以查询结果要允许为空业务侧不能把“查不到”当成“号码非法”。我一般会在应用层返回三个状态命中、未命中、号码格式不合法而不是只返回一个空字符串。2.3 数据粒度省级、市级、虚拟号段与物联网号段这份库说“非常全”落地时仍要看清粒度。号段表能落到市级当然好但有些号段只到省级有些转售号段只标了基础号段城市字段可能是空的。业务如果拿城市做风控要把city_name IS NULL的情况单独处理不能直接拿空值去匹配规则。下面这张表是我拆库时通常先核对的字段粒度字段/场景含义常见取值落地注意province_name归属省份行政区划名称展示用不参与索引等值city_name归属城市行政区划名称或空空值要允许不要强转carrier_code运营商编码0/1/2 等内部编码不要依赖外部名称card_type卡类型0 普通、1 转售、2 物联网转售号段城市可能不准area_code区号字符串有前导 0别存整数post_code邮编字符串同样别存整数转售号段和物联网号段是最容易翻车的地方。普通号段按前 7 位能稳定落到城市转售号段可能共用基础号段城市字段按基础号段落库实际使用地并不一致物联网号段更常见的是只标到省级甚至只标运营商。查询时如果业务不区分卡类型就会出现“城市对不上”的投诉。我的做法是号段表保留card_type查询接口增加一个可选参数include_virtual默认只查普通号段风控和客服场景再按需放开。-- 默认只查普通号段避免转售号段城市误判 SELECT p.prefix, r.province_name, r.city_name FROM t_phone_prefix p JOIN t_region r ON r.region_code p.region_code WHERE p.prefix LEFT(13800001234, 7) AND p.card_type 0;数据粒度还影响更新策略。省级粒度更新频率低市级粒度要跟着号段放号走转售和物联网号段最好单独维护一张小表别和普通号段混在一起全量覆盖。二手库通常给的是全量 SQL直接REPLACE INTO会把自定义的card_type覆盖掉后面第 6 章会讲怎么用新表对比再切换避免这种不可逆操作。3. 把SQL文件跑起来建库、导数据、写查询的完整流程表结构看明白后下一步是把它跑起来。我习惯先在测试实例建一个独立库字符集、排序规则、导入方式都按生产标准走一遍确认号段边界和中文展示没问题再考虑往业务库迁。直接拿生产库试LOAD DATA是血泪经验导入一半报错、字符集不对、主键冲突回滚都来不及。下面按建库、导入、查询、封装四步走。3.1 建库与字符集utf8mb4、排序规则和时区建库时字符集用utf8mb4不要用utf8。行政区划名称里可能有生僻字utf8三字节存不下导入时会出现截断或问号。排序规则用通用的utf8mb4_general_ci兼容性好如果只做等值查询排序规则对性能影响不大但会影响GROUP BY和ORDER BY的结果导入前统一好别中途改。CREATE DATABASE phone_region DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; USE phone_region; -- 会话级字符集也统一避免客户端和库不一致 SET NAMES utf8mb4;SET NAMES utf8mb4这行别省。很多乱码不是表的问题是客户端连接字符集是latin1或utf8导进去再查出来就花了。时区字段updated_at按业务时区设置归属地查询本身不涉及时区计算但增量更新对比时间时要保证导入端和查询端一致否则会出现“刚导的数据看起来比旧数据还旧”的玄学问题。建完库先跑一遍字符集检查SHOW VARIABLES LIKE character_set_%; SHOW VARIABLES LIKE collation_%;看character_set_client、character_set_connection、character_set_results是不是utf8mb4。如果客户端是utf8mb4、表也是utf8mb4导入还乱码再去查源文件编码别一上来就改表。3.2 用LOAD DATA导入百万级号段参数、日志与回滚导入前先确认local_infile开没开。LOAD DATA LOCAL INFILE是客户端读文件往服务端送服务端要允许本地导入客户端也要带参数。不同环境策略不一样先查再导。SHOW VARIABLES LIKE local_infile; SET GLOBAL local_infile 1;命令行导入时带上字符集和本地导入参数mysql -u root -p \ --default-character-setutf8mb4 \ --local-infile1 \ phone_region schema.sql mysql -u root -p \ --default-character-setutf8mb4 \ --local-infile1 \ phone_region data.sql如果数据是 CSV用LOAD DATA比一条条INSERT快得多。下面这条语句按 CSV 逗号分隔、双引号包裹、首行表头来写LOAD DATA LOCAL INFILE /data/phone_prefix.csv INTO TABLE t_phone_prefix CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (prefix, region_code, carrier_code, card_type, area_code, post_code) SET updated_at NOW();参数逐个数CHARACTER SET utf8mb4告诉服务端按什么编码解析文件FIELDS TERMINATED BY ,是列分隔符ENCLOSED BY 处理字段里带逗号的情况IGNORE 1 LINES跳过表头最后括号里的列顺序必须和 CSV 对齐少一列就会错位。SET updated_at NOW()让新导入的数据带时间戳方便后面做增量对比。导入后别只看“Query OK”跑一遍SHOW WARNINGS。如果出现 1366 字符集错误、1262 行截断、1062 主键冲突都要当场处理。大表导入我一般先灌到t_phone_prefix_new确认行数、抽样、重复号段都正常再用RENAME TABLE切换避免直接灌主表后回滚困难。LOAD DATA本身是单语句InnoDB 里可以包事务但百万行大事务会撑大 undo生产上不如新表切换稳。3.3 单号查询与批量查询的三条SQL单号查询最简单前面已经给过。这里补一条带规范化的写法把清洗放在应用层SQL 只负责匹配-- 入参必须是清洗后的11位纯数字 SET phone 13800001234; SELECT p.prefix, r.province_name, r.city_name, p.carrier_code, p.card_type FROM t_phone_prefix p JOIN t_region r ON r.region_code p.region_code WHERE p.prefix LEFT(phone, 7) LIMIT 1;LIMIT 1是给“同一号段多运营商编码”兜底。如果业务要求精确到运营商就不要LIMIT 1把carrier_code也带进条件。批量查询不要用几百个UNION ALL拼直接IN列表在少量前缀时够用SELECT p.prefix, r.province_name, r.city_name FROM t_phone_prefix p JOIN t_region r ON r.region_code p.region_code WHERE p.prefix IN (1380000, 1380001, 1390000);IN列表超过几百条后SQL 解析成本和网络包都会上来优化器也可能放弃索引。批量场景我一般用临时表 JOINCREATE TEMPORARY TABLE tmp_phone_prefix ( prefix CHAR(7) NOT NULL, PRIMARY KEY (prefix) ) ENGINEInnoDB; INSERT INTO tmp_phone_prefix (prefix) VALUES (1380000), (1380001), (1390000); SELECT t.prefix, r.province_name, r.city_name FROM tmp_phone_prefix t JOIN t_phone_prefix p ON p.prefix t.prefix JOIN t_region r ON r.region_code p.region_code;临时表把“一批前缀”变成一张有主键的小表优化器走主键等值 JOIN比长IN稳定。临时表只在当前会话可见连接池复用时记得用完DROP TEMPORARY TABLE或者让会话结束自动清理。批量导入临时表时prefix类型要和主表一致都是CHAR(7)别一边字符串一边整数。3.4 用存储过程封装一个查询入口如果业务方不想直接写 JOIN可以封一个存储过程把参数校验、前 7 位截取、空结果处理收在一处。存储过程不是必须但在多团队共用一个库时能减少“每个人写法不一样”的问题。DELIMITER $$ CREATE PROCEDURE sp_query_phone_region(IN p_phone CHAR(11)) BEGIN -- 只处理11位纯数字格式不对直接返回空结果 IF p_phone REGEXP ^[0-9]{11}$ THEN SELECT p.prefix, r.province_name, r.city_name, p.carrier_code, p.card_type FROM t_phone_prefix p JOIN t_region r ON r.region_code p.region_code WHERE p.prefix LEFT(p_phone, 7) LIMIT 1; ELSE SELECT NULL AS prefix, NULL AS province_name, NULL AS city_name, NULL AS carrier_code, NULL AS card_type; END IF; END$$ DELIMITER ;调用时CALL sp_query_phone_region(13800001234);。参数p_phone是CHAR(11)正则只允许 11 位数字避免把带86的脏数据传进来。返回空结果集而不是报错业务侧好处理。存储过程里用了LIMIT 1如果业务需要多运营商结果把LIMIT 1去掉让调用方按carrier_code选。注意存储过程不适合做高频批量查询批量还是走临时表 JOIN存储过程更适合单号查询入口。4. 查询性能与索引从全表扫描到覆盖索引的调优记录号段表数据量上去后查询慢通常不是 SQL 写错而是类型、索引顺序、批量写法三个地方出了问题。这一章按“字段类型 → 索引顺序 → 批量写法 → 压测对比”走一遍。下面这些数字来自模拟测试实例不是绝对基准但相对关系能说明问题。4.1 号段字段用CHAR(7)还是INT类型选择的三个约束号段前 7 位看起来像数字但它是编号不是数值。用INT有三个坑第一前导 0 会丢0123456存成123456查询时补零对不上第二手机号段长度固定 7 位CHAR(7)定长比较更直接INT还要考虑显示宽度第三应用层传参如果是字符串和INT比较会隐式转换索引可能失效。下面这张表是我选类型时核对的三项约束CHAR(7)INT前导 0保留丢失等值查询字符串比较稳定数值比较需转换与上游参数类型手机号截取后就是字符串要额外转整数索引大小定长 7 字节4 字节略小可读性号段原样展示需要补零数据量百万级时CHAR(7)的索引空间比INT大一点但归属地查询是等值命中这点空间换类型稳定值得。如果历史表已经用了INT查询时要么把参数转成整数要么改字段别在WHERE里对字段做函数运算比如WHERE CAST(prefix AS CHAR) ?那样索引直接失效。4.2 联合索引与最左前缀prefix、region_code、carrier_code的顺序号段表的主键是(prefix, carrier_code)另外建了idx_prefix_region (prefix, region_code)。查询条件里prefix等值命中时这两个索引都能用。联合索引的顺序按“等值条件在前、范围条件在后、覆盖列尽量带上”排。prefix是必带的等值条件放最左region_code用于 JOIN 和覆盖放第二carrier_code如果业务经常按运营商筛可以单独建索引不建议硬塞进idx_prefix_region打乱顺序。用EXPLAIN看执行计划EXPLAIN SELECT p.prefix, r.province_name, r.city_name FROM t_phone_prefix p JOIN t_region r ON r.region_code p.region_code WHERE p.prefix 1380000;重点看type是不是ref或eq_refkey是不是走了idx_prefix_region或主键rows是不是接近 1Extra里有没有Using index。如果type是ALL说明全表扫了先查参数类型和字段类型是否一致。如果Extra出现Using where且rows很大可能是prefix条件没命中索引或者 JOIN 顺序被优化器选反了。覆盖索引的写法是把查询需要的列都放在索引里减少回表。比如只查prefix、region_code、carrier_codeidx_prefix_region能覆盖如果要查province_name、city_name还是要 JOINt_region这次 JOIN 走t_region主键代价很低。不要为了省一次 JOIN 把省名市名冗余进号段表后面更新和去重会更麻烦。4.3 批量查询的两种写法IN列表与临时表JOIN批量查询先看量。几十条用IN没问题几百条以上我倾向临时表。IN列表太长时SQL 文本大、解析慢优化器可能估算走全表更便宜结果反而更慢。临时表把批量数据落到一张有主键的小表JOIN 时走等值匹配执行计划稳定。-- 不推荐几千个前缀直接塞IN SELECT prefix, region_code FROM t_phone_prefix WHERE prefix IN (1380000, 1380001, 1380002 /* ...很多... */); -- 推荐分批进临时表再JOIN CREATE TEMPORARY TABLE tmp_batch ( prefix CHAR(7) NOT NULL, PRIMARY KEY (prefix) ) ENGINEInnoDB; INSERT INTO tmp_batch (prefix) VALUES (1380000), (1380001), (1380002); SELECT p.prefix, p.region_code, p.carrier_code FROM tmp_batch t JOIN t_phone_prefix p ON p.prefix t.prefix;临时表方案要注意两点批量插入时每批 500 到 1000 条别一次塞几万条撑大事务临时表用完DROP TEMPORARY TABLE连接池里会话复用不会串数据。如果批量查询来自文件可以先把文件导成 CSV用LOAD DATA灌临时表再 JOIN比应用层拼 SQL 稳。4.4 压测对比模拟10万次查询的耗时表我在测试实例里用模拟号段数据跑了一组对比环境是 4 核 8G数据量为号段表行数查询走主键等值不看网络延迟。下面数字是相对耗时用来判断趋势号段表行数单号查询IN 100 条IN 1000 条临时表 JOIN 1000 条10 万0.8 ms1.2 ms6 ms2 ms100 万1.1 ms1.8 ms15 ms3 ms500 万1.5 ms2.5 ms35 ms4 ms单号查询在百万级仍然很稳因为等值命中主键或二级索引回表行数少。IN 1000的耗时随数据量上升明显主要是解析和优化器估算成本临时表 JOIN 把批量前缀变成一张小表耗时增长平缓。实际业务里如果 QPS 高单号查询可以加一层应用缓存但缓存 key 要用前 7 位不要用完整手机号避免缓存里存明文。批量查询不要和单号查询抢同一个连接池临时表会话最好单独走一个短连接池。5. 避坑与排查导入乱码、号段冲突、虚拟号段误判的5个现场这一章是我拆这类库时最常遇到的五个现场每个都按“现象 → 原因 → 解决”写。症状看起来像玄学拆开都是字符集、类型、主键、批量写法、卡类型这五件事。5.1 现象查询结果为空手机号带86或空格现象明明号段表里有1380000传入86 138-0000-1234查不到传13800001234就能查到。原因SQL 里直接LEFT(phone, 7)前 7 位取到了86 138和号段表对不上。解决应用层先规范化去掉非数字、去掉86前缀、校验 11 位再进 SQL。不要在 SQL 里套多层REPLACE批量查询时表达式会让索引失效。import re def normalize_phone(raw: str) - str: # 去掉所有非数字字符 digits re.sub(r\D, , raw or ) # 处理带86前缀的13位输入 if digits.startswith(86) and len(digits) 13: digits digits[2:] # 只接受11位纯数字 return digits if len(digits) 11 else 参数说明re.sub(r\D, , raw)去掉空格、短横线、加号startswith(86)只处理 13 位避免把正常 11 位里以 86 开头的号码误切返回空字符串表示格式不合法业务侧要区分“格式错”和“查不到”。这个函数放在入口处后面所有查询都走它。5.2 现象导入后中文乱码SHOW WARNINGS一堆1366现象导入成功但province_name显示问号或乱码SHOW WARNINGS里有 1366 Incorrect string value。原因CSV 文件是 GBK 编码客户端连接字符集是latin1或utf8表是utf8mb4三层不一致。解决先看文件编码file -i phone_prefix.csv如果是 GBK先转 UTF-8 再导或者导入会话统一SET NAMES utf8mb4命令行加--default-character-setutf8mb4。# 先确认文件编码 file -i phone_prefix.csv # 如果是GBK转成UTF-8再导入 iconv -f GBK -t UTF-8 phone_prefix.csv -o phone_prefix_utf8.csv转换后再跑LOAD DATA并在导入前执行SET NAMES utf8mb4;。如果还乱码查SHOW VARIABLES LIKE character_set_%看character_set_client、character_set_connection、character_set_results是不是一致。不要直接改表字符集先定位是文件、连接还是表的问题。5.3 现象同一号段查到两条不同归属地现象查1380000返回两行city_name不一样。原因数据版本叠加多个来源合并时没去重或者主键只建了prefix导入用了REPLACE INTO后一条把前一条顶掉但查询时又关联出旧行政区划。解决主键用(prefix, carrier_code)导入前先查重复SELECT prefix, COUNT(*) AS cnt FROM t_phone_prefix GROUP BY prefix HAVING cnt 1 ORDER BY cnt DESC LIMIT 20;如果有重复按updated_at保留最新或者按carrier_code区分。查询时如果业务只要一条加ORDER BY updated_at DESC LIMIT 1如果要精确运营商把carrier_code带进条件。不要用DISTINCT掩盖问题重复数据还在后面批量查询会放大。5.4 现象IN列表超过2000条后查询突然变慢现象IN100 条很快IN2000 条突然慢到几百毫秒。原因SQL 文本变大解析成本上升优化器估算走全表更便宜如果prefix是CHAR而参数是整数还会隐式转换。解决批量超过 500 条改临时表 JOIN分批插入确保参数类型和字段类型一致用EXPLAIN看type是不是变成ALL。-- 分批插入临时表每批500条 CREATE TEMPORARY TABLE tmp_batch ( prefix CHAR(7) NOT NULL, PRIMARY KEY (prefix) ) ENGINEInnoDB; INSERT INTO tmp_batch (prefix) VALUES (1380000), (1380001), (1380002); SELECT p.prefix, p.region_code FROM tmp_batch t JOIN t_phone_prefix p ON p.prefix t.prefix;临时表也要注意字符集和字段类型一致prefix都用CHAR(7)。如果批量数据来自不同连接临时表只在当前会话有效别跨连接查。5.5 现象虚拟号段城市对不上业务侧投诉现象某些号段查出来城市是 A 市用户实际在 B 市客服反馈“归属地不准”。原因转售号段或物联网号段共用基础号段号段表按基础号段落库城市字段不代表实际使用地或者card_type没维护查询时没过滤。解决号段表保留card_type查询接口默认只查普通号段转售和物联网单独维护业务侧把归属地当“参考值”不要当“实时位置”。-- 按卡类型过滤普通号段才返回城市级结果 SELECT p.prefix, r.province_name, r.city_name, p.card_type FROM t_phone_prefix p JOIN t_region r ON r.region_code p.region_code WHERE p.prefix LEFT(13800001234, 7) AND p.card_type 0;如果业务必须查转售号段返回省级结果并标记 precision 本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
