在数据库架构中弃用列的最佳方法是什么? [英] What's the best way to deprecate a column in a database schema?

问题描述

在阅读了许多有关数据库模式迁移和版本的问题之后，我提出了一种在更新过程中安全地更新数据库模式的方案.基本思想是，在更新期间，我们将数据库导出到文件中，删除并重新创建所有表，然后重新导入所有内容.那里没什么花哨的或冒险的.

After reading through many of the questions here about DB schema migration and versions, I've come up with a scheme to safely update DB schema during our update process. The basic idea is that during an update, we export the database to file, drop and re-create all tables, and then re-import everything. Nothing too fancy or risky there.

问题在于该系统有些病毒"，这意味着添加列或表仅是安全的，因为删除它们会在重新导入数据时引起问题.通常，忽略这些列就可以了，但是问题是实际上已重构了许多被删除的项，而代码中的旧项却使其他程序员误以为他们可以使用它们.

The problem is that this system is somewhat "viral", meaning that it is only safe to add columns or tables, since removing them would cause problems when re-importing the data. Normally, I would be fine just ignoring these columns, but the problem is that many of the removed items have actually been refactored, and the presence of the old ones in the code fools other programmers into thinking that they can use them.

因此，我想找到一种方法可以将列或表标记为已弃用.在理想情况下，将在更新模式时标记不赞成使用的对象，但是在下一次更新期间，我们的备份脚本将不会选择那些已标记方式的对象，从而使我们最终可以逐步淘汰模式的这些部分

So, I would like to find a way to be able to mark columns or tables as deprecated. In the ideal case, the deprecated objects would be marked while updating the schema, but then during the next update our backup script would simply not SELECT the objects which have been marked in this way, allowing us to eventually phase out these parts of the schema.

我发现MySQL(可能还有其他数据库平台，但这是我们正在使用的平台)支持字段和表的COLUMN属性.这是完美的，除了我无法弄清楚如何以有意义的方式实际使用它.我该如何编写SQL查询以获取所有 not 都不包含包含单词"deprecated"的注释匹配文本的列名?还是我看这个问题全错了，而错过了一个更好的方法呢?

I have found that MySQL (and probably other DB platforms too, but this is the one we are using) supports the COLUMN attribute to both fields and tables. This would be perfect, except that I can't figure out how to actually use it in a meaningful manner. How would I go about writing an SQL query to get all column names which do not contain a comment matching text containing the word "deprecated"? Or am I looking at this problem all wrong, and missing a much better way to do this?

推荐答案

也许您应该重构以在表上使用视图，因为视图永远不包含分解后的列.

Maybe you should refactor to use views over your tables, where the views never include the deprocated columns.

这篇关于在数据库架构中弃用列的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！