如何让MySQL执行LOAD DATA INFILE时遇错不退出并跳过错误行

为什么之前添加--force参数没有生效 #

• --force 是mysql命令行客户端的启动参数，不属于LOAD DATA的SQL语法范畴。它的作用仅仅是当客户端执行某条SQL返回错误时，不终止客户端进程、继续执行后续SQL，从根本上不影响数据库服务端对单条SQL的执行逻辑
• 你遇到的“遇到坏行就直接终止导入”的核心触发原因，是MySQL 5.7及以上版本默认开启了STRICT_TRANS_TABLES严格事务模式。在这个模式下，列数不匹配、非法字符串值、字段长度超列定义这类问题，会被判定为致命错误，服务端会直接中断当前正在执行的LOAD DATA查询——哪怕你用了带--force的客户端连接，服务端已经停掉的导入流程也不会恢复，后续数据根本不会被处理

可直接落地的错行跳过配置 #

要实现“自动丢弃错误行、不中断后续有效数据导入”的效果，不需要修改全局数据库配置，只需要在执行LOAD DATA的同一个数据库会话内，先调整会话级的SQL模式即可：

-- 在当前连接会话中，先执行以下配置，关闭严格事务模式
SET SESSION sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION';

-- 执行你原有导入语句，无需修改SQL本身逻辑
LOAD DATA CONCURRENT INFILE 'file_name' 
INTO TABLE table_name 
FIELDS TERMINATED BY ':DELIMITER:' 
LINES TERMINATED BY '\n' 
(@col1,@col2) 
SET column_1=@col1, column_2=@col2;

配置效果说明 #

• 调整完成后，原来会导致查询直接终止的三类错误（列数不匹配、非法字符串值、字段值超出列定义长度），都会被降级为非致命警告，对应的错误行直接被丢弃，后续有效行会正常导入，完全不会中断整个查询流程
• 因为你当前的导入语句是通过@用户变量接收字段值、再赋值给表字段，整个赋值阶段的校验逻辑完全受当前会话的sql_mode控制，调整配置后，该阶段触发的长度超限、非法值类错误也会被自动容错，无需修改原有SQL的语法结构
• 这种会话级的配置仅对当前连接生效，连接断开后自动恢复原配置，不会影响其他业务的数据库严格校验规则，无全局副作用
• 导入完成后，可以通过执行SHOW WARNINGS;查看所有被跳过行的具体位置、错误原因，方便统计无效数据的规模

补充优化建议 #

• 如果想进一步减少长度类错误的警告，可以在SET字段赋值的阶段增加前置判断，比如对长度超限的字段值主动截断或者置空，从根源上避免触发长度校验错误
• 如果确实需要使用--force客户端参数，需要在启动mysql连接时就指定，例如mysql --force -u 账号 -p 库名，注意这个参数必须配合上面的会话级SQL模式调整，才能真正实现错行不中断的效果

内容的提问来源于stack exchange，提问作者dannypas00