如何在关系型数据库中存储不同版本的数据？

Question

以下是一个例子，比如我有一张名为Profile的表格，并且有不同的列，例如：

id, firstName, secondName, address

通常情况下，我会创建一个个人资料，填写信息，数据库将变成这样：

 1| Ted | WONG | Hong Kong |

之后，我可能会更新数据，就像这样。

 1| Ted | WONG | US |

我会使用UPDATE SQL命令更改数据中的香港，但我担心会丢失之前的数据。是否有办法让数据库跟踪以前的数据并维护当前信息？谢谢。

- DNB5brims

---

我希望它是通过UPDATE而不是ALTER更改的 - 你正在使用哪个数据库？ - Damien_The_Unbeliever

你是对的，我只是打错了。MySQL。 - DNB5brims

我已经添加了那个标签并删除了我的回答，因为我认为它对MySQL无效。 - Damien_The_Unbeliever

Answer 1

添加一个版本号列，并在每次更新时递增，但保留相同的ID。然后，在检索给定ID的最新行时，需要执行以下操作：

where versionNo = (select max(versionNo) from table where id = <outerTableAliasOrVariable>.id)

- kaj

---

在每个查询中执行max(versionNo)可能会在处理大量数据时导致性能下降。我做类似的事情，只是我更喜欢将版本0作为最新版本，并在任何更新之前将所有数据存档到版本X。这使得查询变得更加简单。 - Carter Medlin

Answer 2

在你想要跟踪版本的每个表和它们的子表中，都添加一个名为“Version”的列作为第一列。该列应作为聚集主键的第一列添加。当前版本应始终为Version=0，以使编写查询更加方便，并且不需要在每个查询中搜索MAX（version）。确保所有子表也包含Version列并在其外键中引用它。在更新之前，将所有相关表中的Version=0数据复制到Version=1。下一个更新将复制到Version=2等等。最终，你将在Version=1中拥有最老的数据，在Version=0中拥有最新的数据，在Version=X中拥有最新的数据。这样，你就可以创建复杂的数据库模式，控制数据版本，并能够通过从Version=X复制数据到Version=0来完全回滚数据到历史版本。

在你的情况下，你的新表结构如下： Version, id, firstName, secondName, address (PK- VersionId, id) 如果你有一个子表，例如交易表，则会像这样： Version, id, TransactionId, Amount (PK- VersionId, id, TransactionId) 我使用了这种方法来维护数据版本，而无需创建额外的支持表。

- Carter Medlin

Answer 3

这可以通过存储所有历史数据并拥有一个group列来实现：

id, firstname,secondname, address, group

那么当您更新数据时，您永远不会使用ALTER，而是只需添加新的修订版本。

因此，您的表将如下所示：

1, Ted, Wong, Hong Kong, 1
2, Ted, Wong, US, 1

因此，要检索当前（或最后）修订版本，您需要选择相应的修订版本：

SELECT TOP 1 * FROM <table> WHERE Group = 1 ORDER BY id DESC

- Simon

---

但我使用id作为关键字与其他表建立关系，例如，我有一个带有profile_id的用户表，如果我这样做，当我有一个新版本时，我的profile表id会更改，而用户表的profile_id将指向未更新的数据。 - DNB5brims

是的，正确的。在这种情况下，您需要更好地理顺数据库结构。选择不会改变的列并将它们放入第一个表中。然后，您可以将配置文件数据放入另一个表中，并使用类似于我的或KAJ的方案，在数据上执行联接，使用最新的修订版或版本号。 - Simon