是否可以在主要 SQL 数据库中回滚 CREATE TABLE 和 ALTER TABLE 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4692690/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?
提问by joeforker
I am working on a program that issues DDL. I would like to know whether CREATE TABLEand similar DDL can be rolled back in
我正在开发一个发布 DDL 的程序。我想知道是否CREATE TABLE可以回滚类似的DDL
- Postgres
- MySQL
- SQLite
- et al
- Postgres
- MySQL
- SQLite
- 等
Describe how each database handles transactions with DDL.
描述每个数据库如何使用 DDL 处理事务。
回答by joeforker
http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysisprovides an overview of this issue from PostgreSQL's perspective.
http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis从 PostgreSQL 的角度概述了这个问题。
Is DDL transactional according to this document?
根据本文档，DDL 是事务性的吗？
- PostgreSQL - yes
- MySQL - no; DDL causes an implicit commit
- Oracle Database 11g Release 2 and above - by default, no, but an alternative called edition-based redefinition exists
- Older versions of Oracle - no; DDL causes an implicit commit
- SQL Server - yes
- Sybase Adaptive Server - yes
- DB2 - yes
- Informix - yes
- Firebird (Interbase) - yes
- PostgreSQL - 是的
- MySQL - 没有；DDL 导致隐式提交
- Oracle 数据库 11g 第 2 版及更高版本 - 默认情况下，不，但存在称为基于版本的重新定义的替代方案
- 旧版本的 Oracle - 没有；DDL 导致隐式提交
- SQL Server - 是
- Sybase Adaptive Server - 是
- DB2 - 是
- Informix - 是
- Firebird (Interbase) - 是
SQLite also appears to have transactional DDL as well. I was able to ROLLBACKa CREATE TABLEstatement in SQLite. Its CREATE TABLEdocumentation does not mention any special transactional 'gotchas'.
SQLite 似乎也具有事务性 DDL。我能够ROLLBACK一个CREATE TABLE在SQLite的声明。它的CREATE TABLE文档没有提到任何特殊的交易“陷阱”。
回答by araqnid
PostgreSQL has transactional DDL for most database objects (certainly tables, indices etc but not databases, users). However practically any DDL will get an ACCESS EXCLUSIVElock on the target object, making it completely inaccessible until the DDL transaction finishes. Also, not all situations are quite handled- for example, if you try to select from table foowhile another transaction is dropping it and creating a replacement table foo, then the blocked transaction will finally receive an error rather than finding the new footable. (Edit: this was fixed in or before PostgreSQL 9.3)
PostgreSQL 为大多数数据库对象（当然是表、索引等，但不是数据库、用户）提供事务性 DDL。然而，实际上任何 DDL 都会ACCESS EXCLUSIVE锁定目标对象，使其在 DDL 事务完成之前完全无法访问。此外，并非所有情况都得到妥善处理——例如，如果您尝试从表中进行选择，foo而另一个事务正在删除它并创建替换表foo，那么被阻止的事务最终将收到错误而不是找到新foo表。（编辑：这是在 PostgreSQL 9.3 中或之前修复的）
CREATE INDEX ... CONCURRENTLYis exceptional, it uses three transactions to add an index to a table while allowing concurrent updates, so it cannot itself be performed in a transaction.
CREATE INDEX ... CONCURRENTLY特殊的是，它使用三个事务向表添加索引同时允许并发更新，因此它本身不能在一个事务中执行。
Also the database maintenance command VACUUMcannot be used in a transaction.
此外，数据库维护命令VACUUM不能在事务中使用。
回答by PaulMest
Looks like the other answers are pretty outdated.
看起来其他答案已经过时了。
As of 2019:
截至 2019 年：
- Postgres has supported transactional DDL for many releases.
- SQLite has supported transactional DDL for many releases.
- MySQL has supported Atomic DDL since 8.0(which was released in 2018).
- Postgres 已支持许多版本的事务性 DDL。
- SQLite 已支持许多版本的事务性 DDL。
- MySQL从 8.0（2018 年发布）开始支持Atomic DDL。
回答by Dave Costa
While it is not strictly speaking a "rollback", in Oracle the FLASHBACK command can be used to undo these types of changes, if the database has been configured to support it.
虽然严格来说这不是“回滚”，但在 Oracle 中，FLASHBACK 命令可用于撤消这些类型的更改，前提是数据库已配置为支持它。
回答by Robert Sinclair
Can't be done with MySQLit seems, very dumb, but true... (as per the accepted answer)
似乎无法用MySQL完成，非常愚蠢，但确实如此......（根据接受的答案）
"The CREATE TABLE statement in InnoDB is processed as a single transaction. This means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during that transaction."
“InnoDB 中的 CREATE TABLE 语句作为单个事务处理。这意味着来自用户的 ROLLBACK 不会撤消用户在该事务期间所做的 CREATE TABLE 语句。”
https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html
https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html
Tried a few different ways and it simply won't roll back..
尝试了几种不同的方法，它根本不会回滚..
Work around is to simply set a failure flag and do "drop table tblname" if one of the queries failed..
解决方法是简单地设置一个失败标志，如果其中一个查询失败，则执行“删除表 tblname”。
