是否有 MySQL 选项/功能来跟踪记录更改的历史记录?

有人问我是否可以跟踪 MySQL 数据库中记录的更改.因此，当一个字段发生更改时，旧的与新的可用以及发生的日期.是否有实现此目的的功能或通用技术?

如果是这样，我正在考虑做这样的事情.创建一个名为 changes 的表.它将包含与 ma​​ster 表相同的字段，但以 old 和 new 为前缀，但仅适用于实际更改的那些字段和 TIMESTAMP.它将使用 ID 进行索引.这样，可以运行 SELECT 报告来显示每条记录的历史记录.这是一个好方法吗?谢谢！

解决方案

很微妙.

如果业务需求是我想审计对数据的更改 - 谁在什么时候做了什么?"，您通常可以使用审计表(根据 Keethanjan 发布的触发器示例).我不是触发器的忠实粉丝，但它的巨大好处是实现起来相对轻松 - 您现有的代码不需要了解触发器和审计内容.

如果业务需求是向我展示过去给定日期的数据状态"，则意味着随时间变化的方面已进入您的解决方案.虽然您几乎可以通过查看审计表来重建数据库的状态，但这很困难且容易出错，而且对于任何复杂的数据库逻辑，它变得笨拙.例如，如果企业想知道找到我们应该发送给每月第一天未付未付发票的客户的信件地址"，您可能需要搜索六张审计表.

相反，您可以将随时间变化的概念融入到您的架构设计中(这是 Keethanjan 建议的第二个选项).这是对您的应用程序的更改，绝对是在业务逻辑和持久性级别，因此并非微不足道.

例如，如果你有一个这样的表格:

客户---------CUSTOMER_ID PK顾客姓名CUSTOMER_ADDRESS

并且您想随着时间的推移进行跟踪，您可以将其修改如下:

客户------------CUSTOMER_ID PKCUSTOMER_VALID_FROM PKCUSTOMER_VALID_UNTIL PKCUSTOMER_STATUSCUSTOMER_USER顾客姓名CUSTOMER_ADDRESS

每次要更改客户记录，而不是更新记录时，您将当前记录上的 VALID_UNTIL 设置为 NOW()，并插入一个带有 VALID_FROM(现在)和空 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'm 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 just 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.