我需要更新一个表 (bigtable
) 中另一张表的 id (外键约束为 oldsmalltable
) 所在的特定列,使其指向另一个表的 id (外键约束为 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文档,通过使用
START TRANSACTION
,该事务的自动提交模式被禁用,这将不允许:只要您执行更新(修改)表的语句,MySQL就会将更新存储在磁盘上以使其永久化。
我只发现这个问题与我的问题有最少的关联: 如何在MySQL存储过程中使用事务? 如果在事务内发生了我提到的错误,则先前的语句已经执行,并且更新已经“永久地存储在磁盘上”...
我还尝试在创建过程之前放置
SET autocommit=0;
,但行为仍然相同... 我错过了什么吗?还是这是MySQL事务回滚的预期行为?如果有任何区别,我正在使用MySQL v.5.6.17。