webentwicklung-frage-antwort-db.com.de

MySQL LOAD DATA INFILE mit ON DUPLICATE KEY UPDATE

Zum Laden großer Datenmengen in MySQL ist LOAD DATA INFILE bei weitem die schnellste Option. Leider wird ON DUPLICATE KEY UPDATE derzeit nicht unterstützt, obwohl dies so verwendet werden kann, dass INSERT IGNORE oder REPLACE funktionieren.

Jedoch, ON DUPLICATE KEY UPDATE hat Vorteile gegenüber REPLACE. Letzteres löscht und fügt ein, wenn ein Duplikat vorhanden ist. Dies bringt Overhead für die Schlüsselverwaltung. Außerdem bleiben Autoincrement-IDs beim Ersetzen nicht gleich.

Wie kann ON DUPLICATE KEY UPDATE emuliert werden, wenn LOAD DATA INFILE verwendet wird?

39
Jan

Mit diesen Schritten können Sie diese Funktionalität emulieren:

1) Erstellen Sie eine neue temporäre Tabelle.

CREATE TEMPORARY TABLE temporary_table LIKE target_table;

2) Löschen Sie optional alle Indizes aus der temporären Tabelle, um den Vorgang zu beschleunigen.

SHOW INDEX FROM temporary_table;
DROP INDEX `PRIMARY` ON temporary_table;
DROP INDEX `some_other_index` ON temporary_table;

3) Laden Sie die CSV in die temporäre Tabelle

LOAD DATA INFILE 'your_file.csv'
INTO TABLE temporary_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(field1, field2);

4) Kopieren Sie die Daten mit ON DUPLICATE KEY UPDATE

SHOW COLUMNS FROM target_table;
INSERT INTO target_table
SELECT * FROM temporary_table
ON DUPLICATE KEY UPDATE field1 = VALUES(field1), field2 = VALUES(field2);

5) Entfernen Sie die temporäre Tabelle

DROP TEMPORARY TABLE temporary_table;

Mit SHOW INDEX FROM und SHOW COLUMNS FROM Dieser Vorgang kann für jede Tabelle automatisiert werden.

81
Jan

wir können zuerst (zwei Schritte) durch die folgende einzelne Abfrage in der Prozedur ersetzen, die von (Jan) geteilt wird.

1) und 2) können wir eine neue Tabelle mit der gleichen Referenzstruktur und ohne Indizes erstellen.

CREATE TEMPORARY TABLE temporäre_tabelle SELECT * FROM Zieltabelle WHERE 1 = 0;

Anstatt von..

1) Erstellen Sie eine neue temporäre Tabelle.

CREATE TEMPORARY TABLE temporary_table LIKE target_table;

2) Löschen Sie optional alle Indizes aus der temporären Tabelle, um den Vorgang zu beschleunigen.

SHOW INDEX FROM temporary_table; DROP INDEX PRIMARY ON temporary_table; DROP INDEX some_other_index ON temporary_table;

3
Suneet Khurana