Wie kann ich "insert if not exists" in MySQL ausführen?

Question

Ich habe zunächst gegoogelt und diesen Artikel gefunden Wie man INSERT if NOT EXISTS-Abfragen in Standard-SQL schreibt in dem es um Mutex-Tabellen geht.

Ich habe eine Tabelle mit ~14 Millionen Datensätzen. Wenn ich weitere Daten im gleichen Format hinzufügen möchte, gibt es dann eine Möglichkeit, sicherzustellen, dass der Datensatz, den ich einfügen möchte, nicht bereits vorhanden ist, ohne ein Abfragepaar zu verwenden (d. h. eine Abfrage zur Überprüfung und eine zum Einfügen, wenn die Ergebnismenge leer ist)?

Ist ein unique Beschränkung auf ein Feld garantieren die insert scheitern wird, wenn es schon da ist?

Es scheint, dass mit lediglich eine Einschränkung, wenn ich die Einfügung über PHP ausführe, bricht das Skript ab.

Answer 1

Utilice INSERT IGNORE INTO table .

Außerdem gibt es INSERT … ON DUPLICATE KEY UPDATE Syntax, und Sie finden Erklärungen in 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE-Anweisung .

---

Beitrag von bogdan.org.ua laut Der Webcache von Google :

18. Oktober 2007

Zu Beginn: In der neuesten Version von MySQL ist die im Titel genannte Syntax nicht möglich. Aber es gibt mehrere sehr einfache Möglichkeiten, das zu erreichen, was erwartet wird zu erreichen, indem man die vorhandene Funktionalität nutzt.

Es gibt 3 mögliche Lösungen: INSERT IGNORE, REPLACE, oder INSERT ON DUPLICATE KEY UPDATE.

Stellen Sie sich vor, wir haben einen Tisch:

CREATE TABLE `transcripts` (
`ensembl_transcript_id` varchar(20) NOT NULL,
`transcript_chrom_start` int(10) unsigned NOT NULL,
`transcript_chrom_end` int(10) unsigned NOT NULL,
PRIMARY KEY (`ensembl_transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Stellen Sie sich nun vor, dass wir eine automatische Pipeline haben, die Transkripte importiert Metadaten aus Ensembl importiert, und dass die Pipeline aus verschiedenen Gründen aus verschiedenen Gründen bei einem beliebigen Ausführungsschritt unterbrochen werden könnte. Daher müssen wir zwei Dinge sicherstellen Dinge sicherstellen:

1. die wiederholte Ausführung der Pipeline unsere Datenbank nicht zerstört > Datenbank

2. Wiederholte Hinrichtungen werden nicht aufgrund von "doppelten > Primärschlüssel' Fehler.

Methode 1: Verwendung von REPLACE

Es ist ganz einfach:

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Ist der Datensatz vorhanden, wird er überschrieben; ist er noch nicht vorhanden, wird er existiert, wird er neu erstellt. Diese Methode ist jedoch nicht effizient für unseren Fall nicht effizient: Wir müssen keine bestehenden Datensätze überschreiben, es reicht aus sie einfach zu überspringen.

Methode 2: mit INSERT IGNORE Ebenfalls sehr einfach:

INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Wenn die "ensembl_transcript_id" bereits in der Datei Datenbank vorhanden ist, wird sie stillschweigend übersprungen (ignoriert). (Um genauer zu sein, hier ist ein Zitat aus dem MySQL-Referenzhandbuch: "Wenn Sie das IGNORE Schlüsselwort benutzen, werden Fehler, die während der Ausführung der INSERT-Anweisung auftreten stattdessen als Warnungen behandelt. Ohne IGNORE würde zum Beispiel eine Zeile, die einen vorhandenen UNIQUE-Index oder PRIMARY KEY-Wert in der Tabelle dupliziert einen Duplikat-Schlüssel-Fehler und die Anweisung wird abgebrochen."). Wenn der Datensatz noch nicht existiert, wird er erstellt.

Diese zweite Methode hat mehrere potenzielle Schwachstellen, darunter kein Abbruch der Abfrage, wenn ein anderes Problem auftritt (siehe das Handbuch). Daher sollte sie verwendet werden, wenn sie zuvor ohne das IGNORE-Schlüsselwort.

Methode 3: mit INSERT ON DUPLICATE KEY UPDATE:

Die dritte Möglichkeit ist die Verwendung von INSERT … ON DUPLICATE KEY UPDATE Syntax, und im UPDATE-Teil einfach nichts tun einige sinnlose (leere) Operation, wie die Berechnung von 0+0 (Geoffray schlägt vor, die id=id-Zuweisung, damit die MySQL-Optimierungsmaschine diese Operation ignoriert Operation zu ignorieren). Der Vorteil dieser Methode ist, dass sie nur doppelte Schlüsselereignisse ignoriert Schlüsselereignisse ignoriert und bei anderen Fehlern immer noch abbricht.

Zum Schluss noch ein Hinweis: Dieser Beitrag wurde von Xaprb inspiriert. Ich würde auch raten seinen anderen Beitrag über das Schreiben flexibler SQL-Abfragen zu lesen.

Answer 2

Lösung:

INSERT INTO `table` (`value1`, `value2`) 
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL 
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1) 

Erläuterung:

Die innerste Abfrage

SELECT * FROM `table` 
      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1

verwendet als WHERE NOT EXISTS -Bedingung erkennt, ob es bereits eine Zeile mit den einzufügenden Daten gibt. Nachdem eine solche Zeile gefunden wurde, kann die Abfrage abgebrochen werden, daher die LIMIT 1 (Mikro-Optimierung, kann weggelassen werden).

Die Zwischenabfrage

SELECT 'stuff for value1', 'stuff for value2' FROM DUAL

steht für die einzufügenden Werte. DUAL bezieht sich auf eine spezielle Tabelle mit einer Zeile und einer Spalte, die standardmäßig in allen Oracle-Datenbanken vorhanden ist (siehe https://en.wikipedia.org/wiki/DUAL_table ). Auf einem MySQL-Server Version 5.7.26 habe ich eine gültige Abfrage erhalten, wenn ich die FROM DUAL zu verwenden, aber ältere Versionen (wie 5.5.60) scheinen die Option FROM Informationen. Durch die Verwendung von WHERE NOT EXISTS die Zwischenabfrage gibt eine leere Ergebnismenge zurück, wenn die innerste Abfrage passende Daten gefunden hat.

Die äußere Abfrage

INSERT INTO `table` (`value1`, `value2`) 

fügt die Daten ein, sofern sie von der Zwischenabfrage zurückgegeben wurden.

Answer 3

In MySQL, BEI DER AKTUALISIERUNG VON NACHSCHLÜSSELN o EINFÜGEN IGNORE können praktikable Lösungen sein.

---

Ein Beispiel für BEI DER AKTUALISIERUNG VON NACHSCHLÜSSELN Aktualisierung auf der Grundlage von mysql.com :

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

---

Ein Beispiel für EINFÜGEN IGNORE basierend auf mysql.com

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Oder:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Oder:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Answer 4

Jede einfache Einschränkung sollte die Aufgabe erfüllen, wenn eine Ausnahme akzeptabel ist. Beispiele:

• Primärschlüssel, wenn nicht Surrogat
• eindeutige Einschränkung einer Spalte
• mehrspaltige eindeutige Beschränkung

Es tut mir leid, wenn dies trügerisch einfach erscheint. Ich weiß, dass es im Vergleich zu dem Link, den Sie mit uns teilen, schlecht aussieht ;-(

Aber ich gebe trotzdem diese Antwort, weil sie Ihr Bedürfnis zu erfüllen scheint. (Falls nicht, könnte sie Sie dazu veranlassen, Ihre Anforderungen zu aktualisieren, was ebenfalls "eine gute Sache"(TM) wäre).

Wenn eine Einfügung die eindeutige Einschränkung der Datenbank verletzen würde, wird auf Datenbankebene eine Ausnahme ausgelöst, die vom Treiber weitergeleitet wird. Dadurch wird Ihr Skript mit Sicherheit mit einem Fehler abgebrochen. Es muss in PHP möglich sein, diesen Fall zu lösen...

Answer 5

Versuchen Sie das Folgende:

IF (SELECT COUNT(*) FROM beta WHERE name = 'John' > 0)
  UPDATE alfa SET c1=(SELECT id FROM beta WHERE name = 'John')
ELSE
BEGIN
  INSERT INTO beta (name) VALUES ('John')
  INSERT INTO alfa (c1) VALUES (LAST_INSERT_ID())
END