sql - Mises à jour multiples dans MySQL

Translate

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, dans une requête) dans MySQL?

Edit: Par exemple, j'ai ce qui suit

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;
This question and all comments follow the "Attribution Required."

Toutes les réponses

Translate

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);
La source
Translate

Comme vous avez des valeurs dynamiques, vous devez utiliser un IF ou un CASE pour les colonnes à mettre à jour. Ça devient un peu moche, mais ça devrait marcher.

En utilisant votre exemple, vous pouvez le faire comme:

UPDATE table SET Col1 = CASE id 
                          WHEN 1 THEN 1 
                          WHEN 2 THEN 2 
                          WHEN 4 THEN 10 
                          ELSE Col1 
                        END, 
                 Col2 = CASE id 
                          WHEN 3 THEN 3 
                          WHEN 4 THEN 12 
                          ELSE Col2 
                        END
             WHERE id IN (1, 2, 3, 4);
La source
Translate

La question est ancienne, mais j'aimerais étendre le sujet avec une autre réponse.

Mon point est que le moyen le plus simple d'y parvenir est simplement d'encapsuler plusieurs requêtes avec une transaction. La réponse acceptéeINSERT ... ON DUPLICATE KEY UPDATEest un bon hack, mais il faut être conscient de ses inconvénients et limites:

  • Comme cela a été dit, 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 "semi-cuits". Ce n'est probablement pas ce que tu veux
  • 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 n'insérez pas du tout une seule ligne. Cela vous posera des problèmes si vous décidez d'être strict et de transformer les avertissements mysql en exceptions d'exécution dans votre application.

J'ai fait quelques tests de performance pour trois des variantes suggérées, y compris leINSERT ... ON DUPLICATE KEY UPDATEvariante, une variante avec clause "case / when / then" et une approche naïve avec transaction. Vous pouvez obtenir le code et les résultats Pythonici. La conclusion générale est que la variante avec instruction case s'avère être deux fois plus rapide que deux autres variantes, mais il est assez difficile d'écrire un code correct et sûr pour l'injection, donc je m'en tiens personnellement à l'approche la plus simple: utiliser des transactions.

Éditer:Conclusions deDakusanprouve que mes estimations de performances ne sont pas tout à fait valables. S'il te plait regardecette réponsepour une autre recherche plus élaborée.

La source
Translate

Je ne sais pas 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;
La source
Translate

Tout ce qui suit s'applique à InnoDB.

Je pense qu'il est important de connaître les vitesses des 3 méthodes différentes.

Il existe 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 un cas / quand pour chaque enregistrement différent dans une MISE À JOUR

Je viens de tester ceci, et la méthode INSERT était6,7xplus rapide pour moi que la méthode TRANSACTION. J'ai essayé sur 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, même si elle regroupe les résultats en mémoire, ou quelque chose, lors de l'exécution. La méthode TRANSACTION est également assez coûteuse dans les journaux de réplication et de requête.

Pire encore, la méthode CASE était41,1xplus lent que la méthode INSERT avec 30 000 enregistrements (6,1 fois plus lent que TRANSACTION). Et75xplus lent dans MyISAM. Les méthodes INSERT et CASE ont battu même à ~ 1 000 enregistrements. Même à 100 enregistrements, la méthode CASE est à peine plus rapide.

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

Bonus:

La solution au problème INSERT de champ non par défaut est de 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 lesql_moded'abord si vous prévoyez de le rétablir.

En ce qui concerne les autres commentaires, j'ai vu que l'auto_increment monte en utilisant la méthode INSERT, j'ai testé cela aussi et cela ne semble pas être le cas.

Le code pour exécuter les tests est le suivant. Il génère également des fichiers .SQL pour supprimer la surcharge de l'interpréteur 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)."<br>\n";

    file_put_contents("./$TestName.sql", implode(";\n", $TheQueries).';');
}
La source
Translate
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 dansle manuel MySQLpour plusieurs tables.

La source
Translate

Utiliser 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`");
}
La source
Translate

Vous pouvez alias la même table pour vous donner les identifiants que 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;

De plus, il devrait sembler évident que vous pouvez également mettre à jour à partir 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 indiquez explicitement dans votre requête les valeurs de mise à jour, la deuxième table n'est donc pas affectée.

La source
Translate

Pourquoi personne ne mentionneplusieurs instructions dans une seule requête?

En php, vous utilisezmulti_queryméthode de l'instance mysqli.

Dumanuel php

MySQL permet éventuellement d'avoir plusieurs instructions dans une chaîne d'instructions. L'envoi simultané de plusieurs instructions 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 la mise à jour 30 000 raw. Le code peut être trouvéiciqui est basé sur la réponse de @Dakusan

Transaction: 5.5194580554962
Insérer: 0.20669293403625
Cas: 16.474853992462
Multi: 0,0412278175354

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

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

PHP Warning:  Error while sending SET_OPTION packet

Vous devrez peut-être augmenter lemax_allowed_packetdans le fichier de configuration mysql qui dans ma machine est/etc/mysql/my.cnfpuis redémarrez mysqld.

La source
Translate

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 base de données, vous devrez émettre plusieurs requêtes de mise à jour.

La source
Translate

Il existe un paramètre que vous pouvez modifier appelé «instruction multiple» qui désactive le «mécanisme de sécurité» de MySQL implémenté pour empêcher (plus d'une) commande d'injection. Typique de l'implémentation «brillante» de MySQL, elle empêche également l'utilisateur d'effectuer des requêtes efficaces.

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

Si vous utilisez PHP, vous pouvez utiliser mysqli pour faire plusieurs déclarations (je pense que php est livré avec mysqli depuis un certain temps maintenant)

$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 ça t'as aidé.

La source
Translate

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é primaire unique
  • si vous utilisez des clés étrangères pour référencer la table, REPLACE supprime puis insère, donc cela peut provoquer une erreur
La source
Translate

Ce qui suit mettra à jour toutes les lignes dans une table

Update Table Set
Column1 = 'New Value'

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

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

Il y a toutUnkwntechexemple de mise à jour de plusieurs tables

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

Oui .. cela est possible en utilisant l'instruction SQL INSERT ON DUPLICATE KEY UPDATE .. syntaxe: INSERT INTO nom_table (a, b, c) VALUES (1,2,3), (4,5,6) ON DUPLICATE KEY UPDATE a = VALEURS (a), b = VALEURS (b), c = VALEURS (c)

La source
Translate

Avec PHP, je l'ai fait. Utilisez un point-virgule, divisez-le en tableau, puis soumettez-le via une 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 '<br>Sending data to SQL1:<br>'.$qr.'</br>';
            $result = $conn->query($qr);
        }

    }
}
else
{
    $result = $conn->query($queryIn);
}
$con->close();
La source
Translate
UPDATE tableName SET col1='000' WHERE id='3' OR id='5'

Cela devrait réaliser ce que vous recherchez. Ajoutez simplement d'autres identifiants. Je l'ai testé.

La source
Translate
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 des trous avec une seule requête

La source