我们大家都习惯性的使用各种数据库中的自动提交(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,即恢复到默认设置。