使用历史表和触发器跟踪 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中文网其他相关文章！
