Verwendung und Unterschiedsanalyse von „Ersetzen in“ und „Einfügen in“ beim Update doppelter Schlüssel in MySQL

Dieser Artikel veranschaulicht anhand von Beispielen die Verwendung und die Unterschiede zwischen „Replace into“ und „Insert into“ beim Update doppelter Schlüssel in MySQL. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Sowohl „Ersetzen durch“ als auch „Einfügen in“ beim Update eines doppelten Schlüssels sollen ein Problem lösen, das wir normalerweise haben

Das heißt: Wenn der Datensatz in der Datenbank vorhanden ist, aktualisieren Sie die Daten im Datensatz. Wenn nicht, fügen Sie den Datensatz hinzu.

Wir erstellen einen Testtabellentest

CREATE TABLE `test` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT KOMMENTAR 'ID',
 `name` varchar(32) DEFAULT '' KOMMENTAR 'Name',
 `addr` varchar(256) DEFAULT '' KOMMENTAR 'Adresse',
 PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

Fügen Sie einige Daten in die Tabelle ein

INSERT INTO-Test
WERTE
	(NULL, 'a', 'aaa'),
	(NULL, 'b', 'bbb'),
	(NULL, 'c', 'ccc'),
	(NULL, 'd', 'ddd');

Die Anzahl der betroffenen Zeilen beträgt 4 und die Ergebnisse sind wie folgt:

Wir führen die folgende Anweisung aus:

Ersetzen Sie in Testwerte (NULL, 'e', ​​​​'eee');

Das Ergebnis zeigt, dass 1 Zeile betroffen ist und der Datensatz erfolgreich eingefügt wurde.

Beachten Sie, dass wir in der obigen Anweisung die Primärschlüssel-ID nicht eingegeben haben.

Dann führen wir die folgende Anweisung aus:

Ersetzen Sie in Testwerte (1, „aa“, „aaaa“);

Die Ergebnisse zeigen, dass 2 Zeilen betroffen sind und der Datensatz mit der ID 1 erfolgreich aktualisiert wurde.

Warum passiert das? Der Grund dafür ist, dass „Replace into“ zuerst versucht, einen Datensatz in die Tabelle einzufügen. Da unsere ID der Primärschlüssel ist und nicht wiederholt werden kann, kann dieser Datensatz offensichtlich nicht erfolgreich eingefügt werden. Dann löscht „Replace into“ den vorhandenen Datensatz und fügt ihn dann ein. Es wird also angezeigt, dass die Anzahl der betroffenen Zeilen 2 beträgt.

Lassen Sie uns die folgende Anweisung noch einmal ausführen:

Ersetzen Sie in Test (ID, Name) Werte (1, 'aaa');

Hier geben wir nur die Felder „ID“ und „Name“ an. Schauen wir, ob der Inhalt des Felds „Adresse“ nach dem Ersetzen durch „In“ noch vorhanden ist.

Offensichtlich ist der Inhalt des Felds „adr“ verschwunden, was mit unserer obigen Analyse übereinstimmt. „reaplce into“ löscht zuerst den Datensatz mit der ID 1 und fügt dann den Datensatz ein. Wir haben jedoch den Wert von „adr“ nicht angegeben, sodass es wie in der obigen Abbildung aussieht.

Manchmal besteht unsere Anforderung jedoch darin, die Daten des angegebenen Felds zu aktualisieren, wenn der Datensatz vorhanden ist und die ursprünglichen Felddaten weiterhin erhalten bleiben, anstatt dass die Adressfelddaten wie oben gezeigt verloren gehen.

Hier müssen Sie insert into on duplicate key update verwenden

Führen Sie die folgende Anweisung aus:

INSERT INTO Test (ID, Name)
WERTE(2, 'bb') 
BEIM DUPLIZIERTEN SCHLÜSSEL 
AKTUALISIEREN 
Name = WERTE(Name);

VALUES(Feldname) bedeutet, den Spaltenwert der aktuellen Anweisung insert abzurufen, VALUES(Name) bedeutet 'bb'

Die Ergebnisse zeigen, dass 2 Zeilen betroffen sind

Wie in der Abbildung oben gezeigt, bleibt der Wert des Adressfelds erhalten.

Die Anweisung „insert into on duplicate key update“ fügt zuerst den Datensatz ein und aktualisiert ihn, wenn dies fehlschlägt. Warum beträgt die Anzahl der betroffenen Zeilen jedoch 2?

Lassen Sie uns eine Tabelle test2 neu erstellen

CREATE TABLE `test2` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT KOMMENTAR 'ID',
 `sn` varchar(32) DEFAULT '' COMMENT 'Eindeutiger Schlüssel',
 `name` varchar(32) DEFAULT '' KOMMENTAR 'Name',
 `addr` varchar(256) DEFAULT '' KOMMENTAR 'Adresse',
 Primärschlüssel (`id`),
 EINZIGARTIGER SCHLÜSSEL `sn` (`sn`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

Fügen Sie einige Daten ein

INSERT INTO test2
WERTE
	(NULL, '01', 'a', 'aaa'),
	(NULL, '02', 'b', 'bbb'),
	(NULL, '03', 'c', 'ccc'),
	(NULL, '04', 'd', 'ddd');

Wir führen die folgende Anweisung aus:

INSERT INTO test2 (sn, Name, Adresse)
WERTE
	('02', 'bb', 'bbbb')
BEIM DUPLIZIERTEN SCHLÜSSEL 
AKTUALISIEREN 
Name = WERTE(Name),
Adresse = WERTE(Adresse);

Die Ergebnisse sind wie folgt:

Bei jeder Ausführung der obigen Anweisung wird das Auto-Increment-Feld der Tabelle test2 um 1 erhöht, obwohl die Anzahl der betroffenen Zeilen 0 ist.

Wenn die Anweisung „insert into on duplicate key update“ nur den Originaldatensatz aktualisiert, wird das Auto-Increment-Feld offensichtlich nicht automatisch um 1 erhöht, was bedeutet, dass auch ein Datensatzlöschvorgang durchgeführt wird.

Fügen Sie zuerst den Datensatz ein. Wenn dies fehlschlägt, löschen Sie den ursprünglichen Datensatz, behalten Sie jedoch den Wert des Felds nach der Aktualisierungsanweisung bei. Führen Sie dann den beibehaltenen Wert mit dem zu aktualisierenden Wert zusammen und fügen Sie dann einen neuen Datensatz ein.

Zusammenfassen:

Sowohl „replace into“ als auch „insert into on duplicate key update“ versuchen zuerst, den Datensatz einzufügen. Wenn dies fehlschlägt, wird der Datensatz gelöscht. „replace into“ behält den Wert des ursprünglichen Datensatzes nicht bei, „insert into on duplicate key update“ hingegen schon. Fügen Sie dann einen neuen Datensatz ein.

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „MySQL-Abfragekenntnisse“, „Zusammenfassung der allgemeinen MySQL-Funktionen“, „MySQL-Protokolloperationskenntnisse“, „Zusammenfassung der MySQL-Transaktionsoperationskenntnisse“, „MySQL-gespeicherte Prozedurkenntnisse“ und „Zusammenfassung der MySQL-Datenbanksperrenkenntnisse“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.