Mises à jour multiples dans MySQL

Je sais que vous pouvez insérer plusieurs lignes à la fois. Existe-t-il un moyen de mettre à jour plusieurs lignes à la fois (comme dans une requête) dans MySQL?

Edit: Par exemple, j’ai le suivant

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

Je souhaite combiner toutes les mises à jour suivantes en une seule requête

 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; 

Oui, c’est possible – vous pouvez utiliser INSERT … ON DUPLICATE KEY UPDATE.

En utilisant votre exemple:

 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); 

Comme vous avez des valeurs dynamics, vous devez utiliser un IF ou un CASE pour que les colonnes soient mises à jour. Ça devient moche, mais ça devrait marcher.

En utilisant votre exemple, vous pouvez le faire comme:

 Table UPDATE SET Col1 = CASE id 
                           QUAND 1 ALORS 1 
                           QUAND 2 ALORS 2 
                           QUAND 4 ALORS 10 
                           ELSE Col1 
                         FIN, 
                  Col2 = CASE id 
                           QUAND 3 ALORS 3 
                           QUAND 4 ALORS 12 
                           ELSE Col2 
                         FIN
              WHERE id IN (1, 2, 3, 4);

La question est ancienne, mais je voudrais élargir le sujet avec une autre réponse.

Ce que je veux dire, le moyen le plus simple d’y parvenir est de regrouper plusieurs requêtes dans une transaction. La réponse acceptée INSERT ... ON DUPLICATE KEY UPDATE est un bon hack, mais il faut savoir en connaître les inconvénients et les limites:

  • Comme indiqué précédemment, si vous lancez la requête avec des lignes dont les clés primaires n’existent pas dans la table, la requête insère de nouveaux enregistrements “à moitié faits”. Ce n’est probablement pas ce que vous voulez
  • Si vous avez une table avec un champ non nul sans valeur par défaut et que vous ne voulez pas toucher ce champ dans la requête, vous obtiendrez "Field 'fieldname' doesn't have a default value" Avertissement MySQL même si vous ne le faites pas ” t insérer une seule ligne du tout. Cela vous posera des problèmes si vous décidez d’être ssortingct et de transformer les avertissements mysql en exceptions d’exécution dans votre application.

J’ai effectué des tests de performance pour trois des variantes suggérées, y compris la variante INSERT ... ON DUPLICATE KEY UPDATE , une variante avec la clause “case / when / then” et une approche naïve avec la transaction. Vous pouvez obtenir le code python et les résultats ici . La conclusion générale est que la variante avec la déclaration de cas s’avère être deux fois plus rapide que deux autres variantes, mais il est assez difficile d’écrire du code correct et sûr, donc je m’en tiens à l’approche la plus simple: utiliser des transactions.

Edit: Les résultats de Dakusan prouvent que mes estimations de performance ne sont pas tout à fait valables. S’il vous plaît voir cette réponse pour une autre recherche plus élaborée.

Pas sûr pourquoi une autre option utile n’est pas encore mentionnée:

 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; 

Tout ce qui suit s’applique à InnoDB.

Je pense que connaître les vitesses des 3 différentes méthodes est important.

Il y a 3 méthodes:

  1. INSERT: INSERT avec ON DUPLICATE KEY UPDATE
  2. TRANSACTION: Où vous effectuez une mise à jour pour chaque enregistrement dans une transaction
  3. CAS: Dans lequel vous avez un cas / quand pour chaque enregistrement différent dans une mise à jour

Je viens de tester cela, et la méthode INSERT était 6,7 fois plus rapide que la méthode TRANSACTION. J’ai essayé un ensemble de 3 000 et 30 000 lignes.

La méthode TRANSACTION doit toujours exécuter chaque requête individuellement, ce qui prend du temps, bien qu’elle charge les résultats en mémoire ou quelque chose pendant l’exécution. La méthode TRANSACTION est également très coûteuse dans les journaux de réplication et de requête.

Pire encore, la méthode CASE était 41.1x plus lente que la méthode INSERT avec 30.000 enregistrements (6.1x plus lent que TRANSACTION). Et 75x plus lent dans MyISAM. Les méthodes INSERT et CASE ont atteint le seuil de 1 000 enregistrements. Même à 100 enregistrements, la méthode CASE est nettement plus rapide.

Donc, en général, je pense que la méthode INSERT est à la fois la meilleure et la plus facile à utiliser. Les requêtes sont plus petites et plus faciles à lire et ne requièrent qu’une seule requête d’action. Cela s’applique à la fois à InnoDB et à MyISAM.

Bonus:

La solution pour le problème INSERT non-default-field consiste à désactiver temporairement les modes SQL appropriés: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TA‌​BLES",""),"STRICT_AL‌​L_TABLES","") . Assurez-vous de sauvegarder le sql_mode premier si vous prévoyez de le restaurer.

En ce qui concerne les autres commentaires que j’ai vus qui disent que auto_increment monte en utilisant la méthode INSERT, je l’ai aussi testé et il semble que ce ne soit pas le cas.

Le code pour exécuter les tests est le suivant. Il produit également des fichiers .SQL pour supprimer le préfixe d’interprète php

 < ? //Variables $NumRows=30000; //These 2 functions need to be filled in function InitSQL() { } function RunSQLQuery($Q) { } //Run the 3 tests InitSQL(); for($i=0;$i<3;$i++) RunTest($i, $NumRows); function RunTest($TestNum, $NumRows) { $TheQueries=Array(); $DoQuery=function($Query) use (&$TheQueries) { RunSQLQuery($Query); $TheQueries[]=$Query; }; $TableName='Test'; $DoQuery('DROP TABLE IF EXISTS '.$TableName); $DoQuery('CREATE TABLE '.$TableName.' (i1 int NOT NULL AUTO_INCREMENT, i2 int NOT NULL, primary key (i1)) ENGINE=InnoDB'); $DoQuery('INSERT INTO '.$TableName.' (i2) VALUES ('.implode('), (', range(2, $NumRows+1)).')'); if($TestNum==0) { $TestName='Transaction'; $Start=microtime(true); $DoQuery('START TRANSACTION'); for($i=1;$i<=$NumRows;$i++) $DoQuery('UPDATE '.$TableName.' SET i2='.(($i+5)*1000).' WHERE i1='.$i); $DoQuery('COMMIT'); } if($TestNum==1) { $TestName='Insert'; $Query=Array(); for($i=1;$i<=$NumRows;$i++) $Query[]=sprintf("(%d,%d)", $i, (($i+5)*1000)); $Start=microtime(true); $DoQuery('INSERT INTO '.$TableName.' VALUES '.implode(', ', $Query).' ON DUPLICATE KEY UPDATE i2=VALUES(i2)'); } if($TestNum==2) { $TestName='Case'; $Query=Array(); for($i=1;$i<=$NumRows;$i++) $Query[]=sprintf('WHEN %d THEN %d', $i, (($i+5)*1000)); $Start=microtime(true); $DoQuery("UPDATE $TableName SET i2=CASE i1\n".implode("\n", $Query)."\nEND\nWHERE i1 IN (".implode(',', range(1, $NumRows)).')'); } print "$TestName: ".(microtime(true)-$Start)."
\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' 

Cela devrait fonctionner pour toi.

Il y a une référence dans le manuel MySQL pour plusieurs tables.

Utilisez une table temporaire

 // 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`"); } 

Il y a un paramètre que vous pouvez modifier appelé «multi-instruction» qui désactive le «mécanisme de sécurité» de MySQL mis en œuvre pour empêcher (plusieurs) la commande d’injection. Typique de l’implémentation «shinye» de MySQL, elle empêche également l’utilisateur d’effectuer des requêtes efficaces.

Voici ( http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html ) quelques informations sur l’implémentation C du paramètre.

Si vous utilisez PHP, vous pouvez utiliser mysqli pour faire des instructions multiples (je pense que php est livré avec mysqli depuis un moment)

 $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(); 

J’espère que cela pourra aider.

Vous pouvez alias la même table pour vous donner l’ID par lequel vous souhaitez insérer (si vous effectuez une mise à jour ligne par ligne:

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

En outre, il devrait sembler évident que vous pouvez également mettre à jour d’autres tables. Dans ce cas, la mise à jour se double d’une instruction “SELECT”, vous donnant les données de la table que vous spécifiez. Vous déclarez explicitement dans votre requête les valeurs de mise à jour, la seconde table n’est donc pas affectée.

Vous pouvez également être intéressé par l’utilisation de jointures sur les mises à jour, ce qui est également possible.

 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. 

Modifier: Si les valeurs que vous mettez à jour ne proviennent pas d’autre part de la firebase database, vous devrez émettre plusieurs requêtes de mise à jour.

Pourquoi personne ne mentionne plusieurs déclarations dans une même requête ?

En php, vous utilisez la méthode multi_query de l’instance mysqli.

A partir du manuel php

MySQL permet éventuellement d’avoir plusieurs instructions dans une chaîne d’instructions. L’envoi simultané de plusieurs relevés réduit les allers-retours client-serveur, mais nécessite un traitement spécial.

Voici le résultat comparé aux 3 autres méthodes de mise à jour 30 000 brutes. Le code peut être trouvé ici qui est basé sur la réponse de @Dakusan

Transaction: 5.5194580554962
Insérer: 0.20669293403625
Affaire: 16.474853992462
Multi: 0.0412278175354

Comme vous pouvez le voir, la requête de plusieurs instructions est plus efficace que la plus haute réponse.

Si vous obtenez un message d’erreur comme celui-ci:

 PHP Warning: Error while sending SET_OPTION packet 

Vous devrez peut-être augmenter le max_allowed_packet dans le fichier de configuration mysql qui, sur ma machine, est /etc/mysql/my.cnf , puis redémarrer mysqld.

utilisation

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

Notez s’il vous plaît:

  • id doit être une clé unique primaire
  • si vous utilisez des clés étrangères pour référencer la table, REPLACE supprime puis insère, cela peut provoquer une erreur

Ce qui suit mettra à jour toutes les lignes d’une table

 Update Table Set Column1 = 'New Value' 

Le suivant mettra à jour toutes les lignes où la valeur de Column2 est supérieure à 5

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

Il y a tous les exemples d’ Unkwntech de mettre à jour plus d’une table

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

Oui. Il est possible d’utiliser l’instruction INSERT ON DUPLICATE KEY UPDATE SQL. Syntaxe: INSERT INTO nom_table (a, b, c) VALUES (1,2,3), (4,5,6) ON DUPLICATE MISE À JOUR DE LA CLÉ a = VALUES (a), b = VALUES (b), c = VALUES (c)

Avec PHP, je l’ai fait. Utilisez le point-virgule, divisez-le en tableau et soumettez-le via la boucle.

 $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' 

Cela devrait atteindre ce que vous recherchez. Ajoutez simplement d’autres identifiants. Je l’ai testé

 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`) 

// Vous venez de le construire en PHP comme

 $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); 

Ainsi, vous pouvez mettre à jour la table de trous avec une seule requête