mysql中 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');

1. 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`();

回滚成功

2. 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`();

回滚成功

3. 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`();

回滚成功

4. 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`();

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

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