MySQL两种SQL脚本执行方式的事务处理差异咨询

环境信息 #

使用的MySQL客户端版本：

# mysql --version
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

问题现象 #

同一事务脚本，两种执行方式的事务处理逻辑完全不同：

• 重定向执行：脚本中间出错时，整个事务自动回滚，符合预期
  执行命令：

  mysql --show-warnings --verbose -u root -p < script.sql
  

• source命令执行：脚本中间出错后，后续的COMMIT仍会执行，导致已成功的修改被提交，无法实现回滚
  执行命令：

  mysql> source script.sql
  

测试脚本内容 #

script.sql代码如下：

START TRANSACTION;
       
    UPDATE onboarding.components_options
        SET value = 'Business Details​'
        WHERE component_id = (select id from onboarding.components where reference = 'BUSINESS_ENTITY')
            and type = 'title';
           
    UPDATE onboarding.components_options
        SET value2 = 'Is your business incorporated?​'
        WHERE component_id = (select id from onboarding.components where reference = 'BUSINESS_ENTITY')
            and type = 'subtitle';
           
COMMIT;

执行结果对比 #

重定向执行（符合预期） #

# mysql --show-warnings --verbose -u root -p < script.sql
Enter password: 
--------------
START TRANSACTION
--------------
--------------
UPDATE onboarding.components_options
        SET value = 'Business Details​'
        WHERE component_id = (select id from onboarding.components where reference = 'BUSINESS_ENTITY')
            and type = 'title'
--------------
--------------
UPDATE onboarding.components_options
        SET value2 = 'Is your business incorporated?​'
        WHERE component_id = (select id from onboarding.components where reference = 'BUSINESS_ENTITY')
            and type = 'subtitle'
--------------
ERROR 1054 (42S22) at line 8: Unknown column 'value2' in 'field list'
bash-4.4# mysql -u root -p

遇到错误后脚本立即终止，COMMIT未执行，事务自动回滚，第一个UPDATE的修改被撤销。

source命令执行（不符合预期） #

mysql> source script.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
ERROR 1054 (42S22): Unknown column 'value2' in 'field list'
Query OK, 0 rows affected (0.02 sec)

错误发生后脚本继续执行后续语句，COMMIT被成功执行，第一个UPDATE的修改被永久保存。

原因分析 #

两种执行方式的核心差异在于错误处理机制：

1. 重定向执行：属于批量执行模式，mysql客户端默认遇到错误就终止后续语句执行，因此COMMIT不会被运行，未提交的事务会随着客户端会话结束自动回滚。
2. source命令执行：属于交互式执行模式，客户端默认开启“出错后继续执行”逻辑，即使某条语句报错，后续语句依然会被执行，导致错误后的COMMIT提交了已完成的修改。

解决办法 #

方法1：修改脚本，添加事务错误捕获 #

将脚本改为带错误处理的存储过程，确保出错时自动回滚：

DELIMITER //
CREATE PROCEDURE update_component_options()
BEGIN
    -- 捕获所有SQL异常，执行回滚并抛出提示
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '脚本执行出错，已回滚所有操作';
    END;
    
    START TRANSACTION;
    
    UPDATE onboarding.components_options
        SET value = 'Business Details​'
        WHERE component_id = (SELECT id FROM onboarding.components WHERE reference = 'BUSINESS_ENTITY')
            AND type = 'title';
            
    UPDATE onboarding.components_options
        SET value2 = 'Is your business incorporated?​'
        WHERE component_id = (SELECT id FROM onboarding.components WHERE reference = 'BUSINESS_ENTITY')
            AND type = 'subtitle';
            
    COMMIT;
END //
DELIMITER ;

-- 调用存储过程执行逻辑
CALL update_component_options();

-- 可根据需求删除存储过程
DROP PROCEDURE update_component_options;

方法2：调整客户端执行模式 #

• 启动客户端时添加--batch参数，强制使用批量执行的错误处理逻辑，此时source命令遇到错误会终止后续执行：

  mysql --batch -u root -p
  mysql> source script.sql
  

• 或者在交互式环境中使用-e参数执行脚本，效果与重定向执行一致：

  mysql -u root -p -e "source script.sql"
  

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