我可以在ALTER TABLE中使用事务吗?

本文介绍了我可以在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', '4', 'User A'); INSERT INTO `users` VALUES(NULL, 'User B', 'user.b@example', '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?

推荐答案

某些语句(最值得注意的是 DDL )在MySQL 之前，它们将被执行并无法回滚-这样可以防止先前的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', '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.