我可以在ALTER TABLE中使用事务吗? [英] Can I use transactions with ALTER TABLE?
问题描述
我是SQL交易的初学者(实际上是新手)，所以我可能缺少明显的东西.
I'm a beginner (actually complete newbie) to SQL transactions, so I may be missing something obvious.
我有此SQL代码，正在尝试通过 phpMyAdmin 运行:
I have this SQL code, that I'm trying to run through phpMyAdmin:
START TRANSACTION;
INSERT INTO `users` VALUES(NULL, 'User A', 'user.a@example.com', '4', 'User A');
INSERT INTO `users` VALUES(NULL, 'User B', 'user.b@example.com', '3', 'User B');
ALTER TABLE `users` CHANGE `level` `level` TINYINT(3) UNSIGNED NOT NULL;
ALTER TABLE `users` CHANGE `number` `number` INT(10) UNSIGNED NOT NULL;
ALTER TABLE `users` ADD COLUMN `number` INT(10) UNSIGNED NOT NULL AFTER `id`;
COMMIT;
如您所见，第二个ALTER会导致#1054 - Unknown column 'number' in 'users'错误.
As you may see, second ALTER causes #1054 - Unknown column 'number' in 'users' error.
但是，当它发生在 phpMyAdmin 中时，我可以看到，前两个INSERT(或整个事务)没有回滚. users表确实包含两个新记录.
But, when it happens in phpMyAdmin, I can see, that first two INSERTs (or the entire transaction) is not rolled back. The users table does contain two new records.
我想念什么? phpMyAdmin不支持交易吗?还是我不明白交易实际上是如何工作的，这很正常，如果发生错误，这两个INSERT不会回滚?
What am I missing? phpMyAdmin doesn't support transactions? Or I don't understand, how transactions actually work, and this is pretty normal, that these two INSERTs are not rolled back in case of error?
推荐答案
某些语句(最值得注意的是 之前，它们将被执行并无法回滚-这样可以防止先前的DML更改也回滚.
Some statements (most notably DDL) in MySQL cause an implicit commit before they are executed and cannot be rolled back - as such this prevents the prior DML changes from being rolled back as well.
本节中列出的语句(及其任何同义词)隐式结束当前会话中任何活动的事务，就好像您在执行该语句之前执行了COMMIT 一样.从MySQL 5.5.3开始，大多数这些语句在执行后也会引起隐式提交.有关更多详细信息，请参阅本节末尾.
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; for additional details, see the end of this section.
由于ALTER TABLE是受影响的语句之一，因此SQL批处理有效地视为:
Since ALTER TABLE is one of the affected statements, the the SQL batch is effectively treated as:
START TRANSACTION;
INSERT INTO `users` VALUES(NULL, 'User A', 'user.a@example.com', '4', 'User A');
COMMIT; -- prevents ROLLBACK of insert(s), even if DDL fails
ALTER TABLE `users` CHANGE `level` `level` TINYINT(3) UNSIGNED NOT NULL;
建议的解决方案是保持DDL和DML分开. 文档说:
The suggested solution is to keep DDL and DML separated. The documentation says:
您应设计[DML]事务，使其不包含此类[DDL]语句.如果您在事务中提早发布了一个无法回滚的语句，然后又有另一个语句失败，则在这种情况下，通过发出ROLLBACK语句就无法回滚事务的全部效果.
You should design your [DML] transactions not to include such [DDL] statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a ROLLBACK statement.
这篇关于我可以在ALTER TABLE中使用事务吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
