SQL批量数据清洗需遵循“识别问题→设计规则→分步修正→验证结果”四步闭环，通过CTE+CASE构建可验证流水线，先备份再更新，确保千万级表ETL安全高效。
SQL批量数据清洗不是靠一条命令搞定，而是通过“识别问题→设计规则→分步修正→验证结果”四步闭环完成。核心在于用可复用的SQL逻辑替代人工逐条处理，尤其适合千万级表的日常ETL维护。
一、快速定位脏数据类型（先看再动）
不查清问题就写UPDATE，等于蒙眼修车。常用诊断SQL要常备：
- 空值/异常空串：SELECT COUNT(*) FROM orders WHERE phone IS NULL OR TRIM(phone) = '';
- 格式错乱（如手机号含字母、日期非法）：SELECT * FROM users WHERE phone REGEXP '[^0-9]' OR LENGTH(phone) != 11;
- 重复主键或业务键：SELECT user_id, COUNT(*) FROM users GROUP BY user_id HAVING COUNT(*) > 1;
- 数值越界或负值异常：SELECT * FROM sales WHERE amount 1000000;
二、分层清洗：用CTE+CASE构建安全流水线
避免直接UPDATE原表，推荐用WITH递进式清洗，每步可单独验证：
WITH clean_phone AS (
SELECT id,
CASE
WHEN phone REGEXP '^[0-9]{11}$' THEN phone
WHEN phone REGEXP '^[0-9]{12}$' AND SUBSTR(phone,1,1)='8' THEN SUBSTR(phone,2) -- 去掉开头8
WHEN phone LIKE '%-%' THEN REPLACE(phone, '-', '') -- 去横线
ELSE NULL
END AS cleaned_phone
FROM raw_users
),
deduped AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY cleaned_phone ORDER BY updated_at DESC) AS rn
FROM clean_phone
)
SELECT id, cleaned_phone AS phone
FROM deduped
WHERE rn = 1 AND cleaned_phone IS NOT NULL;这样写的好处：逻辑清晰、中间结果可查、出错可回退到任意CTE层调试。
三、批量更新与去重落地（谨慎执行）
确认清洗逻辑无误后，再操作原表。关键原则：先备份，再更新，后校验：
- 备份（必做）：CREATE TABLE users_bak_20240520 AS SELECT * FROM users;
- 更新手机号：UPDATE users u JOIN (SELECT id, cleaned_phone FROM ... ) c ON u.id=c.id SET u.phone = c.cleaned_phone WHERE c.cleaned_phone IS NOT NULL;
- 删除重复记录（保留最新）：DELETE u1 FROM users u1 INNER JOIN users u2 WHERE u1.phone = u2.phone AND u1.updated_at
四、自动化与防复发（让清洗一次生效）
清洗不能只做一次。加两道防线：
- 建清洗视图：CREATE VIEW v_users_clean AS SELECT ..., COALESCE(TRIM(phone), '') AS phone FROM users WHERE status != 'deleted'; 后续报表/接口全走视图，原表只存原始数据。
- 加约束和触发器：ALTER TABLE users ADD CHECK (phone REGEXP '^[0-9]{11}$' OR phone IS NULL); 或用BEFORE INSERT触发器自动标准化。
- 定期巡检脚本：把第一步的诊断SQL写成每日定时任务，结果发钉钉/邮件，异常值超阈值自动告警。
基本上就这些。真正难的不是写SQL，而是把业务规则翻译成可计算的条件——比如“有效手机号”在你们系统里到底指什么？是运营商号段？还是必须能发短信？定义清楚，清洗才不会反复返工。
