Meno query MySQL grazie a INSERT… ON DUPLICATE KEY UPDATE

E’ risaputo che una corretta gestione delle query è requisito essenziale per avere applicazioni stabili ed efficenti. In questo articolo illustreremo una sintassi poco conosciuta ma altamente performante, sto parlando della clausola ON DUPLICATE KEY UPDATE.

Questa clausola viene utilizzata in occasione di una INSERT INTO ed ha lo scopo di verificare, prima dell’inserimento, che non vi sia una duplicazione di una chiave primaria (PRIMARY KEY) o di una chiave unica (UNIQUE KEY); qualora ciò si verificasse il motore di MySQL effettuerà un’UPDATE al posto dell’INSERT.

Il vantaggio di questa clausola è evidente: si scrive una sola query invece di due con vantaggi in termini di performances e pulizia del codice ed allo stesso tempo si riduce la possibilità di errori legati alle procedure di inserimento ed aggiornamento dei dati nel database.

Facciamo un esempio pratico. Supponiamo di voler realizzare un semplice script che effettui il logging dei visitatori del nostro sito. Di seguito la struttura della tabella del nostro DB:

• IP (chiave)
• numero_visite
• ultima_visita

Volendo conteggiare i passaggi di ogni IP sulle nostre pagine dovremo, secondo la logica consueta, effettuare prima di tutto una SELECT preliminare per verificare che l’IP non sia già presente nel DB e solo in caso di risposta negativa (IP non presente) effettuare una query di INSERT.

Grazie alla sintassi INSERT … ON DUPLICATE KEY UPDATE … potremo ottenere lo stesso risultato con una sola query:

INSERT INTO ip_visitatori VALUES('123.123.123.123', 1, NOW()) 
ON DUPLICATE KEY
UPDATE numero_visite = numero_visite + 1, ultima_visita = NOW();

Così facendo se l’IP non è presente viene effettuato l’inserimento, in caso contrario viene eseguito un semplice aggiornamento del record interessato. L’utilizzo di questa sintassi consente di ottenere un beneficio in termini di performances pari al 30%.

Con un po’ di fantasia ed ingegno la clausola in oggetto ci potrà tornare molto utile in svariate circostanze. Ad esempio possiamo utilizzarla in combinazione con una condizione. Facciamo un esempio: supponiamo di avere una tabella di un ipotetico sito di aste on-line così strutturata:

• ID_asta
• migliore_offerta

Si supponga di voler procedere all’inserimento di una nuova offerta (poniamo di 1.000 Euro) per l’asta con ID 1: se l’asta non ha ancora ricevuto offerte si procederà con un INSERT, i caso contrario si procederà ad un semplice aggiornamento ma solo se l’offerta è più alta di quella già presente:

INSERT INTO offerte VALUES(1, 1000) 
ON DUPLICATE KEY
UPDATE migliore_offerta = IF(VALUES(migliore_offerta) < 1000, 1000, VALUES(migliore_offerta))

Mediante una semplice query abbiamo risolto in un colpo solo… in caso contrario avremmo dovuto utilizzare diverse query con conseguente spreco di risorse (ed un maggior rischio di incorrere in errori).