Quello di cui ho bisogno è di impostare i valori di tutti i campi di un record con un particolare tasto (il tasto è effettivamente composto), inserendo il record se non c’è ancora un record con tale chiave.
REPLACE sembra intenzionato a fare il lavoro, ma allo stesso tempo la sua pagina di manuale suggerisce INSERT ... ON DUPLICATE KEY UPDATE .
Quale di loro dovrei scegliere e perché?
L’unico “effetto collaterale” di REPLACE che mi viene in mente è che incrementerebbe i valori di autoincrement (fortunatamente non ne uso nessuno) mentre INSERT ... ON DUPLICATE KEY UPDATE probabilmente non lo farebbe. Quali sono le altre differenze pratiche da tenere a mente? In quali casi particolari si può REPLACE preferenza su INSERT ... ON DUPLICATE KEY UPDATE e viceversa?
REPLACE esegue internamente una cancellazione e quindi un inserimento. Ciò può causare problemi se si dispone di un vincolo di chiave esterna che punta a quella riga. In questa situazione il REPLACE potrebbe fallire o peggiorare: se la tua chiave esterna è impostata su Cascade Delete, il REPLACE provocherà l’eliminazione di righe da altre tabelle. Questo può accadere anche se il vincolo è stato soddisfatto sia prima che dopo l’operazione REPLACE .
L’utilizzo di INSERT ... ON DUPLICATE KEY UPDATE evita questo problema ed è quindi preferibile.
Per rispondere alla domanda in termini di prestazioni, ho fatto un test utilizzando entrambi i metodi
Sostituisci in coinvolge:
1.Prova l’inserto sul tavolo
2. Se 1 fallisce, cancella una riga e inserisci una nuova riga
Inserisci su Duplicate Key Update implica:
1.Prova inserire sul tavolo
2.Se 1 fallisce, aggiorna la riga
Se tutti i passaggi coinvolti sono inserimenti, non dovrebbero esserci differenze nelle prestazioni. La velocità deve dipendere dal numero di aggiornamenti coinvolti. Il caso peggiore è quando tutte le dichiarazioni sono aggiornamenti
Ho provato entrambe le affermazioni sulla mia tabella InnoDB coinvolgendo 62.510 voci (solo aggiornamenti). Alla velocità di campeggiare:
Sostituisci in: 77.411 secondi
Inserisci su Duplicate Key Update: 2.446 secondi
Insert on Duplicate Key update is almost 32 times faster.
Dimensioni tabella: 1.249.250 righe con 12 colonne su Amazon m3.medium
Quando si utilizza REPLACE invece di INSERT ... ON DUPLICATE KEY UPDATE , a volte osservo problemi di blocco della chiave o di deadlock quando più query arrivano rapidamente per una determinata chiave. L’atomicità di quest’ultimo (oltre a non causare eliminazioni a cascata) è una ragione in più per usarlo.
In quali casi particolari si può SOSTITUIRE la preferenza su INSERT … ON DUPLICATE KEY UPDATE e viceversa?
Ho appena scoperto che nel caso di tabelle con un motore di memorizzazione FEDERATED, INSERT...ON DUPLICATE KEY UPDATE istruzioni sono accettate, ma falliscono (con un errore 1022: imansible scrivere, duplicare la chiave nella tabella. ..) se si verifica una violazione della chiave duplicata, consultare il corrispondente punto elenco in questa pagina del Manuale di riferimento MySQL.
Fortunatamente, sono stato in grado di utilizzare REPLACE invece di INSERT...ON DUPLICATE KEY UPDATE all’interno del mio trigger dopo insert per ottenere il risultato desiderato di replicare le modifiche a una tabella FEDERATED.
Sostituisci sembra che faccia due operazioni nel caso in cui la chiave esista già. Forse questo implica che c’è una differenza di velocità tra i due?
(INSERIRE) un aggiornamento rispetto a uno elimina + un inserto (REPLACE)
EDIT: La mia implicazione che la sostituzione potrebbe essere più lenta è in realtà completamente sbagliata. Bene, secondo questo post del blog comunque … http://www.tokutek.com/2010/07/why-insert-on-duplicate-key-update-may-be-slow-by-incurring-disk-seeks /
Se non si elencano tutte le colonne, penso che REPLACE reimposterà tutte le colonne non menzionate con i loro valori predefiniti nelle righe sostituite. ON DUPLICATE KEY UPDATE lascerà invariate le colonne non citate.
“È ansible che nel caso di un errore di chiave duplicata, un motore di archiviazione possa eseguire il REPLACE come un aggiornamento piuttosto che un delete plus insert, ma la semantica è la stessa.”