关于MySQL的事务提交

缺乏、安全感 2022-07-12 20:15 180阅读 0赞

我们大家都习惯性的使用各种数据库中的自动提交（autocommit），如Oracle、Sq lServer、MySQL等。

但有时候自动提交会降低执行效率，比如在使用循环插入、更新等语句时，如下示例：

CREATE PROCEDURE procedure1(count int unsigned)
    
    begin
    
    declare s int unsigned default 1;
    
    declare c char(80) default repeat('a',80);
    
    while s<=count do
    
    insert into table1 select NULL,c;
    
    set s=s+1;
    
    end while;
    
    end;

上面这个存储过程会自动提交count次，即insert一条，提交一次。

这样肯定会降低执行效率的，那如何才能提高效率呢？有没有办法让其只执行一次呢？  
当然有，只要把autocommit禁止掉，然后COMMIT即可，看如下示例：

CREATE PROCEDURE procedure2(count int unsigned)
    
    begin
    
    declare s int unsigned default 1;
    
    declare c char(80) default repeat('a',80);
    
    start transaction;   --显式地开始一个事务
    
    while s<=count do
    
    insert inito table1 select NULL,c;
    
    set s=s+1;
    
    end while;
    
    commit;
    
    end;

可以看到，上面的procedure只提交了一次，提交了一次肯定比提交好多次节省时间咯。

自动提交在刚使用数据库时，容易犯错，产生错误之后甚至无法理解等，大多数数据库都采用的是自动提交的默认设置，当然MySQL亦是。

那如何才能改变当前自动提交的方式呢？主要有两种方式来实现：

第一，直接禁止自动提交设置：

mysql> set autocommit=0;

注意：不同数据库中，自动提交的设置是不同的。这里我们说的是MySQL，如Oracle中为：SQL>SET AUTOCOMMIT ON/OFF; 其中ON为自动提交设置。

第二，使用START TRANSACTION、BEGIN来显式地开启一个事务。如上面的存储过程procedure2。

解释：用START TRANSACTION、BEGIN显式地开启事务后，在默认设置下，MySQL会自动执行SET AUTOCOMMIT=0;命令，即先禁止自动提交设置，并在COMMIT或者ROLLBACK结束一个事务后再执行SET AUTOCOMMIT=1，即恢复到默认设置。