Ich habe zunächst gegoogelt und diesen Artikel gefunden, der ü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 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)?
Garantiert eine "Unique"-Einschränkung für ein Feld, dass das "Einfügen" fehlschlägt, wenn es bereits vorhanden ist?
Es scheint, dass bei nur einer Einschränkung das Skript abbricht, wenn ich die Einfügung über php ausführe.
Verwenden Sie INSERT IGNORE INTO table
siehe http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html
es gibt auch die Syntax INSERT ... ON DUPLICATE KEY UPDATE
, Erklärungen finden Sie auf dev.mysql.com
Post von bogdan.org.ua laut Google's webcache:
- Oktober 2007
Um zu beginnen: ab der neuesten MySQL, Syntax im Titel präsentiert ist nicht möglich. Aber es gibt mehrere sehr einfache Möglichkeiten, das zu erreichen, was man zu erreichen, was erwartet wird, 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 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) ohne Vorzeichen 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 aus verschiedenen Gründen die Pipeline aus verschiedenen Gründen bei jedem Ausführungsschritt unterbrochen werden könnte. Daher müssen wir zwei Dinge sicherstellen Dinge sicherstellen: 1) wiederholte Ausführungen der Pipeline zerstören nicht unsere Datenbank nicht zerstören, und 2) wiederholte Ausführungen nicht aufgrund von "Duplikat Primärschlüsselfehler' sterben.
Methode 1: Verwendung von REPLACE
Es ist sehr einfach:
REPLACE INTO
transcripts
SETensembl_transcript_id
= 'ENSORGT00000000001',transcript_chrom_start
= 12345,transcript_chrom_end
= 12678;Wenn der Datensatz existiert, wird er überschrieben; wenn er noch nicht existiert, wird er erstellt. Die Verwendung dieser Methode ist jedoch nicht effizient für unseren Fall: wir brauchen keine bestehenden Datensätze zu überschreiben, es ist in Ordnung sie einfach zu überspringen.
Methode 2: mit INSERT IGNORE Auch sehr einfach:
INSERT IGNORE INTO
transcripts
SETensembl_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, als stattdessen als Warnungen behandelt. Ohne IGNORE wird 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 Schwächen, darunter Nichtabbruch der Abfrage, falls ein anderes Problem auftritt (siehe das Handbuch). Daher sollte sie verwendet werden, wenn sie zuvor ohne das IGNORE Schlüsselwort.
Es gibt noch eine weitere Möglichkeit:
INSERT ... ON DUPLICATE KEY UPDATE
zu verwenden Syntax zu verwenden, und im UPDATE-Teil einfach nichts zu tun, eine sinnlose (leere) Operation, wie die Berechnung von 0+0 (Geoffray schlägt vor, die id=id-Zuweisung vor, damit die MySQL-Optimierungsmaschine diese Operation zu ignorieren). Der Vorteil dieser Methode ist, dass sie nur doppelte Schlüsselereignisse ignoriert und bei anderen Fehlern abbricht.Ein letzter Hinweis: Dieser Beitrag wurde von Xaprb inspiriert. Ich würde auch raten seinen anderen Beitrag über das Schreiben flexibler SQL-Abfragen zu lesen.
on duplicate key update, oder insert ignore können bei MySQL praktikable Lösungen sein.
Beispiel für on duplicate key 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;
Beispiel für insert 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] ... ]
Jede einfache Einschränkung sollte die Aufgabe erfüllen, wenn eine Ausnahme akzeptabel ist. Beispiele:
Es tut mir leid, dass dies trügerisch einfach erscheint. Ich weiß, dass es im Vergleich zu dem Link, den Sie mit uns teilen, schlecht aussieht ;-(
Aber ich gebe diese Antwort trotzdem, weil sie Ihre Anforderungen zu erfüllen scheint. (Wenn nicht, könnte es Sie dazu bringen, Ihre Anforderungen zu aktualisieren, was ebenfalls eine gute Sache wäre).
Edited: 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. Dies wird Ihr Skript mit Sicherheit mit einem Fehler beenden. Es muss in PHP möglich sein, diesen Fall zu adressieren ...