Esiste un’opzione / funzione MySQL per tracciare la cronologia delle modifiche ai record?

Mi è stato chiesto se posso tenere traccia delle modifiche ai record in un database MySQL. Quindi, quando un campo è stato cambiato, il vecchio contro il nuovo è disponibile e la data in cui questo è avvenuto. C’è una caratteristica o una tecnica comune per fare questo?

Se è così, stavo pensando di fare qualcosa del genere. Crea una TABELLA chiamata modifiche. Conterrebbe gli stessi campi del TAB principale ma prefisso con il vecchio e il nuovo, ma solo per quei campi che sono stati effettivamente modificati e un TIMESTAMP per esso. Sarebbe indicizzato con un ID. In questo modo, è ansible eseguire un report SELECT per mostrare la cronologia di ogni record. È un buon metodo? Grazie!

È sottile

Se il requisito aziendale è “Voglio controllare le modifiche ai dati – chi ha fatto cosa e quando?”, Di solito è ansible utilizzare le tabelle di controllo (come nell’esempio di trigger pubblicato da Keethanjan). Non sono un grande fan dei trigger, ma ha il grande vantaggio di essere relativamente indolore da implementare: il codice esistente non ha bisogno di conoscere i trigger e gli elementi di controllo.

Se il requisito aziendale è “mostrami quale stato dei dati era in una determinata data nel passato”, significa che l’aspetto del cambiamento nel tempo è entrato nella tua soluzione. Mentre è ansible, quasi, ribuild lo stato del database semplicemente guardando le tabelle di controllo, è difficile e sobject a errori, e per qualsiasi logica di database complicata, diventa ingombrante. Ad esempio, se l’azienda vuole sapere “trovare gli indirizzi delle lettere che dovremmo inviare ai clienti che hanno fatture non pagate in sospeso il primo giorno del mese”, è probabile che si debbano trascinare una mezza dozzina di tabelle di controllo.

Invece, puoi creare il concetto di cambiamento nel tempo nella progettazione dello schema (questa è la seconda opzione suggerita da Keethanjan). Questo è un cambiamento alla tua applicazione, sicuramente alla logica aziendale e al livello di persistenza, quindi non è banale.

Ad esempio, se hai una tabella come questa:

CUSTOMER --------- CUSTOMER_ID PK CUSTOMER_NAME CUSTOMER_ADDRESS 

e volevi tenere traccia nel tempo, dovresti modificarlo come segue:

 CUSTOMER ------------ CUSTOMER_ID PK CUSTOMER_VALID_FROM PK CUSTOMER_VALID_UNTIL PK CUSTOMER_STATUS CUSTOMER_USER CUSTOMER_NAME CUSTOMER_ADDRESS 

Ogni volta che desideri modificare un record del cliente, invece di aggiornare il record, imposti VALID_UNTIL sul record corrente su NOW () e inserisci un nuovo record con VALID_FROM (ora) e VALID_UNTIL null. Si imposta lo stato “CUSTOMER_USER” sull’ID di accesso dell’utente corrente (se è necessario mantenerlo). Se il cliente ha bisogno di essere cancellato, usi il flag CUSTOMER_STATUS per indicare ciò – non puoi mai cancellare record da questa tabella.

In questo modo, puoi sempre individuare lo stato della tabella clienti per una determinata data: qual era l’indirizzo? Hanno cambiato nome? Unendo ad altre tabelle con date valid_from e valid_until, è ansible ribuild storicamente l’intera immagine. Per trovare lo stato corrente, cerchi i record con una data VALID_UNTIL null.

È ingombrante (in senso stretto, non è necessario il valid_from, ma rende le query un po ‘più semplici). Complicano il tuo design e il tuo accesso al database. Ma rende molto più facile ribuild il mondo.

Ecco un modo semplice per farlo:

Per prima cosa, crea una tabella di cronologia per ogni tabella di dati che desideri monitorare (esempio di query sotto). Questa tabella avrà una voce per ogni inserimento, aggiornamento ed eliminazione della query eseguita su ogni riga nella tabella dei dati.

La struttura della tabella della cronologia sarà la stessa della tabella di dati che tiene traccia tranne per tre colonne aggiuntive: una colonna per memorizzare l’operazione verificatasi (chiamiamola ‘azione’), la data e l’ora dell’operazione e una colonna per memorizzare un numero di sequenza (‘revisione’), che incrementa per operazione ed è raggruppato dalla colonna chiave primaria della tabella dati.

Per eseguire questo comportamento di sequenziamento, viene creato un indice a due colonne (composito) sulla colonna chiave primaria e sulla colonna di revisione. Nota che puoi fare il sequenziamento solo in questo modo se il motore utilizzato dalla tabella della cronologia è MyISAM ( vedi ‘MyISAM Notes’ in questa pagina)

La tabella della cronologia è abbastanza facile da creare. Nella query ALTER TABLE di seguito (e nelle query di trigger sotto quella), sostituire ‘primary_key_column’ con il nome effettivo di quella colonna nella tabella dei dati.

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

E poi crei i trigger:

 DROP TRIGGER IF EXISTS MyDB.data__ai; DROP TRIGGER IF EXISTS MyDB.data__au; DROP TRIGGER IF EXISTS MyDB.data__bd; 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; 

E hai finito. Ora tutti gli inserti, gli aggiornamenti e le eliminazioni in “MyDb.data” verranno registrati in “MyDb.data_history”, fornendo una tabella della cronologia come questa (meno la colonna “data_columns” inventata)

 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 

Per visualizzare le modifiche per una determinata colonna o colonna dall’aggiornamento all’aggiornamento, è necessario unire la tabella della cronologia a se stessa sulla chiave primaria e sulle colonne della sequenza. È ansible creare una vista per questo scopo, ad esempio:

 CREATE VIEW data_history_changes AS SELECT t2.dt_datetime, t2.action, t1.primary_key_column as 'row id', IF(t1.a_column = t2.a_column, t1.a_column, CONCAT(t1.a_column, " to ", t2.a_column)) as a_column FROM MyDB.data_history as t1 INNER join MyDB.data_history as t2 on t1.primary_key_column = t2.primary_key_column WHERE (t1.revision = 1 AND t2.revision = 1) OR t2.revision = t1.revision+1 ORDER BY t1.primary_key_column ASC, t2.revision ASC 

Edit: Oh wow, la gente come la mia storia del tavolo da 6 anni fa: P

Suppongo che la mia implementazione continui a canticchiare, diventando più grande e più ingombrante. Ho scritto delle viste e un’interfaccia utente molto carina per guardare la cronologia in questo database, ma non credo che sia mai stata usata molto. Così è andata.

Per indirizzare alcuni commenti in nessun ordine particolare:

  • Ho implementato la mia implementazione in PHP che era un po ‘più coinvolgente ed ho evitato alcuni dei problemi descritti nei commenti (con indici trasferiti sopra, in modo significativo. Se si trasferiscono su indici univoci nella tabella della cronologia, le cose si romperanno. questo nei commenti). Seguire questo post alla lettera potrebbe essere un’avventura, a seconda di quanto è stabilito il tuo database.

  • Se la relazione tra la chiave primaria e la colonna di revisione sembra off, di solito significa che la chiave composita viene in qualche modo borked. In alcune rare occasioni ho avuto questo risultato ed ero in perdita per la causa.

  • Ho trovato questa soluzione abbastanza performante, usando i trigger come fa. Inoltre, MyISAM è veloce agli inserti, come fanno tutti i trigger. È ansible migliorare ulteriormente con l’indicizzazione intelligente (o la mancanza di …). L’inserimento di una singola riga in una tabella MyISAM con una chiave primaria non dovrebbe essere un’operazione che è necessario ottimizzare, in realtà, a meno che non si verifichino problemi significativi altrove. Nell’intero periodo in cui stavo eseguendo il database MySQL, questa implementazione della tabella della cronologia era triggers, non è mai stata la causa di nessuno dei (molti) problemi di prestazioni che si sono presentati.

  • se ricevi ripetuti inserti, controlla il tuo livello software per le query di tipo INSERTO IGNORE. Hrmm, non ricordo ora, ma penso che ci siano problemi con questo schema e le transazioni che alla fine falliscono dopo l’esecuzione di più azioni DML. Qualcosa di cui essere a conoscenza, almeno.

  • È importante che i campi nella tabella cronologia e nella tabella dati corrispondano. O, piuttosto, che la tua tabella di dati non ha più colonne della tabella cronologia. Altrimenti, l’inserimento / aggiornamento / interruzione delle query sulla tabella dati avrà esito negativo, quando gli inserimenti nelle tabelle della cronologia inseriscono colonne nella query che non esistono (dovute a d. * Nelle interrogazioni trigger) e il trigger non riesce. Sarebbe fantastico se MySQL avesse qualcosa di simile ai trigger di schema, in cui potresti modificare la tabella della cronologia se le colonne sono state aggiunte alla tabella dei dati. MySQL lo ha ora? Reagisco in questi giorni: P

È ansible creare trigger per risolvere questo. Ecco un tutorial per farlo (link archiviato).

L’impostazione di vincoli e regole nel database è preferibile alla scrittura di codice speciale per gestire la stessa attività poiché impedirà a un altro sviluppatore di scrivere una query diversa che ignora tutto il codice speciale e potrebbe lasciare il database con una scarsa integrità dei dati.

Per molto tempo ho copiato informazioni su un’altra tabella usando uno script poiché MySQL non supportava i trigger al momento. Ora ho trovato questo trigger per essere più efficace nel tenere traccia di tutto.

Questo trigger copierà un vecchio valore in una tabella di cronologia se viene modificato quando qualcuno modifica una riga. Editor ID e l’ last mod sono memorizzati nella tabella originale ogni volta che qualcuno modifica quella riga; il tempo corrisponde a quando è stato cambiato nella sua forma attuale.

 DROP TRIGGER IF EXISTS history_trigger $$ CREATE TRIGGER history_trigger BEFORE UPDATE ON clients FOR EACH ROW BEGIN IF OLD.first_name != NEW.first_name THEN INSERT INTO history_clients ( client_id , col , value , user_id , edit_time ) VALUES ( NEW.client_id, 'first_name', NEW.first_name, NEW.editor_id, NEW.last_mod ); END IF; IF OLD.last_name != NEW.last_name THEN INSERT INTO history_clients ( client_id , col , value , user_id , edit_time ) VALUES ( NEW.client_id, 'last_name', NEW.last_name, NEW.editor_id, NEW.last_mod ); END IF; END; $$ 

Un’altra soluzione sarebbe quella di mantenere un campo Revisione e aggiornare questo campo al salvataggio. Potresti decidere che il massimo è la revisione più recente o che 0 è la riga più recente. Dipende da te.

Ecco come l’abbiamo risolto

una tabella utenti sembrava così

 Users ------------------------------------------------- id | name | address | phone | email | created_on | updated_on 

E i requisiti aziendali sono cambiati e avevamo la necessità di verificare tutti gli indirizzi e i numeri di telefono precedenti che un utente abbia mai avuto. il nuovo schema ha questo aspetto

 Users (the data that won't change over time) ------------- id | name UserData (the data that can change over time and needs to be tracked) ------------------------------------------------- id | id_user | revision | city | address | phone | email | created_on 1 | 1 | 0 | NY | lake st | 9809 | @long | 2015-10-24 10:24:20 2 | 1 | 2 | Tokyo| lake st | 9809 | @long | 2015-10-24 10:24:20 3 | 1 | 3 | Sdny | lake st | 9809 | @long | 2015-10-24 10:24:20 4 | 2 | 0 | Ankr | lake st | 9809 | @long | 2015-10-24 10:24:20 5 | 2 | 1 | Lond | lake st | 9809 | @long | 2015-10-24 10:24:20 

Per trovare l’indirizzo corrente di qualsiasi utente, cerchiamo UserData con revisione DESC e LIMIT 1

Per ottenere l’indirizzo di un utente tra un certo periodo di tempo possiamo usare created_on bewteen (date1, date 2)

Solo i miei 2 centesimi. Creerei una soluzione che registra esattamente ciò che è cambiato, molto simile alla soluzione del transiente.

La mia tabella delle modifiche sarebbe semplice:

DateTime | WhoChanged | TableName | Action | ID |FieldName | OldValue

1) Quando un’intera riga viene cambiata nella tabella principale, molte voci entrano in questa tabella, MA è molto improbabile, quindi non è un grosso problema (le persone di solito cambiano solo una cosa) 2) OldVaue (e NewValue se tu voglio) deve essere una sorta di “anytype” epico dal momento che potrebbe essere qualsiasi dato, ci potrebbe essere un modo per farlo con i tipi RAW o semplicemente usando le stringhe JSON per convertire dentro e fuori.

Utilizzo minimo dei dati, memorizza tutto ciò di cui hai bisogno e può essere utilizzato per tutte le tabelle contemporaneamente. Sto facendo ricerche su questo anche adesso, ma questo potrebbe finire per essere il modo in cui vado.

Per creare ed eliminare, solo l’ID riga, nessun campo necessario. Sulla cancellazione di un flag sul tavolo principale (attivo?) Sarebbe buono.

Perché non usare semplicemente i file di registro del cestino? Se la replica è impostata sul server Mysql e il formato del file binlog è impostato su ROW, tutte le modifiche potrebbero essere acquisite.

È ansible utilizzare una buona libreria Python chiamata noplay. Maggiori informazioni qui .

Il modo diretto per farlo è creare trigger sui tavoli. Impostare alcune condizioni o metodi di mapping. Quando si verifica l’aggiornamento o l’eliminazione, questa verrà inserita automaticamente nella tabella ‘cambia’.

Ma la parte più grande è che se avessimo un sacco di colonne e un sacco di tavoli. Dobbiamo digitare il nome di ogni colonna di ogni tabella. Ovviamente, è una perdita di tempo.

Per gestirlo in modo più sfarzoso, possiamo creare alcune procedure o funzioni per recuperare il nome delle colonne.

Possiamo anche usare lo strumento di terze parti semplicemente per fare ciò. Qui, scrivo un programma java Mysql Tracker

MariaDB supporta System Versioning dal 10.3, che è la funzione SQL standard che fa esattamente ciò che vuoi: memorizza la cronologia dei record delle tabelle e ti dà accesso tramite query SELECT . MariaDB è un fork di sviluppo aperto di MySQL. Puoi trovare ulteriori informazioni sulla sua versione del sistema tramite questo link:

https://mariadb.com/kb/en/library/system-versioned-tables/