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