Wie kann ich in MySQL “einfügen, wenn nicht vorhanden” ausführen?

Ich fing an zu googeln und fand den Artikel So schreiben Sie INSERT if NOT EXISTS-Abfragen in Standard-SQL die über Mutex-Tabellen spricht.

Ich habe eine Tabelle mit ~14 Millionen Datensätzen. Wenn ich weitere Daten im gleichen Format hinzufügen möchte, gibt es eine Möglichkeit sicherzustellen, dass der einzufügende Datensatz nicht bereits existiert, ohne ein Abfragepaar zu verwenden (d. h. eine zu prüfende und eine einzufügende Abfrage ist die Ergebnismenge leer)?

Tut ein unique Beschränkung auf ein Feld garantieren die insert wird fehlschlagen, wenn es bereits da ist?

Es scheint, dass mit nur eine Einschränkung, wenn ich das Insert per PHP ausführe, krächzt das Skript.

Verwenden INSERT IGNORE INTO table.

Es gibt auch 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 entsprechend Googles Webcache:

18. Oktober 2007

Zum Anfang: Ab dem neuesten MySQL ist die im Titel dargestellte Syntax nicht möglich. Es gibt jedoch mehrere sehr einfache Möglichkeiten, um das zu erreichen, was mit vorhandener Funktionalität erwartet wird.

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

Stellen Sie sich vor, wir haben eine Tabelle:

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 Transkript-Metadaten aus Ensembl importiert, und dass die Pipeline aus verschiedenen Gründen bei jedem Ausführungsschritt unterbrochen werden könnte. Daher müssen wir zwei Dinge sicherstellen:

1. wiederholte Ausführungen der Pipeline zerstören unsere > Datenbank nicht

2. Wiederholte Ausführungen sterben nicht aufgrund von Fehlern „Duplizieren > Primärschlüssel“.

Methode 1: Verwenden von REPLACE

Es ist sehr einfach:

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

Wenn der Datensatz existiert, wird er überschrieben; wenn es noch nicht existiert, wird es erstellt. Die Verwendung dieser Methode ist jedoch in unserem Fall nicht effizient: Wir müssen vorhandene Datensätze nicht überschreiben, es ist in Ordnung, sie einfach zu überspringen.

Methode 2: mit INSERT IGNORE Auch ganz 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 Datenbank vorhanden ist, wird sie stillschweigend übersprungen (ignoriert). (Um genauer zu sein, hier ein Zitat aus dem MySQL-Referenzhandbuch: „Wenn Sie das IGNORE-Schlüsselwort verwenden, werden Fehler, die während der Ausführung der INSERT-Anweisung auftreten, stattdessen als Warnungen behandelt. Zum Beispiel ohne IGNORE eine Zeile, die einen vorhandenen UNIQUE-Index dupliziert oder PRIMARY KEY-Wert in der Tabelle verursacht einen Duplicate-Key-Fehler und die Anweisung wird abgebrochen.”.) Wenn der Datensatz noch nicht existiert, wird er erstellt.

Diese zweite Methode hat mehrere potenzielle Schwächen, einschließlich des Nichtabbruchs der Abfrage, falls ein anderes Problem auftritt (siehe Handbuch). Daher sollte es verwendet werden, wenn es zuvor ohne das Schlüsselwort IGNORE getestet wurde.

Methode 3: Verwenden von INSERT … ON DUPLICATE KEY UPDATE:

Die dritte Option ist die Verwendung INSERT … ON DUPLICATE KEY UPDATE
Syntax, und im UPDATE-Teil tun Sie einfach nichts, führen Sie eine bedeutungslose (leere) Operation aus, wie die Berechnung von 0+0 (Geoffray schlägt vor, die id=id-Zuweisung für die MySQL-Optimierungs-Engine vorzunehmen, um diese Operation zu ignorieren). Vorteil dieser Methode ist, dass sie nur doppelte Tastenereignisse ignoriert und trotzdem bei anderen Fehlern abbricht.

Als letzte Anmerkung: Dieser Beitrag wurde von Xaprb inspiriert. Ich würde auch raten, seinen anderen Beitrag zum Schreiben flexibler SQL-Abfragen zu konsultieren.

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 Frage

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

verwendet als die WHERE NOT EXISTS-condition erkennt, ob bereits eine Zeile mit einzufügenden Daten existiert. Nachdem eine Zeile dieser Art gefunden wurde, kann die Abfrage beendet werden, daher die LIMIT 1 (Mikrooptimierung, kann weggelassen werden).

Die Zwischenabfrage

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

stellt die einzufügenden Werte dar. 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 bekam ich beim Auslassen eine gültige Abfrage FROM DUALaber ältere Versionen (wie 5.5.60) scheinen das zu erfordern FROM Information. Durch die Nutzung WHERE NOT EXISTS Die Zwischenabfrage gibt eine leere Ergebnismenge zurück, wenn die innerste Abfrage übereinstimmende Daten gefunden hat.

Die äußere Abfrage

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

fügt die Daten ein, falls welche von der Zwischenabfrage zurückgegeben werden.

Bei MySQL, BEI DUPLIKAT-SCHLÜSSEL-UPDATE oder IGNORIEREN EINFÜGEN können praktikable Lösungen sein.

Ein Beispiel für BEI DUPLIKAT-SCHLÜSSEL-UPDATE Update basierend auf 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 IGNORIEREN EINFÜGEN 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] ... ]

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

• Primärschlüssel, wenn kein Ersatz
• Unique Constraint für eine Spalte
• Eindeutigkeitsbedingung für mehrere Spalten

Entschuldigung, wenn dies täuschend einfach erscheint. Ich weiß, es sieht schlecht aus angesichts des Links, den Sie mit uns teilen. ;-(

Aber ich gebe trotzdem diese Antwort, weil sie Ihren Bedarf zu decken scheint. (Falls nicht, kann dies dazu führen, dass Sie Ihre Anforderungen aktualisieren, was ebenfalls „eine gute Sache“(TM) wäre).

Wenn eine Einfügung die Eindeutigkeitsbeschränkung der Datenbank verletzen würde, wird auf Datenbankebene eine Ausnahme ausgelöst, die vom Treiber weitergeleitet wird. Es wird sicherlich Ihr Skript mit einem Fehler stoppen. In PHP muss es möglich sein, diesen Fall zu adressieren …

Versuche Folgendes:

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

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

Wenn der Datensatz existiert, wird er überschrieben; wenn es noch nicht existiert, wird es erstellt.

Hier ist eine PHP-Funktion, die nur dann eine Zeile einfügt, wenn nicht alle angegebenen Spaltenwerte bereits in der Tabelle vorhanden sind.

• Wenn sich eine der Spalten unterscheidet, wird die Zeile hinzugefügt.

• Wenn die Tabelle leer ist, wird die Zeile hinzugefügt.

• Wenn eine Zeile vorhanden ist, in der alle angegebenen Spalten die angegebenen Werte aufweisen, wird die Zeile nicht hinzugefügt.

   function insert_unique($table, $vars)
   {
     if (count($vars)) {
       $table = mysql_real_escape_string($table);
       $vars = array_map('mysql_real_escape_string', $vars);
  
       $req = "INSERT INTO `$table` (`". join('`, `', array_keys($vars)) ."`) ";
       $req .= "SELECT '". join("', '", $vars) ."' FROM DUAL ";
       $req .= "WHERE NOT EXISTS (SELECT 1 FROM `$table` WHERE ";
  
       foreach ($vars AS $col => $val)
         $req .= "`$col`='$val' AND ";
  
       $req = substr($req, 0, -5) . ") LIMIT 1";
  
       $res = mysql_query($req) OR die();
       return mysql_insert_id();
     }
     return False;
   }
  

Beispielnutzung:

<?php
  insert_unique('mytable', array(
    'mycolumn1' => 'myvalue1',
    'mycolumn2' => 'myvalue2',
    'mycolumn3' => 'myvalue3'
    )
  );
?>