如何对mysql中存储的数据进行版本控制
我正在尝试使用一个简单的 mysql 数据库，但对其进行调整，以便每个字段都备份到无限数量的版本。我可以说明这一点的最好方法是用该字段曾经拥有的所有值的堆栈替换每个表的每个字段（每个值都应该带有时间戳）。我想这有点像为我的所有数据定制版本控制。
关于如何做到这一点有什么想法吗？
I'm trying to use a simple mysql database but tweak it so that every field is backed up up to an indefinite number of versions. The best way I can illustrate this is by replacing each and every field of every table with a stack of all the values this field has ever had (each of these values should be timestamped). I guess it's kind of like having customized version control for all my data..
Any ideas on how to do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
对表“跟踪任何更改”的常用方法是在表上添加插入/更新/删除触发过程，并将这些记录保存在历史表中。
例如，如果您的主数据表是“ItemInfo”，那么您还会有一个 ItemInfo_History 表，每次发生任何更改（通过触发器）时，该表都会获取新记录的副本。
这可以保持主表的性能一致，但如果需要，您可以访问任何更改的历史记录。
以下是一些示例，它们适用于 SQL Server，但它们演示了逻辑：
我的 存储库表
我的存储库历史记录表
我的存储库插入触发器过程
我的存储库更新触发过程
The usual method for "tracking any changes" to a table is to add insert/update/delete trigger procedures on the table and have those records saved in a history table.
For example, if your main data table is "ItemInfo" then you would also have an ItemInfo_History table that got a copy of the new record every time anything changed (via the triggers).
This keeps the performance of your primary table consistent, yet gives you access to the history of any changes if you need it.
Here are some examples, they are for SQL Server but they demonstrate the logic:
My Repository table
My Repository History table
My Repository Insert trigger procedure
My Repository Update trigger procedure
嗯，你所说的听起来类似于缓慢变化的维度。
请注意，对任意数据库结构的版本控制正式是一个相当困难的问题。 :-)
Hmm, what you're talking about sounds similar to Slowly Changing Dimension.
Be aware that version control on arbitrary database structures is officially a rather Hard Problem. :-)
一个简单的解决方案是向表中添加版本/修订字段，并且每当更新记录时，不是就地更新它，而是插入应用了更改且版本号递增的副本。那么选择的时候，一定要选择最新版本的记录。这就是大多数此类方案的实施方式（例如维基媒体几乎就是以这种方式实现的）。
A simple solution would be to add a version/revision field to the tables, and whenever a record is updated, instead of updating it in place, insert a copy with the changes applied and the version number incremented. Then when selecting, always choose the record with the latest version. That's roughly how most such schemes are implemented (e.g. Wikimedia does it pretty much this exact way).
也许有一个工具可以帮助您做到这一点。看看 nextep 设计师：
https://github.com/christophefondacci/nextep-designer
使用此 IDE，您将能够拍摄数据库结构和数据的快照并将其置于版本控制之下。之后，您可以计算任意 2 个版本之间的差异，并生成可以插入/更新/删除数据的适当 SQL。
也许这是实现您想要的目标的另一种方式。
Maybe a tool can help you to do that for you. Have a look at nextep designer :
https://github.com/christophefondacci/nextep-designer
With this IDE you will be able to take snapshots of your database structure and data and put it under version control. After this you can compute the differences between any 2 versions and generate the appropriate SQL that can insert / update / delete your data.
Maybe this is an alternative way to achieve what you wanted.