Wie kann ich Datensatzänderungen in MySQL mithilfe von Verlaufstabellen und Triggern verfolgen?

Verfolgen von Datensatzänderungen in MySQL mithilfe von Verlaufstabellen und Triggern

MySQL bietet mehrere Optionen zum Verfolgen von Änderungen an Datensätzen in einer Datenbank. Ein effektiver Ansatz besteht darin, für jede Zieldatentabelle eine Verlaufstabelle zu erstellen. In dieser Verlaufstabelle werden alle Einfüge-, Aktualisierungs- und Löschvorgänge gespeichert, die für jede Zeile in der Originaltabelle ausgeführt werden.

Erstellen der Verlaufstabelle

Um eine Verlaufstabelle zu erstellen, Sie können die folgende SQL-Abfrage verwenden und dabei „MyDB.data“ durch den Namen der Datentabelle, die Sie verfolgen möchten, und „primary_key_column“ durch den Namen ihres Primärschlüssels ersetzen Spalte:

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

Trigger zum Erfassen von Änderungen verwenden

Nachdem Sie die Verlaufstabelle erstellt haben, können Sie Trigger einrichten, um Änderungen an der ursprünglichen Datentabelle automatisch zu protokollieren. Für Einfüge-, Aktualisierungs- und Löschvorgänge können die folgenden Auslöser verwendet werden:

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;

Beispiel-Verlaufstabelle

Die Verlaufstabelle erfasst alle an der vorgenommenen Änderungen Originaldatentabelle. Bei einer gegebenen Datentabelle mit einer „ID“-Spalte als Primärschlüssel könnten beispielsweise die folgenden Einträge in der Verlaufstabelle generiert werden:

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 

Änderungen anzeigen

Um die an bestimmten Spalten vorgenommenen Änderungen anzuzeigen, können Sie die Verlaufstabelle in den Primärschlüssel- und Revisionsspalten mit sich selbst verknüpfen und entsprechende SQL-Anweisungen verwenden, um vorherige und aktuelle Werte zu vergleichen.

Das obige ist der detaillierte Inhalt vonWie kann ich Datensatzänderungen in MySQL mithilfe von Verlaufstabellen und Triggern verfolgen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!