sql - Mehrere Updates in MySQL

Translate

Ich weiß, dass Sie mehrere Zeilen gleichzeitig einfügen können. Gibt es eine Möglichkeit, mehrere Zeilen gleichzeitig (wie in einer Abfrage) in MySQL zu aktualisieren?

Bearbeiten: Zum Beispiel habe ich folgendes

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

Ich möchte alle folgenden Updates in einer Abfrage zusammenfassen

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."

Alle Antworten

Translate

Ja, das ist möglich - Sie können INSERT ... ON DUPLICATE KEY UPDATE verwenden.

Anhand Ihres Beispiels:

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

Da Sie dynamische Werte haben, müssen Sie eine IF oder CASE verwenden, damit die Spalten aktualisiert werden. Es wird ein bisschen hässlich, aber es sollte funktionieren.

Anhand Ihres Beispiels können Sie Folgendes tun:

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

Die Frage ist alt, aber ich möchte das Thema mit einer anderen Antwort erweitern.

Mein Punkt ist, der einfachste Weg, dies zu erreichen, besteht darin, mehrere Abfragen mit einer Transaktion zu versehen. Die akzeptierte AntwortINSERT ... ON DUPLICATE KEY UPDATEist ein netter Hack, aber man sollte sich seiner Nachteile und Einschränkungen bewusst sein:

  • Wie gesagt, wenn Sie die Abfrage mit Zeilen starten, deren Primärschlüssel nicht in der Tabelle vorhanden sind, fügt die Abfrage neue "halbgebackene" Datensätze ein. Wahrscheinlich ist es nicht das, was du willst
  • Wenn Sie eine Tabelle mit einem Feld ungleich Null ohne Standardwert haben und dieses Feld in der Abfrage nicht berühren möchten, erhalten Sie"Field 'fieldname' doesn't have a default value"MySQL-Warnung, auch wenn Sie überhaupt keine einzelne Zeile einfügen. Es wird Sie in Schwierigkeiten bringen, wenn Sie sich dazu entschließen, streng zu sein und MySQL-Warnungen in Laufzeitausnahmen in Ihrer App umzuwandeln.

Ich habe einige Leistungstests für drei der vorgeschlagenen Varianten durchgeführt, einschließlich derINSERT ... ON DUPLICATE KEY UPDATEVariante, eine Variante mit "case / when / then" -Klausel und einem naiven Ansatz mit Transaktion. Möglicherweise erhalten Sie den Python-Code und die ErgebnisseHier. Die allgemeine Schlussfolgerung ist, dass die Variante mit case-Anweisung doppelt so schnell ist wie zwei andere Varianten, aber es ist ziemlich schwierig, korrekten und injektionssicheren Code dafür zu schreiben. Ich persönlich halte mich daher an den einfachsten Ansatz: die Verwendung von Transaktionen.

Bearbeiten:Ergebnisse vonDakusanbeweisen, dass meine Leistungsschätzungen nicht ganz gültig sind. Bitte sehen Siediese Antwortfür eine andere, ausführlichere Forschung.

Quelle
Translate

Nicht sicher, warum eine andere nützliche Option noch nicht erwähnt wird:

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;
Quelle
Translate

Für InnoDB gilt Folgendes.

Ich halte es für wichtig, die Geschwindigkeit der drei verschiedenen Methoden zu kennen.

Es gibt 3 Methoden:

  1. INSERT: INSERT mit ON DUPLICATE KEY UPDATE
  2. TRANSAKTION: Hier führen Sie eine Aktualisierung für jeden Datensatz innerhalb einer Transaktion durch
  3. FALL: In dem Sie einen Fall / wann für jeden unterschiedlichen Datensatz innerhalb eines UPDATE

Ich habe das gerade getestet und die INSERT-Methode war6,7xschneller für mich als die TRANSACTION-Methode. Ich habe sowohl 3.000 als auch 30.000 Zeilen ausprobiert.

Die TRANSACTION-Methode muss weiterhin jede einzelne Abfrage ausführen, was einige Zeit in Anspruch nimmt, obwohl sie die Ergebnisse während der Ausführung im Speicher oder so stapelt. Die TRANSACTION-Methode ist sowohl in Replikations- als auch in Abfrageprotokollen ziemlich teuer.

Noch schlimmer war die CASE-Methode41,1xlangsamer als die INSERT-Methode mit 30.000 Datensätzen (6,1-mal langsamer als TRANSACTION). Und75xlangsamer in MyISAM. Die Methoden INSERT und CASE haben bei ~ 1.000 Datensätzen die Gewinnschwelle überschritten. Selbst bei 100 Datensätzen ist die CASE-Methode kaum schneller.

Im Allgemeinen halte ich die INSERT-Methode für die beste und einfachste Anwendung. Die Abfragen sind kleiner und leichter zu lesen und nehmen nur eine Aktionsabfrage auf. Dies gilt sowohl für InnoDB als auch für MyISAM.

Bonusmaterial:

Die Lösung für das Problem des INSERT-Nicht-Standardfelds besteht darin, die relevanten SQL-Modi vorübergehend auszuschalten:SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TA‌​BLES",""),"STRICT_AL‌​L_TABLES",""). Stellen Sie sicher, dass Sie die speichernsql_modezuerst, wenn Sie vorhaben, es zurückzusetzen.

Was andere Kommentare betrifft, die besagen, dass das auto_increment mit der INSERT-Methode hochgefahren wird, habe ich dies ebenfalls getestet und es scheint nicht der Fall zu sein.

Der Code zum Ausführen der Tests lautet wie folgt. Es gibt auch .SQL-Dateien aus, um den Overhead des PHP-Interpreters zu beseitigen

<?
//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).';');
}
Quelle
Translate
UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'

Das sollte für dich funktionieren.

Es gibt eine Referenz indas MySQL-Handbuchfür mehrere Tabellen.

Quelle
Translate

Verwenden Sie eine temporäre Tabelle

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

Sie können dieselbe Tabelle als Alias verwenden, um die IDs anzugeben, nach denen Sie einfügen möchten (wenn Sie eine zeilenweise Aktualisierung durchführen:

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

Darüber hinaus sollte es offensichtlich sein, dass Sie auch von anderen Tabellen aus aktualisieren können. In diesem Fall wird das Update auch als "SELECT" -Anweisung verwendet, sodass Sie die Daten aus der von Ihnen angegebenen Tabelle erhalten. Sie geben in Ihrer Abfrage explizit die Aktualisierungswerte an, sodass die zweite Tabelle nicht betroffen ist.

Quelle
Translate

Warum erwähnt niemandmehrere Anweisungen in einer Abfrage?

In PHP verwenden Siemulti_queryMethode der MySQL-Instanz.

Von demPHP-Handbuch

MySQL erlaubt optional mehrere Anweisungen in einer Anweisungszeichenfolge. Das gleichzeitige Senden mehrerer Anweisungen reduziert Client-Server-Roundtrips, erfordert jedoch eine spezielle Behandlung.

Hier ist das Ergebnis im Vergleich zu anderen 3 Methoden in Update 30.000 Raw. Code kann gefunden werdenHierDas basiert auf der Antwort von @Dakusan

Transaktion: 5.5194580554962
Einfügen: 0.20669293403625
Rechtssache: 16.474853992462
Multi: 0,0412278175354

Wie Sie sehen können, ist die Abfrage mehrerer Anweisungen effizienter als die höchste Antwort.

Wenn Sie folgende Fehlermeldung erhalten:

PHP Warning:  Error while sending SET_OPTION packet

Möglicherweise müssen Sie die erhöhenmax_allowed_packetin der MySQL-Konfigurationsdatei, die sich auf meinem Computer befindet/etc/mysql/my.cnfund starten Sie dann mysqld neu.

Quelle
Translate

Möglicherweise möchten Sie auch Joins für Updates verwenden, was ebenfalls möglich ist.

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.

Bearbeiten: Wenn die Werte, die Sie aktualisieren, nicht von einer anderen Stelle in der Datenbank stammen, müssen Sie mehrere Aktualisierungsabfragen ausführen.

Quelle
Translate

Sie können eine Einstellung namens "Multi-Anweisung" ändern, die den implementierten "Sicherheitsmechanismus" von MySQL deaktiviert, um (mehr als einen) Injektionsbefehl zu verhindern. Typisch für die "brillante" Implementierung von MySQL, verhindert es auch, dass Benutzer effiziente Abfragen durchführen.

Hier (http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html) enthält einige Informationen zur C-Implementierung der Einstellung.

Wenn Sie PHP verwenden, können Sie mit mysqli mehrere Anweisungen ausführen (ich glaube, php wird bereits seit einiger Zeit mit mysqli ausgeliefert).

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

Hoffentlich hilft das.

Quelle
Translate

verwenden

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

Bitte beachten Sie:

  • id muss ein eindeutiger Primärschlüssel sein
  • Wenn Sie Fremdschlüssel verwenden, um auf die Tabelle zu verweisen, löscht REPLACE und fügt dann ein, sodass dies zu einem Fehler führen kann
Quelle
Translate

Im Folgenden werden alle Zeilen in einer Tabelle aktualisiert

Update Table Set
Column1 = 'New Value'

Im nächsten Schritt werden alle Zeilen aktualisiert, in denen der Wert von Spalte 2 mehr als 5 beträgt

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

Da ist allesUnkwntechBeispiel für die Aktualisierung mehrerer Tabellen

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

Ja. Dies ist mit der SQL-Anweisung INSERT ON DUPLICATE KEY UPDATE möglich. Syntax: INSERT INTO Tabellenname (a, b, c) VALUES (1,2,3), (4,5,6) ON DUPLICATE KEY UPDATE a = WERTE (a), b = WERTE (b), c = WERTE (c)

Quelle
Translate

Mit PHP habe ich das gemacht. Verwenden Sie ein Semikolon, teilen Sie es in ein Array auf und senden Sie es dann über eine Schleife.

$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();
Quelle
Translate
UPDATE tableName SET col1='000' WHERE id='3' OR id='5'

Dies sollte das erreichen, wonach Sie suchen. Fügen Sie einfach weitere IDs hinzu. Ich habe es getestet.

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

// Du baust es einfach in PHP wie

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

So können Sie die Lochtabelle mit einer Abfrage aktualisieren

Quelle