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

Hier ist eine PHP-Funktion, die eine Zeile nur dann einfügt, wenn alle angegebenen Spaltenwerte nicht 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 existiert, in der alle angegebenen Spalten die angegebenen Werte haben, 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;
   }

Beispiel für die Verwendung:

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

Answer 2

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

Ist der Datensatz bereits vorhanden, wird er überschrieben; ist er noch nicht vorhanden, wird er neu angelegt.

Answer 3

Es gibt mehrere Antworten auf die Frage, wie Sie dieses Problem lösen können, wenn Sie eine UNIQUE Index, gegen den Sie mit ON DUPLICATE KEY o INSERT IGNORE . Das ist nicht immer der Fall, und als UNIQUE eine Längenbeschränkung (1000 Byte) hat, können Sie das möglicherweise nicht ändern. Ich musste zum Beispiel mit Metadaten arbeiten in WordPress ( wp_postmeta ).

Ich habe das Problem schließlich mit zwei Abfragen gelöst:

UPDATE wp_postmeta SET meta_value = ? WHERE meta_key = ? AND post_id = ?;
INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT DISTINCT ?, ?, ? FROM wp_postmeta WHERE NOT EXISTS(SELECT * FROM wp_postmeta WHERE meta_key = ? AND post_id = ?);

Abfrage 1 ist eine reguläre UPDATE Abfrage ohne jede Wirkung, wenn der betreffende Datensatz nicht vorhanden ist. Abfrage 2 ist eine INSERT die von einer NOT EXISTS d.h. die INSERT wird nur ausgeführt, wenn der Datensatz nicht vorhanden ist.

Answer 4

Bemerkenswert ist, dass INSERT IGNORE den Primärschlüssel unabhängig davon, ob die Anweisung erfolgreich war oder nicht, genauso wie eine normale INSERT-Anweisung inkrementiert.

Dies führt zu Lücken in Ihren Primärschlüsseln, die einen Programmierer geistig instabil machen könnten. Oder wenn Ihre Anwendung schlecht konzipiert ist und von perfekten inkrementellen Primärschlüsseln abhängt, könnte dies zu Kopfschmerzen führen.

Blick in innodb_autoinc_lock_mode = 0 (Server-Einstellung, mit leichten Leistungseinbußen), oder verwenden Sie zuerst ein SELECT, um sicherzustellen, dass Ihre Abfrage nicht fehlschlägt (was ebenfalls mit Leistungseinbußen und zusätzlichem Code verbunden ist).

Answer 5

Aktualisieren oder Einfügen ohne bekannten Primärschlüssel

Wenn Sie bereits einen eindeutigen oder primären Schlüssel haben, sind die anderen Antworten mit entweder INSERT INTO ... ON DUPLICATE KEY UPDATE ... o REPLACE INTO ... sollte gut funktionieren (beachten Sie, dass replace into löscht, wenn vorhanden, und dann einfügt - somit werden vorhandene Werte nicht teilweise aktualisiert).

Aber wenn Sie die Werte für some_column_id y some_type die in ihrer Kombination bekanntermaßen einzigartig sind. Und Sie wollen aktualisieren some_value wenn vorhanden, oder einfügen, wenn nicht vorhanden. Und Sie möchten dies in nur einer Abfrage tun (um eine Transaktion zu vermeiden). Dies könnte eine Lösung sein:

INSERT INTO my_table (id, some_column_id, some_type, some_value)
SELECT t.id, t.some_column_id, t.some_type, t.some_value
FROM (
    SELECT id, some_column_id, some_type, some_value
    FROM my_table
    WHERE some_column_id = ? AND some_type = ?
    UNION ALL
    SELECT s.id, s.some_column_id, s.some_type, s.some_value
    FROM (SELECT NULL AS id, ? AS some_column_id, ? AS some_type, ? AS some_value) AS s
) AS t
LIMIT 1
ON DUPLICATE KEY UPDATE
some_value = ?

Grundsätzlich läuft die Abfrage so ab (weniger kompliziert, als es vielleicht aussieht):

• Wählen Sie eine vorhandene Zeile über die Schaltfläche WHERE Klausel übereinstimmen.
• Vereinigen Sie dieses Ergebnis mit einer potenziellen neuen Zeile (Tabelle s ), wobei die Spaltenwerte explizit angegeben werden (s.id ist NULL, so dass ein neuer Auto-Inkrement-Bezeichner erzeugt wird).
• Wenn eine vorhandene Zeile gefunden wird, wird die potenzielle neue Zeile aus der Tabelle s wird verworfen (aufgrund von LIMIT 1 in der Tabelle t ), und es wird immer eine ON DUPLICATE KEY die UPDATE die some_value Spalte.
• Wenn keine vorhandene Zeile gefunden wird, wird die potenzielle neue Zeile eingefügt (wie in der Tabelle s ).

Hinweis: Jede Tabelle in einer relationalen Datenbank sollte mindestens eine primäre automatisch inkrementierende Tabelle haben. id Spalte. Wenn Sie diese nicht haben, fügen Sie sie hinzu, auch wenn Sie sie auf den ersten Blick nicht brauchen. Sie wird für diesen "Trick" auf jeden Fall benötigt.