多条相关数据的修订控制
我正在尝试找出如何最好地保留有关多行数据修订的修订/历史信息，以防由于某种原因我们需要恢复该数据。
这是一般的布局：
item
---------------
id
title
etc...
region
---------------
id
title
etc...
release_type
-----------------
id
title
etc...
items_released_dates_data
---------------------
item_id
region_id
release_type_id (these three form the primary key)
date
因此，每个项目可以有一个发布日期+region_id+release_type，我们基本上只跟踪日期（就这个问题而言，“日期”可以是数字、字符串或其他任何内容。我肯定会再次遇到这个问题）
更改是批量提交的，当新数据添加到 items_released_dates_data 中的所有内容时，其中 item_id=your_id 首先被删除，然后插入语句添加新值（也许这不是最好的方法这样做吗？）
我的想法是创建一个表，如下所示：
items_release_dates_data_history
-------------------------------------
item_id
timestamp
description
raw_data
使描述成为更新内容的简短摘要，并包含某种格式的数据，例如 json 或 xml 或可以在客户端快速解码的格式，以便为用户提供审查更改并选择修改给定版本。然后 items_released_dates_data 的每个条目也需要 items_released_dates_data_history 的条目（听起来不像是一个问题吗？:|）
我读过一些关于 mysql 触发器的内容，这对这里会有帮助，但坦率地说，我对此一无所知所以我正在按照我所理解的方式进行工作。
我的问题是，我是否遵循正确的路径来版本化这些东西，以及是否有人可以给我关于如何改进此方法的任何建议/最佳实践？
I'm trying to figure out how to best keep revision/history information on revisions to multiple rows of data, in case for some reason we need to revert to that data.
This is the general sort of layout:
item
---------------
id
title
etc...
region
---------------
id
title
etc...
release_type
-----------------
id
title
etc...
items_released_dates_data
---------------------
item_id
region_id
release_type_id (these three form the primary key)
date
So you can have one release date per item + region_id + release_type and we basically only track the date (For the purposes of this question the 'date' could be a number, a string, or whatever. I'm certain to run into this issue again)
Changes are submitted in bulk, when new data is added everything in items_released_dates_data where item_id=your_id is first deleted then an insert statement adds the new values (perhaps this isn't the best way to do this?)
My thought was to create a table like:
items_release_dates_data_history
-------------------------------------
item_id
timestamp
description
raw_data
Making description a short summary of what was updated, and including the data in some format like json or xml or something that could be quickly decoded on the client side to give the user a review of the changes and a choice to revise to a given version. Then every entry to items_released_dates_data also requires an entry to items_released_dates_data_history (doesn't sound like a question does it? :| )
I've read something about mysql triggers that would be helpful here, but quite frankly I don't know a thing about them so I'm working with what I understand.
My question is, am I following the right path to version this stuff, and is there any advice/best practices anyone can give me on how to improve this method?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我赞同亚历克斯·米勒的评论。到目前为止，你写的所有内容都有意义。
尽管您有所保留，但我还是强烈建议您研究一下触发因素。它们相当容易掌握，并且在这种情况下成为非常强大的工具。使用触发器，您可以在每次更新（或删除）记录时将行的副本存储到单独的表中。如果您想尽情发挥，您可以在触发器内将传入数据与现有数据进行比较，然后仅写入已更改的数据。
对于此类表，还可以考虑使用 Archive 存储引擎，而不是 MyISAM 或 InnoDB - 它们是为此类工作而设计的。
此外，您可能正在寻找的搜索短语是“审核跟踪”。
I second Alex Miller's comment. Everything you write make sense so far.
I'd strongly recommend looking into triggers though, despite your reservations. They're fairly easy to grasp, and make for a very powerful tool in such scenarios. Using triggers you can store a copy of the row into a separate table each time a record is updated (or deleted). If you want to go all fancy you can, within the trigger, compare the incoming data to the existing data, and write only what has changed.
Also consider the Archive storage engine instead of MyISAM or InnoDB for these kinds of tables - they're made for this kind of job.
Also, the search phrase you're probably looking for is "audit trail".
我想说你绝对走在正确的道路上。不过，您可能希望将区域 ID 存储在历史记录中，以便您可以根据区域而不是仅按整个项目检查发布历史记录。
至于删除+插入，只要您不会产生太多流量就可以，因为这都是锁定操作。插入或删除行来更新索引会花费大量时间。如果您使用的是 MyISAM 表，它也会停止对表的所有读取，直到这些操作完成。更新也会，但时间要短得多。 InnoDB 只会锁定行，因此这并不是真正的问题。
I'd say that you're definitely on the right track. Although, you may want to store the region ID in the history so you can check release history based on a region rather than just by entire items.
As for the delete+insert, that's fine as long as you don't end up with too much traffic, as those are both locking actions. There is a lot of time used when inserting or deleting a row to update the index. If you're using a MyISAM table, it's also going to halt all reads on the table until those actions complete. Update will as well, but for a much shorter time. InnoDB will only lock the row, so that's not really a concern.