如何在MySQL中实现时态数据

I currently have a non-temporal MySQL DB and need to change it to a temporal MySQL DB. In other words, I need to be able to retain a history of changes that have been made to a record over time for reporting purposes.

我目前有一个非时态MySQL数据库，需要将其更改为时态MySQL数据库。换句话说，为了报告目的，我需要能够保留随时间对记录所做的更改历史记录。

My first thought for implementing this was to simply do inserts into the tables instead of updates, and when I need to select the data, simply doing a GROUP BY on some column and ordering by the timestamp DESC.

我实现这一点的第一个想法是简单地在表中插入而不是更新，当我需要选择数据时，只需在某些列上执行GROUP BY并按时间戳DESC进行排序。

However, after thinking about things a bit, I realized that that will really mess things up because the primary key for each insert (which would really just be simulating a number of updates on a single record) will be different and thus mess up any linkage that uses the primary key to link to other records in the DB.

但是，在考虑了一些事情之后，我意识到这将真的搞砸了，因为每个插入的主键（实际上只是模拟单个记录上的一些更新）将是不同的，因此搞乱了任何连接使用主键链接到数据库中的其他记录。

As such, my next thought was to continue updating the main tables in the DB, but also create a new insert into an "audit table" that is simply a copy of the full record after the update, and then when I needed to report on temporal data, I could use the audit table for querying purposes.

因此，我的下一个想法是继续更新数据库中的主表，但也创建一个新的插入到“审计表”，它只是更新后的完整记录的副本，然后当我需要报告时时态数据，我可以使用审计表进行查询。

Can someone please give me some guidance or links on how to properly do this?
Thank you.

有人可以给我一些关于如何正确执行此操作的指导或链接吗？谢谢。

1 个解决方案

#1

Make the given table R temporal(ie, to maintain the history).

使给定的表R暂时（即，保持历史）。

One design is to leave the table R as it is and create a new table R_Hist with valid_start_time and valid_end_time. Valid time is the time when the fact is true.

一种设计是保持表R不变，并创建一个带有valid_start_time和valid_end_time的新表R_Hist。有效时间是事实为真的时间。

The CRUD operations can be given as:

CRUD操作可以如下：

INSERT

插

Insert into both R
插入两个R
Insert into R_Hist with valid_end_time as infinity
使用valid_end_time作为无穷大插入R_Hist

UPDATE

UPDATE

Update in R
R中更新
Insert into R_Hist with valid_end_time as infinity
使用valid_end_time作为无穷大插入R_Hist
Update valid_end_time with the current time for the “latest” tuple
使用“最新”元组的当前时间更新valid_end_time

DELETE

删除

Delete from R
从R中删除
Update valid_end_time with the current time for the “latest” tuple
使用“最新”元组的当前时间更新valid_end_time

SELECT

选择

Select from R for ‘snapshot’ queries (implicitly ‘latest’ timestamp)
从R中选择“快照”查询（隐式“最新”时间戳）
Select from R_Hist for temporal operations
从R_Hist中选择时间操作

Instead, you can choose to design new table for every attribute of table R. By this particular design you can capture attribute level temporal data as opposed to entity level in the previous design. The CRUD operations are almost similar.

相反，您可以选择为表R的每个属性设计新表。通过此特定设计，您可以捕获属性级时态数据，而不是先前设计中的实体级。 CRUD操作几乎相似。

#1