履歴テーブルとトリガーを使用して MySQL のレコードの変更を追跡するにはどうすればよいですか?

履歴テーブルとトリガーを使用した MySQL でのレコード変更の追跡

MySQL には、データベース内のレコードに加えられた変更を追跡するためのいくつかのオプションが用意されています。効果的なアプローチの 1 つは、ターゲット データ テーブルごとに履歴テーブルを作成することです。この履歴テーブルには、元のテーブルの各行に対して実行されたすべての挿入、更新、削除操作が保存されます。

履歴テーブルの作成

履歴テーブルを作成するには、次の 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

Viewing Changes

特定の列に加えられた変更を表示するには、主キー列とリビジョン列で履歴テーブルをそれ自体に結合し、適切な SQL ステートメントを使用して以前の値と現在の値を比較します。

以上が履歴テーブルとトリガーを使用して MySQL のレコードの変更を追跡するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。