MySQL INSERT vs UPDATE: Sintassi, Esempi, ON DUPLICATE KEY UPDATE e REPLACE

1. Introduzione

MySQL è un popolare sistema di gestione di database relazionali utilizzato in molte applicazioni web e sistemi di gestione di database. Tra le sue funzionalità, la istruzione INSERT e la istruzione UPDATE — usate per aggiungere e modificare i dati — svolgono un ruolo essenziale come operazioni fondamentali sui dati. Comprendendole correttamente e usandole in modo efficiente, le operazioni sul database diventano più fluide.

In questo articolo forniamo una spiegazione dettagliata di MySQL INSERT e UPDATE, dall’uso di base alle operazioni avanzate. Il contenuto è adatto a principianti e utenti di livello intermedio, quindi usatelo come riferimento.

2. Nozioni di base su INSERT

Sintassi di base per INSERT

La sintassi di base di un’istruzione INSERT è la seguente.

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

Come esempio, consideriamo l’aggiunta di un nuovo utente alla tabella users.

INSERT INTO users (name, email, age)
VALUES ('Taro Yamada', 'taro@example.com', 30);

Questo SQL inserisce i valori “Taro Yamada”, “taro@example.com” e “30” nelle colonne name, email e age della tabella users, rispettivamente.

Inserimento di più righe

In MySQL, è possibile inserire più righe di dati contemporaneamente. In tal caso, la sintassi è la seguente.

INSERT INTO users (name, email, age)
VALUES
('Hanako Sato', 'hanako@example.com', 25),
('Ichiro Suzuki', 'ichiro@example.com', 40);

Utilizzare questo metodo riduce il numero di accessi al database e può migliorare le prestazioni.

Gestione dei valori NULL

Quando si utilizza un’istruzione INSERT, potrebbe essere necessario gestire valori NULL. Ad esempio, se age non è impostato, scrivilo così.

INSERT INTO users (name, email, age)
VALUES ('Jiro Tanaka', 'jiro@example.com', NULL);

Nota che se una colonna ha un vincolo NOT NULL, inserire un valore NULL genererà un errore. In tal caso, è necessario impostare un valore predefinito o specificare esplicitamente un valore.

3. Nozioni di base su UPDATE

Sintassi di base per UPDATE

L’istruzione UPDATE è usata per modificare i dati nei record esistenti. In questa sezione spieghiamo la sintassi di base, come aggiornare con condizioni e perché la clausola WHERE è importante.

UPDATE table_name
SET column1 = new_value1, column2 = new_value2
WHERE condition;

Come esempio, consideriamo l’aggiornamento dell’età di un utente specifico nella tabella users.

UPDATE users
SET age = 35
WHERE name = 'Taro Yamada';

Questo SQL aggiorna l’età a 35 per l’utente il cui nome è “Taro Yamada” nella tabella users.

Perché la clausola WHERE è importante

Se ometti la clausola WHERE in un’istruzione UPDATE, tutte le righe della tabella verranno aggiornate. Questo può causare perdita di dati non intenzionale, quindi assicurati di specificare le condizioni.

-- When the WHERE clause is omitted
UPDATE users
SET age = 30;

Questo SQL imposta l’età di tutti gli utenti a 30.

Aggiornamenti condizionali

Quando specifichi più condizioni, usa AND o OR.

UPDATE users
SET age = 28
WHERE name = 'Hanako Sato' AND email = 'hanako@example.com';

In questo modo, puoi aggiornare i dati con condizioni più precise.

4. Combinare INSERT e UPDATE

Nelle operazioni di database, potresti incontrare situazioni in cui a volte è necessario aggiungere nuovi dati e altre volte aggiornare dati esistenti. In tali scenari, è possibile gestire in modo efficiente usando INSERT ... ON DUPLICATE KEY UPDATE o l’istruzione REPLACE. In questa sezione spieghiamo come usare ciascuna e a cosa fare attenzione.

Come usare INSERT … ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE è efficace quando esistono vincoli di chiave primaria o chiave unica. Con questa sintassi, è possibile eseguire “aggiorna se esiste, inserisci se non esiste” in un’unica istruzione SQL.

Sintassi

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

Considera l’aggiunta di un nuovo utente alla tabella users. Se lo stesso email esiste già, aggiorna il name e age di quell’utente.

INSERT INTO users (email, name, age)
VALUES ('taro@example.com', 'Taro Yamada', 30)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
age = VALUES(age);

Questa istruzione SQL fa quanto segue:

1. Se non esiste un record con email = 'taro@example.com' , inserisci i dati.
2. Se esiste un record, aggiorna name e age .

Note

• Se c’è una colonna AUTO_INCREMENT, il contatore aumenta anche quando si verifica una chiave duplicata. Questo può causare comportamenti non intenzionali, quindi fai attenzione.
• Utilizzando la funzione VALUES(), puoi riutilizzare i valori che hai tentato di inserire per l’aggiornamento.

Come Funziona REPLACE e Come Differisce

L’istruzione REPLACE elimina completamente i dati esistenti (quando viene trovata una chiave duplicata) e poi inserisce i nuovi dati. A differenza di INSERT ... ON DUPLICATE KEY UPDATE, il record originale viene eliminato.

Sintassi

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

Esempio

Inserisci dati nella tabella users, e se email è duplicato, elimina i dati esistenti e inserisci i nuovi dati.

REPLACE INTO users (email, name, age)
VALUES ('taro@example.com', 'Taro Yamada', 30);

Questa istruzione SQL fa quanto segue:

1. Se esiste un record con email = 'taro@example.com' , elimina quel record.
2. Inserisci i nuovi dati.

Note

• Poiché esegue delete + insert, potrebbe influenzare i trigger e i vincoli di chiave esterna.
• Sii consapevole degli effetti collaterali dalla cancellazione (come la perdita di dati correlati).

Considerazioni sulle Prestazioni

INSERT ... ON DUPLICATE KEY UPDATE e REPLACE hanno ciascuno pro e contro. Per database grandi o operazioni ad alta frequenza, le differenze di prestazioni contano, quindi considera i seguenti punti.

Scegliere il Caso d’Uso Giusto

• Quando INSERT … ON DUPLICATE KEY UPDATE è adatto
• Quando esistono vincoli di chiave esterna o trigger e vuoi evitare le cancellazioni.
• Quando gli aggiornamenti avvengono frequentemente.
• Quando REPLACE è adatto
• Quando hai bisogno di una sostituzione completa dei dati.
• Per tabelle semplici non influenzate da vincoli di chiave esterna o trigger.

5. Esempi Pratici

Qui, introduciamo casi d’uso reali per MySQL INSERT e UPDATE, nonché l’uso pratico di “INSERT … ON DUPLICATE KEY UPDATE” e “REPLACE.” Questo ti aiuterà a capire come applicare ciò che hai imparato nel lavoro effettivo.

Caso d’Uso 1: Sistema di Gestione dell’Inventario

In un sistema di gestione dell’inventario, la registrazione dei prodotti e gli aggiornamenti delle scorte avvengono frequentemente. Usa INSERT per aggiungere nuovi prodotti, e usa UPDATE o “INSERT … ON DUPLICATE KEY UPDATE” per aggiornare i prodotti esistenti.

Inserimento e Aggiornamento dei Dati del Prodotto

Ad esempio, supponi che una tabella di prodotti products sia strutturata come segue.

Registrazione di un Nuovo Prodotto

INSERT INTO products (product_id, name, stock)
VALUES (1, 'Laptop', 50);

Aggiornamento della Quantità di Scorta (Prodotto Esistente)

UPDATE products
SET stock = stock + 20
WHERE product_id = 1;

Inserisci Nuovo o Aggiorna Quantità di Scorta

Se vuoi registrare un nuovo prodotto o aggiornare la scorta per un prodotto esistente, usa “INSERT … ON DUPLICATE KEY UPDATE.”

INSERT INTO products (product_id, name, stock)
VALUES (1, 'Laptop', 50)
ON DUPLICATE KEY UPDATE
stock = stock + 50;

Questo SQL realizza quanto segue:

• Se non esistono dati per l’ID prodotto 1, inseriscili.
• Se esistono dati per l’ID prodotto 1, aggiungi 50 alla quantità di scorta.

Caso d’Uso 2: Gestione delle Informazioni Utente

Nelle applicazioni web, la registrazione degli utenti e gli aggiornamenti sono routine. Usa INSERT per registrare nuovi utenti, e usa UPDATE o “INSERT … ON DUPLICATE KEY UPDATE” per aggiornare le informazioni utente esistenti.

Struttura della Tabella Utente

Registrazione di un Nuovo Utente

INSERT INTO users (user_id, name, email, last_login)
VALUES (1, 'Taro Yamada', 'taro@example.com', NOW());

Aggiornamento delle informazioni utente

Ad esempio, quando un utente modifica il proprio profilo.

UPDATE users
SET name = 'Hanako Yamada', email = 'hanako@example.com'
WHERE user_id = 1;

Registrare o aggiornare le informazioni

Quando un utente accede per la prima volta, registralo; se l’utente esiste già, aggiorna il timestamp dell’ultimo accesso.

INSERT INTO users (user_id, name, email, last_login)
VALUES (1, 'Taro Yamada', 'taro@example.com', NOW())
ON DUPLICATE KEY UPDATE
last_login = NOW();

Caso d’uso 3: Aggiornamenti periodici dei dati

Quando si gestiscono dati di sensori o di log, nuovi dati possono essere inseriti ogni minuto o secondo. In questo caso, è possibile inserire nuovi dati con INSERT o aggiornare i dati esistenti in modo condizionale.

Inserimento dei dati di log

Ecco una tabella di esempio sensor_logs per la registrazione dei dati dei sensori.

Registrazione di nuovi dati del sensore

INSERT INTO sensor_logs (sensor_id, temperature, last_updated)
VALUES (1, 25.5, NOW());

Aggiornare o inserire dati

Se l’ID del sensore esiste già, aggiorna i dati; altrimenti, inseriscili.

INSERT INTO sensor_logs (sensor_id, temperature, last_updated)
VALUES (1, 25.5, NOW())
ON DUPLICATE KEY UPDATE
temperature = VALUES(temperature),
last_updated = VALUES(last_updated);

Note e migliori pratiche

1. Gestione degli errori: Quando si utilizza ON DUPLICATE KEY UPDATE o REPLACE, è importante verificare in anticipo l’impatto di trigger e vincoli di chiave esterna.
2. Ottimizzazione delle prestazioni: Per dati su larga scala, utilizza la progettazione di indici e le transazioni per operare in modo efficiente.
3. Integrità dei dati: Specialmente con REPLACE, avviene delete + insert, quindi è necessario adottare misure per evitare il rischio di perdita dei dati correlati.

6. Errori comuni e come risolverli

Quando si utilizzano le istruzioni MySQL INSERT e UPDATE, possono verificarsi vari errori. In questa sezione, spieghiamo gli errori comuni, le loro cause e i modi specifici per risolverli.

Esempi di errori comuni

1. Errore di voce duplicata

Messaggio di errore:

Error: Duplicate entry '1' for key 'PRIMARY'

Causa:

• Ciò si verifica quando si tenta di inserire un valore che esiste già in una colonna con chiave primaria o vincolo unico (UNIQUE).

Come risolvere:

• Usa ON DUPLICATE KEY UPDATE: Esegui un aggiornamento quando esiste una voce duplicata.

  INSERT INTO users (user_id, name, email)
  VALUES (1, 'Taro Yamada', 'taro@example.com')
  ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email);
  

• Verifica l’esistenza prima di inserire: Per evitare duplicati, controlla in anticipo se i dati esistono già.

  SELECT COUNT(*) FROM users WHERE user_id = 1;
  

2. Errore di vincolo di chiave esterna

Messaggio di errore:

Error: Cannot add or update a child row: a foreign key constraint fails

Causa:

• Ciò si verifica quando i dati della tabella padre di riferimento non esistono a causa di un vincolo di chiave esterna (FOREIGN KEY).

Come risolvere:

• Inserisci i dati correlati nella tabella padre.

  INSERT INTO parent_table (id, name) VALUES (1, 'Parent data');
  

• Disabilita temporaneamente i vincoli di chiave esterna (non consigliato).

  SET FOREIGN_KEY_CHECKS = 0;
  -- Data operations
  SET FOREIGN_KEY_CHECKS = 1;
  

3. Errore relativo a NULL

Messaggio di errore:

Error: Column 'name' cannot be null

Causa:

• Ciò si verifica quando si tenta di inserire un valore NULL anche se la colonna ha un vincolo NOT NULL.

Come risolvere:

• Imposta un valore predefinito.

  ALTER TABLE users MODIFY name VARCHAR(255) NOT NULL DEFAULT 'Unspecified';
  

• Inserisci un valore appropriato con INSERT.

  INSERT INTO users (name, email, age)
  VALUES ('Taro Yamada', 'taro@example.com', NULL);
  

4. Errore di tipo di dato

Messaggio di errore:

Error: Data truncated for column 'age' at row 1

Causa:

• Questo si verifica quando si tenta di inserire o aggiornare un valore che non corrisponde al tipo di dato della colonna.

Come risolvere:

• Controllare il tipo di dato e utilizzare un valore appropriato.

  INSERT INTO users (age) VALUES (30); -- For an INT column
  

• Modificare il tipo di dato della colonna (se necessario).

  ALTER TABLE users MODIFY age VARCHAR(10);
  

5. Errore Relativo al Blocco della Tabella

Messaggio di errore:

Error: Lock wait timeout exceeded; try restarting transaction

Causa:

• Questo si verifica quando un’altra transazione blocca la tabella e il blocco non viene rilasciato entro un certo tempo.

Come risolvere:

• Per evitare la contesa delle transazioni, considerare azioni come:
• Suddividere le query per ridurre il blocco della tabella.
• Creare indici appropriati per velocizzare l’esecuzione delle query.

Best Practices per Prestazioni e Prevenzione degli Errori

1. Utilizzare la gestione delle transazioni

• Quando si eseguono più INSERT e UPDATE, utilizzare le transazioni per gestire le operazioni in modo affidabile.

  START TRANSACTION;
  INSERT INTO orders (order_id, user_id) VALUES (1, 1);
  UPDATE users SET last_order = NOW() WHERE user_id = 1;
  COMMIT;
  

2. Ottimizzare gli indici

• Impostare indici appropriati su chiavi primarie e chiavi esterne riduce il rischio di errori e migliora le prestazioni.

  ALTER TABLE users ADD INDEX (email);
  

3. Rollback in caso di errori

• Quando si verifica un errore, eseguire un rollback per mantenere l’integrità dei dati.

  START TRANSACTION;
  -- Some operations
  ROLLBACK; -- On error
  

7. FAQ

Quando si utilizzano le istruzioni MySQL INSERT e UPDATE, molte persone hanno domande simili. In questa sezione approfondiamo la comprensione attraverso domande e risposte comuni.

Q1: Quale dovrei usare, INSERT o UPDATE?

Risposta:

Usare INSERT per aggiungere nuovi dati e UPDATE per modificare i dati esistenti. Tuttavia, se l’aggiunta di nuovi dati e l’aggiornamento di dati esistenti sono mescolati, utilizzare “INSERT … ON DUPLICATE KEY UPDATE” è l’opzione migliore.

Esempio:

INSERT INTO users (user_id, name, email)
VALUES (1, 'Taro Yamada', 'taro@example.com')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
email = VALUES(email);

Questa sintassi può gestire l’inserimento di nuovi dati e l’aggiornamento di dati esistenti in un’unica query.

Q2: Posso usare ON DUPLICATE KEY UPDATE per ogni caso d’uso?

Risposta:

No. ON DUPLICATE KEY UPDATE ha le seguenti limitazioni:

1. Funziona solo quando è definita una chiave primaria o una chiave unica. Se non è definita, non si verifica alcun errore, ma non si comporta come previsto.
2. Per aggiornamenti su larga scala, le prestazioni possono degradare. In tal caso, considerare l’uso di transazioni o la suddivisione dei dati.

Q3: Qual è la differenza tra REPLACE e ON DUPLICATE KEY UPDATE?

Risposta:

Sono simili, ma il loro comportamento differisce significativamente.

Come linea guida, usare ON DUPLICATE KEY UPDATE quando si desidera aggiornare senza cancellare i dati, e usare REPLACE quando si desidera una sostituzione completa.

Q4: Cosa succede se dimentico la clausola WHERE?

Risposta:

Se si esegue un’istruzione UPDATE senza specificare una clausola WHERE, tutti i record della tabella verranno aggiornati. Questo è estremamente pericoloso e può causare modifiche involontarie dei dati.

Esempio:

-- The age of all records is updated to 30
UPDATE users
SET age = 30;

Prevenzione:

• Specificare sempre una clausola WHERE in modo che vengano aggiornati solo i dati che corrispondono a condizioni specifiche.
• Come buona pratica, eseguire prima una query SELECT per confermare i dati target prima di eseguire l’UPDATE.

  SELECT * FROM users WHERE name = 'Taro Yamada';
  UPDATE users SET age = 35 WHERE name = 'Taro Yamada';
  

Q5: Esiste un modo per velocizzare INSERT e UPDATE?

Risposta:

È possibile migliorare le prestazioni utilizzando i seguenti metodi.

1. Ottimizzare gli indici: Impostare indici appropriati sulle colonne necessarie per velocizzare le operazioni di ricerca e aggiornamento.

   CREATE INDEX idx_email ON users(email);
   

2. Operazioni batch: È più efficiente inserire o aggiornare più righe contemporaneamente piuttosto che elaborare una riga alla volta.

   INSERT INTO users (name, email, age)
   VALUES
   ('Hanako Sato', 'hanako@example.com', 25),
   ('Ichiro Suzuki', 'ichiro@example.com', 40);
   

3. Usa le transazioni: Elaborare più operazioni in un’unica transazione riduce la contesa sui lock.

   START TRANSACTION;
   INSERT INTO orders (order_id, user_id) VALUES (1, 1);
   UPDATE users SET last_order = NOW() WHERE user_id = 1;
   COMMIT;
   

4. Evita operazioni non necessarie: Controlla i dati in anticipo per evitare aggiornamenti o inserimenti superflui.

   SELECT COUNT(*) FROM users WHERE user_id = 1;
   -- Avoid unnecessary inserts/updates
   

Q6: Come posso prevenire errori di INSERT o UPDATE?

Risposta:

Per prevenire gli errori, utilizza i seguenti approcci.

• Verifica i tipi di dato: Assicurati che i dati che inserisci/aggiorni corrispondano al tipo di dato della colonna.
• Imposta correttamente i vincoli: Configura correttamente chiavi primarie, chiavi uniche e vincoli di chiave esterna per mantenere l’integrità dei dati.
• Implementa la gestione degli errori: Aggiungi logica nel tuo programma per gestire gli errori quando si verificano.

  -- Roll back on error
  START TRANSACTION;
  INSERT INTO users (user_id, name, email) VALUES (1, 'Taro Yamada', 'taro@example.com');
  ROLLBACK; -- As needed
  

8. Riepilogo

In questo articolo abbiamo coperto una vasta gamma di argomenti: le basi e l’uso avanzato delle istruzioni MySQL INSERT e UPDATE, casi d’uso pratici, gestione degli errori e risposte alle domande più comuni. Di seguito trovi un riepilogo dei punti chiave.

Punti chiave

1. Nozioni di base su INSERT

• Usa INSERT per inserire nuovi dati in una tabella.
• Puoi inserire più righe, consentendo operazioni efficienti.
• Fai attenzione ai valori NULL e ai vincoli NOT NULL.

2. Nozioni di base su UPDATE

• Usa UPDATE per modificare dati esistenti in base a condizioni.
• Specifica sempre una clausola WHERE per evitare di aggiornare involontariamente tutte le righe.

3. Combinare INSERT e UPDATE

• INSERT ... ON DUPLICATE KEY UPDATE consente di inserire nuovi dati e aggiornare quelli esistenti in un’unica operazione.
• REPLACE elimina i dati e li reinserisce, quindi fai attenzione a trigger e chiavi esterne.

4. Esempi pratici

• Hai imparato come usare INSERT e UPDATE in casi d’uso come la gestione dell’inventario e la gestione delle informazioni degli utenti.
• Abbiamo anche presentato le migliori pratiche per elaborare efficientemente più operazioni.

5. Errori e correzioni

• Abbiamo spiegato cause e soluzioni per problemi comuni come voci duplicate, vincoli di chiave esterna e errori di inserimento di NULL.
• L’uso di transazioni e della progettazione degli indici è importante.

6. FAQ

• Abbiamo risposto a domande comuni su come scegliere tra INSERT e UPDATE, l’ambito di ON DUPLICATE KEY UPDATE e l’ottimizzazione delle prestazioni.

Prossimi passi

Le istruzioni MySQL INSERT e UPDATE sono fondamentali per le operazioni di database e competenze essenziali per lo sviluppo di applicazioni. In base a quanto appreso in questo articolo, considera i seguenti prossimi passi.

1. Impara la gestione delle transazioni: Per eseguire operazioni di database più avanzate, approfondisci la tua comprensione di come utilizzare le transazioni.
2. Ottimizza la progettazione degli indici: Impara a progettare gli indici per mantenere le prestazioni delle query man mano che il volume dei dati cresce.
3. Migliora il logging per il troubleshooting: Introduci la registrazione e l’analisi dei log così da poter identificare rapidamente le cause quando si verificano errori.
4. Usa la documentazione ufficiale di MySQL: Per maggiori dettagli e le ultime funzionalità, consulta la documentazione ufficiale di MySQL .

Nota finale

Ci auguriamo che questo articolo ti aiuti a comprendere INSERT e UPDATE e a usarli in modo efficiente. Padroneggiare le operazioni di base sui dati migliorerà le tue capacità di gestione del database e ti consentirà di affrontare sviluppi applicativi più avanzati.

Continua ad approfondire la tua conoscenza di MySQL!