sql批量数据清洗应遵循“查中改、改中查”思维，先用select精准定位脏数据，再分步原子化UPDATE，结合跨表校验与留痕验证，确保可追溯、可回滚、可复用。
SQL批量数据清洗不是写一堆UPDATE，而是用“查中改、改中查”的思维，把清洗变成可验证、可回滚、可复用的查询逻辑。核心是：先用SELECT精准定位问题数据，再套上UPDATE/delete/INSERT，最后用count或抽样校验。
一、识别脏数据：别猜，用聚合+条件组合筛
真实场景：用户表user_info里有12万条记录，电话字段phone出现空格、短横线、中文括号、长度异常（如11位以外）、重复手机号等问题。
不建议逐条看，直接用以下SELECT快速画像：
- 查空格和符号残留：SELECT id, phone FROM user_info WHERE phone regexp ‘[[:space:]-()u4e00-u9fa5]’;
- 查长度异常：SELECT phone, lenGTH(phone) len FROM user_info WHERE Length(TRIM(phone)) NOT IN (11, 0);
- 查疑似重复（去噪后）：SELECT REPLACE(REPLACE(REPLACE(TRIM(phone), ‘ ‘, ”), ‘-‘, ”), ‘)’, ”) clean_p, COUNT(*) FROM user_info GROUP BY clean_p HAVING COUNT(*) > 1;
二、清洗动作要“原子化”：分步UPDATE，每步只做一件事
错误做法：一条UPDATE干掉所有问题（易出错、难调试、无法回滚）。正确做法是拆解为语义清晰的独立步骤：
- 第一步：统一去空格和常见符号
UPDATE user_info SET phone = TRIM(REPLACE(REPLACE(REPLACE(phone, ‘ ‘, ”), ‘-‘, ”), ‘)’, ”)); - 第二步：补全11位（仅对纯数字且长度为10的加’1’前缀）
UPDATE user_info SET phone = CONCAT(‘1’, phone) WHERE phone REGEXP ‘^[0-9]{10}$’; - 第三步：清空非法值（非11位纯数字）
UPDATE user_info SET phone = NULL WHERE phone NOT REGEXP ‘^1[0-9]{10}$’;
每执行一步，都跟一句SELECT COUNT(*) FROM user_info WHERE phone IS NULL;或抽样检查，确认影响范围可控。
三、关联清洗：跨表校验+补全，避免“闭门造车”
案例：订单表orders里user_id存在但对应user_info中已删除（逻辑删除标记is_deleted=1），导致统计口径混乱。
- 先查出“孤儿订单”：
SELECT o.id, o.user_id FROM orders o LEFT JOIN user_info u ON o.user_id = u.id AND u.is_deleted = 0 WHERE u.id IS NULL; - 再安全处理（比如打标签而非直接删）：
UPDATE orders SET status = ‘invalid_user’ WHERE id IN (SELECT id FROM (SELECT o.id FROM orders o LEFT JOIN user_info u ON o.user_id = u.id AND u.is_deleted = 0 WHERE u.id IS NULL) t);
注意子查询套一层(SELECT …)，规避mysql“不能在UPDATE中直接SELECT同一张表”的限制。
四、留痕与验证：清洗不是终点，而是新数据质量的起点
上线前必须做三件事：
- 备份原始字段（加临时列存旧值）：
ALTER table user_info ADD column phone_raw VARCHAR(20); UPDATE user_info SET phone_raw = phone; - 记录清洗日志（哪怕只是简单INSERT）：
INSERT INTO clean_log(table_name, action, affected_rows, run_time) VALUES (‘user_info’, ‘phone_normalize’, 118320, NOW()); - 跑一致性校验SQL（例如清洗前后总记录数、有效手机号占比）：
SELECT ‘before’ phase, COUNT(*) total, COUNT(phone) valid FROM user_info_old union ALL SELECT ‘after’, COUNT(*), COUNT(phone) FROM user_info;
基本上就这些。复杂查询思维的本质，是把“我要修什么”转化成“我能用哪几个条件锁定它”，再用SQL一层层逼近答案——清洗不是体力活，是逻辑推演。