企业官网建设流程全解析

简介：这份压缩包面向需要手机号归属地查询与数据分析的开发者、数据库学习者及营销客服等业务人员，提供以 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，排序规则跟库一致，别在表级、列级、连接级各写一套，否则导入时中文很容易翻车。

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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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 灌进 MySQL：LOAD 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 '"'就是干这个的。

我一般会先拿 100 行做小批量试导，跑一遍SELECT COUNT(*)和SELECT * LIMIT 5，确认中文没变问号、号段长度没被截断，再放全量。这个习惯看着慢，实际能省掉后面重新洗数据的几个小时。

3.2 LOAD DATA 导入脚本：先设字符集再分批提交

mysql --local-infile=1 -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 推给 MySQL；SET 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-infile=1只对当前连接生效，不要图省事写进全局配置；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应该是const，key是PRIMARY，rows是 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或ref，key是不是PRIMARY，rows是不是接近 1。如果type是ALL，说明在扫全表；如果key是NULL，说明索引没命中；如果rows是几千，说明条件写松了。

EXPLAIN SELECT province, city, isp FROM phone_location WHERE prefix = '1000000' AND status = 1 LIMIT 1;

预期结果里type = const，key = PRIMARY，rows = 1，Extra里可能带Using where。如果看到Using filesort或Using temporary，说明查询里混进了排序或分组，单条归属地查询不该出现这些。参数说明：EXPLAIN不会真正执行查询，可以放心在生产低峰跑；EXPLAIN FORMAT=JSON信息更全，但日常排查看表格版就够了。

覆盖索引这块要算清楚。主键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 = f"phone: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-8：iconv -f GBK -t UTF-8 phone_location.csv -o phone_location_utf8.csv。导入脚本里SET NAMES utf8mb4，LOAD DATA里再写CHARACTER SET utf8mb4，两处都写，别只写一处。最后检查表和列的字符集：SHOW CREATE TABLE phone_location，看到DEFAULT CHARSET=utf8mb4才算齐。如果列级字符集是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'写成了1000000，char和整数比较会触发转换。批量 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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 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占比超过业务阈值就先补号段。版本号用日期串只是示例，换成自己的发布批次也行。从那以后我每次导完库，都会先跑覆盖率、冲突、字符集三项检查，再让业务接。希望帮到你。

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