向后兼容数据库更改
我与我们的 DBA 讨论了如何更改数据库模式。他的观点是所有变化都必须是可逆的。例如：
- 过时的表/列不应在变得多余后立即删除。相反，它们应该至少保留几个版本。
- 不要重命名表/列，而是创建一个新表/列，并将旧表/列的内容复制到新表/列中。
- 当需要修改名为“foo”的存储过程/触发器时，保留原始存储过程/触发器并创建一个名为“foo2”的新存储过程/触发器。当然，这意味着对存储过程/触发器的所有引用都必须更新以引用新名称。
这种方法的好处是，如果（例如）发布失败并且需要，数据库可以切换到以前的版本恢复到应用程序的先前版本。如果只是删除表和列，这是不可能的。
对于这种方法是否明智，我有自己的看法，但我暂时不会透露，因为担心反应会产生偏差。如果有什么不同的话，环境是一家开发社交网络应用程序的初创公司。
I had a discussion with our DBA about how to change a database schema. His opinion is that all changes must be reversible. For example:
- Obsolete tables/columns should not be dropped as soon as they become redundant. Instead, they should be kept for at least a few releases.
- Instead of renaming a table/column, create a new table/column and copy the contents from the old into the new
- When a stored proc/trigger named 'foo' needs to be modified, leave the original stored proc/trigger in place and create a new stored proc/trigger named 'foo2'. Of course, this means that all references to the stored proc/trigger must be updated to refer to the new name
The benefit of this approach is that the database can be switched to a previous version if (for example) a release fails and one needs to revert to a previous version of the application. This would not be possible if tables and columns were simply dropped.
I have my own opinions about the wisdom of this approach, but I'll keep them to myself for the time being for fear of biasing the responses. In case it makes any difference, the environment is a startup developing a social network app.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
你没有说你所处的软件环境是什么，但从企业（银行）工作来看，这些是我的观点。
一般原则是正确的，发布过程中可能会出现问题，可能不是 SQL 代码，而是客户端代码，并且您需要能够恢复服务器。我已经多次看到这种情况发生。
如果问题是在发布后一段时间（例如几个小时）发现的，那么您将不得不处理在此期间输入的所有数据。
可能可以使用新数据更新发布时获取的数据库副本，但环境可能不允许这样做（尽管这是我完成大型发布的主要方式）。
根据我的经验，发布问题可能会影响系统的一小部分，但大部分都没有问题，因此您不希望只是为了恢复一小部分而关闭并恢复整个系统。
然而，考虑到更改需要是可逆的，我认为您的 dba 有点保守。
表和列应该在某个阶段删除，但是可以等到以后的版本，以便您可以恢复是的，
总是复制数据（事实上，最好不要重命名，除非名称完全不合适，否则肯定会存在更改的风险）成本任何利益）。如果要更改列的类型，则取决于 SQL 服务器以及正在执行的操作。例如，在 Sybase 上，我允许增加列的大小，因为这不会改变数据，但减小列的大小将需要副本，因为数据值可能会受到影响。
至于存储过程和触发器，我不会重命名而只是覆盖，因为这就像编译的代码。您正在更改的对象不依赖于数据，因此可以立即重新创建。虽然这确实假设您可以轻松地从版本控制等获取存储过程的任何先前版本。（我见过数据库，其中代码不受版本控制，唯一的版本在数据库中，然后我可以看到不需要覆盖代码 - 但我会在下一个版本之前控制代码）
You don't say what software environment you are in but from enterprise (banking) work these are my views.
The general principle is correct something can go wrong with the release perhaps not the SQL code but the client code and you would need to be able to revert the server. I have seen this happen several times.
If the problem is found some time after the release say a few hours then you will have to deal with any data entered in the meantime.
It might be that a duplicate of the database taken at the release time could be updated with new data but the environment might not allow this (although this is the main way I have done large releases).
In my experience a release issue could affect one small part of the system and most of it is OK and so you do not want to shut down and revert the whole system just to recover the small part.
However given that the changes need to be reversible I think your dba is being a bit conservative.
Tables and columns should be dropped at some stage but that could wait until a later release so that you could revert back
Yes always copy the data (In fact probably best not to rename as unless the name is totally inappropiate the risk of doing a change surely out costs any benefit). If the type of the column is to be changed it depends on the SQL server and what is being done. For examle on Sybase I would allow an increase in the size of the column as that does not alter data but decreasing teh size would require a copy as data values could be affected.
As for stored procedures and triggers I would not do a renaming and just overwrite as this is like compiled code. The object you are altering does not depend on the data and so can be recreated immediately. Although this does assume that yo can easily get any previous version of the stored procedure from version control etc. (I have seen dbs where code is not under version control and the only version is in the db and then I can see the need not to overwrite the code - but I would get the code under control before the next release)
我同意您应该始终备份数据库，但您也不应该用无用的信息污染您的数据库。就像您不应该用无用的代码污染您的代码一样。
备份数据库，然后制作你的模组。如果发生问题，请恢复到备份。
始终将所有内容保留在数据库中会导致令人难以置信的膨胀。不仅如此，您还可能会受到一些性能问题的影响。 最重要的是，以后没有人会想碰它，因为他们不知道它为什么在那里。与代码不同，将来要弄清楚为什么数据库中会有额外的列等要困难得多。他们不会知道这是遗留数据/代码，因此他们只会继续维护它！
I agree that you should always make backups of your database, but you also shouldn't pollute your database with useless information. The same as you shouldn't keep your code polluted with useless code.
Make a backup of the database, and then make your mods. If something happens, revert to your backup.
Keeping everything in the database all the time will lead to incredible bloat. Not only that, you can get hit by some performance issues. And MOST IMPORTANTLY, later no one will want to touch it since they won't know why it's there. Unlike code, it's way harder to figure out why there are additional columns, etc. in a database at a future date. They won't know it's legacy data/code and therefore they'll just keep maintaining it!
“过时的表/列不应在变得多余后立即删除。相反，它们应该保留至少几个版本。”
然后，他是否还保留了管理那些他不想立即删除的列的约束？
这意味着更新失败可能会因为用户声明的约束不再是业务规则的一部分而出现？
我对那些始终寻求“逐步、谨慎地逐步淘汰”的人表示同情。我只是不知道这种方法在您提到的所有示例中的数据库上下文中是否可行。
"Obsolete tables/columns should not be dropped as soon as they become redundant. Instead, they should be kept for at least a few releases."
And does he then also keep the constraints that govern those columns that he doesn't want to drop immediately ?
Meaning that update failures might possibly arise because of constraints that the user has declared are no longer part of the business rules ?
I'm kind of sympathetic to people who consistently seek to "phase out gradually and carefully". I just don't know whether that approach is tenable in a database context in all the examples you mention.
听起来DBA懒得做备份。 ;)
This sounds like the DBA is too lazy to do backups. ;)