MySQL有哪些选项或特性可用于跟踪记录更改的历史？

Question

有人询问我能否跟踪MySQL数据库记录的更改。因此，当一个字段被更改时，旧值和新值以及更改日期都应该可用。是否存在某种功能或常见技术来实现这一点呢？

如果有，我会考虑像这样做。创建一个名为changes的表。它将包含与主表相同的字段，但是前缀为“old”和“new”，但仅针对实际更改的那些字段，并带有一个TIMESTAMP。它将用一个ID进行索引。这样，可以运行SELECT报告以显示每个记录的历史记录。这是一个好方法吗？谢谢！

- Edward

Answer 1

这里有一个简单的方法来做到这一点：

首先，为您想要跟踪的每个数据表创建一个历史表（以下是示例查询）。该表将为数据表中的每一行执行的每个插入、更新和删除查询记录一个条目。

历史表的结构与所跟踪的数据表相同，除了三个额外的列：一个用于存储发生的操作（我们称之为“action”）的列，操作的日期和时间，以及一个用于存储序列号（“revision”）的列，它按照数据表的主键列分组并随着每个操作递增。

为了实现这种序列化行为，需要在主键列和修订列上创建一个两列（复合）索引。请注意，只有在历史表所使用的引擎是MyISAM时才能以此方式进行序列化（参见本页面上的“MyISAM说明”链接）。

创建历史表相当简单。在下面的ALTER TABLE查询中（以及后面的触发器查询中），将'primary_key_column'替换为您数据表中该列的实际名称。

CREATE TABLE MyDB.data_history LIKE MyDB.data;

ALTER TABLE MyDB.data_history MODIFY COLUMN primary_key_column int(11) NOT NULL, 
   DROP PRIMARY KEY, ENGINE = MyISAM, ADD action VARCHAR(8) DEFAULT 'insert' FIRST, 
   ADD revision INT(6) NOT NULL AUTO_INCREMENT AFTER action,
   ADD dt_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER revision,
   ADD PRIMARY KEY (primary_key_column, revision);

然后你创建触发器：

DROP TRIGGER IF EXISTS MyDB.data__ai;
DROP TRIGGER IF EXISTS MyDB.data__au;
DROP TRIGGER IF EXISTS MyDB.data__bd;

CREATE TRIGGER MyDB.data__ai AFTER INSERT ON MyDB.data FOR EACH ROW
    INSERT INTO MyDB.data_history SELECT 'insert', NULL, NOW(), d.* 
    FROM MyDB.data AS d WHERE d.primary_key_column = NEW.primary_key_column;

CREATE TRIGGER MyDB.data__au AFTER UPDATE ON MyDB.data FOR EACH ROW
    INSERT INTO MyDB.data_history SELECT 'update', NULL, NOW(), d.*
    FROM MyDB.data AS d WHERE d.primary_key_column = NEW.primary_key_column;

CREATE TRIGGER MyDB.data__bd BEFORE DELETE ON MyDB.data FOR EACH ROW
    INSERT INTO MyDB.data_history SELECT 'delete', NULL, NOW(), d.* 
    FROM MyDB.data AS d WHERE d.primary_key_column = OLD.primary_key_column;

你已经完成了。现在，所有对'MyDb.data'的插入、更新和删除操作都将记录在'MyDb.data_history'中，为你提供一个历史表，就像这样（不包括人为构造的'data_columns'列）。

ID    revision   action    data columns..
1     1         'insert'   ....          initial entry for row where ID = 1
1     2         'update'   ....          changes made to row where ID = 1
2     1         'insert'   ....          initial entry, ID = 2
3     1         'insert'   ....          initial entry, ID = 3 
1     3         'update'   ....          more changes made to row where ID = 1
3     2         'update'   ....          changes made to row where ID = 3
2     2         'delete'   ....          deletion of row where ID = 2 

要显示从更新到更新的给定列或多个列的更改，您需要将历史表与其自身连接，使用主键和序列列。您可以为此目的创建一个视图，例如：

CREATE VIEW data_history_changes AS 
   SELECT t2.dt_datetime, t2.action, t1.primary_key_column as 'row id', 
   IF(t1.a_column = t2.a_column, t1.a_column, CONCAT(t1.a_column, " to ", t2.a_column)) as a_column
   FROM MyDB.data_history as t1 INNER join MyDB.data_history as t2 on t1.primary_key_column = t2.primary_key_column 
   WHERE (t1.revision = 1 AND t2.revision = 1) OR t2.revision = t1.revision+1
   ORDER BY t1.primary_key_column ASC, t2.revision ASC

- transient closure

---

我很喜欢这个解决方案。但是如果您的主表没有主键或您不知道主键是什么，那就有点棘手了。 - Benjamin Eckstein

最近在项目中使用这个解决方案时遇到了问题，因为原始表中的所有索引都被复制到历史表中（由于 CREATE TABLE ... LIKE .... 的工作方式）。在历史表上有唯一索引可能会导致 AFTER UPDATE 触发器中的 INSERT 查询失败，因此它们需要被删除。在我的 PHP 脚本中，我查询新创建的历史表上的任何唯一索引（使用 "SHOW INDEX FROM data_table WHERE Key_name != 'PRIMARY' and Non_unique = 0"），然后将其删除。 - transient closure

每次备份表都会插入重复的数据。例如，如果我们有一个包含10个字段的表，并更新了其中2个字段，则会为其余8个字段添加重复的数据。如何解决这个问题？ - itzmukeshy7

为避免不小心复制各种索引，请将创建表语句更改为“CREATE TABLE MyDB.data_history as select * from MyDB.data limit 0;”。注意，这样做不会改变原始含义。 - Eric Hayes

@transientclosure，你会如何提议将原查询未包含的其他字段加入历史记录呢？例如，我想追踪谁进行了这些更改。对于插入操作，它已经有一个“所有者”字段，对于更新操作，我可以添加一个“更新者”字段，但是对于删除操作，我不确定如何通过触发器实现。在数据历史记录行中更新用户ID感觉很不规范 :P - Horse

Answer 2

这很微妙。

如果业务需求是“我想审计数据的更改——谁在何时进行了什么操作？”您通常可以使用审计表（如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（现在）和空VALID_UNTIL。如果需要保留，则将“CUSTOMER_USER”状态设置为当前用户的登录ID。如果需要删除客户，则使用CUSTOMER_STATUS标志来指示此操作-您永远不应从此表中删除记录。

这样，您始终可以找到特定日期的客户表的状态-地址是什么？他们改变了名字吗？通过与具有类似valid_from和valid_until日期的其他表连接，您可以历史性地重建整个画面。要查找当前状态，请搜索具有null VALID_UNTIL日期的记录。

虽然这种方法很麻烦（严格来说，您不需要valid_from，但是它使查询稍微容易一些），但它使重建世界变得更加容易。

- Neville Kuyt

---

但这会为那些没有更新的字段添加重复数据？如何管理它？ - itzmukeshy7

使用第二种方法，在生成报告时会出现问题，如果客户记录在一段时间内被编辑，则很难识别某个条目是属于同一客户还是不同客户。 - Akshay Joshi

到目前为止，这是我看过的解决这个问题的最好建议。 - Worthy7

哦，针对评论，如果其他未更改的内容只存储null怎么样？因此，最新版本将是所有最新数据，但如果名称5天前曾经是“Bob”，那么只需有一行，名称= bob并且有效期至5天前。 - Worthy7

客户ID和日期的组合是主键，因此它们保证是唯一的。 - Neville Kuyt

Answer 3

你可以创建触发器来解决这个问题。 这里有一个教程 来实现这个功能（存档链接）。

在数据库中设置约束和规则比编写特殊代码处理相同任务更好，因为它将防止另一个开发人员编写绕过所有特殊代码的不同查询，并可能导致您的数据库具有较差的数据完整性。

很长一段时间，我一直在使用脚本将信息复制到另一个表中，因为当时MySQL不支持触发器。 我现在发现使用这个触发器更有效地跟踪所有内容。

当某人编辑行时，此触发器将把旧值复制到历史记录表中。 编辑器ID和最后修改时间存储在原始表中，每次有人编辑该行时都会存储； 时间对应于何时将其更改为当前形式。

DROP TRIGGER IF EXISTS history_trigger $$

CREATE TRIGGER history_trigger
BEFORE UPDATE ON clients
    FOR EACH ROW
    BEGIN
        IF OLD.first_name != NEW.first_name
        THEN
                INSERT INTO history_clients
                    (
                        client_id    ,
                        col          ,
                        value        ,
                        user_id      ,
                        edit_time
                    )
                    VALUES
                    (
                        NEW.client_id,
                        'first_name',
                        NEW.first_name,
                        NEW.editor_id,
                        NEW.last_mod
                    );
        END IF;

        IF OLD.last_name != NEW.last_name
        THEN
                INSERT INTO history_clients
                    (
                        client_id    ,
                        col          ,
                        value        ,
                        user_id      ,
                        edit_time
                    )
                    VALUES
                    (
                        NEW.client_id,
                        'last_name',
                        NEW.last_name,
                        NEW.editor_id,
                        NEW.last_mod
                    );
        END IF;

    END;
$$

另一种解决方案是保留一个修订字段，并在保存时更新该字段。您可以决定最大值是最新的修订版本，还是将0视为最近的行。这取决于您。

- Keethanjan

Answer 4

MariaDB 支持自10.3版本起的系统版本控制，这是标准 SQL 功能，可以实现你想要的功能：它存储表记录的历史并通过SELECT查询提供访问。MariaDB是MySQL的一个开放式发展分支。您可以通过此链接了解有关其系统版本控制的更多信息：

https://mariadb.com/kb/en/library/system-versioned-tables/

- midenok

---

请注意上面链接中的以下内容：“mysqldump不会读取版本化表中的历史行，因此历史数据将不会被备份。此外，时间戳的恢复也是不可能的，因为它们不能由插入/用户定义。” - Daniel

@Daniel 目前计划在10.8版本中实现此功能：https://jira.mariadb.org/browse/MDEV-16029 - midenok

mariabackup命令是mysqldump的替代品。https://mariadb.com/kb/en/mariabackup-overview/ - hyamanieu

Answer 5

以下是我们的解决方案：

一个名为“用户”的表格如下：

Users
-------------------------------------------------
id | name | address | phone | email | created_on | updated_on

业务需求发生了变化，我们需要检查用户曾经拥有的所有地址和电话号码。 新的架构如下所示

Users (the data that won't change over time)
-------------
id | name

UserData (the data that can change over time and needs to be tracked)
-------------------------------------------------
id | id_user | revision | city | address | phone | email | created_on
 1 |   1     |    0     | NY   | lake st | 9809  | @long | 2015-10-24 10:24:20
 2 |   1     |    2     | Tokyo| lake st | 9809  | @long | 2015-10-24 10:24:20
 3 |   1     |    3     | Sdny | lake st | 9809  | @long | 2015-10-24 10:24:20
 4 |   2     |    0     | Ankr | lake st | 9809  | @long | 2015-10-24 10:24:20
 5 |   2     |    1     | Lond | lake st | 9809  | @long | 2015-10-24 10:24:20

要找到任何用户的当前地址，我们搜索具有降序修订和1个限制的UserData

要在一定时间段内获取用户的地址，可以使用created_on bewteen（date1，date2）

- Zenex

---

这是我想要的解决方案，但我想知道如何使用触发器在此表中插入id_user？ - thecassion

id_user=1 的 revision=1 发生了什么？一开始我以为你的计数是 0,2,3,...，但后来我发现对于 id_user=2，修订计数是 0,1, ...。 - Pathros

你不需要 id 和 id_user 列。只需使用 id（用户ID）和 revision 的组ID。 - Gajus

当你需要使用JOINS创建查询时，这种方法会让你噩梦连连。每次想要引用表格时，你都必须按用户ID进行分组并按时间戳降序排序。你可以创建一个视图，但这仍然会增加你的复杂性。最好的方法是拥有一个单独的审计表，并使用触发器来处理INSERT、UPDATE和DELETE操作。 - TheRealChx101

Answer 6

为什么不直接使用binlog文件呢？如果在MySQL服务器上设置了复制，且binlog文件格式被设置为ROW，那么所有更改都可以被捕获。

一个称为noplay的好的Python库可供使用。更多信息请参见这里。

- Ouroboros

---

即使您没有/不需要复制，也可以使用Binlog。 Binlog具有许多有益的用例。 复制可能是最常见的用例，但正如在此处提到的那样，它也可以用于备份和审计历史记录。 - webaholik

Answer 7

仅供参考。我会创建一个解决方案，记录所做更改的详细信息，非常类似于临时解决方案。

我的ChangesTable很简单：

日期时间 | 谁变更了 | 表名 | 操作 | ID |字段名 | 旧值

1）当主表中整行发生更改时，将有大量条目进入此表，但这很不可能发生，因此不是一个大问题（人们通常只更改一件事情） 2）旧值（如果需要新值）必须是某种史诗级别的“任何类型”，因为它可以是任何数据，可能有一种方法使用RAW类型或仅使用JSON字符串进行转换。

最小化数据使用，存储您需要的所有内容，并可同时用于所有表格。我现在正在研究这个问题，但这可能会成为我走的路。

对于创建和删除，只需行ID，不需要字段。在删除时，可以在主表上设置一个标志（活动？）。

- Worthy7

---

我曾经使用过类似的方法，但是将FieldName和OldValue字段替换为一个名为changedValues的字段。在该字段中，我会有一个JSON序列化对象，其中包含fieldName和oldValue两个属性。这样，无论更改了多少个字段，每次更新只有一个记录。折衷之处在于：如果数据库管理系统不支持原生的JSON数据类型，则查找哪个字段已更改可能会效率低下。 - Sunny

Answer 8

在MariaDB 10.5+中，设置这个非常容易。

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING 
  PARTITION BY SYSTEM_TIME;

可以通过以下方式查询过去的历史记录：

SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP '2016-10-09 08:07:06';

MySQL目前没有这方面的对应物。

请查看文档以获取更多信息。如果您使用的是较旧版本的MariaDB，则可以在文档中找到替代语法，该语法自MariaDB 10.3.4起已可用。

- hostingutilities.com

Answer 9

直接的方法是在表上创建触发器。设置一些条件或映射方法。当更新或删除发生时，它会自动插入到“更改”表中。

但最大的问题是如果我们有很多列和很多表怎么办。我们必须输入每个表的每个列的名称。显然，这是浪费时间。

为了更加优雅地处理这个问题，我们可以创建一些过程或函数来检索列名。

我们也可以使用第三方工具来简单地完成这项工作。在这里，我编写了一个Java程序Mysql Tracker

- goforu

---

我该如何使用你们的Mysql Tracker？ - webchun

1. 确保每个表中都有一个id列作为主键。
2. 将Java文件复制到本地（或IDE）。
3. 导入库并根据数据库配置和结构编辑第9-15行的静态变量。
4. 解析并运行Java文件。
5. 复制控制台日志并将其作为MySQL命令执行。

- goforu

创建类似表的语句可以轻松地复制所有列。 - Jonathan