Aggiornamenti multipli in MySQL

So che è ansible inserire più righe contemporaneamente, esiste un modo per aggiornare più righe contemporaneamente (come in una query) in MySQL?

Modifica: Ad esempio, ho il seguente

Name id Col1 Col2 Row1 1 6 1 Row2 2 2 3 Row3 3 9 5 Row4 4 16 8 

Voglio combinare tutti i seguenti aggiornamenti in un’unica query

 UPDATE table SET Col1 = 1 WHERE id = 1; UPDATE table SET Col1 = 2 WHERE id = 2; UPDATE table SET Col2 = 3 WHERE id = 3; UPDATE table SET Col1 = 10 WHERE id = 4; UPDATE table SET Col2 = 12 WHERE id = 4; 

Sì, è ansible – puoi usare INSERIRE … ON DUPLICATE KEY UPDATE.

Usando il tuo esempio:

 INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12) ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2); 

Poiché hai valori dinamici, devi utilizzare un IF o CASE per aggiornare le colonne. Diventa piuttosto brutto, ma dovrebbe funzionare.

Usando il tuo esempio, potresti farlo come:

 Tabella UPDATE SET Col1 = ID CASE 
                           QUANDO 1 ALLORA 1 
                           QUANDO 2 ALLORA 2 
                           QUANDO 4 ALLORA 10 
                           ELSE Col1 
                         FINE, 
                  Col2 = ID CASE 
                           QUANDO 3 ANCORA 3 
                           QUANDO 4 ALLORA 12 
                           ELSE Col2 
                         FINE
              DOVE id IN (1, 2, 3, 4);

La domanda è vecchia, ma mi piacerebbe estendere l’argomento con un’altra risposta.

Il punto è che il modo più semplice per ottenerlo è racchiudere più query con una transazione. La risposta accettata INSERT ... ON DUPLICATE KEY UPDATE è un bel trucco, ma bisogna essere consapevoli dei suoi limiti e limitazioni:

  • Come già detto, se ti capita di avviare la query con righe le cui chiavi primarie non esistono nella tabella, la query inserisce nuovi record “infornati”. Probabilmente non è quello che vuoi
  • Se hai una tabella con un campo non nullo senza valore predefinito e non vuoi toccare questo campo nella query, otterrai "Field 'fieldname' doesn't have a default value" MySQL warning anche se non lo fai ” t inserire una singola riga. Ti metterà nei guai se decidi di essere severo e triggers gli avvisi di mysql nelle eccezioni di runtime nella tua app.

Ho effettuato alcuni test delle prestazioni per tre delle varianti suggerite, inclusa la variante INSERT ... ON DUPLICATE KEY UPDATE , una variante con clausola “case / when / then” e un approccio ingenuo con la transazione. Puoi ottenere il codice Python e i risultati qui . La conclusione generale è che la variante con l’affermazione del caso risulta essere due volte più veloce di altre due varianti, ma è piuttosto difficile scrivere codice corretto e sicuro per l’iniezione, quindi mi attengo personalmente all’approccio più semplice: usare le transazioni.

Modifica: i risultati di Dakusan dimostrano che le mie stime delle prestazioni non sono abbastanza valide. Si prega di vedere questa risposta per un’altra ricerca più elaborata.

Non so perché un’altra opzione utile non sia ancora menzionata:

 UPDATE my_table m JOIN ( SELECT 1 as id, 10 as _col1, 20 as _col2 UNION ALL SELECT 2, 5, 10 UNION ALL SELECT 3, 15, 30 ) vals ON m.id = vals.id SET col1 = _col1, col2 = _col2; 

Tutto quanto segue si applica a InnoDB.

Sento che la velocità dei 3 diversi metodi è importante.

Ci sono 3 metodi:

  1. INSERT: INSERT con ON DUPLICATE KEY UPDATE
  2. TRANSAZIONE: dove si esegue un aggiornamento per ogni record all’interno di una transazione
  3. CASO: in cui si presenta un caso / quando per ogni record diverso all’interno di un UPDATE

L’ho appena testato e il metodo INSERT era 6,7 volte più veloce rispetto al metodo TRANSACTION. Ho provato su un set di entrambe le 3000 e 30.000 righe.

Il metodo TRANSACTION deve ancora eseguire ogni query individuale, che richiede tempo, anche se esegue il batch dei risultati in memoria o qualcosa del genere durante l’esecuzione. Il metodo TRANSACTION è anche piuttosto costoso nei registri di replica e di query.

Ancor peggio, il metodo CASE era 41.1x più lento del metodo INSERT con 30.000 record (6.1x più lento di TRANSACTION). E 75 volte più lento in MyISAM. I metodi INSERT e CASE hanno rotto anche a ~ 1.000 record. Anche a 100 record, il metodo CASE è BARELY più veloce.

Quindi, in generale, ritengo che il metodo INSERT sia il migliore e il più semplice da usare. Le query sono più piccole e più facili da leggere e occupano solo 1 query di azione. Questo vale sia per InnoDB che per MyISAM.

Bonus:

La soluzione per il problema del campo non predefinito INSERT consiste nel distriggersre temporaneamente le modalità SQL pertinenti: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TA‌​BLES",""),"STRICT_AL‌​L_TABLES","") . Assicurati di salvare prima la sql_mode se hai intenzione di ripristinarla.

Per quanto riguarda gli altri commenti che ho visto che dicono che auto_increment aumenta usando il metodo INSERT, l’ho testato anch’io e sembra non essere il caso.

Il codice per eseguire i test è il seguente. Emette anche i file .SQL per rimuovere l’overhead dell’interprete php

 \n"; file_put_contents("./$TestName.sql", implode(";\n", $TheQueries).';'); } 
 UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567' 

Questo dovrebbe funzionare per te.

C’è un riferimento nel manuale MySQL per più tabelle.

Usa una tabella temporanea

 // Reorder items function update_items_tempdb(&$items) { shuffle($items); $table_name = uniqid('tmp_test_'); $sql = "CREATE TEMPORARY TABLE `$table_name` (" ." `id` int(10) unsigned NOT NULL AUTO_INCREMENT" .", `position` int(10) unsigned NOT NULL" .", PRIMARY KEY (`id`)" .") ENGINE = MEMORY"; query($sql); $i = 0; $sql = ''; foreach ($items as &$item) { $item->position = $i++; $sql .= ($sql ? ', ' : '')."({$item->id}, {$item->position})"; } if ($sql) { query("INSERT INTO `$table_name` (id, position) VALUES $sql"); $sql = "UPDATE `test`, `$table_name` SET `test`.position = `$table_name`.position" ." WHERE `$table_name`.id = `test`.id"; query($sql); } query("DROP TABLE `$table_name`"); } 

C’è un’impostazione che puoi modificare chiamata ‘multi statement’ che disabilita il ‘meccanismo di sicurezza’ di MySQL implementato per prevenire (più di un) comando di iniezione. Tipicamente all’implementazione “geniale” di MySQL, impedisce anche all’utente di eseguire query efficienti.

Qui ( http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html ) sono alcune informazioni sull’implementazione C delle impostazioni.

Se stai usando PHP, puoi usare mysqli per fare multi statement (penso che php sia stato spedito con mysqli per un po ‘di tempo)

 $con = new mysqli('localhost','user1','password','my_database'); $query = "Update MyTable SET col1='some value' WHERE id=1 LIMIT 1;"; $query .= "UPDATE MyTable SET col1='other value' WHERE id=2 LIMIT 1;"; //etc $con->multi_query($query); $con->close(); 

Spero possa aiutare.

È ansible creare alias la stessa tabella per fornire gli ID che si desidera inserire (se si sta eseguendo un aggiornamento riga per riga:

 UPDATE table1 tab1, table1 tab2 -- alias references the same table SET col1 = 1 ,col2 = 2 . . . WHERE tab1.id = tab2.id; 

Inoltre, dovrebbe sembrare ovvio che puoi anche aggiornare da altri tavoli. In questo caso, l’aggiornamento raddoppia come un’istruzione “SELECT”, dandoti i dati dalla tabella che stai specificando. Stai esplicitamente affermando nella tua query che i valori di aggiornamento sono così, la seconda tabella non è influenzata.

Potresti anche essere interessato a utilizzare i join sugli aggiornamenti, il che è anche ansible.

 Update someTable Set someValue = 4 From someTable s Inner Join anotherTable a on s.id = a.id Where a.id = 4 -- Only updates someValue in someTable who has a foreign key on anotherTable with a value of 4. 

Modifica: se i valori che si stanno aggiornando non provengono da qualche altra parte nel database, sarà necessario emettere più query di aggiornamento.

Perché nessuno menziona più affermazioni in una query ?

In PHP, usi il metodo multi_query dell’istanza mysqli.

Dal manuale php

MySQL consente facoltativamente di avere più istruzioni in una stringa di istruzioni. L’invio di più istruzioni contemporaneamente riduce i round trip client-server ma richiede una gestione speciale.

Ecco il risultato confrontando altri 3 metodi nell’aggiornamento 30.000 raw. Il codice può essere trovato qui che si basa sulla risposta di @Dakusan

Transazione: 5.5194580554962
Inserire: 0.20669293403625
Caso: 16.474853992462
Multi: 0,0412278175354

Come puoi vedere, la query di più istruzioni è più efficiente della risposta più alta.

Se ricevi un messaggio di errore come questo:

 PHP Warning: Error while sending SET_OPTION packet 

Potrebbe essere necessario aumentare il max_allowed_packet nel file di configurazione mysql che nella mia macchina è /etc/mysql/my.cnf e quindi riavviare mysqld.

uso

 REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES (1,6,1),(2,2,3),(3,9,5),(4,16,8); 

Notare che:

  • id deve essere una chiave univoca primaria
  • se si utilizzano chiavi esterne per fare riferimento alla tabella, REPLACE cancella quindi inserisce, pertanto ciò potrebbe causare un errore

Quanto segue aggiornerà tutte le righe in una tabella

 Update Table Set Column1 = 'New Value' 

Il prossimo aggiornerà tutte le righe in cui il valore di Column2 è maggiore di 5

 Update Table Set Column1 = 'New Value' Where Column2 > 5 

C’è tutto l’esempio di Unkwntech di aggiornare più di una tabella

 UPDATE table1, table2 SET table1.col1 = 'value', table2.col1 = 'value' WHERE table1.col3 = '567' AND table2.col6='567' 

Sì, è ansible utilizzare INSERT ON DUPLICATE KEY UPDATE istruzione sql .. syntax: INSERT INTO table_name (a, b, c) VALORI (1,2,3), (4,5,6) SU DUPLICATE KEY UPDATE a = VALORI (a), b = VALORI (b), c = VALORI (c)

Con PHP l’ho fatto. Utilizza il punto e virgola, dividerlo in array e quindi inviare tramite loop.

 $con = new mysqli('localhost','user1','password','my_database'); $batchUpdate = true; /*You can choose between batch and single query */ $queryIn_arr = explode(";", $queryIn); if($batchUpdate) /* My SQL prevents multiple insert*/ { foreach($queryIn_arr as $qr) { if(strlen($qr)>3) { //echo '
Sending data to SQL1:
'.$qr.''; $result = $conn->query($qr); } } } else { $result = $conn->query($queryIn); } $con->close();
 UPDATE tableName SET col1='000' WHERE id='3' OR id='5' 

Questo dovrebbe ottenere ciò che stai cercando. Basta aggiungere altri ID. L’ho provato

 UPDATE `your_table` SET `something` = IF(`id`="1","new_value1",`something`), `smth2` = IF(`id`="1", "nv1",`smth2`), `something` = IF(`id`="2","new_value2",`something`), `smth2` = IF(`id`="2", "nv2",`smth2`), `something` = IF(`id`="4","new_value3",`something`), `smth2` = IF(`id`="4", "nv3",`smth2`), `something` = IF(`id`="6","new_value4",`something`), `smth2` = IF(`id`="6", "nv4",`smth2`), `something` = IF(`id`="3","new_value5",`something`), `smth2` = IF(`id`="3", "nv5",`smth2`), `something` = IF(`id`="5","new_value6",`something`), `smth2` = IF(`id`="5", "nv6",`smth2`) 

// Lo stai solo costruendo in php

 $q = 'UPDATE `your_table` SET '; foreach($data as $dat){ $q .= ' `something` = IF(`id`="'.$dat->id.'","'.$dat->value.'",`something`), `smth2` = IF(`id`="'.$dat->id.'", "'.$dat->value2.'",`smth2`),'; } $q = substr($q,0,-1); 

Quindi puoi aggiornare la tabella dei fori con una query