Comment puis-je suivre les modifications enregistrées dans MySQL à l'aide de tables d'historique et de déclencheurs ?

Suivi des modifications des enregistrements dans MySQL à l'aide de la table d'historique et des déclencheurs

MySQL propose plusieurs options pour suivre les modifications apportées aux enregistrements dans une base de données. Une approche efficace consiste à créer une table d'historique pour chaque table de données cible. Cette table d'historique stockera toutes les opérations d'insertion, de mise à jour et de suppression effectuées sur chaque ligne de la table d'origine.

Création de la table d'historique

Pour créer une table d'historique, vous pouvez utiliser la requête SQL suivante, en remplaçant 'MyDB.data' par le nom de la table de données que vous souhaitez suivre et 'primary_key_column' par le nom de sa clé primaire 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);

Utilisation de déclencheurs pour capturer les modifications

Après avoir créé la table d'historique, vous pouvez configurer des déclencheurs pour enregistrer automatiquement les modifications apportées à la table de données d'origine. Pour les opérations d'insertion, de mise à jour et de suppression, les déclencheurs suivants peuvent être utilisés :

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;

Exemple de table d'historique

La table d'historique capturera toutes les modifications apportées au tableau de données original. Par exemple, étant donné une table de données avec une colonne « ID » comme clé primaire, les entrées de table d'historique suivantes peuvent être générées :

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 

Affichage des modifications

Pour afficher les modifications apportées à des colonnes spécifiques, vous pouvez joindre la table d'historique à elle-même sur les colonnes de clé primaire et de révision et utiliser les instructions SQL appropriées pour comparer les valeurs précédentes et actuelles.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!