mysql - InnoDB 外键和提交

Question

我正在将现有数据库从 MyISAM 转换为 InnoDB 并实现各种外键，但在数据库上运行转换脚本时遇到问题:-

我正在运行以下所有查询

DELETE FROM example WHERE user NOT IN (select id FROM users);
ALTER TABLE `example` CHANGE `user` `user` INT( 11 ) UNSIGNED NOT NULL ;
ALTER TABLE example ADD FOREIGN KEY (user) REFERENCES users(ID);
ALTER TABLE example ADD FOREIGN KEY (car) REFERENCES cars(ID);

当我运行所有查询时，它由于外键约束而失败，因为 DELETE 语句尚未运行 - 如果我单独运行它们，那很好 - 这是 innodb 数据库上提交的问题还是是因为在下一个查询之前删除速度没有完成吗？

有两个ID外键也可以吗？ (两个不同的表users.id和cars.id)。

谢谢!

Answer 1

不知道错误消息可能会说什么或您想要完成什么，但 ALTER TABLE 是 DDL 语句，并且这些语句无法在 MySQL 中回滚。 Statements That Cause an Implicit Commi手册章节说明:

The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement. As of MySQL 5.5.3, most of these statements also cause an implicit commit after executing;

[...]

Data definition language (DDL) statements that define or modify database objects

[...]

ALTER TABLE, CREATE TABLE, and DROP TABLE do not commit a transaction if the TEMPORARY keyword is used. (This does not apply to other operations on temporary tables such as CREATE INDEX, which do cause a commit.) However, although no implicit commit occurs, neither can the statement be rolled back. Therefore, use of such statements will violate transaction atomicity: For example, if you use CREATE
TEMPORARY TABLE and then roll back the transaction, the table remains in existence.