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

Möglicherweise sind Sie auch an der Verwendung von Joins bei Aktualisierungen interessiert, 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 irgendwo anders in der Datenbank stammen, müssen Sie mehrere Aktualisierungsabfragen durchführen.

Answer 2

Und jetzt der einfache Weg

update my_table m, -- let create a temp table with populated values
    (select 1 as id, 20 as value union -- this part will be generated
     select 2 as id, 30 as value union -- using a backend code
     -- for loop 
     select N as id, X as value
        ) t
set m.value = t.value where t.id=m.id -- now update by join - quick

Answer 3

Ich habe die Antwort von @newtover übernommen und sie mit der neuen json_table-Funktion in MySql 8 erweitert. Dadurch können Sie eine gespeicherte Prozedur erstellen, um die Arbeitslast zu bewältigen, anstatt Ihren eigenen SQL-Text im Code zu erstellen:

drop table if exists `test`;
create table `test` (
  `Id` int,
  `Number` int,
  PRIMARY KEY (`Id`)
);
insert into test (Id, Number) values (1, 1), (2, 2);

DROP procedure IF EXISTS `Test`;
DELIMITER $$
CREATE PROCEDURE `Test`(
    p_json json
)
BEGIN
    update test s
        join json_table(p_json, '$[*]' columns(`id` int path '$.id', `number` int path '$.number')) v 
        on s.Id=v.id set s.Number=v.number;
END$$
DELIMITER ;

call `Test`('[{"id": 1, "number": 10}, {"id": 2, "number": 20}]');
select * from test;

drop table if exists `test`;

Es ist ein paar Millisekunden langsamer als reines SQL, aber ich nehme das gerne in Kauf, anstatt den SQL-Text im Code zu generieren. Nicht sicher, wie performant es mit riesigen Datensätzen ist (das JSON-Objekt hat eine maximale Größe von 1 GB), aber ich benutze es die ganze Zeit beim Aktualisieren von 10k Zeilen auf einmal.

Answer 4

使い道

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

Bitte beachten:

• id muss ein eindeutiger Primärschlüssel sein
• wenn Sie Fremdschlüssel verwenden, um auf die Tabelle verweisen, löscht REPLACE erst und fügt dann ein, so dass dies einen Fehler verursachen

Answer 5

Ja es ist möglich, mit INSERT ON DUPLICATE KEY UPDATE sql-Anweisung. Syntax: INSERT INTO table_name (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE a=VALUES(a),b=VALUES(b),c=VALUES(c)