如何使用歷史表和觸發器來追蹤 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中文網其他相關文章！