MySQL LOAD DATA导入脏行并保留错误记录的处理方案

批量导入 CSV 时，真正难处理的不是把文件读进 MySQL，而是让少量脏行不拖垮整批数据，同时还能知道哪些行被转换、跳过或截断。实践中可以把方案固定为“显式字段映射 + 用户变量暂存 + IGNORE 继续导入 + 立即保存警告”。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/load-data.html

先把输入格式和目标列固定下来

假设文件为 UTF-8 编码的逗号分隔 CSV，第一行是表头，目标表只接收业务字段。显式列清单能避免表结构后来增加列时发生错位：

-- 明确输入格式，避免把整行误读成一个字段
LOAD DATA LOCAL INFILE '/data/orders.csv'
IGNORE INTO TABLE orders
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(order_no, customer_no, amount_text, paid_at_text);

如果文件来自 Windows，换行通常要改成 LINES TERMINATED BY '\r\n'。LOCAL 表示客户端读取本机文件；非 LOCAL 模式则由服务端读取文件，并受 FILE 权限和 secure_file_priv 约束。

用用户变量接住脏值，再决定如何转换

生产导入不建议直接把原始文本绑定到日期、金额等强类型列。先接入用户变量，可以把空字符串、非法日期和多余空格的处理写在 SET 中，字段映射也更容易复查：

-- 先保存原始文本，再做可解释的转换；空金额保留为 NULL
LOAD DATA LOCAL INFILE '/data/orders.csv'
IGNORE INTO TABLE orders
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@order_no, @customer_no, @amount, @paid_at)
SET order_no = NULLIF(TRIM(@order_no), ''),
    customer_no = NULLIF(TRIM(@customer_no), ''),
    amount = NULLIF(TRIM(@amount), ''),
    paid_at = NULLIF(TRIM(@paid_at), ''); -- 后续可按业务格式增加 STR_TO_DATE

这里的关键不是“尽量转换”，而是让默认值、截断和 NULL 都有业务含义。金额字段若不能接受隐式变成 0，就应该先导入隔离表，或在 SET 中使用条件表达式把异常值标记出来。

用 IGNORE 继续导入，但把警告当作结果的一部分

没有 IGNORE 或 LOCAL 时，严格 SQL 模式下的数据解释错误可能终止导入；加入 IGNORE 后，许多错误会转成警告并继续处理。重复唯一键的输入行则会被丢弃，因此它适合“可接受部分成功”的批次，不适合无条件覆盖旧数据。

-- 导入后立即读取本次语句产生的诊断信息
SHOW COUNT(*) WARNINGS;
SHOW WARNINGS LIMIT 100;

返回信息中的 Records、Skipped、Warnings 要一起保存。SHOW WARNINGS 只显示当前会话最近一条非诊断语句的条件；如果中间先执行了别的 SQL，原始警告列表就不再可靠。大量脏行时还要关注 max_error_count：它限制被保存和展示的消息条数，但不限制 warning_count 的计数。

按警告类型决定重跑、补偿还是隔离

对财务、库存和结算数据，推荐先落隔离表，保留原始文本、批次号和导入时间，再将通过校验的记录写入正式表。对可重放的日志类数据，可以用批次号和唯一键保证幂等。无论哪种方案，都要把警告数量与源文件标识写入导入记录，避免“看起来成功”却无法追溯。

常见问题

为什么加了 IGNORE 仍然看不到每一条脏行？

IGNORE 只改变部分错误的处理方式，不会自动创建错误明细表。应在同一连接中立即执行 SHOW WARNINGS，并按批次把结果复制到日志或隔离表。

SHOW WARNINGS 为什么比实际问题少？

首先确认没有执行其他语句，其次检查 max_error_count。它限制保存的消息数量，@@warning_count 才是本次条件总数。

LOCAL 和 IGNORE 可以一起使用吗？

可以，但要理解它们的边界：LOCAL 还改变文件位置和权限要求，并且在没有 REPLACE 时具有类似 IGNORE 的错误处理效果。是否使用应由文件来源和安全配置决定。

一个可运营的 LOAD DATA 流程，验收标准不是命令返回“Query OK”，而是能回答三件事：处理了多少行、跳过了多少行、每类异常是否有后续动作。