有没有一个MySQL选项/特性可以跟踪记录的更改历史？

这个问题的出现原因是希望能够跟踪并记录数据库中记录的更改历史，以便于对数据的修改进行追踪和回溯。

解决方法是创建一个历史表来跟踪每个数据表中的插入、更新和删除操作。历史表的结构与数据表相同，只是增加了三列额外的列：用于存储操作类型的列（'action'），操作发生的日期和时间的列，以及一个用于存储序列号（'revision'）的列，该列根据数据表的主键列进行分组并递增。

为了实现这种序列化行为，需要在主键列和修订列上创建一个两列（复合）索引。需要注意的是，只有在历史表使用的引擎是MyISAM时才能以这种方式进行序列化。

创建历史表的步骤如下：

1. 创建一个与数据表相同结构的历史表：CREATE TABLE MyDB.data_history LIKE MyDB.data;

2. 修改历史表的结构，增加额外的列：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);

然后创建触发器：

1. 创建插入触发器：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;

2. 创建更新触发器：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;

3. 创建删除触发器：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'中，您将得到一个类似下面的历史表：

ID revision action data columns..

1 1 'insert' .... ID为1的行的初始记录

1 2 'update' .... 对ID为1的行的更改记录

2 1 'insert' .... ID为2的行的初始记录

3 1 'insert' .... ID为3的行的初始记录

1 3 'update' .... 对ID为1的行的更多更改记录

3 2 'update' .... 对ID为3的行的更改记录

2 2 'delete' .... 删除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

如果主表没有主键或者不知道主键是什么，可以通过以下步骤解决：

1. 查询新创建的历史表上的唯一索引：SHOW INDEX FROM data_table WHERE Key_name != 'PRIMARY' and Non_unique = 0

2. 删除唯一索引。

另外，如果在备份表中重复插入数据，可以通过更改创建表语句来避免。可以将创建表语句改为CREATE TABLE MyDB.data_history as select * from MyDB.data limit 0;

如果希望在历史中记录其他不在原始查询中的字段，可以通过在触发器中更新data_history行来实现。

有没有MySQL选项/功能来跟踪记录更改的历史？

问题的出现原因是，MySQL本身不提供一种内置的方式来跟踪记录数据的更改历史。这可能导致在应用程序中需要手动编写代码来实现此功能。

解决方法之一是使用触发器。触发器可以在数据发生更改时自动执行特定的操作。下面的代码展示了一个MySQL触发器的示例，它将旧值复制到一个历史表中，以便跟踪记录的更改历史。

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

这个触发器将在"clients"表中的每一行更新之前执行。它会检查每个字段的旧值和新值之间的差异，并将差异的字段值插入到"history_clients"表中，以便记录更改的历史。

另一种解决方法是在表中保留一个"Revision"字段，并在保存时更新该字段。您可以决定最大值是最新的修订版，或者0是最近的行。这取决于您的需求和设计决策。

有没有一个MySQL选项/功能来跟踪记录的更改历史？

原因：

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

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

这种方法有点复杂（严格来说，您不需要valid_from，但它使查询稍微容易一些）。它使设计和数据库访问变得复杂。但它使重构整个数据库状态变得更容易。

对于那些没有被更新的字段，这样做会添加重复的数据。如何管理这些字段？

有个最好的建议是：如果其他所有没有更改的字段都存储为null会怎样？因此，最新版本将包含所有最新的数据，但是如果名称在5天前曾经是“Bob”，那么只有一行，名称为bob，有效直到5天前。

我真的很喜欢这个建议。如果你想要消除这种设计在查询中添加的复杂性，可以创建一个视图，从表中选择*，其中valid_until = null，这样可以简化每个表的查询逻辑。

这个设计中的customer_id如何工作？在你的例子中，customer_id仍然是主键，如果将其重复，怎么办？

customer_id和日期的组合是主键，所以它们保证唯一。

GDPR合规性不是这个例子的目标；这是一个非常复杂的领域，尤其是与关系数据库结合使用时，因此可能需要单独提出一个问题...