SQL把脏数据里的手机号格式统一怎么处理

文章导读
把脏数据里的手机号统一成一种格式,真正要处理的是两类东西:一类是格式噪声,比如前后空格、短横线、括号、+86 或 0086 前缀、全角数字,这些可以用 SQL 表达式确定性地剥离掉;另一类是信息已经损坏的号码,比如被表格软件转成科学计数法、位数不足、整列都是占位符,这类不能靠正则救回来,只能在打上标记后再单独处理。先分类再统一,顺序如果反了,就只是把坏数据改成了看起来干净的坏数据。
📋 目录
  1. 壹 先确认当前场景和目标
  2. 贰 按这个顺序处理
  3. 叁 改完后怎么验证
  4. 肆 容易忽略的边界
A A

把脏数据里的手机号统一成一种格式,真正要处理的是两类东西:一类是格式噪声,比如前后空格、短横线、括号、+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 明显偏小,说明存在截断或占位数据,需要先看样本再定规则。

按这个顺序处理

  1. 保留原始值。先建备份表或者新增 phone_raw 列保存原值,这是后面唯一的回退依据。
  2. 只读预览候选值。把清洗结果和原值并排查出来,只看不写,先确认逻辑对不对。
  3. 生成候选列并打状态。把结果写入 phone_norm,同时用 phone_status 标出 ok、empty、suspect 三类。
  4. 处理可疑行。对 suspect 单独抽样、必要时找业务确认,不要批量套默认值。
  5. 回写并加约束。确认无误后再把 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'),写法略有差异):

SQL把脏数据里的手机号格式统一怎么处理
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」当成不可逆操作来处理。