Definizione di UPSERT
Spesso capita di dover gestire situazione nelle quali l’inserimento di un nuovo record nel database può comportare la presenza di chiavi duplicate. Storicamente l’algoritmo usato prevede una SELECT atta ad individuare la presenza di chiavi duplicate e conseguente utilizzo di una istruzione di INSERT o di UPDATE a seconda del caso da gestire. Il comportamento che si ottiene con questo tipo di algoritmo prende il nome di UPSERT che chiaramente deriva dalla commistione tra UPDATE e INSERT. (qui trovate un po’ di bibliografia in merito) .
Il problema
Dove sta il problema? Beh, non c’è nessun problema se non che scrivere questo tipo di algoritmo (per quanto banale) è una rottura di scatole ed inoltre non è affatto efficiente in quanto richiede sempre una SELECT di verifica.
La soluzione
Esiste una soluzione più comoda e pratica? Certo! E quasi tutti i vari DBMS, nelle loro incarnazioni più recenti, la prevedono. In particolare mi occuperò della versione MySql in quanto questo mi tocca usare tutti i santi giorni e questo è il motivo del presente post.
L’istruzione che risolve il tutto è INSERT … ON DUPLICATE KEY UPDATE. La logica di funzionamento è abbastanza banale da comprendere: fai la tua INSERT normalmente e, in caso di chiavi duplicate, anziché uscirtene con un bel errore, aggiorna le chiavi secondo quanto richiesto dal pisquano che sta scrivendo la query. Direi che i vantaggi sono evidenti: meno codice da scrivere e una gestione più efficiente del tutto giacché se ne occupa il DBMS e non il programmatore.
Il caso particolare
Uso questo statement da un po’ di tempo e lo trovo molto pratico e veloce oggi però mi sono imbattuto in un’esigenza particolare nell’uso di questa soluzione che apparentemente dava dei problemi. Supponiamo di dover effettuare la copia di un set di record relativi ad una tabella che dispone del classico campo id auto incrementale e di una ulteriore chiave univoca. L’uso dello statement sopra esposto risolve in maniera piuttosto semplice il problema ma c’è un ulteriore requisito che devo soddisfare. Trattandosi di una tabella di tipo “anagrafico” ho la necessità di tracciare una corrispondenza tra l’id originario del record da duplicare ed il nuovo id auto incrementale assegnato al record duplicato.
Perché tutto ciò? Perché dovrò poi duplicare anche un set di record relativi ad una tabella che fa uso dei dati appena duplicati voglio quindi che eventuali puntamenti ai record duplicati vengano “tradotti” i puntamenti ai nuovi record duplicati. Dove sta il problema? E’ presto detto: come faccio a tracciare il legame?
Un primo tentativo è quello di usare, SUBITO DOPO l’istruzione di upsert, l’istruzione LAST_INSERT_ID di MySql. Questo funziona molto bene nel caso il record non esista e venga di fatto eseguita una INSERT ma ritorna un bel 0 nel caso di UPDATE! E perché questo? Perché mentre la INSERT di fatto aggiorna la chiave auto incrementale l’istruzione di UPDATE non lo fa.. e questo ha perfettamente senso!
La gestione del caso particolare
Quindi, che si fa? Si torna alla vecchia SELECT + INSERT o UPDATE? Giammai! La soluzione a questo problema esiste ed è gestita da un parametro specifico di LAST_INSERT_ID. Infatti è previsto che se la funzione in oggetto viene invocata passando come parametro un id tale id venga ritornato dalla stessa e sia considerato l’id dell’ultima operazione di INSERT effettuata. Ok tutto bellissimo… e quindi? Quindi la nostra istruzione di UPSERT diventa:
INSERT INTO tabella SET campo1 = val1, campo2=val2 ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), campo1 = val1, campo2 = val2;
Se usiamo una istruzione LAST_INSERT_ID subito dopo l’UPSERT ci verrà ritornato il nuovo id nel caso di INSERT oppure l’id del record aggiornato nel caso di UPDATE. Fantastico!