SET autocommit=0 与 START TRANSACTION 的区别

在MySQL中, SET autocommit=0;指事务非自动提交，自此句命令执行以后，每个SQL语句或者语句块所在的事务都需要显式调用commit才能提交事务。

不管autocommit 是1还是0 :

START TRANSACTION + commit数据才会生效，ROLLBACK会回滚。

当autocommit 为 0 时:

有没有START TRANSACTION都没关系。

只有当commit数据才会生效，ROLLBACK会回滚。

当autocommit 为1时 ，如果没有START TRANSACTION, 调用ROLLBACK是没有用的, 即便设置了SAVEPOINT。 也就是说, 必须设置START TRANSACTION才能回滚。

代码演示之前, 我们先建个table:

USE `test`;
CREATE TABLE `autocommit_trans`(
    `id` INT unsigned NOT NULL AUTO_INCREMENT,
    `user_name` varchar(20) NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE=InnoDB;
INSERT INTO `autocommit_trans`(`id`, `user_name`)VALUES(1, 'aben'),(2, 'sky');
一键获取完整项目代码
sql
SET autocommit=1 + START TRANSACTION: 回滚成功
SET autocommit =1;
 
DROP PROCEDURE IF EXISTS  `sp_autocommit1_trans`;
DELIMITER $$
CREATE  PROCEDURE `sp_autocommit1_trans`()
BEGIN
    -- 错误代码
    DECLARE err_code INTEGER DEFAULT 0;
    -- 如果sql执行异常, 或者警告, 或者数据不存在, 都设置错误代码为1
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  SET err_code=1;
 
    START TRANSACTION;
    SAVEPOINT p1;  -- 设置埋点
 
    UPDATE `autocommit_trans` SET `user_name` = 'aben2' WHERE `id` =1; 
    UPDATE `autocommit_trans` SET `gender` = 'female' WHERE `id` =2; -- 栏位不存在, SQL错误
    UPDATE `autocommit_trans` SET `user_name` = 'john' WHERE `id` =3; -- 记录不存在
 
    IF err_code = 1 THEN 
        ROLLBACK to p1; -- 回滚到埋点 p1
    ELSE 
        COMMIT; 
    END IF;
 
END$$
DELIMITER ;
 
CALL `test`.`sp_autocommit1_trans`();
一键获取完整项目代码
sql

回滚成功

SET autocommit=0 + START TRANSACTION: 回滚成功
SET autocommit =0;
 
DROP PROCEDURE IF EXISTS  `sp_autocommit0_trans`;
DELIMITER $$
CREATE  PROCEDURE `sp_autocommit0_trans`()
BEGIN
    -- 错误代码
    DECLARE err_code INTEGER DEFAULT 0;
    -- 如果sql执行异常, 或者警告, 或者数据不存在, 都设置错误代码为1
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  SET err_code=1;
 
    START TRANSACTION;
    SAVEPOINT p1;  -- 设置埋点
 
    UPDATE `autocommit_trans` SET `user_name` = 'aben2' WHERE `id` =1; 
    UPDATE `autocommit_trans` SET `gender` = 'female' WHERE `id` =2; -- 栏位不存在, SQL错误
    UPDATE `autocommit_trans` SET `user_name` = 'john' WHERE `id` =3; -- 记录不存在
 
    IF err_code = 1 THEN 
        ROLLBACK to p1; -- 回滚到埋点 p1
    ELSE 
        COMMIT; 
    END IF;
 
END$$
DELIMITER ;
 
CALL `test`.`sp_autocommit0_trans`();
一键获取完整项目代码
sql

回滚成功

SET autocommit=0 , 没有START TRANSACTION: 回滚成功
SET autocommit =0;
 
DROP PROCEDURE IF EXISTS  `sp_autocommit0_notrans`;
DELIMITER $$
CREATE  PROCEDURE `sp_autocommit0_notrans`()
BEGIN
    -- 错误代码
    DECLARE err_code INTEGER DEFAULT 0;
    -- 如果sql执行异常, 或者警告, 或者数据不存在, 都设置错误代码为1
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  SET err_code=1;
 
    -- START TRANSACTION; -- 这里没有哦!
    SAVEPOINT p1;  -- 设置埋点
 
    UPDATE `autocommit_trans` SET `user_name` = 'aben2' WHERE `id` =1; 
    UPDATE `autocommit_trans` SET `gender` = 'female' WHERE `id` =2; -- 栏位不存在, SQL错误
    UPDATE `autocommit_trans` SET `user_name` = 'john' WHERE `id` =3; -- 记录不存在
 
    IF err_code = 1 THEN 
        ROLLBACK to p1; -- 回滚到埋点 p1
    ELSE 
        COMMIT; 
    END IF;
 
END$$
DELIMITER ;
 
CALL `test`.`sp_autocommit0_notrans`();
一键获取完整项目代码
sql

回滚成功

SET autocommit=1 , 没有START TRANSACTION: 即使有埋点, 也无法回滚
SET autocommit =1;
 
DROP PROCEDURE IF EXISTS  `sp_autocommit1_notrans`;
DELIMITER $$
CREATE  PROCEDURE `sp_autocommit1_notrans`()
BEGIN
    -- 错误代码
    DECLARE err_code INTEGER DEFAULT 0;
    -- 如果sql执行异常, 或者警告, 或者数据不存在, 都设置错误代码为1
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  SET err_code=1;
 
    -- START TRANSACTION; -- 这里没有哦!
    SAVEPOINT p1;  -- 设置埋点
 
    UPDATE `autocommit_trans` SET `user_name` = 'aben2' WHERE `id` =1; 
    UPDATE `autocommit_trans` SET `gender` = 'female' WHERE `id` =2; -- 栏位不存在, SQL错误
    UPDATE `autocommit_trans` SET `user_name` = 'john' WHERE `id` =3; -- 记录不存在
 
    IF err_code = 1 THEN
        ROLLBACK to p1; -- 回滚到埋点p1
    ELSE 
        COMMIT; 
    END IF;
 
END$$
DELIMITER ;
 
CALL `test`.`sp_autocommit1_notrans`();
一键获取完整项目代码
sql

回滚失败, 我们可以从结果中看到数据已经被修改:

 

每种情况的演示代码执行后, " SELECT * FROM autocommit_trans "即可确认是否回滚.