是否有一个MySQL选项/功能来跟踪记录更改的历史记录？ [英] Is there a MySQL option/feature to track history of changes to records?

问题描述

我被问过是否可以跟踪MySQL数据库中记录的更改。因此，当字段已更改时，旧的vs新的可用和发生的日期。有这样的功能或常见的技术吗？

如果是这样，我想做这样的事情。创建一个名为changes的TABLE。它将包含与主表相同的字段，但前缀为旧和旧，但仅适用于实际更改的字段和它的TIMESTAMP。它将用ID编制索引。这样，可以运行SELECT报告以显示每个记录的历史记录。这是一个好的方法吗？谢谢！

解决方案

这是微妙的。

我想审计数据的更改 - 谁做什么和什么时候？，通常可以使用审计表（根据触发示例Keethanjan发布）。我不是一个巨大的触发器的粉丝，但它有很大的好处是相对无痛的实现 - 你现有的代码不需要知道触发器和审计的东西。

如果业务要求是显示数据在过去的给定日期的状态，则表示随时间变化的方面已输入你的解决方案。虽然你可以通过查看审计表重建数据库的状态，它是困难的和容易出错的，对于任何复杂的数据库逻辑，它变得笨拙。例如，如果商家想知道查找我们应该发送给在月份第一天有未清付款发票的客户的信件地址，那么您可能需要拖动六个审计表。相反，您可以将改变的概念随时间推移到您的模式设计中（这是Keethanjan建议的第二个选项）。这是对你的应用程序的改变，绝对在业务逻辑和持久性级别，所以它不是微不足道。

例如，如果您有这样的表：

  CUSTOMER 
 --------- 
 CUSTOMER_ID PK 
 CUSTOMER_NAME 
 CUSTOMER_ADDRESS 
  

并且您希望随着时间的推移进行跟踪，您可以按以下方式进行修改：

  CUSTOMER 
 ------------ 
 CUSTOMER_ID PK 
 CUSTOMER_VALID_FROM PK 
 CUSTOMER_VALID_UNTIL PK 
 CUSTOMER_STATUS 
 CUSTOMER_USER 
 CUSTOMER_NAME 
 CUSTOMER_ADDRESS 
  

每次要更改客户记录时，将当前记录上的VALID_UNTIL设置为NOW（），并插入一个带有VALID_FROM（now）和空VALID_UNTIL的新记录。您将CUSTOMER_USER状态设置为当前用户的登录ID（如果需要保留）。如果客户需要删除，您可以使用CUSTOMER_STATUS标志来指示这一点 - 您可以从此表中删除记录。

这样，你总是可以找到一个给定日期的客户表的状态 - 地址是什么？他们改名了吗？通过加入具有类似valid_from和valid_until日期的其他表，您可以历史地重建整个图片。要查找当前状态，请搜索具有空VALID_UNTIL日期的记录。

这很难用（严格来说，你不需要valid_from，但它使查询更容易一些）。它使您的设计和数据库访问复杂化。但它使重建世界容易得多。

I've been asked if I can keep track of the changes to the records in a MySQL database. So when a field has been changed, the old vs new is available and the date this took place. Is there a feature or common technique to do this?

If so, I was thinking of doing something like this. Create a TABLE called changes. It would contain the same fields as the master TABLE but prefixed with old and new, but only for those fields which were actually changed and a TIMESTAMP for it. It would be indexed with an ID. This way, a SELECT report could be run to show the history of each record. Is this a good method? Thanks!

解决方案

It's subtle.

If the business requirement is "I want to audit the changes to the data - who did what and when?", you can usually use audit tables (as per the trigger example Keethanjan posted). I'd not a huge fan of triggers, but it has the great benefit of being relatively painless to implement - your existing code doesn't need to know about the triggers and audit stuff.

If the business requirement is "show me what the state of the data was on a given date in the past", it means that the aspect of change over time has entered your solution. Whilst you can, just about, reconstruct the state of the database by looking at audit tables, it's hard and error prone, and for any complicated database logic, it becomes unwieldy. For instance, if the business wants to know "find the addresses of the letters we should have sent to customers who had outstanding, unpaid invoices on the first day of the month", you likely have to trawl half a dozen audit tables.

Instead, you can bake the concept of change over time into your schema design (this is the second option Keethanjan suggests). This is a change to your application, definitely at the business logic and persistence level, so it's not trivial.

For example, if you have a table like this:

CUSTOMER
---------
CUSTOMER_ID PK
CUSTOMER_NAME
CUSTOMER_ADDRESS

and you wanted to keep track over time, you would amend it as follows:

CUSTOMER
------------
CUSTOMER_ID            PK
CUSTOMER_VALID_FROM    PK
CUSTOMER_VALID_UNTIL   PK
CUSTOMER_STATUS
CUSTOMER_USER
CUSTOMER_NAME
CUSTOMER_ADDRESS

Every time you want to change a customer record, instead of updating the record, you set the VALID_UNTIL on the current record to NOW(), and insert a new record with a VALID_FROM (now) and a null VALID_UNTIL. You set the "CUSTOMER_USER" status to the login ID of the current user (if you need to keep that). If the customer needs to be deleted, you use the CUSTOMER_STATUS flag to indicate this - you may never delete records from this table.

That way, you can always find what the status of the customer table was for a given date - what was the address? Have they changed name? By joining to other tables with similar valid_from and valid_until dates, you can reconstruct the entire picture historically. To find the current status, you search for records with a null VALID_UNTIL date.

It's unwieldy (strictly speaking, you don't need the valid_from, but it makes the queries a little easier). It complicates your design and your database access. But it makes reconstructing the world a lot easier.

这篇关于是否有一个MySQL选项/功能来跟踪记录更改的历史记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！