简介这份压缩包面向需要手机号归属地查询与数据分析的开发者、数据库学习者及营销客服等业务人员提供以 MySQL 存储的手机号与省份、城市关联数据可用于单号码查询、批量统计与 SQL 练习。包内共 1 个文件为 SQL 数据脚本压缩包约 2.22MB导入 MySQL 后即可按号码字段检索归属地也可结合分组、聚合等语句做区域分布分析。已有 270 人学习说明该数据集在同类资料中具备一定参考价值。读者可据此练习建表、导入、条件查询、GROUP BY 统计等操作并理解手机号归属地数据的字段组织方式涉及个人信息时还应关注合法合规使用、权限控制与数据安全适合作为数据库查询与敏感数据处理的实践素材。1. 手机号归属地查询真正难的是号段表不是那条 SQL注册页填完 11 位手机号前端要立刻带出省市订单分拣要按区域落仓风控要判断号码前缀是否异常——这些场景都指向同一件事用手机号归属地做一次快速映射。很多人第一反应是建个 MySQL 表存手机号前 7 位和省市查询时LEFT(phone,7)一匹配就完事。真到线上会发现慢查询、号段冲突、新号段查不到、中文乱码轮番出现。这份资源是一套按 MySQL 组织好的手机号归属地号段库核心价值不是 SQL 写法多花哨而是号段粒度和字段结构已经替你压过一轮。适合做用户注册、订单地址补全、数据分拣的开发者也适合需要离线维护号段表的运维。下面按建表、导入、查询、排坑、进阶验证的顺序拆。2. 号段表怎么建从 11 位手机号拆出 7 位前缀2.1 先定匹配粒度7 位前缀是底线6 位会误判手机号是 11 位前 3 位承担网络识别前 7 位才是号段归属的核心。把前缀截到 6 位省市区会出现大面积串号截到 8 位表行数膨胀命中率却提升有限。常见做法是固定 7 位前缀查询时只取前 7 位做等值匹配。这里有个反直觉点号段表不是越细越好粒度要和数据源对齐。资源里的号段表按 7 位组织字段上留了source和updated_at就是给后面多来源合并留退路。虚拟号段和转网号码会让“一个前缀只对应一个城市”的假设松动。我的处理方式是主表仍然以prefix做主键把服务商代号单独放isp字段遇到同前缀多城市的数据先落临时表按来源优先级和更新时间做合并。这样查询侧永远是一次主键等值查找复杂度留在导入阶段不留给线上。2.2 主表字段与索引别把省市区塞进一个字段表结构我一般拆成prefix、province、city、isp、area_code、post_code、status、source、updated_at这几个字段。省和市分开存后面做区域统计、城市补全、地址回填都方便area_code和post_code用字符串不要用整数前导零在整数类型里会被吃掉。status用来软停用问题号段比直接删行安全。索引不要贪多。主键prefix已经覆盖单条查询额外加(province, city)是给后台按区域筛选用的updated_at是给增量同步扫尾用的。字符集统一utf8mb4排序规则跟库一致别在表级、列级、连接级各写一套否则导入时中文很容易翻车。字段类型说明prefixchar(7)手机号前 7 位主键示例数据为虚构号段provincevarchar(32)省份名称示例值不指向真实地区cityvarchar(32)城市名称示例值不指向真实地区ispvarchar(16)服务商代号用 A/B/C 这类代号area_codevarchar(8)行政区划代码字符串存前导零post_codevarchar(8)邮政编码字符串存前导零statustinyint1 有效0 停用sourcevarchar(16)数据来源标记方便回溯updated_atdatetime更新时间增量同步看这一列2.3 建表 SQL 与示例数据主键、字符集和检查约束CREATE DATABASE IF NOT EXISTS phone_geo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; USE phone_geo; CREATE TABLE phone_location ( prefix char(7) NOT NULL COMMENT 手机号前7位示例数据为虚构号段, province varchar(32) NOT NULL DEFAULT COMMENT 省份名称示例值, city varchar(32) NOT NULL DEFAULT COMMENT 城市名称示例值, isp varchar(16) NOT NULL DEFAULT COMMENT 服务商代号, area_code varchar(8) NOT NULL DEFAULT COMMENT 行政区划代码示例, post_code varchar(8) NOT NULL DEFAULT COMMENT 邮政编码示例, status tinyint NOT NULL DEFAULT 1 COMMENT 1有效 0停用, source varchar(16) NOT NULL DEFAULT seed COMMENT 数据来源标记, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (prefix), KEY idx_province_city (province, city), KEY idx_isp (isp), KEY idx_updated_at (updated_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT手机号归属地号段表; ALTER TABLE phone_location ADD CONSTRAINT chk_prefix_len CHECK (CHAR_LENGTH(prefix) 7); ALTER TABLE phone_location ADD CONSTRAINT chk_status CHECK (status IN (0, 1)); INSERT INTO phone_location (prefix, province, city, isp, area_code, post_code, status, source) VALUES (1000000, 示例省, 示例市, 服务商A, 000000, 000000, 1, seed), (1000001, 示例省, 样例市, 服务商B, 000001, 000001, 1, seed), (1000002, 样例省, 示例市, 服务商A, 000002, 000002, 1, seed), (1000003, 样例省, 样例市, 服务商C, 000003, 000003, 1, seed), (1234567, 示例省, 示例市, 服务商B, 000004, 000004, 1, seed) ON DUPLICATE KEY UPDATE province VALUES(province), city VALUES(city), isp VALUES(isp), area_code VALUES(area_code), post_code VALUES(post_code), status VALUES(status), source VALUES(source), updated_at NOW();这段 SQL 做了四件事建库时锁定utf8mb4建表时把prefix定为主键补两个检查约束拦住长度和状态异常最后用ON DUPLICATE KEY UPDATE让示例数据可以重复执行。prefix用char(7)而不是整数是因为号段前缀要参与等值匹配和字符串截取整数会把前导零和比较规则搅在一起。province、city不设联合主键是因为同一城市会对应多个前缀联合主键会让导入去重变复杂。参数上重点看三个CHAR_LENGTH(prefix) 7拦住脏号段status IN (0,1)拦住批量脚本写错状态updated_at带ON UPDATE CURRENT_TIMESTAMP后面做增量同步不用手工维护时间。示例里的号段和省市名都是虚构值目的只是验证匹配链路不对应任何真实号码或地区。查询单条时这样写SET phone 10000001234; SELECT province, city, isp, area_code FROM phone_location WHERE prefix SUBSTRING(phone, 1, 7) AND status 1 LIMIT 1;这段查询的关键在SUBSTRING(phone, 1, 7)只对常量做一次截取不会对表字段做函数运算主键等值查找能正常走。LIMIT 1是兜底正常主键命中只有一行status 1把停用号段挡在业务外。参数说明phone是 11 位手机号示例号码也是虚构的如果走应用层传参最好在应用层先截好 7 位再传进来别在 SQL 里写LEFT(?, 7)参数化查询里对占位符做函数优化器不一定能折叠。3. 把 CSV 灌进 MySQLLOAD DATA 的字段映射与字符集3.1 资源包文件对齐CSV 列顺序与表字段映射拿到 CSV 先别急着导先对齐列。资源里的文本一般按prefix,province,city,isp,area_code,post_code,status,source排列但不同批次的列顺序可能变表头也可能带 BOM。导入前用文本编辑器看第一行确认分隔符是逗号还是制表符确认换行是\n还是\r\n。如果 CSV 里有城市名带逗号字段必须用双引号包住LOAD DATA的OPTIONALLY ENCLOSED BY 就是干这个的。CSV 列表字段类型注意prefixprefixchar(7)必须是 7 位前导零不能丢provinceprovincevarchar(32)中文编码要和连接字符集一致citycityvarchar(32)空值给默认空串别给 NULLispispvarchar(16)用代号不要写真实服务商全称area_codearea_codevarchar(8)字符串存避免前导零丢失post_codepost_codevarchar(8)同上statusstatustinyint只允许 0 或 1sourcesourcevarchar(16)标记批次方便回滚我一般会先拿 100 行做小批量试导跑一遍SELECT COUNT(*)和SELECT * LIMIT 5确认中文没变问号、号段长度没被截断再放全量。这个习惯看着慢实际能省掉后面重新洗数据的几个小时。3.2 LOAD DATA 导入脚本先设字符集再分批提交mysql --local-infile1 -u root -p phone_geo SQL SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS 0; LOAD DATA LOCAL INFILE /data/import/phone_location.csv INTO TABLE phone_location CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (prefix, province, city, isp, area_code, post_code, status, source) SET updated_at NOW(); SET FOREIGN_KEY_CHECKS 1; SHOW WARNINGS; SQL这段脚本先开local_infile让客户端能把本地 CSV 推给 MySQLSET NAMES utf8mb4把连接字符集锁定避免客户端默认字符集和表字符集打架LOAD DATA LOCAL INFILE指定文件路径CHARACTER SET utf8mb4明确文件编码FIELDS TERMINATED BY ,和OPTIONALLY ENCLOSED BY 处理逗号分隔和引号包裹IGNORE 1 LINES跳过表头最后SET updated_at NOW()给本批数据打时间戳。SHOW WARNINGS必须保留截断、空值、类型转换的问题都会在这里冒出来。参数说明文件路径/data/import/phone_location.csv是示例路径实际换成自己的目录--local-infile1只对当前连接生效不要图省事写进全局配置FOREIGN_KEY_CHECKS 0在单表导入里不是必须但批量脚本里常用来避免外键检查拖慢速度导完记得改回 1。数据量大时按 5 万到 10 万行切一个文件分批提交别一个 500MB 的 CSV 硬灌回滚和排错都难受。3.3 去重与补全临时表加 ON DUPLICATE KEY UPDATE全量 CSV 直接往主表灌遇到重复前缀会报主键冲突用REPLACE INTO又会把旧行删掉重插历史来源和更新时间全丢。稳妥做法是先落临时表再合并。CREATE TABLE phone_location_stage LIKE phone_location; TRUNCATE TABLE phone_location_stage; LOAD DATA LOCAL INFILE /data/import/phone_location_stage.csv INTO TABLE phone_location_stage CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (prefix, province, city, isp, area_code, post_code, status, source) SET updated_at NOW(); INSERT INTO phone_location (prefix, province, city, isp, area_code, post_code, status, source, updated_at) SELECT prefix, province, city, isp, area_code, post_code, status, source, NOW() FROM phone_location_stage ON DUPLICATE KEY UPDATE province VALUES(province), city VALUES(city), isp VALUES(isp), area_code VALUES(area_code), post_code VALUES(post_code), status VALUES(status), source VALUES(source), updated_at NOW(); SELECT COUNT(*) AS stage_rows FROM phone_location_stage; SELECT COUNT(*) AS main_rows FROM phone_location;这段 SQL 用CREATE TABLE ... LIKE复制出同结构临时表TRUNCATE清空后灌入新批次再用INSERT ... SELECT ... ON DUPLICATE KEY UPDATE做合并。重复前缀不会报错会按新批次的值更新省市、服务商和状态source记录本批来源updated_at刷新时间。VALUES()在新版本里有弃用提示老项目仍常见如果环境支持行别名可以改成AS new再引用new.province语义一样。参数说明临时表名phone_location_stage可以按批次加后缀方便出问题回查TRUNCATE比DELETE快但会重置自增本表没有自增列影响不大合并前先跑SELECT prefix, COUNT(*) FROM phone_location_stage GROUP BY prefix HAVING COUNT(*) 1看同批次内部有没有重复内部重复不先处理合并结果会取决于扫描顺序容易变成玄学。4. 查询侧怎么写JOIN 前缀表比 LIKE 快在哪4.1 单条查询应用层截前缀别在 SQL 里对参数做函数单条查询最常见的翻车写法是WHERE phone LIKE CONCAT(prefix, %)或者WHERE LEFT(phone, 7) prefix。前者把手机号整列拿来做前缀匹配后者对字段做函数索引都用不上。正确姿势是反过来先把手机号截成 7 位再和prefix做主键等值匹配。SELECT province, city, isp FROM phone_location WHERE prefix 1000000 AND status 1 LIMIT 1;这条查询的prefix是常量主键等值查找EXPLAIN里type应该是constkey是PRIMARYrows是 1。status 1是额外过滤因为主键已经定位到一行回表后判断一下状态成本可以忽略。参数说明prefix传 7 位字符串不要传整数如果业务侧拿到的是完整手机号在应用层做phone[:7]不要把截取逻辑塞进 SQL 的占位符里。批量查询时这样写SELECT u.id, p.province, p.city, p.isp FROM user_profile u LEFT JOIN phone_location p ON p.prefix LEFT(u.phone, 7) AND p.status 1 WHERE u.id IN (1001, 1002, 1003);这段 SQL 把LEFT(u.phone, 7)放在连接条件里p.prefix是主键优化器可以对p表做ref查找。注意LEFT(u.phone, 7)仍然是对u表字段做函数u表如果很大驱动表扫描成本还是存在。更稳的做法是给user_profile加生成列把前缀物化出来连接条件变成p.prefix u.phone_prefix两边都是等值执行计划更干净。生成列的写法放在最后一章。4.2 EXPLAIN 看三列type、key、rows查询写完别凭感觉跑EXPLAIN。我只看三列type是不是const或refkey是不是PRIMARYrows是不是接近 1。如果type是ALL说明在扫全表如果key是NULL说明索引没命中如果rows是几千说明条件写松了。EXPLAIN SELECT province, city, isp FROM phone_location WHERE prefix 1000000 AND status 1 LIMIT 1;预期结果里type constkey PRIMARYrows 1Extra里可能带Using where。如果看到Using filesort或Using temporary说明查询里混进了排序或分组单条归属地查询不该出现这些。参数说明EXPLAIN不会真正执行查询可以放心在生产低峰跑EXPLAIN FORMATJSON信息更全但日常排查看表格版就够了。覆盖索引这块要算清楚。主键prefix定位到行后province、city、isp不在主键索引里需要回表一次。单条查询回表一次无所谓批量查询如果每次都要回表成本会放大。可以建一个(prefix, status, province, city, isp)的二级索引让查询只读索引。但这会增加写入和存储成本号段表更新频率低读多写少建覆盖索引是划算的。我的习惯是先在测试库对比EXPLAIN的Extra有没有Using index再决定要不要加。4.3 高频查询的缓存与预热先算命中率再上缓存归属地查询是典型的读多写少、结果稳定。单条走 MySQL 主键已经很快但注册高峰每秒几千次查询全打到数据库也没必要。缓存 key 用phone:geo:{prefix}value 存省市和服务商TTL 设 1 小时到 24 小时都行。重点是空值也要缓存不然不存在的号段会反复穿透到数据库。def get_location(phone: str): prefix phone[:7] key fphone:geo:{prefix} cached cache.get(key) if cached is not None: return cached row db.query( SELECT province, city, isp FROM phone_location WHERE prefix %s AND status 1 LIMIT 1, (prefix,) ) value row if row else {province: , city: , isp: } ttl 3600 if row else 300 cache.setex(key, ttl, value) return value这段代码先截 7 位前缀拼缓存 key命中直接返回未命中查 MySQL查到就缓存 1 小时查不到缓存 5 分钟空值防止恶意号码或新号段打穿。参数说明prefix必须是 7 位字符串ttl按业务容忍度调号段表更新后要主动删对应 key别等自然过期空值缓存时间不要设太长新号段上线后要能较快恢复。缓存预热放在号段表更新之后做。把高频前缀跑一遍SELECT prefix FROM phone_location WHERE status 1 ORDER BY updated_at DESC LIMIT 10000批量查出来塞缓存。别在启动时全量预热号段表几十万行启动会拖慢。命中率怎么算在查询入口埋点统计cache_hit / total低于 80% 就先查 key 设计是不是漏了前导零或者 TTL 太短。5. 避坑与排查号段冲突、字符集乱码和更新断层5.1 同一个前缀查出两个城市号段冲突不是脏数据现象同一个 7 位前缀导入后在临时表里出现两行一行是示例市一行是样例市合并后线上查出来的城市来回跳。原因通常有三个多批数据源合并时没有定优先级虚拟号段本身跨区域号码转网后服务商变了但归属地表还留着旧记录。很多人第一反应是删一行删完发现另一批业务查不到了。解决分三步。第一在临时表阶段就查冲突SELECT prefix, COUNT(*) c FROM phone_location_stage GROUP BY prefix HAVING c 1把冲突号段捞出来。第二给数据源定优先级比如新批次覆盖旧批次、高置信来源覆盖低置信来源合并时用ORDER BY source_priority DESC, updated_at DESC取第一条。第三主表保留source和updated_at查询侧默认取status 1且updated_at最新的一条如果业务要求严格唯一就在合并前把冲突号段人工确认别让脚本自动猜。我的血泪经验是冲突号段不要用REPLACE INTO一把梭。REPLACE会先删后插旧行的来源和时间全没了后面想回溯是哪批数据覆盖的都查不到。用ON DUPLICATE KEY UPDATE至少能保留一行再配合版本表出问题还能翻旧账。5.2 导入后中文变问号字符集三层没对齐现象CSV 在编辑器里看是正常中文导进 MySQL 后SELECT出来全是???或乱码。原因几乎都是三层字符集没对齐CSV 文件本身是 GBK客户端连接用的是默认字符集表或列又是latin1。三层里有一层不对中文就会翻车。解决顺序是从文件到连接再到表。先用file -i /data/import/phone_location.csv看文件编码如果是iso-8859-1或gbk先转成 UTF-8iconv -f GBK -t UTF-8 phone_location.csv -o phone_location_utf8.csv。导入脚本里SET NAMES utf8mb4LOAD DATA里再写CHARACTER SET utf8mb4两处都写别只写一处。最后检查表和列的字符集SHOW CREATE TABLE phone_location看到DEFAULT CHARSETutf8mb4才算齐。如果列级字符集是latin1用ALTER TABLE ... MODIFY province varchar(32) CHARACTER SET utf8mb4改过来再重新导入。注意改字符集之前先备份。已经有乱码的数据改字符集救不回来只能重新导。导入后跑SELECT HEX(province) FROM phone_location LIMIT 1看十六进制是不是 UTF-8 的中文编码比肉眼判断靠谱。5.3 新号段查不到更新断层与缓存旧值现象业务反馈新放号的号段查不到归属地但数据库里明明有这批前缀。原因一般两条增量脚本只追加新前缀没有处理旧前缀的状态变更缓存里的空值还没过期查询一直命中旧缓存。还有一种是号段表更新了但缓存 key 没删读到的还是旧城市。解决增量更新按updated_at拉取不要只按prefix是否存在判断。新批次里如果某个前缀从有效变停用要更新status不是跳过。缓存侧在更新完成后按前缀批量删 key删完再预热高频前缀。监控上加一个“未命中率”指标连续升高就说明号段表该更新了。我的习惯是每次更新号段表后跑一遍抽样校验从用户表随机取 1000 个手机号LEFT JOIN号段表看未命中数量超过阈值就先别发布。5.4 查询突然变慢前缀字段被函数包住或类型隐式转换现象同一条查询之前 1 毫秒某天开始变成几百毫秒EXPLAIN显示type ALL。原因常见两种SQL 里写了WHERE LEFT(phone, 7) prefix对字段做函数索引失效或者prefix是char(7)传参传了整数MySQL 做隐式转换索引也用不上。解决先把查询改成WHERE prefix ?参数在应用层截好再用EXPLAIN确认key PRIMARY。如果是类型问题检查应用层传参是不是把1000000写成了1000000char和整数比较会触发转换。批量 JOIN 场景给用户表加生成列把LEFT(phone, 7)物化成phone_prefix连接条件变成等值匹配。改完压测一轮别只看单条。6. 进阶用法把归属地查询压到 1 毫秒内并做版本校验6.1 用生成列把批量 JOIN 压成等值匹配批量查询里LEFT(u.phone, 7)虽然能走p表主键但u表字段被函数包住驱动表扫描成本下不来。给用户表加一个存储生成列把前缀物化连接条件变成两边等值ALTER TABLE user_profile ADD COLUMN phone_prefix char(7) GENERATED ALWAYS AS (LEFT(phone, 7)) STORED, ADD INDEX idx_phone_prefix (phone_prefix); SELECT u.id, p.province, p.city, p.isp FROM user_profile u LEFT JOIN phone_location p ON p.prefix u.phone_prefix AND p.status 1 WHERE u.id BETWEEN 1001 AND 2000;STORED生成列会实际占存储写入时自动算好查询时直接读VIRTUAL不占存储但索引支持和版本有关号段查询场景用STORED更稳。加列时大表会锁表走在线 DDL 工具或低峰执行。参数说明phone必须是 11 位字符串LEFT(phone, 7)结果不足 7 位时生成列会存短值导入前先用CHECK约束拦住。6.2 版本表与覆盖率校验给数据留后悔药号段表每次更新都覆盖主表出问题想回滚只能重新导。加一张版本表发布前把当前主表快照进去查询侧默认读主表排查时按版本号查历史CREATE TABLE phone_location_version ( version varchar(16) NOT NULL, prefix char(7) NOT NULL, province varchar(32) NOT NULL, city varchar(32) NOT NULL, isp varchar(16) NOT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (version, prefix) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO phone_location_version SELECT 20250101, prefix, province, city, isp FROM phone_location;校验跑三条 SQL总行数和唯一前缀数看有没有重复冲突检查看同前缀多行覆盖率抽样看用户表有多少手机号匹配不上。SELECT COUNT(*) AS total, COUNT(DISTINCT prefix) AS uniq_prefix FROM phone_location; SELECT prefix, COUNT(*) AS c FROM phone_location GROUP BY prefix HAVING c 1; SELECT COUNT(*) AS miss FROM user_profile u LEFT JOIN phone_location p ON p.prefix LEFT(u.phone, 7) WHERE p.prefix IS NULL;total和uniq_prefix应该相等不等就是主键失效或数据重复冲突查询返回空才发布miss占比超过业务阈值就先补号段。版本号用日期串只是示例换成自己的发布批次也行。从那以后我每次导完库都会先跑覆盖率、冲突、字符集三项检查再让业务接。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/11/4 20:21:47 · 阅读 1.2k 手机号归属地查询：MySQL号段表设计与导入查询优化
简介这份压缩包面向需要手机号归属地查询与数据分析的开发者、数据库学习者及营销客服等业务人员提供以 MySQL 存储的手机号与省份、城市关联数据可用于单号码查询、批量统计与 SQL 练习。包内共 1 个文件为 SQL 数据脚本压缩包约 2.22MB导入 MySQL 后即可按号码字段检索归属地也可结合分组、聚合等语句做区域分布分析。已有 270 人学习说明该数据集在同类资料中具备一定参考价值。读者可据此练习建表、导入、条件查询、GROUP BY 统计等操作并理解手机号归属地数据的字段组织方式涉及个人信息时还应关注合法合规使用、权限控制与数据安全适合作为数据库查询与敏感数据处理的实践素材。1. 手机号归属地查询真正难的是号段表不是那条 SQL注册页填完 11 位手机号前端要立刻带出省市订单分拣要按区域落仓风控要判断号码前缀是否异常——这些场景都指向同一件事用手机号归属地做一次快速映射。很多人第一反应是建个 MySQL 表存手机号前 7 位和省市查询时LEFT(phone,7)一匹配就完事。真到线上会发现慢查询、号段冲突、新号段查不到、中文乱码轮番出现。这份资源是一套按 MySQL 组织好的手机号归属地号段库核心价值不是 SQL 写法多花哨而是号段粒度和字段结构已经替你压过一轮。适合做用户注册、订单地址补全、数据分拣的开发者也适合需要离线维护号段表的运维。下面按建表、导入、查询、排坑、进阶验证的顺序拆。2. 号段表怎么建从 11 位手机号拆出 7 位前缀2.1 先定匹配粒度7 位前缀是底线6 位会误判手机号是 11 位前 3 位承担网络识别前 7 位才是号段归属的核心。把前缀截到 6 位省市区会出现大面积串号截到 8 位表行数膨胀命中率却提升有限。常见做法是固定 7 位前缀查询时只取前 7 位做等值匹配。这里有个反直觉点号段表不是越细越好粒度要和数据源对齐。资源里的号段表按 7 位组织字段上留了source和updated_at就是给后面多来源合并留退路。虚拟号段和转网号码会让“一个前缀只对应一个城市”的假设松动。我的处理方式是主表仍然以prefix做主键把服务商代号单独放isp字段遇到同前缀多城市的数据先落临时表按来源优先级和更新时间做合并。这样查询侧永远是一次主键等值查找复杂度留在导入阶段不留给线上。2.2 主表字段与索引别把省市区塞进一个字段表结构我一般拆成prefix、province、city、isp、area_code、post_code、status、source、updated_at这几个字段。省和市分开存后面做区域统计、城市补全、地址回填都方便area_code和post_code用字符串不要用整数前导零在整数类型里会被吃掉。status用来软停用问题号段比直接删行安全。索引不要贪多。主键prefix已经覆盖单条查询额外加(province, city)是给后台按区域筛选用的updated_at是给增量同步扫尾用的。字符集统一utf8mb4排序规则跟库一致别在表级、列级、连接级各写一套否则导入时中文很容易翻车。字段类型说明prefixchar(7)手机号前 7 位主键示例数据为虚构号段provincevarchar(32)省份名称示例值不指向真实地区cityvarchar(32)城市名称示例值不指向真实地区ispvarchar(16)服务商代号用 A/B/C 这类代号area_codevarchar(8)行政区划代码字符串存前导零post_codevarchar(8)邮政编码字符串存前导零statustinyint1 有效0 停用sourcevarchar(16)数据来源标记方便回溯updated_atdatetime更新时间增量同步看这一列2.3 建表 SQL 与示例数据主键、字符集和检查约束CREATE DATABASE IF NOT EXISTS phone_geo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; USE phone_geo; CREATE TABLE phone_location ( prefix char(7) NOT NULL COMMENT 手机号前7位示例数据为虚构号段, province varchar(32) NOT NULL DEFAULT COMMENT 省份名称示例值, city varchar(32) NOT NULL DEFAULT COMMENT 城市名称示例值, isp varchar(16) NOT NULL DEFAULT COMMENT 服务商代号, area_code varchar(8) NOT NULL DEFAULT COMMENT 行政区划代码示例, post_code varchar(8) NOT NULL DEFAULT COMMENT 邮政编码示例, status tinyint NOT NULL DEFAULT 1 COMMENT 1有效 0停用, source varchar(16) NOT NULL DEFAULT seed COMMENT 数据来源标记, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (prefix), KEY idx_province_city (province, city), KEY idx_isp (isp), KEY idx_updated_at (updated_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT手机号归属地号段表; ALTER TABLE phone_location ADD CONSTRAINT chk_prefix_len CHECK (CHAR_LENGTH(prefix) 7); ALTER TABLE phone_location ADD CONSTRAINT chk_status CHECK (status IN (0, 1)); INSERT INTO phone_location (prefix, province, city, isp, area_code, post_code, status, source) VALUES (1000000, 示例省, 示例市, 服务商A, 000000, 000000, 1, seed), (1000001, 示例省, 样例市, 服务商B, 000001, 000001, 1, seed), (1000002, 样例省, 示例市, 服务商A, 000002, 000002, 1, seed), (1000003, 样例省, 样例市, 服务商C, 000003, 000003, 1, seed), (1234567, 示例省, 示例市, 服务商B, 000004, 000004, 1, seed) ON DUPLICATE KEY UPDATE province VALUES(province), city VALUES(city), isp VALUES(isp), area_code VALUES(area_code), post_code VALUES(post_code), status VALUES(status), source VALUES(source), updated_at NOW();这段 SQL 做了四件事建库时锁定utf8mb4建表时把prefix定为主键补两个检查约束拦住长度和状态异常最后用ON DUPLICATE KEY UPDATE让示例数据可以重复执行。prefix用char(7)而不是整数是因为号段前缀要参与等值匹配和字符串截取整数会把前导零和比较规则搅在一起。province、city不设联合主键是因为同一城市会对应多个前缀联合主键会让导入去重变复杂。参数上重点看三个CHAR_LENGTH(prefix) 7拦住脏号段status IN (0,1)拦住批量脚本写错状态updated_at带ON UPDATE CURRENT_TIMESTAMP后面做增量同步不用手工维护时间。示例里的号段和省市名都是虚构值目的只是验证匹配链路不对应任何真实号码或地区。查询单条时这样写SET phone 10000001234; SELECT province, city, isp, area_code FROM phone_location WHERE prefix SUBSTRING(phone, 1, 7) AND status 1 LIMIT 1;这段查询的关键在SUBSTRING(phone, 1, 7)只对常量做一次截取不会对表字段做函数运算主键等值查找能正常走。LIMIT 1是兜底正常主键命中只有一行status 1把停用号段挡在业务外。参数说明phone是 11 位手机号示例号码也是虚构的如果走应用层传参最好在应用层先截好 7 位再传进来别在 SQL 里写LEFT(?, 7)参数化查询里对占位符做函数优化器不一定能折叠。3. 把 CSV 灌进 MySQLLOAD DATA 的字段映射与字符集3.1 资源包文件对齐CSV 列顺序与表字段映射拿到 CSV 先别急着导先对齐列。资源里的文本一般按prefix,province,city,isp,area_code,post_code,status,source排列但不同批次的列顺序可能变表头也可能带 BOM。导入前用文本编辑器看第一行确认分隔符是逗号还是制表符确认换行是\n还是\r\n。如果 CSV 里有城市名带逗号字段必须用双引号包住LOAD DATA的OPTIONALLY ENCLOSED BY 就是干这个的。CSV 列表字段类型注意prefixprefixchar(7)必须是 7 位前导零不能丢provinceprovincevarchar(32)中文编码要和连接字符集一致citycityvarchar(32)空值给默认空串别给 NULLispispvarchar(16)用代号不要写真实服务商全称area_codearea_codevarchar(8)字符串存避免前导零丢失post_codepost_codevarchar(8)同上statusstatustinyint只允许 0 或 1sourcesourcevarchar(16)标记批次方便回滚我一般会先拿 100 行做小批量试导跑一遍SELECT COUNT(*)和SELECT * LIMIT 5确认中文没变问号、号段长度没被截断再放全量。这个习惯看着慢实际能省掉后面重新洗数据的几个小时。3.2 LOAD DATA 导入脚本先设字符集再分批提交mysql --local-infile1 -u root -p phone_geo SQL SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS 0; LOAD DATA LOCAL INFILE /data/import/phone_location.csv INTO TABLE phone_location CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (prefix, province, city, isp, area_code, post_code, status, source) SET updated_at NOW(); SET FOREIGN_KEY_CHECKS 1; SHOW WARNINGS; SQL这段脚本先开local_infile让客户端能把本地 CSV 推给 MySQLSET NAMES utf8mb4把连接字符集锁定避免客户端默认字符集和表字符集打架LOAD DATA LOCAL INFILE指定文件路径CHARACTER SET utf8mb4明确文件编码FIELDS TERMINATED BY ,和OPTIONALLY ENCLOSED BY 处理逗号分隔和引号包裹IGNORE 1 LINES跳过表头最后SET updated_at NOW()给本批数据打时间戳。SHOW WARNINGS必须保留截断、空值、类型转换的问题都会在这里冒出来。参数说明文件路径/data/import/phone_location.csv是示例路径实际换成自己的目录--local-infile1只对当前连接生效不要图省事写进全局配置FOREIGN_KEY_CHECKS 0在单表导入里不是必须但批量脚本里常用来避免外键检查拖慢速度导完记得改回 1。数据量大时按 5 万到 10 万行切一个文件分批提交别一个 500MB 的 CSV 硬灌回滚和排错都难受。3.3 去重与补全临时表加 ON DUPLICATE KEY UPDATE全量 CSV 直接往主表灌遇到重复前缀会报主键冲突用REPLACE INTO又会把旧行删掉重插历史来源和更新时间全丢。稳妥做法是先落临时表再合并。CREATE TABLE phone_location_stage LIKE phone_location; TRUNCATE TABLE phone_location_stage; LOAD DATA LOCAL INFILE /data/import/phone_location_stage.csv INTO TABLE phone_location_stage CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (prefix, province, city, isp, area_code, post_code, status, source) SET updated_at NOW(); INSERT INTO phone_location (prefix, province, city, isp, area_code, post_code, status, source, updated_at) SELECT prefix, province, city, isp, area_code, post_code, status, source, NOW() FROM phone_location_stage ON DUPLICATE KEY UPDATE province VALUES(province), city VALUES(city), isp VALUES(isp), area_code VALUES(area_code), post_code VALUES(post_code), status VALUES(status), source VALUES(source), updated_at NOW(); SELECT COUNT(*) AS stage_rows FROM phone_location_stage; SELECT COUNT(*) AS main_rows FROM phone_location;这段 SQL 用CREATE TABLE ... LIKE复制出同结构临时表TRUNCATE清空后灌入新批次再用INSERT ... SELECT ... ON DUPLICATE KEY UPDATE做合并。重复前缀不会报错会按新批次的值更新省市、服务商和状态source记录本批来源updated_at刷新时间。VALUES()在新版本里有弃用提示老项目仍常见如果环境支持行别名可以改成AS new再引用new.province语义一样。参数说明临时表名phone_location_stage可以按批次加后缀方便出问题回查TRUNCATE比DELETE快但会重置自增本表没有自增列影响不大合并前先跑SELECT prefix, COUNT(*) FROM phone_location_stage GROUP BY prefix HAVING COUNT(*) 1看同批次内部有没有重复内部重复不先处理合并结果会取决于扫描顺序容易变成玄学。4. 查询侧怎么写JOIN 前缀表比 LIKE 快在哪4.1 单条查询应用层截前缀别在 SQL 里对参数做函数单条查询最常见的翻车写法是WHERE phone LIKE CONCAT(prefix, %)或者WHERE LEFT(phone, 7) prefix。前者把手机号整列拿来做前缀匹配后者对字段做函数索引都用不上。正确姿势是反过来先把手机号截成 7 位再和prefix做主键等值匹配。SELECT province, city, isp FROM phone_location WHERE prefix 1000000 AND status 1 LIMIT 1;这条查询的prefix是常量主键等值查找EXPLAIN里type应该是constkey是PRIMARYrows是 1。status 1是额外过滤因为主键已经定位到一行回表后判断一下状态成本可以忽略。参数说明prefix传 7 位字符串不要传整数如果业务侧拿到的是完整手机号在应用层做phone[:7]不要把截取逻辑塞进 SQL 的占位符里。批量查询时这样写SELECT u.id, p.province, p.city, p.isp FROM user_profile u LEFT JOIN phone_location p ON p.prefix LEFT(u.phone, 7) AND p.status 1 WHERE u.id IN (1001, 1002, 1003);这段 SQL 把LEFT(u.phone, 7)放在连接条件里p.prefix是主键优化器可以对p表做ref查找。注意LEFT(u.phone, 7)仍然是对u表字段做函数u表如果很大驱动表扫描成本还是存在。更稳的做法是给user_profile加生成列把前缀物化出来连接条件变成p.prefix u.phone_prefix两边都是等值执行计划更干净。生成列的写法放在最后一章。4.2 EXPLAIN 看三列type、key、rows查询写完别凭感觉跑EXPLAIN。我只看三列type是不是const或refkey是不是PRIMARYrows是不是接近 1。如果type是ALL说明在扫全表如果key是NULL说明索引没命中如果rows是几千说明条件写松了。EXPLAIN SELECT province, city, isp FROM phone_location WHERE prefix 1000000 AND status 1 LIMIT 1;预期结果里type constkey PRIMARYrows 1Extra里可能带Using where。如果看到Using filesort或Using temporary说明查询里混进了排序或分组单条归属地查询不该出现这些。参数说明EXPLAIN不会真正执行查询可以放心在生产低峰跑EXPLAIN FORMATJSON信息更全但日常排查看表格版就够了。覆盖索引这块要算清楚。主键prefix定位到行后province、city、isp不在主键索引里需要回表一次。单条查询回表一次无所谓批量查询如果每次都要回表成本会放大。可以建一个(prefix, status, province, city, isp)的二级索引让查询只读索引。但这会增加写入和存储成本号段表更新频率低读多写少建覆盖索引是划算的。我的习惯是先在测试库对比EXPLAIN的Extra有没有Using index再决定要不要加。4.3 高频查询的缓存与预热先算命中率再上缓存归属地查询是典型的读多写少、结果稳定。单条走 MySQL 主键已经很快但注册高峰每秒几千次查询全打到数据库也没必要。缓存 key 用phone:geo:{prefix}value 存省市和服务商TTL 设 1 小时到 24 小时都行。重点是空值也要缓存不然不存在的号段会反复穿透到数据库。def get_location(phone: str): prefix phone[:7] key fphone:geo:{prefix} cached cache.get(key) if cached is not None: return cached row db.query( SELECT province, city, isp FROM phone_location WHERE prefix %s AND status 1 LIMIT 1, (prefix,) ) value row if row else {province: , city: , isp: } ttl 3600 if row else 300 cache.setex(key, ttl, value) return value这段代码先截 7 位前缀拼缓存 key命中直接返回未命中查 MySQL查到就缓存 1 小时查不到缓存 5 分钟空值防止恶意号码或新号段打穿。参数说明prefix必须是 7 位字符串ttl按业务容忍度调号段表更新后要主动删对应 key别等自然过期空值缓存时间不要设太长新号段上线后要能较快恢复。缓存预热放在号段表更新之后做。把高频前缀跑一遍SELECT prefix FROM phone_location WHERE status 1 ORDER BY updated_at DESC LIMIT 10000批量查出来塞缓存。别在启动时全量预热号段表几十万行启动会拖慢。命中率怎么算在查询入口埋点统计cache_hit / total低于 80% 就先查 key 设计是不是漏了前导零或者 TTL 太短。5. 避坑与排查号段冲突、字符集乱码和更新断层5.1 同一个前缀查出两个城市号段冲突不是脏数据现象同一个 7 位前缀导入后在临时表里出现两行一行是示例市一行是样例市合并后线上查出来的城市来回跳。原因通常有三个多批数据源合并时没有定优先级虚拟号段本身跨区域号码转网后服务商变了但归属地表还留着旧记录。很多人第一反应是删一行删完发现另一批业务查不到了。解决分三步。第一在临时表阶段就查冲突SELECT prefix, COUNT(*) c FROM phone_location_stage GROUP BY prefix HAVING c 1把冲突号段捞出来。第二给数据源定优先级比如新批次覆盖旧批次、高置信来源覆盖低置信来源合并时用ORDER BY source_priority DESC, updated_at DESC取第一条。第三主表保留source和updated_at查询侧默认取status 1且updated_at最新的一条如果业务要求严格唯一就在合并前把冲突号段人工确认别让脚本自动猜。我的血泪经验是冲突号段不要用REPLACE INTO一把梭。REPLACE会先删后插旧行的来源和时间全没了后面想回溯是哪批数据覆盖的都查不到。用ON DUPLICATE KEY UPDATE至少能保留一行再配合版本表出问题还能翻旧账。5.2 导入后中文变问号字符集三层没对齐现象CSV 在编辑器里看是正常中文导进 MySQL 后SELECT出来全是???或乱码。原因几乎都是三层字符集没对齐CSV 文件本身是 GBK客户端连接用的是默认字符集表或列又是latin1。三层里有一层不对中文就会翻车。解决顺序是从文件到连接再到表。先用file -i /data/import/phone_location.csv看文件编码如果是iso-8859-1或gbk先转成 UTF-8iconv -f GBK -t UTF-8 phone_location.csv -o phone_location_utf8.csv。导入脚本里SET NAMES utf8mb4LOAD DATA里再写CHARACTER SET utf8mb4两处都写别只写一处。最后检查表和列的字符集SHOW CREATE TABLE phone_location看到DEFAULT CHARSETutf8mb4才算齐。如果列级字符集是latin1用ALTER TABLE ... MODIFY province varchar(32) CHARACTER SET utf8mb4改过来再重新导入。注意改字符集之前先备份。已经有乱码的数据改字符集救不回来只能重新导。导入后跑SELECT HEX(province) FROM phone_location LIMIT 1看十六进制是不是 UTF-8 的中文编码比肉眼判断靠谱。5.3 新号段查不到更新断层与缓存旧值现象业务反馈新放号的号段查不到归属地但数据库里明明有这批前缀。原因一般两条增量脚本只追加新前缀没有处理旧前缀的状态变更缓存里的空值还没过期查询一直命中旧缓存。还有一种是号段表更新了但缓存 key 没删读到的还是旧城市。解决增量更新按updated_at拉取不要只按prefix是否存在判断。新批次里如果某个前缀从有效变停用要更新status不是跳过。缓存侧在更新完成后按前缀批量删 key删完再预热高频前缀。监控上加一个“未命中率”指标连续升高就说明号段表该更新了。我的习惯是每次更新号段表后跑一遍抽样校验从用户表随机取 1000 个手机号LEFT JOIN号段表看未命中数量超过阈值就先别发布。5.4 查询突然变慢前缀字段被函数包住或类型隐式转换现象同一条查询之前 1 毫秒某天开始变成几百毫秒EXPLAIN显示type ALL。原因常见两种SQL 里写了WHERE LEFT(phone, 7) prefix对字段做函数索引失效或者prefix是char(7)传参传了整数MySQL 做隐式转换索引也用不上。解决先把查询改成WHERE prefix ?参数在应用层截好再用EXPLAIN确认key PRIMARY。如果是类型问题检查应用层传参是不是把1000000写成了1000000char和整数比较会触发转换。批量 JOIN 场景给用户表加生成列把LEFT(phone, 7)物化成phone_prefix连接条件变成等值匹配。改完压测一轮别只看单条。6. 进阶用法把归属地查询压到 1 毫秒内并做版本校验6.1 用生成列把批量 JOIN 压成等值匹配批量查询里LEFT(u.phone, 7)虽然能走p表主键但u表字段被函数包住驱动表扫描成本下不来。给用户表加一个存储生成列把前缀物化连接条件变成两边等值ALTER TABLE user_profile ADD COLUMN phone_prefix char(7) GENERATED ALWAYS AS (LEFT(phone, 7)) STORED, ADD INDEX idx_phone_prefix (phone_prefix); SELECT u.id, p.province, p.city, p.isp FROM user_profile u LEFT JOIN phone_location p ON p.prefix u.phone_prefix AND p.status 1 WHERE u.id BETWEEN 1001 AND 2000;STORED生成列会实际占存储写入时自动算好查询时直接读VIRTUAL不占存储但索引支持和版本有关号段查询场景用STORED更稳。加列时大表会锁表走在线 DDL 工具或低峰执行。参数说明phone必须是 11 位字符串LEFT(phone, 7)结果不足 7 位时生成列会存短值导入前先用CHECK约束拦住。6.2 版本表与覆盖率校验给数据留后悔药号段表每次更新都覆盖主表出问题想回滚只能重新导。加一张版本表发布前把当前主表快照进去查询侧默认读主表排查时按版本号查历史CREATE TABLE phone_location_version ( version varchar(16) NOT NULL, prefix char(7) NOT NULL, province varchar(32) NOT NULL, city varchar(32) NOT NULL, isp varchar(16) NOT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (version, prefix) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO phone_location_version SELECT 20250101, prefix, province, city, isp FROM phone_location;校验跑三条 SQL总行数和唯一前缀数看有没有重复冲突检查看同前缀多行覆盖率抽样看用户表有多少手机号匹配不上。SELECT COUNT(*) AS total, COUNT(DISTINCT prefix) AS uniq_prefix FROM phone_location; SELECT prefix, COUNT(*) AS c FROM phone_location GROUP BY prefix HAVING c 1; SELECT COUNT(*) AS miss FROM user_profile u LEFT JOIN phone_location p ON p.prefix LEFT(u.phone, 7) WHERE p.prefix IS NULL;total和uniq_prefix应该相等不等就是主键失效或数据重复冲突查询返回空才发布miss占比超过业务阈值就先补号段。版本号用日期串只是示例换成自己的发布批次也行。从那以后我每次导完库都会先跑覆盖率、冲突、字符集三项检查再让业务接。希望帮到你。本文还有配套的精品资源点击获取