把脏数据里的手机号统一成一种格式,真正要处理的是两类东西:一类是格式噪声,比如前后空格、短横线、括号、+86 或 0086 前缀、全角数字,这些可以用 SQL 表达式确定性地剥离掉;另一类是信息已经损坏的号码,比如被表格软件转成科学计数法、位数不足、整列都是占位符,这类不能靠正则救回来,只能在打上标记后再单独处理。先分类再统一,顺序如果反了,就只是把坏数据改成了看起来干净的坏数据。
手机号格式统一建议分三步走:先用只读查询把现状统计清楚(空值、位数、号段、重复),再用清洗表达式生成候选值并写入一个状态列,最后人工确认异常行之后才回写原列。目标格式通常定成纯数字、去掉国际区号、符合本地号段规则,例如中国大陆按 11 位、1 开头、第二位 3 到 9 判断。无法确定性修复的行不要强行填默认值,保留原值加标记,并为回滚留好原始列或备份表。函数可用性取决于数据库和版本,需要结合环境确认后再执行。
先确认当前场景和目标
动手前先把下面几件事确认清楚,否则清洗结果没有验收标准:
- 目标格式是什么。是统一成 11 位纯数字,还是保留下国家码;是只处理中国大陆手机号,还是也要兼容境外号码和座机。这决定了后面正则可以写多严格。
- 原列能不能改。如果这列被接口、报表、唯一索引或外键依赖,通常更稳妥的做法是新增
phone_norm和phone_status两列,而不是直接覆盖原值。 - 数据库种类和版本。多数数据库提供
REGEXP_REPLACE,但老版本 MySQL 通常没有,只能改用嵌套REPLACE或者用程序侧清洗,这一步需要结合环境确认。 - 表有多大、能否分批。大表一次性 UPDATE 会带来长时间锁等待和大量日志,一般建议按主键范围分批。
确认之后,先跑一条只读的现状画像,不要急着改。下面这条按需替换表名和列名:
SELECT
COUNT(*) AS total,
SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) AS null_cnt,
SUM(CASE WHEN TRIM(COALESCE(phone,'')) = '' THEN 1 ELSE 0 END) AS blank_cnt,
SUM(CASE WHEN phone REGEXP '^1[3-9][0-9]{9}$' THEN 1 ELSE 0 END) AS already_ok,
MIN(LENGTH(TRIM(phone))) AS min_len,
MAX(LENGTH(TRIM(phone))) AS max_len
FROM customer;
如果 already_ok 已经接近总行数,说明主要问题是少数格式噪声;如果 min_len 明显偏小,说明存在截断或占位数据,需要先看样本再定规则。
按这个顺序处理
- 保留原始值。先建备份表或者新增
phone_raw列保存原值,这是后面唯一的回退依据。 - 只读预览候选值。把清洗结果和原值并排查出来,只看不写,先确认逻辑对不对。
- 生成候选列并打状态。把结果写入
phone_norm,同时用phone_status标出ok、empty、suspect三类。 - 处理可疑行。对
suspect单独抽样、必要时找业务确认,不要批量套默认值。 - 回写并加约束。确认无误后再把
phone_norm作为对外使用的列,视情况加唯一约束或校验规则。
下面是一个可替换的清洗表达式骨架,思路是「先去常见装饰字符,再去国际区号和前导 0,最后校验长度和号段」。MySQL 8.0 及以上可以这样写,表名列名按实际情况替换:
SELECT
id,
phone AS phone_raw,
REGEXP_REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(TRIM(phone),
'+86',''), '-',''), ' ',''), '(', ''),
'[^0-9]', ''
) AS digits
FROM customer;
拿到 digits 之后再做一次前缀裁剪和校验,写成状态列的形式(PostgreSQL 用 regexp_replace(..., 'g'),写法略有差异):

UPDATE customer
SET phone_norm = CASE
WHEN digits LIKE '0086%' THEN SUBSTR(digits, 5)
WHEN digits LIKE '86%' AND LENGTH(digits) = 13 THEN SUBSTR(digits, 3)
ELSE digits
END,
phone_status = CASE
WHEN phone IS NULL OR TRIM(phone) = '' THEN 'empty'
WHEN LENGTH(CASE WHEN digits LIKE '0086%' THEN SUBSTR(digits,5)
WHEN digits LIKE '86%' AND LENGTH(digits)=13 THEN SUBSTR(digits,3)
ELSE digits END) = 11
AND (CASE WHEN digits LIKE '0086%' THEN SUBSTR(digits,5)
WHEN digits LIKE '86%' AND LENGTH(digits)=13 THEN SUBSTR(digits,3)
ELSE digits END) REGEXP '^1[3-9][0-9]{9}$' THEN 'ok'
ELSE 'suspect'
END
FROM (SELECT id, phone, REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(phone),'+86',''),'-',''),' ',''),'(',''),'[^0-9]','') AS digits
FROM customer) t
WHERE t.id = customer.id;
这段语句只是骨架,实际执行前建议先复制成 SELECT 版本跑一遍,把 phone_status 的分布和可疑样本看清楚了再执行 UPDATE。表达式里的号段规则、国际区号长度都是按中国大陆手机号的常见情况写的,换成其他地区时要整体替换。
改完后怎么验证
验证的重点不是「语句跑成功了」,而是「改动范围和结果符合预期」。建议依次做这几件事:
- 行数核对:清洗前后总行数应一致,UPDATE 影响行数应等于目标行数,对不上就立刻停下排查条件写错的可能。
- 状态分布:按
phone_status分组计数,确认ok的数量与预期的可修复量大致吻合,suspect的行要能逐条解释原因。 - 重复检查:
SELECT phone_norm, COUNT(*) FROM customer WHERE phone_status='ok' GROUP BY phone_norm HAVING COUNT(*) > 1;查出来的就是清洗后才暴露出来的重复号,需要业务判断去重口径。 - 抽样比对:随机取几十行,把
phone_raw和phone_norm并排看,重点挑带前缀、带符号、长度异常的那几类。 - 业务侧验证:让调用方用新列查一次,确认登录、短信或导出功能取到的是同一批数据。
预期信号是:ok 行全部满足目标格式,suspect 行数量可控且原因清楚,原值仍然完整保存在备份里。
容易忽略的边界
有几类情况不能按上面的思路硬套。境外号码、座机加分机、虚拟号段,用 11 位大陆规则去校验一定会被判成 suspect,这时候应该保留多套规则或者干脆不自动修复;被表格软件转成科学计数法的号码,原始数字已经丢失,任何 SQL 都还原不回来,只能标记后走人工补录;NULL 和空字符串在业务语义上可能不同,统一清洗时不要顺手把它们合并成同一个值。
执行层面还要注意:大表 UPDATE 建议按主键区间分批提交,避免长事务;如果列上有唯一索引、触发器或同步链路,先确认写新值不会触发冲突;REGEXP_REPLACE 这类函数老版本可能不支持,替换成嵌套 REPLACE 或放到程序侧处理。回退方案要提前准备好——保留原始列或备份表,把整个清洗放在事务里执行,验证不通过时直接回滚,不要把「已经跑完的 UPDATE」当成不可逆操作来处理。