每个 DDL SQL 命令都是可逆的吗？ 【数据库版本控制】
我想设置一种用于跟踪数据库架构更改的机制，例如 this 中描述的机制答案：
对于您对 数据库，您编写一个新的迁移。 迁移通常有两种方法： 一种“向上”方法，其中的变化 被应用和“向下”方法 更改将被撤消。 单个 命令使数据库达到 日期，也可以用来携带 数据库到特定版本 架构。
我的问题如下：“up”方法中的每个 DDL 命令都是可逆的吗？ 换句话说，我们总能提供一个“向下”的方法吗？ 你能想象有什么 DDL 命令不能被“down”吗？
请不要考虑典型的数据迁移问题，即在“up”方法期间我们会丢失数据：例如，将字段类型从 datetime (DateOfBirth) 更改为 int (YearOfBirth) 我们正在丢失无法恢复的数据。
I want to setup a mechanism for tracking DB schema changes, such the one described in this answer:
For every change you make to the
database, you write a new migration.
Migrations typically have two methods:
an "up" method in which the changes
are applied and a "down" method in
which the changes are undone. A single
command brings the database up to
date, and can also be used to bring
the database to a specific version of
the schema.
My question is the following: Is every DDL command in an "up" method reversible? In other words, can we always provide a "down" method? Can you imagine any DDL command that can not be "down"ed?
Please, do not consider the typical data migration problem where during the "up" method we have loss of data: e.g. changing a field type from datetime (DateOfBirth) to int (YearOfBirth) we are losing data that can not be restored.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的，您已经发现了丢失数据的情况，无论是通过转换数据还是简单地在“向上”迁移中删除列。
另一个例子是您可以删除 SEQUENCE 对象，从而丢失其状态。 “向下”迁移将重新创建序列，但会从 1 重新开始。这可能会导致序列生成重复值。 如果您在空数据库上执行迁移，并且您希望序列从 1 开始，那么这不是问题，但如果您有一定数量的数据行，您希望将序列重置为最大值当前正在使用，这很难可靠地完成，除非您对该表有独占锁。
任何其他依赖于数据库中数据状态的 DDL 也有类似的问题。 首先，这可能不是一个好的模式设计，我只是想考虑适合您问题的任何情况。
Yes, you've identified cases where you lose data, either by transforming it or simply DROP COLUMN in the "up" migration.
Another example is that you could drop a SEQUENCE object, thus losing its state. The "down" migration would recreate the sequence, but it would start over at 1. This could cause duplicate values to be generated by the sequence. Not a problem if you're performing a migration on an empty database, and you want the sequence to start at 1 anyway, but if you have some number of rows of data, you'd want the sequence to be reset to the greatest value currently in use, which is hard to do reliably, unless you have an exclusive lock on that table.
Any other DDL that is dependent on the state of data in the database has similar problems. That's probably not a good schema design in the first place, I'm just trying to think of any cases that fit your question.
在 SQL Server 中，我所知道的每个 DDL 命令都是向上/向下对。
in sql server every DDL command that i know of is an up/down pair.
除了数据丢失之外，我所做的每一次迁移都是可逆的。 也就是说，Rails 提供了一种将迁移标记为“破坏性”的方法：
请参阅此处的 API 文档。
Other than loss of data, every migration I've ever done is reversible. That said, Rails offers a way to mark a migration as "destructive":
See the API documentation here.