企业官网建设流程全解析

简介：面向数据库查询与数据分析场景的手机号归属地数据包，适合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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这段建表语句里有几个取舍。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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 实际数据里是行政区划名称，这里用占位符演示字段关系 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的情况单独处理，不能直接拿空值去匹配规则。下面这张表是我拆库时通常先核对的字段粒度：

转售号段和物联网号段是最容易翻车的地方。普通号段按前 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-set=utf8mb4 \ --local-infile=1 \ phone_region < schema.sql mysql -u root -p \ --default-character-set=utf8mb4 \ --local-infile=1 \ 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 解析成本和网络包都会上来，优化器也可能放弃索引。批量场景我一般用临时表 JOIN：

CREATE TEMPORARY TABLE tmp_phone_prefix ( prefix CHAR(7) NOT NULL, PRIMARY KEY (prefix) ) ENGINE=InnoDB; 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大一点，但归属地查询是等值命中，这点空间换类型稳定值得。如果历史表已经用了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_ref，key是不是走了idx_prefix_region或主键，rows是不是接近 1，Extra里有没有Using index。如果type是ALL，说明全表扫了，先查参数类型和字段类型是否一致。如果Extra出现Using where且rows很大，可能是prefix条件没命中索引，或者 JOIN 顺序被优化器选反了。

覆盖索引的写法是把查询需要的列都放在索引里，减少回表。比如只查prefix、region_code、carrier_code，idx_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) ) ENGINE=InnoDB; 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 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-set=utf8mb4。

# 先确认文件编码 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) ) ENGINE=InnoDB; 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 =

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