如何使用历史表和触发器跟踪 MySQL 中的记录更改？

使用历史表和触发器跟踪 MySQL 中的记录更改

MySQL 提供了多个选项来跟踪对数据库中的记录所做的更改。一种有效的方法是为每个目标数据表创建一个历史表。该历史表将存储对原始表中每一行执行的所有插入、更新和删除操作。

创建历史表

要创建历史表，您可以使用以下 SQL 查询，将“MyDB.data”替换为要跟踪的数据表的名称，将“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);

使用触发器捕获更改

创建历史表后，您可以设置触发器以自动将更改记录到原始数据表中。对于插入、更新和删除操作，可以使用以下触发器：

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;

示例历史表

历史表将捕获对原始数据表。例如，给定一个以“ID”列为主键的数据表，可能会生成以下历史表条目：

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 

查看更改

要查看对特定列所做的更改，您可以在主键和修订列上将历史表与其自身连接，并使用适当的 SQL 语句来比较以前的值和当前的值。

以上是如何使用历史表和触发器跟踪 MySQL 中的记录更改？的详细内容。更多信息请关注PHP中文网其他相关文章！