mysql事务回滚卡住_mysql事务-在任何异常时回滚

Is it possible to roll back automatically if any error occurs on a list of mysql commands?

for example something along the lines of:

begin transaction;

insert into myTable values1 ...

insert into myTable values2 ...; -- will throw an error

commit;

now, on execute i want the whole transaction to fail, and therefore i should NOT see values1 in myTable.

but unfortunately the table is being pupulated with values1 even though the transaction has errors.

any ideas how i make it to roll back? (again, on any error)?

EDIT - changed from DDL to standard SQL

解决方案DELIMITER $$

CREATE PROCEDURE `sp_fail`()

BEGIN

DECLARE `_rollback` BOOL DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

START TRANSACTION;

INSERT INTO `tablea` (`date`) VALUES (NOW());

INSERT INTO `tableb` (`date`) VALUES (NOW());

INSERT INTO `tablec` (`date`) VALUES (NOW()); -- FAIL

IF `_rollback` THEN

ROLLBACK;

ELSE

COMMIT;

END IF;

END$$

DELIMITER ;

For a complete example, check the following SQL Fiddle.