Die MySQL INSERT-Anweisung hat die Syntax "INSERT ... ON DUPLICATE KEY UPDATE". Dies ist viel praktischer als das separate Schreiben von INSERT- und UPDATE-Anweisungen, wenn Sie Datensätze einfügen oder aktualisieren möchten, wenn Duplikate vorhanden sind. In diesem Artikel werden wir zusammenfassen, wie ein solches "INSERT ... ON DUPLICATE KEY UPDATE", nützliche Punkte und andere Spezifikationen verwendet werden.
Verwenden Sie eine Tabelle wie die folgende.
select version();
+-----------+
| version() |
+-----------+
| 8.0.17 |
+-----------+
CREATE TABLE users (
id INT(8) NOT NULL,
name VARCHAR(128),
age INT(3),
registered_timestamp timestamp not null default current_timestamp,
updated_timestamp timestamp not null default current_timestamp on update current_timestamp,
primary key(id)
);
INSERT INTO users (id, name, age) VALUES(1, 'taro', 20), (2, 'jiro', 18);
+----+------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 18 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
+----+------+------+----------------------+---------------------+
Angenommen, Sie möchten dieser Tabelle einen Datensatz mit der ID: 2 hinzufügen. Wenn Sie diesen Datensatz jedoch bereits haben, möchten Sie seinen Inhalt neu schreiben. Überprüfen Sie zuerst, ob der Datensatz mit der ID: "2" vorhanden ist. Wenn er nicht vorhanden ist, dann INSERT, falls vorhanden, dann die Anweisung UPDATE. Die erforderliche SQL ist wie folgt.
SELECT * FROM users where id = 2;
--Wenn kein Ergebnis zurückgegeben wird
INSERT INTO users (id, name, age) values (2, 'jiro', 19);
--Wenn das Ergebnis zurückgegeben wird
UPDATE users SET age = 19 where id = 2;
Versuchen wir, diese Abfrage mithilfe von Platzhaltern aus Java zu erstellen und auszuführen.
//Holen Sie sich die Verbindungsinstanzverbindung mit MySQL
String sqlSelect = "SELECT * FROM users where id = ?;";
PreparedStatement psSelect = connection.prepareStatement(sqlSelect);
psSelect.setInt(1, 2);
ResultSet rs = psSelect.excuteQuery();
if(!rs.next()){
String sqlInsert = "INSERT INTO users (id, name, age) values (?, ?, ?);";
PreparedStatement psInsert = connection.prepareStatement(sqlInsert);
psInsert.setInt(1, 2);
psInsert.setString(2, "jiro");
psInsert.setInt(3, 19);
psInsert.excute();
} else {
String sqlUpdate = "UPDATE users SET age = ? where id = ?;";
PreparedStatement psUpdate = connection.prepareStatement(sqlUpdate);
psUpdate.setInt(1, 19);
psUpdate.setInt(2, 2);
psUpdate.excute();
}
connection.commit();
Es ist ziemlich nervig.
Weil "INSERT ... ON DUPLICATE KEY UPDATE" eine solche bedingte Verzweigung für die Abfrage ausführt
Es bringt die drei Abfragen von zusammen.
INSERT INTO users (id, name, age) values (2, 'jiro', 19) ON DUPLICATE KEY UPDATE age = 19;
+----+------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 19 | 2020-06-05 11:05:52 | 2020-06-05 11:06:24 |
+----+------+------+----------------------+---------------------+
In diesem Fall wird dieser Datensatz automatisch aktualisiert, da die ID-Spalte der Primärschlüssel ist. Wenn im Teil "INSERT INTO" eine doppelte ID vorhanden ist, wird dieser Datensatz automatisch aktualisiert. Wenn dies aus Java zusammengesetzt ist
String sql = "INSERT INTO users (id, name, age) values (?, ?, ?) "+
"ON DUPLICATE KEY UPDATE age = ?;";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1, 2);
ps.setString(2, "jiro");
ps.setInt(3, 19);
ps.setInt(4, 2);
ps.excute();
connection.commit();
Es ist definitiv einfacher.
Darüber hinaus erleichtert eine Funktion namens VALUES (), die nur mit "ON DUPLICATE KEY UPDATE" verwendet werden kann, die Abfragekonstruktion. Sie können diese Funktion wie folgt verwenden, um auf den Wert zu verweisen, den Sie versucht haben, in den INSERT-Teil einzufügen.
-- VALUES(age)Rückgabe 19
INSERT INTO users (id, name, age) values (2, 'jiro', 19) ON DUPLICATE KEY UPDATE age = VALUES(age);
Darüber hinaus können Sie mit dieser Funktion mehrere Zeilen mit "ON DUPLICATE KEY UPDATE" einfügen / aktualisieren.
-- VALUES(age)Ist id:"1"In der Zeile von 20, id:"3"Gibt 17 in der Zeile zurück
INSERT INTO users (id, name, age) values (1, 'taro', 20), (3, 'saburo', 17) ON DUPLICATE KEY UPDATE age = VALUES(age);
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 19 | 2020-06-05 11:05:52 | 2020-06-05 11:06:24 |
| 3 | saburo | 17 | 2020-06-05 11:07:01 | 2020-06-05 11:07:01 |
+----+--------+------+----------------------+---------------------+
Wie oben erwähnt, kann ein Duplikat im Primärschlüssel entsprechend dem Alterswert aktualisiert werden, den Sie versucht haben, in jeden Datensatz einzufügen.
Wie Sie hier sehen können, wird UPDATE nicht ausgeführt und "ON UPDATE" hinzugefügt, wenn Sie versuchen, UPDATE aufgrund doppelter Schlüssel auszuführen, wenn der Wert, den Sie aktualisieren wollten, und alle vorhandenen Werte übereinstimmen. Die Spalte wird ebenfalls nicht aktualisiert. (Entspricht den Spezifikationen von UPDATE selbst)
Wenn Sie versuchen, eine Abfrage programmgesteuert zu erstellen, ohne "ON DUPLICATE KEY UPDATE" für diesen Vorgang zu verwenden, müssen Sie das Muster selbst mehrmals schleifen, wodurch mit der obigen SELECT-Anweisung eine bedingte Verzweigung erstellt wird. Wenn Sie jedoch die Funktion VALUES () mit "ON DUPLICATE KEY UPDATE" verwenden, müssen Sie nur die Zuordnung zum Platzhalter wie unten gezeigt wiederholen.
int numRecord = 2;
List<String> placeholderList = new ArrayList<>();
for (int i = 0; i < numRecord; i++){
placeholdersList.add("(?, ?, ?)");
}
String sql = "INSERT INTO users (id, name, age) values "+
StringUtils.join(placeholdersList, ", ") +
"ON DUPLICATE KEY UPDATE age = VALUES(age)";
PreparedStatement ps = connection.prepareStatement(sql);
//Sie müssen diesen Teil nur für die Anzahl der Datensätze schleifen
//Erste Aufnahme
ps.setInt(1, 1);
ps.setString(2, "taro");
ps.setInt(3, 20);
//Zweiter Rekord
ps.setInt(5, 3);
ps.setString(6, "saburo");
ps.setInt(7, 17);
ps.excute();
connection.commit();
Der Wechsel zwischen INSERT und UPDATE durch Überprüfen, ob jeder Datensatz bereits in der Datenbank vorhanden ist, kann jetzt mit nur einer Abfrage ausgeführt werden.
[IF / CASE-Steuerungsflussfunktion](https://dev.mysql.com/doc/refman/5.6/ja/control-flow-functions.html] mit der Syntax "ON DUPLICATE KEY UPDATE" ) Kann für bedingte Updates verwendet werden.
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 19 | 2020-06-05 11:05:52 | 2020-06-05 11:06:24 |
| 3 | saburo | 17 | 2020-06-05 11:07:01 | 2020-06-05 11:07:01 |
+----+--------+------+----------------------+---------------------+
--Der Wert des Alters, den Sie einfügen wollten(VALUES(age))Wird nur aktualisiert, wenn der ursprüngliche Alterswert größer ist
INSERT INTO users (id, name, age) values (2, 'jiro', 20), (3, 'sabro', 17) ON DUPLICATE KEY UPDATE age = IF(VALUES(age) > age, VALUES(age), age);
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:07:38 |
| 3 | saburo | 17 | 2020-06-05 11:07:01 | 2020-06-05 11:07:01 |
+----+--------+------+----------------------+---------------------+
--Der Wert des Alters, den Sie einfügen wollten(VALUES(age))Wenn der Wert größer als der ursprüngliche Alterswert ist, wird 1 hinzugefügt. Wenn er gleich ist, bleibt er unverändert. Wenn er kleiner ist, wird er auf einen neuen Wert aktualisiert.
INSERT INTO users (id, name, age) values (2, 'jiro', 22), (3, 'saburo', 15)
ON DUPLICATE KEY UPDATE
age =
CASE
WHEN VALUES(age) > age THEN age + 1
WHEN VALUES(age) = age THEN age
WHEN VALUES(age) < age THEN VALUES(age)
END;
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 21 | 2020-06-05 11:05:52 | 2020-06-05 11:08:06 |
| 3 | saburo | 15 | 2020-06-05 11:07:01 | 2020-06-05 11:08:06 |
+----+--------+------+----------------------+---------------------+
Artikel, der besagt, dass bei einer Spalte mit automatischer Nummerierung (AUTO_INCREMENT) die Seriennummer um eins vorgerückt wird, auch wenn UPDATE ohne Aktualisierung ausgeführt wird, wenn die Syntax "ON DUPLICATE KEY UPDATE" verwendet wird. Kommt oft raus. (Vorteile und Vorsichtsmaßnahmen von INSERT ON DUPLICATE KEY UPDATE usw.)
Aber in der MySQL Version 5.6 Referenz
(Diese Effekte sind nicht für InnoDB-Tabellen identisch, bei denen a eine Spalte für das automatische Inkrementieren ist. Wenn Sie eine Spalte für das automatische Inkrementieren verwenden, erhöht die INSERT-Anweisung den Wert für das automatische Inkrementieren, nicht jedoch für UPDATE.)
Es gibt [^ 1].
[^ 1]: In der von mir verwendeten Referenz der Version 8 heißt es außerdem: "(Die Effekte sind für eine InnoDB-Tabelle, in der a eine Auto-Inkrement-Spalte ist, nicht identisch. Bei einer Auto-Inkrement-Spalte erhöht eine INSERT-Anweisung die Auto-" Inkrementwert, UPDATE jedoch nicht.) ".
Ich habe versucht zu überprüfen.
CREATE TABLE users_increment (
id INT(8) NOT NULL AUTO_INCREMENT,
name VARCHAR(128),
age INT(3),
registered_timestamp timestamp not null default current_timestamp,
updated_timestamp timestamp not null default current_timestamp on update current_timestamp,
primary key(id)
);
INSERT INTO users_increment (name, age) VALUES('taro', 20), ('jiro', 18);
+----+------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:17:58 | 2020-06-05 11:17:58 |
| 2 | jiro | 18 | 2020-06-05 11:17:58 | 2020-06-05 11:17:58 |
+----+------+------+----------------------+---------------------+
--INSERT-Anweisung wird nicht ausgeführt, da der Schlüssel dupliziert ist
--Wenn die automatische Seriennummer auch nach UPDATE vorgerückt wird, lautet die ID des nächsten INSERTed-Datensatzes"4"Sollte sein
INSERT INTO users_increment (id, name, age) values (2, 'jiro', 19) ON DUPLICATE KEY UPDATE age = VALUES(age);
+----+------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:17:58 | 2020-06-05 11:17:58 |
| 2 | jiro | 19 | 2020-06-05 11:17:58 | 2020-06-05 11:19:01 |
+----+------+------+----------------------+---------------------+
--Der nächste tatsächlich eingefügte Datensatz ist"3"
INSERT INTO users_increment (name, age) values ('saburo', 17);
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:17:58 | 2020-06-05 11:17:58 |
| 2 | jiro | 19 | 2020-06-05 11:17:58 | 2020-06-05 11:19:01 |
| 3 | saburo | 17 | 2020-06-05 11:19:46 | 2020-06-05 11:19:46 |
+----+--------+------+----------------------+---------------------+
Wie oben erwähnt, wird in neueren Versionen von MySQL die automatische Seriennummer nur fortgesetzt, wenn "ON DUPLICATE KEY UPDATE" ausgeführt wird, es sei denn, sie ist INSERTed.
MySQL 5.6 Referenzhandbuch Kontrollflussfunktionen
MySQL 5.6 Referenzhandbuch INSERT ... ON DUPLICATE KEY UPDATE Syntax
MySQL: INSERT ... ON DUPLICATE KEY UPDATE Zusammenfassung
Vorteile und Vorsichtsmaßnahmen von INSERT ON DUPLICATE KEY UPDATE