mysql回滚语句是什么意思_如何回滚MySQL事务中的所有语句？

我需要更新包含另一个表(FK约束到oldsmalltable)的id的表(bigtable)的特定列,以指向另一个表上的id(FK约束到newsmalltable).基本上这就是我在做的事情：

DELIMITER //

CREATE PROCEDURE updatebigtable ()

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING ROLLBACK;

START TRANSACTION;

ALTER TABLE bigtable DROP FOREIGN KEY bigtable_ibfk_1,

MODIFY smalltable_id SMALLINT ;

UPDATE bigtable SET smalltable_id=CASE smalltable_id

WHEN 1 THEN 1592

WHEN 2 THEN 1593

WHEN 3 THEN 1602

...

ELSE 0

END;

ALTER TABLE bigtable ADD CONSTRAINT bigtable_ibfk_1

FOREIGN KEY(smalltable_id) REFERENCES newsmalltable(id);

COMMIT;

END//

DELIMITER ;

CALL updatebigtable();

DROP PROCEDURE updatebigtable;

我需要确保如果由于某种原因新的外键约束失败(例如,对于具有不同类型的列,错误将发生在最后一个alter table语句上),UPDATE和第一个ALTER TABLE也应该回滚,即他们应该保持原状.

根据MySQL documentation,通过使用START TRANSACTION,该事务禁用自动提交模式,这将不允许：

that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent.

我只发现这个问题与我的问题微不足道：

如果我提到的错误发生在事务中,之前的语句已经执行,更新“永久在磁盘上完成”……

我也试过放置SET autocommit = 0;在创建程序之前但行为仍然相同……我错过了什么吗？或者这是MySQL事务回滚的预期行为？

如果它有任何区别,我使用的是MySQL v.5.6.17.

最佳答案 ALTER TABLE语句总是导致隐式提交(

section 13.3.3 from MySQL docs,谢谢

wchiquito),这意味着即使它们在START TRANSACTION中; ……承诺;块,将在该块内完成的更改次数与提交的次数相同.

If you use ALTER TABLE on a transactional table or if you are using Windows, ALTER TABLE unlocks the table if you had done a LOCK TABLE on it. This is done because InnoDB and these operating systems cannot drop a table that is in use.

在执行alter和update语句时,唯一可以避免不需要的读/写操作的选项是模拟ALTER TABLE的所有步骤：

Create a new table named A-xxx with the requested structural changes.

Copy all rows from the original table to A-xxx.

Rename the original table to B-xxx.

Rename A-xxx to your original table name.

Delete B-xxx.

这样,更新可以在新表中完成(在步骤2之后),并且bigtable不可用的唯一时间是在执行步骤3和4(重命名)时.