MySQL数据预处理实操指南：清洗、去重、转换与批处理全解-北京尧图网络

写数据预处理这活儿说难不难说简单也不简单。我在做数据报表、数据仓库这类项目时最耗时间的往往不是后面那些花里胡哨的可视化而是最开头把一张张原始表拾掇干净的过程。MySQL的数据预处理说白了就是在 SQL 层面完成数据的清洗、转换、去重、标准化让下游的分析查询、指标计算不用再跟脏数据纠缠。这篇文章就把我在 MySQL 里做数据预处理的全套思路、实操 SQL 和踩过的坑整理出来适合刚从 Excel 转过来做数据分析的同学也适合要负责数据抽取入库的后端开发翻到哪节都能直接用。1. 什么是 MySQL 里的数据预处理为什么非要它不可1.1 核心定位数据库内的 ETL 轻量方案很多人一听到“数据预处理”第一反应是上 Python、写 Pandas再高级点用 Spark 之类的大数据组件。这个思路没错但不是所有场景都值得这么干。我遇到的很多实际项目数据量在几十万到几千万行之间就跑在公司的 MySQL 实例里下游是报表平台或者运营同学的临时取数需求。这种量级如果把数据导出来再导回去光传输时间和环境折腾就够喝一壶了。MySQL 的数据预处理本质上是一个轻量版的 ETL从原始表里把数据抽出来Extract在 SQL 层面对照业务规则做清洗和转换Transform最后写回一张干净的宽表或者结果表Load。这样做的最大好处是数据不用出库全程在数据库内部流转既快又安全而且整个处理过程有 SQL 留痕后面出了问题也好回溯。我个人的判断标准很简单单表数据量在千万行以下、处理逻辑能用 SQL 表达、时效要求不是毫秒级这三点满足的话直接在 MySQL 里做预处理是性价比最高的方案。1.2 预处理要解决的六大类问题结合我做过的项目MySQL 数据预处理一般绕不开下面六类工作数据探查先搞清楚表里有多少行、多少个去重值、空值多不多、字段分布长什么样。数据清洗处理重复记录、空值空串、格式不统一、乱码、全角半角混用、异常值。数据转换改字段类型、解析日期字符串、拆分合并字段、计算派生列。数据去重合并把多来源数据按业务键合并成一条去掉物理重复和逻辑重复。数据校验处理完之后做行数对比、空值率复检、分布抽查确保没改坏。归档备份处理前留一份原始数据备份防止改错之后抓瞎。这六块听着多但只要养成流程化习惯每一步用到的 SQL 其实就那几十条熟练之后就是肌肉记忆。下面从第一步说起。1.3 动手前的环境准备和版本差异开始写 SQL 之前先确认你的 MySQL 版本。5.7 和 8.0 在预处理能力上差别不小8.0 支持窗口函数ROW_NUMBER、RANK、LAG 这些处理重复数据和累计计算非常顺手5.7 没有窗口函数去重只能靠 GROUP BY 加临时表绕。另外 8.0 还有 REGEXP_REPLACE 函数做正则替换清洗文本特别香5.7 里就得靠多层 REPLACE 嵌套硬怼。还要看一眼 sql_mode。如果开着 ONLY_FULL_GROUP_BY那么 SELECT 里出现的非聚合列必须都在 GROUP BY 里很多老 SQL 会直接报错但不是数据库不行是模式限制。字符集这个坑更要提前排查尽量统一成 utf8mb4不然从不同来源导入的数据在 JOIN 的时候容易出现“Illegal mix of collations”的报错。我的建议是正式处理之前先查一下版本和字符集SELECT VERSION(); SHOW VARIABLES LIKE char%; SHOW VARIABLES LIKE sql_mode;2. 拿到一张脏表先别急着改数据探查是预处理的地基2.1 一份能直接抄的“数据体检”SQL 脚本我拿到任何一张表第一件事不是写 UPDATE而是跑一遍“体检”。目的是快速判断这张表脏在哪些维度为后面的清洗方案提供依据。下面这个脚本几乎适用于所有业务表你只要替换表名和关键字段就行SELECT COUNT(*) AS 总行数, COUNT(DISTINCT user_id) AS 去重用户数, SUM(user_id IS NULL) AS user_id空值数, SUM(IF(TRIM(user_name) , 1, 0)) AS 用户名为空串数, MIN(create_time) AS 最早创建时间, MAX(create_time) AS 最晚创建时间, COUNT(DISTINCT status) AS 状态种类数 FROM orders;这个脚本能告诉你四件事表体量多大、业务键去重后剩多少、关键字段的空值和空串情况、时间范围和状态枚举范围。如果发现去重用户数远远小于总行数说明重复很多后面要去重如果某个关键字段的空值占比超过 5%你就得跟业务方确认这个字段是否真的允许为空还是采集端出了问题。2.2 常见脏数据长什么样根据我的经验MySQL 原始表里的脏数据形态就那么几类做一个对照表方便你排查时按图索骥脏数据类型具体表现典型原因物理重复两行所有字段完全一样数据重复导入、接口重放逻辑重复业务键相同但其他字段不同多来源数据合并、主键生成规则变化空值缺失关键字段为 NULL采集端遗漏、关联查询没匹配上空串与空格字段显示为空但实际是空格前端录入不规范、Excel 导入残留格式不统一日期有斜杠有点号有汉字多个系统导出格式混乱全角半角混用字母数字带全角形式中文输入法录入隐藏字符字符串带换行符、制表符爬虫数据、长文本粘贴异常值年龄 200 岁、金额为负数逻辑错误、单位不统一编码错误中文变问号、乱码字符集不一致、传输编码错误2.3 探查阶段的三个细节提醒探查阶段有三个坑我反复踩过先说给你们。第一不要拿到表就开始改数据。我见过有人直接对线上核心业务表执行 UPDATE结果把状态字段的值全改错了最后只能靠备份恢复。正确做法是探查阶段只看 SELECT不动 UPDATE 和 DELETE所有写操作都放在业务低峰期并且先备份。第二COUNT(DISTINCT) 在千万行以上的大表上很慢如果只是想知道大概去重量级可以用采样。比如先随机抽 10 万行估算去重比例不用扛着全表去跑。第三探查时一定要结合业务口径。空值多不一定是坏事比如用户表里“注销时间”为空代表用户还没注销这是有效信息。所以每发现一个异常都要想它是否符合业务逻辑而不是机械地全填上默认值。3. 数据清洗实战去重、空值、文本脏数据一次清干净3.1 重复数据处理的三套打法先说去重。重复数据分物理重复和逻辑重复物理重复就是整行一模一样这种最简单保留一行即可逻辑重复是业务键一样、但非业务字段有差异比如同一个人下了两单订单号不同但身份证号相同这种情况要不要去重得看分析口径。先讲三个常用的去重 SQL。MySQL 8.0 里最推荐用窗口函数逻辑清楚且不容易误删DELETE FROM orders WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER(PARTITION BY order_no ORDER BY create_time DESC) AS rn FROM orders ) t WHERE rn 1 );这个 SQL 的意思是按订单号分组按创建时间倒序编号编号大于 1 的都是重复的只保留每组最新的一条。PARTITION BY 后面就是你定义的“业务键”ORDER BY 后面是保留顺序想保留最早一条就改成 ASC。MySQL 5.7 没有窗口函数可以用“保留每组最小 id”的思路DELETE FROM orders WHERE id NOT IN ( SELECT id FROM ( SELECT MIN(id) AS id FROM orders GROUP BY order_no ) t );注意这里的 NOT IN 子查询如果包含 NULL 值会导致 DELETE 查不出任何行。所以务必保证分组键没有 NULL或者用 LEFT JOIN 的方式改写更稳妥DELETE t1 FROM orders t1 LEFT JOIN ( SELECT MIN(id) AS id FROM orders GROUP BY order_no ) t2 ON t1.id t2.id WHERE t2.id IS NULL;第三种是 DELETE JOIN 直接删适合小表快速处理大表性能一般DELETE t1 FROM orders t1 JOIN orders t2 ON t1.order_no t2.order_no AND t1.id t2.id;3.2 空值处理NULL 和空串是两个世界空值处理前必须先搞懂一个概念NULL 和空字符串是完全不同的东西。NULL 代表“没有值”空串代表“有一个值为空字符串”。MySQL 里 COUNT(column) 会自动忽略 NULL 但不忽略空串SUM 也是遇到 NULL 就当没看见但遇到空串会变成 0 或报错。这个差异对统计影响极大。处理空值时我通常做三步。第一步统一口径把空串统一转成 NULL方便后续用 IS NULL 统一判断UPDATE users SET nickname NULL WHERE TRIM(nickname) ;第二步对业务字段做默认值填充UPDATE users SET gender COALESCE(gender, 未知) WHERE gender IS NULL;COALESCE 会从左到右返回第一个非 NULL 的值比 IFNULL 更灵活能支持多个备选值。第三步对数值字段如果要参与 SUM 聚合建议在聚合时处理而不是UPDATE时处理因为改原表可能会影响其他统计口径SELECT COALESCE(SUM(COALESCE(amount, 0)), 0) AS 总金额 FROM orders;3.3 文本清洗空格、换行、全角符号、乱码一把抓文本脏数据是最让人头大的。最常见的几种字符串前后有空格、中间有全角空格、里面混着换行符、数字字母是全角形式。我的处理手段是组合拳。去除首尾空格和全角空格UPDATE users SET user_name TRIM(REPLACE(user_name, , )) WHERE user_name LIKE % % OR user_name LIKE % OR user_name LIKE % ;去除换行符和制表符CHAR(10) 是换行CHAR(13) 是回车CHAR(9) 是制表符UPDATE comments SET content REPLACE(REPLACE(REPLACE(content, CHAR(10), ), CHAR(13), ), CHAR(9), ) WHERE content LIKE CONCAT(%, CHAR(10), %);MySQL 8.0 里用 REGEXP_REPLACE 可以把非数字字母字符直接清掉比如手机号字段里混了横杠和空格UPDATE users SET phone REGEXP_REPLACE(phone, [^0-9], ) WHERE phone REGEXP [^0-9];全角转半角稍微复杂一点常见做法是把全角数字字母用 REPLACE 逐一替换或者用正则匹配到全角字符后按 Unicode 码位偏移转换。实际项目中我遇到最多的还是全角空格和换行符处理完一定要再验证一遍确认没有漏网的。4. 数据转换类型、日期、拆分拼接与业务派生字段4.1 类型转换的正确姿势与隐式转换陷阱数据进入 MySQL 时经常因为源系统定义不严格导致类型不对。比如金额字段是 VARCHAR 存了“1,234.50”带千分号逗号日期字段是 VARCHAR 存了“2024年1月5日”。这些都要先转成标准类型。MySQL 提供 CAST 和 CONVERT 两个函数。把带千分号逗号的金额字符串转成十进制数要先去掉逗号再转SELECT CAST(REPLACE(1,234.50, ,, ) AS DECIMAL(10,2));把字符串整数转成数字UPDATE products SET price CAST(price AS DECIMAL(10,2)) WHERE price REGEXP ^[0-9](\\.[0-9])?$;这里有个隐式转换的坑必须提醒MySQL 里字符串和数字比较时字符串会尝试转成数字。比如WHERE status 0如果 status 是 VARCHAR 且存了 abcMySQL 会把它转成 0 再比较导致本不该匹配的行被匹配上。解决办法有两个一是查询时给字符串加引号写成WHERE status 0二是把字段类型彻底改对。数据类型转换的核心原则是先验证数据能转再批量改转换前用正则或者 CASE 判断兜底。4.2 日期时间处理统一格式才能分组统计日期是重灾区。同一个库里可能同时有“2024-01-05”、“2024/01/05”、“20240105”、“2024年1月5日”四种格式。不统一的话按天按周分组全是乱的。第一步用 STR_TO_DATE 把各种格式都解析成标准 DATETIMEUPDATE orders SET create_time STR_TO_DATE(create_time_str, %Y/%m/%d %H:%i:%s) WHERE create_time_str LIKE %/%;不同格式对应不同格式化字符串把常见映射列出来源字符串格式STR_TO_DATE 格式串2024-01-05 10:30:00%Y-%m-%d %H:%i:%s2024/01/05%Y/%m/%d20240105%Y%m%d2024年1月5日%Y年%m月%d日解析完之后再做报表分组就方便了。按天、按周、按月统计SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS 日期, COUNT(*) AS 订单数 FROM orders GROUP BY DATE_FORMAT(create_time, %Y-%m-%d) ORDER BY 日期;DATE_FORMAT 的另一个常用场景是按周分组%x-%v 是 ISO 周格式跨年时比直接用 WEEK 更可靠。日期计算方面DATE_ADD 和 DATEDIFF 是标配SELECT order_id, DATE_ADD(create_time, INTERVAL 1 DAY) AS 加一天, DATEDIFF(CURDATE(), create_time) AS 距今天数 FROM orders;4.3 字符串拆分与拼接从地址里拆省市、从编号里拆业务线字符串拆分最实用的函数是 SUBSTRING_INDEX。比如地址字段“北京市朝阳区某某路 88 号”要拆出省份SELECT address, SUBSTRING_INDEX(address, 省, 1) AS 省份 FROM users;但这话有个坑直辖市地址里没有“省”字比如“北京市朝阳区”上面的写法就拆不出来。所以更稳妥的方式是用正则或者 CASE 判断。比如先判断是不是“北京市”“上海市”开头再决定拆法SELECT address, CASE WHEN address LIKE 北京市% THEN 北京市 WHEN address LIKE %省% THEN SUBSTRING_INDEX(address, 省, 1) ELSE 其他 END AS 省份 FROM users;拼接字段则用 CONCAT_WS好处是自动跳过 NULL不会拼出“null”SELECT CONCAT_WS(-, province, city, district) AS 完整地区 FROM users;SUBSTRING 截取也很常用比如订单编号规则是“前缀业务线流水号”取中间几位SELECT order_no, SUBSTRING(order_no, 3, 3) AS 业务线编号 FROM orders;4.4 CASE WHEN 派生字段把业务口径落成 SQL预处理里最有价值的一步是生成业务口径统一的派生字段。比如把年龄转成年龄段把金额转成客单价档位把状态码转成中文标签。这一步做得好下游报表根本不用再各自写 CASE指标口径自然统一。SELECT user_id, age, CASE WHEN age 18 THEN 未成年 WHEN age BETWEEN 18 AND 30 THEN 青年 WHEN age BETWEEN 31 AND 59 THEN 中年 ELSE 老年 END AS age_group, order_id, amount, CASE WHEN amount 100 THEN 低客单 WHEN amount BETWEEN 100 AND 500 THEN 中客单 ELSE 高客单 END AS amount_level FROM user_orders;MySQL 8.0 还可以用窗口函数生成累计值、排名比如算每个用户的历史累计消费排名SELECT user_id, SUM(amount) AS total_amount, RANK() OVER(ORDER BY SUM(amount) DESC) AS 消费排名 FROM user_orders GROUP BY user_id;这些派生字段处理完成后我习惯直接写入一张宽表后续所有报表只查询宽表不改原始表追踪链路非常清晰。5. 千万行级数据的预处理大批量操作不锁死5.1 分批 UPDATE 和 DELETE避免长事务和锁竞争处理千万行级的数据最忌讳一条 UPDATE 全表。MySQL 执行全表 UPDATE 时会对涉及的行加锁如果表太大事务长时间不提交会把 InnoDB 的 undo 日志撑大还会拖慢主从同步甚至把连接池打满。所以批量操作要分成小批次执行。原则是每次处理的行数不要太大1 万到 10 万行适中。比如清空用户表里的测试数据DELETE FROM users WHERE is_test 1 AND id BETWEEN 1 AND 100000;手动分段太啰嗦可以用循环脚本。在存储过程里写循环搞定DELIMITER $$ CREATE PROCEDURE batch_update() BEGIN DECLARE i INT DEFAULT 0; DECLARE max_id INT; SELECT MAX(id) INTO max_id FROM orders; WHILE i max_id DO UPDATE orders SET amount amount * 1.1 WHERE id BETWEEN i AND i 50000; SET i i 50001; COMMIT; END WHILE; END$$ DELIMITER ;这里有几个细节每次循环后 COMMIT 一次释放锁UPDATE 语句的 WHERE 一定要走主键或索引不然照样全表扫描如果表有外键约束或者关联触发器分批更要小心。5.2 临时表的正确用法和三个容易踩的坑预处理经常需要把中间结果存起来再二次加工临时表是最趁手的工具。MySQL 的临时表是会话级的连接断开自动消失不会污染正式表。CREATE TEMPORARY TABLE tmp_clean_users AS SELECT user_id, TRIM(user_name) AS user_name, COALESCE(gender, 未知) AS gender FROM raw_users;但这个写法有个大坑临时表的字段类型和索引不会完整继承。比如原表里 DECIMAL 类型经过 SELECT 某些函数处理后很可能变成字符串。所以如果你希望临时表有正确的类型和索引最好先建表再插入CREATE TEMPORARY TABLE tmp_clean_users ( user_id BIGINT PRIMARY KEY, user_name VARCHAR(50), gender VARCHAR(10), KEY idx_gender (gender) ); INSERT INTO tmp_clean_users (user_id, user_name, gender) SELECT user_id, TRIM(user_name), COALESCE(gender, 未知) FROM raw_users;使用临时表的第三个坑如果后面还有 JOIN 操作临时表一定要有索引否则大表 JOIN 会直接内存炸掉。建好临时表后按 JOIN 的字段补索引。5.3 处理完如何自检行数、空值率、分布三张表对比每次大规模 UPDATE 或者转换之后我不急着继续下一步先跑一遍自检。自检脚本要覆盖三块行数是否变化、关键字段空值率是否下降、数据分布是否符合预期。-- 行数对比 SELECT COUNT(*) FROM orders; -- 空值率复检 SELECT COUNT(*) AS 总行数, SUM(user_name IS NULL OR TRIM(user_name) ) AS 非法用户名数, SUM(user_name IS NULL OR TRIM(user_name) ) / COUNT(*) AS 空值率 FROM orders; -- 维度分布检查 SELECT status, COUNT(*) AS 数量 FROM orders GROUP BY status ORDER BY 数量 DESC;自检发现问题时如果只是少量数据问题用 UPDATE 修正如果大面积出错就要从备份表恢复重新处理。所以我在每次预处理前都会先建备份表格式如下CREATE TABLE orders_bak_20240101 AS SELECT * FROM orders;备份表建好之后原表随便折腾至少心里有底。6. 常见问题与排查技巧实录6.1 高频问题速查表把我在各类项目里遇到的高频问题整理成一张速查表遇到对号入座问题现象根本原因解决办法中文变问号乱码连接字符集或表字符集不是 utf8mb4统一库表字段为 utf8mb4连接串加 characterEncodingutf8查询报 Illegal mix of collationsJOIN 两表的排序规则不一致用 COLLATE utf8mb4_unicode_ci 统一或修改字段 collation明明有重复数据却去不掉重复行里有隐藏空格或不可见字符先 TRIM、REPLACE 清理再去重GROUP BY 查询报错sql_mode 开启了 ONLY_FULL_GROUP_BY改写 SQL 让非聚合列都在分组内或修改 sql_modeNOT IN 查不出数据子查询结果集里有 NULL用 LEFT JOIN 替代 NOT IN字符串转数字结果全变 0字符串开头不是数字用 CASE 和正则先过滤再加 CAST大表 DELETE 巨慢或锁死没有索引、单条事务删除量过大按主键范围分批删除每次少删日期分组对不上日期字段类型是字符串且格式混乱先 STR_TO_DATE 统一转 DATETIMEDELETE 之后磁盘没变小InnoDB 碎片未回收执行 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINEInnoDB临时表里 JOIN 超慢临时表没有索引插入前建好索引或用 JOIN 字段加 KEY6.2 数据校验的最佳校验顺序整个处理链路的校验我有一个固定的执行顺序基本不会漏先查总量、再查空值、再查重复、再查格式、最后抽样人工核对。总量看有没有误删误增空值看有没有清干净重复看有没有处理掉格式看转换是否成功最后抽样核对是最保险的一道防线尤其是日期和金额这两个字段抽样十行看看对不对肉眼比任何脚本都好使。如果每一步都过了这个预处理流程才算真正收尾。6.3 千万别在正式表上直接做实验这一点我说过无数次但每次项目还是会有人犯。我见过一次最惨的教训有人在对线上订单表做去重时直接用 DELETE JOIN 一把梭结果因为业务键定义错了把一个用户的所有历史订单全删了只剩最新的一单。开发环境测试和正式环境实操是两码事。任何清洗、去重、转换第一步永远是在备份表或者复制出来的测试表上跑通。正确流程是先 CREATE TABLE ... AS SELECT 复制一张子集表在上面验证 SQL 影响行数是否正确再切换到正式表分批执行。这条规矩立住预处理的坑起码少一半。6.4 几个我长期在用的实操心得最后分享几个我这些年沉淀下来的小习惯。第一预处理脚本一定要模块化每个步骤拆成独立的 SQL 文件文件名带上编号和日期比如 01_data_profile_20240101.sql、02_deduplicate_20240102.sql。这样出了问题能快速定位是哪一步改坏了。第二每处理完一个大步骤把影响行数和执行时间记录下来。这不仅是审计需要也是你预判大数据量执行时间的依据。比如发现某条 UPDATE 在 100 万行上跑了 3 秒你就能估算 1000 万行大概 30 秒提前安排好业务低峰期窗口。第三字符串处理时我习惯先把 NULL 和空串统一成 NULL 再做清洗所有判断一律用WHERE col IS NULL这样可以避免因为空串的干扰导致 REPLACE 白做。第四日期和时间字段的时区问题要提前问清楚。如果源库用的是 UTC而报表需要北京时间在预处理阶段就要统一用 CONVERT_TZ 处理掉而不是等报表开发时再各自转否则容易翻车。数据预处理这件事表面上是一堆 SQL 的堆砌实际上考验的是对数据形态的理解和对业务口径的把握。把前面这些步骤串成一条固定流水线任何一张脏表进来你都有一套标准动作去应对。这样后面的分析、建模、报表才真正站在一个干净的地基上。

MySQL数据预处理实操指南：清洗、去重、转换与批处理全解

相关推荐

电动汽车直流充电机仿真建模与谐波分析实战解析

开源代码审查增强工具 open-code-review：从规则引擎到团队工程实践

open-code-review：用AST和复杂度指标重塑代码评审流程

更多资讯

暗光旗舰“月之暗面”如何凭影像实力挤进决赛圈？

给AI装上眼睛：多模态视觉识别全流程实战

给AI助手装上“眼睛”：摄像头+视觉大模型+语音闭环实战

从橡皮鸭调试法到拆解AI黑盒：可解释性实践指南

LTspice变压器仿真完全指南：从耦合电感建模到反激电源实战

PyTorch实战：猫狗图像分类从入门到部署

今日更新

悟空开源无代码平台实战：从实体建模到应用设计避坑指南

基于.NET9的多租户权限系统：数据权限与动态API落地实践

场地预约小程序毕设实战：SSM+微信小程序+MySQL数据库设计到避坑

本周精选

金融AI落地指南：从调研报告到技术选型与工程实践

大模型上下文成本与注意力优化技术深度解析——为何O(n²)平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测

基于GMM的语音识别系统原理与MATLAB实现详解

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？