SQL批量数据清洗怎么做_真实案例解析强化复杂查询思维【技巧】

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;或抽样检查，确认影响范围可控。

音疯

音疯是昆仑万维推出的一个AI音乐创作平台，每日可以免费生成6首歌曲。

178

查看详情

三、关联清洗：跨表校验+补全，避免“闭门造车”

案例：订单表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一层层逼近答案——清洗不是体力活，是逻辑推演。

以上就是SQL批量数据清洗怎么做_真实案例解析强化复杂查询思维【技巧】的详细内容，更多请关注php中文网其它相关文章！