如何让MySQL中批量LOAD DATA INFILE操作受事务约束实现整体回滚？

首先确保所有目标表使用InnoDB存储引擎（MyISAM不支持事务回滚）。

方法1：每个表的LOAD操作独立包裹事务 #

通过批处理为每个表单独启动事务，执行LOAD操作后根据结果决定提交或回滚，单个表的失败不会影响其他表的提交状态。

批处理示例代码：

@echo off
set "MYSQL_CMD=mysql -u你的用户名 -p你的密码 你的数据库名"

:: 处理表1
%MYSQL_CMD% -e "
START TRANSACTION;
-- 先清空表（确保加载前表为0行，不需要可删除此语句）
DELETE FROM table1;
LOAD DATA INFILE 'D:/data/table1.csv' 
INTO TABLE table1 
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS;
IF @@ERROR = 0 THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;"

:: 处理表2
%MYSQL_CMD% -e "
START TRANSACTION;
DELETE FROM table2;
LOAD DATA INFILE 'D:/data/table2.csv' 
INTO TABLE table2 
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS;
IF @@ERROR = 0 THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;"

:: 后续表按此格式添加

关键说明： #

• DELETE FROM 表名：确保加载前表为空，LOAD失败回滚后表保持0行；若无需清空原有数据，可删除此语句。
• @@ERROR：MySQL系统变量，存储最近一次操作的错误码，0表示执行成功。
• 每个表的操作独立在一个事务中，某表LOAD失败仅回滚自身事务，其他表的成功事务已提交。

方法2：单事务内使用保存点（适合统一提交但允许单表回滚场景） #

如果希望所有表加载完成后统一提交，但某表失败时仅回滚该表操作（其他表操作保留，最终一起提交），可使用保存点：

@echo off
set "MYSQL_CMD=mysql -u你的用户名 -p你的密码 你的数据库名"

%MYSQL_CMD% -e "
START TRANSACTION;
-- 处理表1
SAVEPOINT sp_table1;
DELETE FROM table1;
LOAD DATA INFILE 'D:/data/table1.csv' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
IF @@ERROR != 0 THEN
    ROLLBACK TO sp_table1;
END IF;

-- 处理表2
SAVEPOINT sp_table2;
DELETE FROM table2;
LOAD DATA INFILE 'D:/data/table2.csv' INTO TABLE table2 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
IF @@ERROR != 0 THEN
    ROLLBACK TO sp_table2;
END IF;

-- 所有表处理完成后统一提交
COMMIT;"

关键说明： #

• 保存点（SAVEPOINT）标记每个表操作的起始点，LOAD失败时回滚到该点，仅撤销当前表的清空和LOAD操作，不影响其他表的操作。
• 最终统一提交所有成功的表操作，失败的表保持操作前状态（若执行了DELETE则回滚到0行）。

注意事项 #

• 确保MySQL开启local_infile权限（使用本地CSV文件时），可通过SET GLOBAL local_infile = 1;开启。
• CSV文件路径需符合MySQL权限要求，避免因文件权限导致LOAD失败。
• 若表原有数据需保留，删除DELETE FROM 表名语句，回滚后表将恢复到LOAD前的原有数据状态。

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