Frage "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"


Während der Ausführung eines INSERT Anweisung mit vielen Zeilen, möchte ich doppelte Einträge überspringen, die andernfalls einen Fehler verursachen würden. Nach einigen Nachforschungen scheinen meine Optionen die Verwendung von entweder:

  • ON DUPLICATE KEY UPDATE was eine unnötige Aktualisierung um einige Kosten impliziert, oder
  • INSERT IGNORE was eine Einladung für andere Arten des Versagens bedeutet, unangemeldet hereinzukommen.

Habe ich Recht in diesen Annahmen? Was ist der beste Weg, um einfach die Zeilen zu überspringen, die Duplikate verursachen könnten, und einfach zu den anderen Zeilen weiterzugehen?


735
2018-02-14 05:24


Ursprung


Antworten:


Ich würde empfehlen, zu verwenden INSERT...ON DUPLICATE KEY UPDATE.

Wenn du benutzt INSERT IGNORE, dann wird die Zeile nicht tatsächlich eingefügt, wenn es zu einem doppelten Schlüssel kommt. Die Anweisung erzeugt jedoch keinen Fehler. Es wird stattdessen eine Warnung generiert. Diese Fälle umfassen:

  • Einfügen eines doppelten Schlüssels in Spalten mit PRIMARY KEY oder UNIQUE Einschränkungen.
  • Einfügen eines NULL in eine Spalte mit einem NOT NULL Zwang.
  • Einfügen einer Zeile in eine partitionierte Tabelle, aber die Werte, die Sie einfügen, werden keiner Partition zugeordnet.

Wenn du benutzt REPLACEMySQL macht tatsächlich ein DELETE gefolgt von einem INSERT intern, die einige unerwartete Nebenwirkungen hat:

  • Eine neue Auto-Inkrement-ID wird zugewiesen.
  • Abhängige Zeilen mit Fremdschlüsseln können gelöscht werden (wenn Sie kaskadierende Fremdschlüssel verwenden) oder andernfalls verhindern REPLACE.
  • Auslöser, die angehen DELETE werden unnötig ausgeführt.
  • Nebeneffekte werden auch an Replikations-Slaves weitergegeben.

Korrektur: beide REPLACE und INSERT...ON DUPLICATE KEY UPDATE sind nicht standardisierte, firmenspezifische Erfindungen, die spezifisch für MySQL sind. ANSI SQL 2003 definiert a MERGE Anweisung, die den gleichen Bedarf (und mehr) lösen kann, aber MySQL unterstützt das nicht MERGE Erklärung.


Ein Benutzer hat versucht, diesen Beitrag zu bearbeiten (die Änderung wurde von den Moderatoren abgelehnt). Die Bearbeitung versuchte, einen Anspruch hinzuzufügen, der INSERT...ON DUPLICATE KEY UPDATE bewirkt, dass eine neue Auto-Inkrement-ID zugewiesen wird. Es ist wahr, dass die neue ID ist erzeugt, aber es wird nicht in der geänderten Zeile verwendet.

Siehe folgende Demonstration, getestet mit Percona Server 5.5.28. Die Konfigurationsvariable innodb_autoinc_lock_mode=1 (der Standard):

mysql> create table foo (id serial primary key, u int, unique key (u));
mysql> insert into foo (u) values (10);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   10 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> insert into foo (u) values (10) on duplicate key update u = 20;
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

Obiges zeigt, dass die IODKU-Anweisung das Duplikat erkennt und das Update aufruft, um den Wert von zu ändern u. Beachten Sie das AUTO_INCREMENT=3 Gibt an, dass eine ID generiert, aber nicht in der Zeile verwendet wurde.

Wohingegen REPLACE Löscht die ursprüngliche Zeile und fügt eine neue Zeile ein und Speichern einer neuen Auto-Increment-ID:

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+

896
2018-02-14 05:51



Wenn Sie sehen wollen, was das alles bedeutet, hier ist ein Schlag von allem:

CREATE TABLE `users_partners` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `pid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`pid`),
  KEY `partner_user` (`pid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Der Primärschlüssel basiert auf beiden Spalten dieser Schnellreferenztabelle. Ein Primärschlüssel erfordert eindeutige Werte.

Lass uns anfangen:

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...1 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1);
...0 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid
...0 row(s) affected

Beachten Sie, dass das oben genannte zu viel zusätzliche Arbeit gespart hat, indem Sie die Spalte auf sich selbst gestellt haben, keine Aktualisierung wird tatsächlich benötigt

REPLACE INTO users_partners (uid,pid) VALUES (1,1)
...2 row(s) affected

und jetzt einige mehrreihige Tests:

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...3 row(s) affected

In der Konsole wurden keine anderen Nachrichten generiert. Diese 4 Werte befinden sich nun in den Tabellendaten. Ich habe alles außer (1,1) gelöscht, damit ich aus dem gleichen Spielfeld testen konnte

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4) ON DUPLICATE KEY UPDATE uid=uid
...3 row(s) affected

REPLACE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...5 row(s) affected

So, da hast du es. Da dies alles auf einer frischen Tabelle mit fast keinen Daten und nicht in der Produktion durchgeführt wurde, waren die Ausführungszeiten mikroskopisch und irrelevant. Jeder, der Daten aus der realen Welt hat, wäre mehr als willkommen, sie beizutragen.


156
2017-10-21 18:23



Etwas Wichtiges zum Hinzufügen: Wenn INSERT IGNORE verwendet wird und Sie Schlüsselverletzungen haben, löst MySQL KEINE Warnung aus!

Wenn Sie beispielsweise versuchen, jeweils 100 Datensätze mit einem fehlerhaften Datensatz einzufügen, gelangen Sie in den interaktiven Modus:

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

Wie Sie sehen: Keine Warnungen! Dieses Verhalten wird in der offiziellen Mysql-Dokumentation sogar falsch beschrieben.

Wenn Ihr Skript informiert werden muss, wenn einige Datensätze nicht hinzugefügt wurden (aufgrund von Schlüsselverletzungen), müssen Sie mysql_info () aufrufen und für den Wert "Duplicates" analysieren.


37
2018-04-21 10:04



Ich weiß, das ist alt, aber ich werde diese Notiz hinzufügen, falls jemand (wie ich) auf dieser Seite ankommt, während sie versucht, Informationen zu INSERT..IGNORE zu finden.

Wie oben erwähnt, werden Fehler, die beim Ausführen der INSERT-Anweisung auftreten, stattdessen als Warnungen behandelt, wenn Sie INSERT..IGNORE verwenden.

Eine Sache, die nicht explizit erwähnt wird, ist, dass INSERT..IGNORE dazu führt, dass ungültige Werte beim Einfügen den engsten Werten angepasst werden (während ungültige Werte dazu führen würden, dass die Abfrage abgebrochen wird, wenn das Schlüsselwort IGNORE nicht verwendet wurde).


16
2017-09-16 14:48



Ich benutze routinemäßig INSERT IGNOREund es klingt genau nach dem Verhalten, nach dem du suchst. Solange Sie wissen, dass Zeilen, die Indexkonflikte verursachen würden, nicht eingefügt werden und Sie Ihr Programm dementsprechend planen, sollte es keine Probleme verursachen.


14
2018-02-14 05:53



ON DUPLICATE KEY UPDATE ist nicht Ja wirklich im Standard. Es ist so standardgemäß wie REPLACE. Sehen SQL MERGE.

Im Wesentlichen sind beide Befehle alternative Syntaxversionen von Standardbefehlen.


7
2018-02-14 05:57



Replace Into scheint eine Option. Oder Sie können mit überprüfen

IF NOT EXISTS(QUERY) Then INSERT

Dies wird einfügen oder löschen dann einfügen. Ich neige dazu, für eine IF NOT EXISTS überprüfe zuerst.


5
2018-02-14 05:34