是否有 MySQL 选项/特性来跟踪记录变更的历史？

您可以创建触发器来解决这个问题。

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

很长一段时间以来，我都是使用脚本将信息复制到另一个表中 因为 MySQL 当时不支持触发器 触发更有效地跟踪一切。

如果旧值被更改，此触发器将把它复制到历史表中 当某人编辑一行时。 Editor ID和 last mod存储在 每次有人编辑该行时，都会返回原始表; 时间对应于 变成现在的样子。

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;
$$

另一个解决方案是保留一个 Revison 字段，并在保存时更新该字段。您可以确定 max 是最新的修订，或者0是最新的一行。这取决于你。

很微妙。

如果业务需求是“我想审计对数据的更改——谁做了什么，什么时候做的?”，您通常可以使用审计表(根据触发器示例 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 (如果需要保留该 ID)。如果需要删除客户，可以使用 CUSTOMER _ STATUS 标志来表明这一点——您可能永远不会从这个表中删除记录。

这样，您总是可以找到某个给定日期的 customer 表的状态-地址是什么？他们改名字了吗？通过连接到其他具有相似的 valid_ from 和 valid_ until 日期的表，您可以历史性地重新构建整个图片。若要查找当前状态，请搜索带有空 VALID _ UNTIL 日期的记录。

它非常笨拙(严格地说，您不需要 valid_ from，但是它使查询变得更容易一些)。它使您的设计和数据库访问复杂化。但这让重建世界变得容易多了。

这里有一个简单的方法:

首先，为要跟踪的每个数据表创建一个历史记录表(下面的示例查询)。对于数据表中的每一行执行的每个插入、更新和删除查询，该表都有一个条目。

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

为了执行此排序行为，将在主键列和修订列上创建两列(复合)索引。注意，只有当历史表使用的引擎是 MyISAM (请参阅本页的「 MyISAM 备注」))时，才能以这种方式进行排序

创建历史记录表相当容易。在下面的 ALTERTABLE 查询中(以及在下面的触发器查询中) ，用数据表中该列的实际名称替换“置换 _ 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 _ column’列)

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

编辑: 哦，哇，人们喜欢我6年前的历史表: P

我认为，我的实现工作仍在继续，而且越来越庞大，越来越笨拙。我写了视图和相当不错的用户界面来查看这个数据库中的历史记录，但我不认为它曾经被使用过很多次。就这样。

不按特定顺序处理某些评论:

• 我自己用 PHP 实现了一些更复杂的东西，并且避免了注释中描述的一些问题(显著地将索引转移了过来)。如果将唯一索引转移到历史表中，事情就会中断。在评论中有解决方案)。根据您的数据库的建立程度，一直跟随这篇文章可能是一次冒险。

• 如果主键和修订列之间的关系看起来不对劲，通常意味着复合键以某种方式出错。在一些罕见的情况下，我发生了这样的事情，对事业感到茫然不知所措。

• 我发现这个解决方案非常有效，使用了触发器。此外，MyISAM 在插入方面也很快，这是触发器所做的全部工作。您可以通过智能索引(或缺少...)进一步改进这一点。实际上，在 MyISAM 表中插入带有主键的单行不应该是需要优化的操作，除非您在其他地方遇到重大问题。在我运行 MySQL 数据库的整个过程中，这个历史表的实现一直处于运行状态，它从来不会导致出现任何(许多)性能问题。

• 如果要重复插入，请检查软件层中的 INSERT IGNORE 类型查询。嗯，现在想不起来了，但是我认为这个方案和事务在运行多个 DML 操作后最终会失败。至少有些东西需要注意。

• 历史表和数据表中的字段匹配非常重要。或者，更确切地说，您的数据表没有比历史表更多的列。否则，对数据表的插入/更新/del 查询将失败，当对历史表的插入在查询中放入不存在的列时(由于触发器查询中的 d *) ，触发器将失败。如果 MySQL 具有类似模式触发器的东西，那将是非常棒的，在这里，如果向数据表中添加了列，您可以修改历史表。MySQL 现在有这个吗？这些天我确实做出了反应

我们是这样解决的

一个用户表如下所示

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

要查找任何用户的当前地址，我们使用修订版 DESC 和 LIMIT 1搜索 UserData

在某段时间内获取用户的地址 我们可以使用 create _ on between (date1，date 2)

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

但最重要的是，如果我们有很多列和很多表。我们必须键入每个表的每个列的名称。很明显，这是在浪费时间。

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

我们也可以简单地使用第三部分工具来完成这项工作 神奇追踪器

只要我的2美分。我会创建一个解决方案，记录确切的变化，非常类似于瞬态的解决方案。

我的 ChangesTable 很简单:

DateTime | WhoChanged | TableName | Action | ID |FieldName | OldValue

1)当主表中的一整行被更改时，会有大量条目进入这个表，但这种情况不太可能发生，所以不是什么大问题(人们通常只更改一件事) 2) OldVaue (如果你愿意，还有 NewValue)必须是某种史诗般的“ anytype”，因为它可以是任何数据，可能有一种方法可以用 RAW 类型或者仅仅使用 JSON 字符串进行转换。

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

对于 Create 和 Delete，只需要行 ID，不需要字段。在删除主表(active?)上的标志就好了。

为什么不简单地使用 bin 日志文件呢？如果在 Mysql 服务器上设置了复制，并且将 binlog 文件格式设置为 ROW，则可以捕获所有更改。

可以使用名为 noplay 的好的 Python 库。

MariaDB 从10.3开始就支持系统版本控制，这是一个标准的 SQL 特性，可以完成您想要的任务: 它存储表记录的历史，并通过 SELECT查询提供对历史记录的访问。MariaDB 是 MySQL 的一个开放式开发分支。你可透过以下连结找到更多有关系统版本控制的资料:

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

在 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以来就可用的替代语法。