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

标签： mysql 数据清洗