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

SQL批量数据清洗应遵循“查中改、改中查”思维，先用SELECT精准定位脏数据，再分步原子化UPDATE，结合跨表校验与留痕验证，确保可追溯、可回滚、可复用。

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

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 H*ING COUNT(*) > 1;

错误做法：一条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 Code Reviewer

AI自动审核代码

112 查看详情 AI Code Reviewer

案例：订单表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批量数据清洗怎么做_真实案例解析强化复杂查询思维【技巧】的详细内容，更多请关注其它相关文章！

2025-12-16

了解您产品搜索量及市场趋势，制定营销计划