Il mondo di MySQL e MariaDB

Guida UPSERT MySQL: Come utilizzare INSERT ON DUPLICATE KEY UPDATE con esempi

Fondamenti di SQL
目次

1. Cos’è UPSERT?

Panoramica

“UPSERT” si riferisce a un’operazione di database che combina sia INSERT sia UPDATE. In altre parole, se i dati non esistono già, verranno inseriti; se esistono, verranno aggiornati. Questa funzionalità consente operazioni efficienti mantenendo la coerenza dei dati.

In MySQL, la sintassi INSERT ... ON DUPLICATE KEY UPDATE fornisce questa funzionalità. Con essa, è possibile evitare errori di chiave duplicata e aggiornare i record esistenti anche quando si verificano situazioni di dati duplicati.

Casi d’uso

  • Sistemi di gestione clienti: Inserire nuovi dati cliente se non esistono, o aggiornare le informazioni esistenti quando cambiano.
  • Gestione dell’inventario: Aggiungere nuovi prodotti aggiornando al contempo le quantità di magazzino dei prodotti esistenti.

Vantaggi di UPSERT in MySQL

  • Evita errori di chiave duplicata
  • Semplifica le query SQL
  • Mantiene l’integrità dei dati

2. Utilizzo base di UPSERT in MySQL

In MySQL, le operazioni UPSERT sono implementate usando la sintassi INSERT ... ON DUPLICATE KEY UPDATE. Con questa, se viene trovata una chiave duplicata, il record esistente viene aggiornato invece di inserirne uno nuovo.

Sintassi di base

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON DUPLICATE KEY UPDATE
column1 = value1, column2 = value2;

Spiegazione:

  1. INSERT INTO tenta di inserire dati nella tabella.
  2. Se i dati esistono già la clausola ON DUPLICATE KEY UPDATE viene eseguita e aggiorna il record esistente.

Esempio:

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON DUPLICATE KEY UPDATE
name = 'Taro Tanaka';

In questo esempio, se un utente con user_id = 1 esiste già, il suo name verrà aggiornato a ‘Taro Tanaka’. Altrimenti, verrà inserito un nuovo record.

3. Sintassi SQL dettagliata ed esempi per UPSERT

Aggiornamento di più colonne

Quando si utilizza UPSERT, è possibile scegliere di aggiornare solo alcune colonne. In tali casi, specificare solo le colonne di destinazione nella clausola ON DUPLICATE KEY UPDATE.

INSERT INTO products (product_id, name, price)
VALUES (100, 'Laptop', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Qui, se un prodotto con product_id = 100 esiste già, solo la colonna price verrà aggiornata, lasciando inalterate le altre colonne come name.

4. Differenze con altri database

Altri database forniscono anche funzionalità simili a UPSERT. Ad esempio, PostgreSQL e SQLite usano INSERT ... ON CONFLICT o MERGE come loro equivalenti.

Esempio PostgreSQL

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON CONFLICT (user_id) DO UPDATE SET
name = 'Taro Tanaka';

In PostgreSQL e SQLite, la clausola ON CONFLICT è usata per controllare il comportamento quando si verificano errori di chiave duplicata. Al contrario, MySQL utilizza ON DUPLICATE KEY UPDATE.

Unicità di MySQL

  • MySQL utilizza INSERT ... ON DUPLICATE KEY UPDATE, che differisce dagli altri database. È necessaria particolare attenzione durante la migrazione del database.

5. Utilizzo avanzato di UPSERT

UPSERT in blocco (più record contemporaneamente)

UPSERT può anche essere applicato a più record in un’unica operazione, migliorando significativamente l’efficienza delle operazioni di database.

INSERT INTO products (product_id, name, price)
VALUES
(100, 'Laptop', 50000),
(101, 'Smartphone', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Qui, più prodotti vengono inseriti contemporaneamente e, se esiste una chiave duplicata, solo il price viene aggiornato.

Utilizzo di stored procedure per UPSERT

È anche possibile utilizzare stored procedure per semplificare le operazioni UPSERT. Questo rende il codice riutilizzabile, più leggibile e più facile da mantenere.

6. Insidie e considerazioni per UPSERT

Transazioni e deadlock

Quando si utilizza UPSERT con grandi set di dati, possono verificarsi deadlock. Se il livello di isolamento delle transazioni di MySQL è impostato su REPEATABLE READ, i gap lock sono più probabili.

Evitare i gap lock

  • Cambiare il livello di isolamento della transazione a READ COMMITTED può ridurre i rischi di deadlock.
  • Considerare di suddividere le operazioni UPSERT in query più piccole quando necessario.

7. Conclusione

La funzionalità UPSERT di MySQL è uno strumento potente per inserire e aggiornare dati in modo efficiente, evitando errori di chiave duplicata. Tuttavia, è fondamentale considerare attentamente le impostazioni delle transazioni e i potenziali deadlock. Se usato correttamente, UPSERT può semplificare e rendere più efficaci le operazioni sul database.