MySQL INSERT

L'istruzione SQL di INSERT e' ritenuta una banale necessita' per inserire i dati...

In realta' vi sono diverse possibilita' per l'istruzione di INSERT presenti nel dialetto SQL di MySQL che e' importante conoscere per sfruttarne al pieno le possibilita'.

In questo documento vedremo infatti le diverse forme alternative dell'istruzione SQL di INSERT con MySQL. Nell'ordine:

Lo Standard SQL
La gestione dei NOT NULL
La gestione dei valori multipli
INSERT IGNORE
ON DUPLICATE KEY
REPLACE
...

Si tratta di forme dialettali a volte presenti anche in altri database relazionali con sintassi differenti... come vedremo nell'ultimo capitolo.

Standard SQL

Nello standard SQL sono previste due forme di INSERT, entrambe disponibili in MySQL:

La classica INSERT INTO che consente di inserire un record nella base dati indicandone i valori:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

La versione INSERT... AS SELECT che utilizza una clausola di SELECT per determinare le righe da inserire:
```
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...

  FROM ...;
```
La select utilizzata per popolare i dati puo' far riferimento ad una sola tabella o essere anche molto complessa.

Con entrambe le sintassi l'elenco delle colonne puo' essere omesso se si utilizza lo stesso ordine con cui e' stata creata la tabella.
Naturalmente debbono essere presenti tutte le colonne con il costraint NOT NULL anche se in realta'... continuate a leggere!

NOT NULL

Il comportamento di default di MySQL e' quello di perdonare gli errori dei programmatori!

In pratica se si inserisce un record con alcuni dati errati o mancanti MySQL li "aggiusta" mettendo 0 o stringhe vuote a seconda dei casi. E' possibile modificare tale comportamento inserendo nell'sql_mode la modalita' STRICT_TRANS_TABLES [NdA introdotto nella 5.6 e default nella 5.7] o STRICT_ALL_TABLES con:

SET sql_mode = 'STRICT_TRANS_TABLES';

In strict mode MySQL si comporta come richiede lo standard SQL, restituisce un errore e non inserisce il dato errato.

Maggiori dettagli si trovano nella documentazione ufficiale.

Valori multipli

Una prima significativa estensione MySQL e' la possibilita' di indicare nello stesso statement di INSERT piu' righe:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...),(value1, value2, value3, ...),(value1, value2, value3, ...), ... ;

Questa forma compatta e' piu' efficiente per i caricamenti massivi ed e' quella utilizzata di default dall'utility mysqldump.

INSERT IGNORE

Nel caso di un inserimento di un record con una chiave primaria o univoca duplicata naturalmente la INSERT fallisce...

Con MySQL e' possibile utilizzare la clausola INSERT IGNORE che, nel caso di record duplicati, semplicemente li ignora e prosegue con le altre insert. E' un'opzione molto utile per allineare i dati e' quindi spesso utilizzata anche con gli ETL evitando costosi lookup.

E' possibile utilizzare IGNORE con entrambe le forme di INSERT, ad esempio con:

INSERT IGNORE INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

ON DUPLICATE KEY

Ma se si volessero trattare in modo diverso i record quando questi sono gia' presenti? Vediamo un esempio:

INSERT INTO ordine (scelta, quantita) VALUES ('pizza', 1)
    ON DUPLICATE KEY UPDATE quantita=quantita+1;

REPLACE

Un'altra alternativa disponibile con MySQL e' lo statement REPLACE. La sintassi e' la stessa della INSERT (per entrambe le forme):

REPLACE INTO table_name (column1, column2, column3, ...)
 VALUES (value1, value2, value3, ...);

Con Replace prima vengono cancellati i dati per chiave e poi vengono inseriti.
Quindi se nel caso di un ribaltamento dati tra due tabelle MySQL serve mantenere i dati gia' presenti si utilizza INSERT IGNORE, in caso contrario se si vogliono sostituire i dati eventualmente gia' presenti si utilizza REPLACE.

E gli altri database?

Oracle utilizza lo statement MERGE per unire piu' dati. Le clausole WHEN MATCHED e WHEN NOT MATCHED permettono di indicare le istruzioni da eseguire a seconda casi. MERGE e' disponibile da Oracle 9i.

In Postgres la funzionalita' di UPSERT e' stata inserita nella versione 9.5 [NdA quindi piuttosto di recente: solo dal 2015].

Varie ed eventuali

Le differenti sintassi MySQL per la INSERT descritte in questa pagina sono presenti praticamente in tutte le versioni.

Titolo: MySQL INSERT
Livello: Medio (2/5)
Data: 14 Febbraio 2018
Versione: 1.0.0 - 14 Febbraio 2018 ❤️ San Valentino
Autore: mail [AT] meo.bogliolo.name