Commenti(0)
Inserire record già esistenti
L’articolo descrive tutte le possibili situazioni in cui ci si può trovare nell’inserimento di record esistenti
La versione originale di questo articolo è tratta da http://santecaserio.altervista.org
L’SQL è un linguaggio imperativo. Questo significa che è costituito da una serie di comandi che indicano un’azione da compiere, lasciando poco spazio per le strutture di controllo del flusso.
Sappiamo che il suo sottoinsieme noto come DML (Data Manipulation Language => http://it.wikipedia.org/wiki/Data_Manipulation_Language ) comprende tre istruzioni fondamentali: DELETE per cancellare i record , UPDATE per aggiornare i record e INSERT per inserirli. Sono azioni piuttosto elementari, che nel nostro lavoro quotidiano dovrebbero combinarsi formando istruzioni più complesse. Un esempio di richiesta complessa che spesso si vorrebbe rivolgere a un database è: “se un certo record nella tabella XXX esiste già, allora fai questo”. Mancando in SQL le istruzioni condizionali, generalmente questo tipo di logica è contenuta nel programma che si occupa di lanciare le query (esempio: uno script PHP). Un’alternativa è scrivere una Stored Procedure che se ne occupi, perchè tali procedure ammettono estensioni procedurali al linguaggio SQL, o possono essere scritte in altri linguaggi.
MySQL però comprende alcune comode estensioni non standard che permettono di comunicare al server come si debba comportare nel caso in cui un certo record già esista, di seguito viene proposta una descrizione delle estensioni:
Valori unici
Cosa significa per un database “questo record esiste già”? Come sappiamo, un record può essere identificato da una o più chiavi, ognuna delle quali può essere un campo, la prima parte di un campo o una combinazione di più campi. Generalmente la Chiave Primaria, che è il modo principale per identificare un record, è costituita da un campo numerico (UNSIGNED INTEGER) chiamato `id`. Vi possono essere delle Chiavi Candidate, che consistono in valori o combinazioni di valori che sono unici per ogni record. In MySQL questi sono i campi che rappresentano gli indici UNIQUE.
INSERT IGNORE
La prima di queste estensioni è la clausola IGNORE dell’istruzione INSERT. Essa semplicemente chiede a MySQL di ignorare gli errori dovuti al tentativo di inserire valori duplicati nella chiave primaria. In altre parole, se si tenta di inserire un record ma esso non può essere scritto poiché contiene un valore unico già presente in tabella, non viene generato alcun messaggio di errore. Se si tenta di inserire diversi record con un’unica INSERT e alcuni di questi non possono essere scritti perchè contengono valori duplicati, gli altri record verranno scritti senza problemi e non viene restituito alcun errore. Spesso infatti, i messaggi di errore hanno effetti collaterali che si desidera evitare. Un’ulteriore possibilità è usare, oltre alla clausola IGNORE, anche la clausola DELAYED. Essa chiede a MySQL di non generare alcun messaggio di errore in nessun caso. Questo vale per gli errori di tipo “Duplicate key”, ma anche ogni altro tipo di errore – ad esempio la mancanza dei permessi necessari per scrivere sulla tabella, disco pieno, etc. Se tale comportamento ci è comunque gradito (perchè non abbiamo intenzione di gestire alcun tipo di errore) allora la clausola DELAYED è da preferire per ragioni di performance. Infatti ciò che rallenta maggiormente le applicazioni sono le comunicazioni di rete e il mancato invio di messaggi di errore che verrebbero comunque ignorati è un’ottimizzazione intelligente. Comunque, è bene ricordare che gli unici Storage Engine a supportare questa clausola sono MyISAM, MEMORY, ARCHIVE e BLACKHOLE. Se usata su tabelle che usano altri motori, DELAYED provoca un errore.
Esempi:
REPLACE
Il comportamento appena illustrato è desiderabile nel caso in cui il record che si tenta di inserire non possa essere più completo di quello già esistente. Se però nel record già presente in tabella alcuni campi sono vuoti (o meglio, impostati ai valori di DEFAULT), anche nel caso in cui l’istruzione INSERT contenga tali valori, essi andranno persi.
Esiste però il caso opposto, cioè quello in cui si presume che il record già presente contenga meno informazioni (o informazioni meno aggiornate) rispetto a quelle che si sta tentando di inserire. In tal caso è ragionevole chiedere a MySQL si inserire sempre e comunque il nuovo record, sostituendo quello che eventualmente potrebbe essere già presente. Per fare questo si utilizza l’istruzione REPLACE. Essa è una combinazione di DELETE + INSERT. Questo implica alcune conseguenze non del tutto ovvie. La prima riguarda i permessi: per eseguire REPLACE bisogna avere i permessi di DELETE e INSERT. Inoltre, se si esegue REPLACE su una tabella InnoDB (o su altre tabelle che supportano le Chiavi Esterne) prima di inserire il nuovo record si cancellerà il vecchio, scatenando l’evento ad esso associato (come ad esempio ON DELETE SET NULL). Infine, se sulla tabella sulla quale si esegue una REPLACE esiste un trigger associato all’evento DELETE, questo verrà lanciato.
Esempi:
Si noti, infine, che REPLACE potrebbe eliminare più di un record prima di inserire il nuovo. Infatti, come si è detto, una tabella può avere più chiavi uniche (PRIMARY KEY e campi UNIQUE) e naturalmente un record potrebbe contenere diversi valori duplicati. Dopo aver lanciato questa istruzione, il numero di affected_rows() restituito corrisponde ad _1 (il nuovo record inserito) più il numero di record eliminati. Sapere questo ci permette di determinare facilmente se uno o più record sono stati eliminati e quanti:
1$deleted_rows = mysqli_affected_rows($con) – 1;
INSERT … ON DUPLICATE KEY UPDATE
Vi è poi un ultimo caso, che è una via di mezzo tra i due precedenti. Può darsi che si voglia inserire un record nel caso esso non sia già esistente, ma che se esso esiste non vogliamo perdere né alcune informazioni già scritte in tabella, né alcune informazioni presenti nell’istruzione. In tal caso si utilizzerà INSERT con la clausola ON DUPLICATE KEY UPDATE.
Vediamo subito un esempio, nel quale tentiamo di inserire un nuovo utente; se esso esiste, vengono modificati il suo indirizzo email e l’url del suo blog, lasciando però inalterate tutte le altre informazioni.
INSERT … ON DUPLICATE KEY UPDATE non può essere utilizzata con DELAYED, sebbene ciò non generi alcun errore. E’ possibile inserire diversi record come si fa talvolta con le INSERT anche utilizzando la clausola ON DUPLICATE KEY UPDATE. Il numero di affected_rows() equivale al numero di record inseriti + (il numero di record modificati * 2). Questo implica che non è possibile sapere con esattezza quanti record sono stati aggiunti e quanti sono stati modificati basandosi esclusivamente su affected_rows().
Condividi su:
Esprimi un voto:
Argomenti chiave:
MySQL Report un tool di shell per tenere tutto sotto controllo