Mehrfache Aktualisierungen in MySQL

Question

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

Bearbeiten: Ich habe zum Beispiel 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 Aktualisierungen in einer Abfrage kombinieren

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;

Answer 1

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

Das sollte für Sie funktionieren.

Es gibt einen Hinweis in das MySQL-Handbuch für mehrere Tabellen.

Answer 2

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

Answer 3

Warum erwähnt niemand mehrere Anweisungen in einer Abfrage ?

In php verwenden Sie multi_query Methode der mysqli-Instanz.

Von der php-Handbuch

MySQL erlaubt es optional, mehrere Anweisungen in einer Anweisungsfolge zu haben. Das gleichzeitige Senden mehrerer Anweisungen verringert die Anzahl der Client-Server-Roundtrips, erfordert aber eine besondere Handhabung.

Hier ist das Ergebnis im Vergleich zu den anderen 3 Methoden in Update 30.000 raw. Der Code kann gefunden werden aquí die auf der Antwort von @Dakusan basiert

Transaktion: 5.5194580554962
Einsatz: 0,20669293403625
Gehäuse: 16.474853992462
Multi: 0,0412278175354

Wie Sie sehen können, ist eine Abfrage mit mehreren Aussagen effizienter als die höchste Antwort.

Wenn Sie eine Fehlermeldung wie diese erhalten:

PHP Warning:  Error while sending SET_OPTION packet

Möglicherweise müssen Sie die max_allowed_packet in der mysql-Konfigurationsdatei, die auf meinem Rechner lautet /etc/mysql/my.cnf und starten Sie dann mysqld neu.

Answer 4

Es gibt eine Einstellung, die Sie ändern können und die "Multi-Statement" genannt wird und die den "Sicherheitsmechanismus" von MySQL deaktiviert, der implementiert wurde, um (mehr als einen) Injektionsbefehl zu verhindern. Typisch für die "geniale" Implementierung von MySQL, verhindert sie auch, dass der Benutzer effiziente Abfragen durchführen kann.

Hier ( http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html ) finden Sie einige Informationen über die C-Implementierung der Einstellung.

Wenn Sie PHP verwenden, können Sie mysqli verwenden, um Multi-Statements zu tun (ich glaube, php hat mit mysqli für eine Weile jetzt 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();

Ich hoffe, das hilft.

Answer 5

Sie können der gleichen Tabelle einen Alias zuweisen, um die IDs zu erhalten, die Sie einfügen möchten (wenn Sie eine zeilenweise Aktualisierung vornehmen):

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

Außerdem sollte es offensichtlich sein, dass man auch aus anderen Tabellen aktualisieren kann. In diesem Fall fungiert die Aktualisierung als SELECT-Anweisung, die Ihnen die Daten aus der von Ihnen angegebenen Tabelle liefert. Sie geben in Ihrer Abfrage ausdrücklich die Aktualisierungswerte an, so dass die zweite Tabelle davon nicht betroffen ist.