DROP TABLE IF EXISTS A; CREATE TABLE A (ID INT); INSERT INTO A SELECT 1;START TRANSACTION; DELETE FROM A; SELECT; --这里出错了,但是没有回滚 COMMIT; SELECT * FROM A;我的理解是,事务开始后先删除了A表的数据,然后遇到了错误,应该会自动回滚,但是没有回滚,A里面的数据还是被删了。
DECLARE EXIT HANDLER FOR SQLEXCEPTION rollback; START TRANSACTION; DELETE FROM A; SELECT; --这里出错了,但是没有回滚 COMMIT; 没有rollback怎么回滚
MYSQL不支持匿名块 DROP TABLE IF EXISTS Ac; CREATE TABLE `ac` ( `ID` INT(11) DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO Ac SELECT 1; DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @aa=1; START TRANSACTION; DELETE FROM Ac; SELECT; IF @aa=0 THEN COMMIT; END IF; SELECT * FROM Ac;
DROP TABLE IF EXISTS Ac; CREATE TABLE `ac` ( `ID` INT(11) DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO Ac SELECT 6; SET @aa=0; DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @aa=1; START TRANSACTION; DELETE FROM Ac; SELECT; IF @aa=0 THEN COMMIT; END IF; SELECT * FROM Ac;
START TRANSACTION; DELETE FROM A; SELECT; --这里出错了,但是没有回滚 COMMIT; 贴出你的错误信息。如果只是SELECT; 是不会出错的。
一般是在执行过程中出现错误,就会rollback,如果执行过程中没有出现错误,则不会回滚
我是这样测试的,在一个事务里面有一个语句出错,但是没有回滚前面的语句:mysql> DROP TABLE IF EXISTS A;CREATE TABLE A(id INT)ENGINE = INNODB;START TRANSACTION;INSERT INTO A SELECT 1;SELECT * FROM sss; #sss不存在COMMIT;SELECT * FROM A; Query OK, 0 rows affectedQuery OK, 0 rows affectedQuery OK, 0 rows affectedQuery OK, 1 row affected Records: 1 Duplicates: 0 Warnings: 01146 - Table 'testlink.sss' doesn't exist Query OK, 0 rows affected+----+ | id | +----+ | 1 | +----+ 1 row in set
`ID` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
是InnoDB
START TRANSACTION;
DELETE FROM A;
SELECT; --这里出错了,但是没有回滚
COMMIT; 没有rollback怎么回滚
DROP TABLE IF EXISTS Ac;
CREATE TABLE `ac` (
`ID` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO Ac SELECT 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @aa=1;
START TRANSACTION;
DELETE FROM Ac;
SELECT;
IF @aa=0 THEN
COMMIT;
END IF;
SELECT * FROM Ac;
CREATE TABLE `ac` (
`ID` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO Ac SELECT 6;
SET @aa=0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @aa=1;
START TRANSACTION;
DELETE FROM Ac;
SELECT;
IF @aa=0 THEN
COMMIT;
END IF;
SELECT * FROM Ac;
DELETE FROM A;
SELECT; --这里出错了,但是没有回滚
COMMIT;
贴出你的错误信息。如果只是SELECT; 是不会出错的。
Query OK, 0 rows affectedQuery OK, 0 rows affectedQuery OK, 0 rows affectedQuery OK, 1 row affected
Records: 1 Duplicates: 0 Warnings: 01146 - Table 'testlink.sss' doesn't exist
Query OK, 0 rows affected+----+
| id |
+----+
| 1 |
+----+
1 row in set