La clausola insert on duplicate key update fa parte di quelle funzionalità di MySQL non proprio celeberrime, ma che offrono una importante comodità, soprattutto considerando il consumo ridotto di risorse e la relativa velocità di esecuzione.

Il costrutto permette di istruire MySQL su una operazione di update da lanciare nel caso una query di insert andasse male per un problema di duplicazione di dati.

Facciamo un esempio molto concreto.

EAN, e-commerce e CSV

Immaginiamo un processo di importazione prodotti da un file CSV, all'interno del quale ogni riga ha un codice EAN univoco. Il nostro script dovrà quindi occuparsi di inserire i prodotti nuovi ed aggiornare i campi nel caso il prodotto dovesse già esistere.

Una soluzione poco approfondita potrebbe essere qualcosa di simile a:

$csvRows = [...]; //contenuto dei file CSV

foreach($row as $csvRows)
{
    $ean = $row['ean'];
    $name = $row['name'];
    $availability = (int) $row['availability'];

    $product = Product::firstWhere('ean', $ean);

    if(!$product) {
        $product = new Product
    }

    $product->name = $name;
    $product->availability = $name;
    $product->save();
}

Per ogni riga del file, verifichiamo la presenza del record a database. In caso positivo utilizziamo il modello ottenuto per fare un aggiornamento mentre in caso negativo creiamo una nuova istanza di Product e procediamo con la insert.

Ricordiamo che il metodo save è abbastanza intelligente da capire quale operazione MySQL invocare sulla base della presenza o meno del campo id, disponibile solamente se il prodotto è già presente a database.

Riassunto: per ogni riga del file effettuiamo una select e una insert o una update.

Eloquent ci da una mano, ma non basta

Eloquent ci offre un metodo, updateOrCreate per rendere il codice più leggibile. Possiamo riscrivere l'esempio di prima in questo modo:

$csvRows = [...]; //contenuto dei file CSV

foreach($row as $csvRows)
{
    $ean = $row['ean'];
    $name = $row['name'];
    $availability = (int) $row['availability'];

    Product::updateOrCreate([
        'ean' => $ean
    ], [
        'name' => $name,
        'availability' => $availability
    ]);
}

Il metodo è fantastico sia nel nome che nella funzionalità, ma questo non ci basta. Essendo solamente una variazione stilistica rispetto alla soluzione precedente, presenta gli stessi difetti, ovvero il fatto che per ogni riga del file CSV, lo script esegue ben 2 query, una select e una insert/update.

Troppe query, troppo tempo

Nonostante il numero di query possa sembrare comunque ridotto, se il file CSV cresce i tempi possono diventare biblici. Proviamo ad immaginare un file con 5 milioni di righe; 10 milioni di query sono un bel numero anche per un MySQL super-ottimizzato.

Pensiamo lateralmente

Torniamo quindi all'argomento principale dell'articolo, la clausola insert on duplicate key update. Questa condizione di MySQL è una sorta di try/catch sotto forma di query.

Questo un esempio:

INSERT INTO table (ean, a, b, c) 
    VALUES('xxx', 'aa', 'bb', 'cc') 
ON DUPLICATE KEY 
    UPDATE table SET a = 'aa', b = 'bb', c = 'cc';

Il comportamento è molto semplice: - provo ad inserire un determinato record con i valori indicati nella seconda riga - se tutto procede normalmente, nessun problema - se ottengo un errore di "duplicate key", procedo con l'aggiornamento delle colonne indicate nella quarta riga

Tramite questa query è possibile quindi spostare il comportamento condizionale da PHP a MySQL, dimezzando quindi il numero di query trasmesse al nostro caro database.

Unico vincolo, da tenere sempre in considerazione, è quello che dobbiamo creare un indice unique sulla/sulle colonna/e incriminata/e, cosí da comunicare a MySQL quale è la nostra regola di univocità e finire quindi nel caso on duplicate key.

CREATE UNIQUE INDEX ean_unique
    ON table_name(ean);

Query raw? No grazie!

Una volta scoperto questo trucchetto, quello che ci resta da fare è renderlo disponibile tramite Eloquent cosí da evitare di dover costruire a mano la query.

Ma grazie a questo pacchetto non dobbiamo preoccuparci di niente!

Il suo funzionamento è, per nostra fortuna, triviale.

Basta aggiungere il trait Yadakhov\InsertOnDuplicateKey ai modelli ai quali vogliamo abilitare questa funzionalità e richiamare il metodo statico insertOnDuplicateKey. Ecco un esempio:

$csvRows = [...]; //contenuto dei file CSV

foreach($row as $csvRows)
{
    $ean = $row['ean'];
    $name = $row['name'];
    $availability = (int) $row['availability'];

    Product::insertOnDuplicateKey([[
        'ean' => $ean,
        'name' => $name,
        'availability' => $availability
    ]], [
        'name',
        'availability'
    ]);
}

Il primo parametro rappresenta i dati da inserire nel database, mentre il secondo l'elenco delle colonne da aggiornare in caso di errore di duplicate key.

Siamo quindi passati da 10 milioni a 5 milioni di query cambiando pochissimo il nostro codice. Ma non finisce qua...

Spingiamoci oltre

I più attenti avranno notato una doppia parentesi quadra nel primo parametro passato al metodo insertOnDuplicateKey. Questo perchè la fantastica clausola MySQL permette di eseguire operazioni di questo tipo in maniera bulk, inserendo le condizioni per insert multiple in una medesima query, esattamente come una insert tradizonale:

INSERT INTO table (ean, a, b, c) 
    VALUES
        ('xxx', 'aa1', 'bb1', 'cc1'),
        ('yyy', 'aa2', 'bb2', 'cc2'),
        ('yyy', 'aa3', 'bb3', 'cc3'),
        ('yyy', 'aa4', 'bb4', 'cc4')
ON DUPLICATE KEY 
    UPDATE table SET a = VALUES(a), b = VALUES(b), c = VALUES(c);

Grazie alla funzione values possiamo referenziare il valore corrente della colonna, proprio come se fosse una variabile all'interno di un ciclo.

Con pò di ottimizzazione quindi possiamo riscrivere il nostro codice in questo modo:

$csvRows = [...]; //contenuto dei file CSV

$data = array_map(function($row) {
    $ean = $row['ean'];
    $name = $row['name'];
    $availability = (int) $row['availability'];

    return [
        'ean' => $ean,
        'name' => $name,
        'availability' => $availability
    ];
}, $csvRows);

Product::insertOnDuplicateKey($data, [
    'name',
    'availability'
]);

Neanche fosse magia, con questo aggiornamento aggiorniamo l'intero CSV con una singola query!

Attenzione però che la lunghezza di una query non è infinita, cosí come non sono infiniti i placeholder che possiamo utilizzare all'interno di essa. Un refactoring quindi più affidabile e conservativo potrebbe essere questo:

$csvRows = [...]; //contenuto dei file CSV

$chunkedCsvRows = array_chunk($csvRows, 1000);

foreach($chunkedCsvRows as $chunk) {

    $data = array_map(function($row) {
        $ean = $row['ean'];
        $name = $row['name'];
        $availability = (int) $row['availability'];

        return [
            'ean' => $ean,
            'name' => $name,
            'availability' => $availability
        ];
    }, $chunk);

    Product::insertOnDuplicateKey($data, [
        'name',
        'availability'
    ]);

}

Grazie ad array_chunk, creiamo dei sotto-array di 1000 righe ciascuno, e per ognuno di essi lanciamo una query MySQL. Nonostante possa sembrare una limitazione, questa soluzione ci permette di evitare i problemi indicati sopra, essendo certi di non lanciare una query con più di 1000 insert contemporanee.

Ovviamente il numero 1000 è un numero totalmente casuale, potremmo ulteriormente ottimizzare questo numero, tenendo in considerazione anche quante sono le colonne della tabella da aggiornare.

Dai 10 milioni iniziali, siamo quindi passati a 5 mila query. Non male come ottimizzazione!

Che faccio? Lascio?

La libreria indicata in realtà permette anche di eseguire altre operazioni, similari a quella analizzata in questo articolo. Lasciamo a voi l'onere (o forse il piacere) di scoprirle.