LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]

Die Anweisung LOAD DATA INFILE liest Datensätze mit extrem hoher Geschwindigkeit aus einer Textdatei in eine Tabelle ein. Der Dateiname muss als literaler String übergeben werden.

Die Syntax für die FIELDS- und LINES-Klauseln gilt auch für die SELECT ... INTO OUTFILE-Anweisung, die im weiteren Verlauf dieses Abschnitts beschrieben wird. (Siehe auch Abschnitt 13.2.7, „SELECT“.)

Weitere Informationen zur Effektivität von INSERT im Vergleich zu LOAD DATA INFILE und zur Beschleunigung von LOAD DATA INFILE finden Sie in Abschnitt 7.2.16, „Geschwindigkeit von INSERT-Anweisungen“.

Der von der Systemvariablen character_set_database angegebene Zeichensatz wird zur Interpretation der Daten in der Datei verwendet. SET NAMES und die Einstellung von character_set_client wirken sich hingegen nicht auf die Interpretation der Eingabe aus.

Beachten Sie, dass es derzeit nicht möglich ist, Datendateien zu laden, die den Zeichensatz ucs2 verwenden.

Sie können Datendateien auch mithilfe des Hilfsprogramms mysqlimport laden; dieses sendet dann eine LOAD DATA INFILE-Anweisung an den Server. Die Option --local bewirkt, dass mysqlimport Datendateien vom Clienthost liest. Sie können die Option --compress angeben, um bei langsamen Netzwerken eine bessere Leistung zu erzielen, sofern Client und Server das Komprimierungsprotokoll unterstützen. Siehe auch Abschnitt 8.12, „mysqlimport — Programm zum Datenimport“.

Wenn Sie das Schlüsselwort LOW_PRIORITY angeben, wird die Ausführung von LOAD DATA verzögert, bis kein Client mehr aus der Tabelle liest.

Geben Sie CONCURRENT bei einer MyISAM-Tabelle an, die die Bedingungen für gleichzeitige Einfügeoperationen erfüllt (d. h. keine freien Blöcke in der Mitte enthält), dann können andere Threads Daten aus der Tabelle abrufen, während LOAD DATA ausgeführt wird. Die Verwendung dieser Option beeinflusst die Leistungsfähigkeit von LOAD DATA auch dann geringfügig, wenn gleichzeitig kein anderer Thread die Tabelle verwendet.

Das Schlüsselwort LOCAL wird, sofern angegeben, in Bezug auf die Clientseite der Verbindung interpretiert:

Beachten Sie, dass, sofern keine LOCAL-Option angegeben wurde, die Regeln zur Folge haben, dass eine Datei namens ./myfile.txt im Datenverzeichnis des Servers gesucht wird, während die Datei myfile.txt aus dem Datenbankverzeichnis der Standarddatenbank gelesen wird. Ist also beispielsweise db1 die Standarddatenbank, dann liest die folgende LOAD DATA-Anweisung die Datei data.txt aus dem Datenbankverzeichnis für db1 aus, obwohl die Anweisung die Datei explizit in die Datenbank db2 lädt:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Windows-Pfadnamen müssen unter Verwendung von Schrägstrichen (statt Backslashs) angegeben werden. Wenn Sie Backslashs (umgekehrte Schrägstriche) verwenden, müssen Sie sie doppelt angeben.

Aus Sicherheitsgründen müssen Textdateien, die auf dem Server ausgelesen werden, entweder im Datenbankverzeichnis liegen oder von allen gelesen werden können. Außerdem benötigen Sie zur Verwendung von LOAD DATA INFILE für Serverdateien die Berechtigung FILE. Siehe auch Abschnitt 5.8.3, „Von MySQL zur Verfügung gestellte Berechtigungen“.

Die Verwendung von LOCAL ist ein wenig langsamer als der Direktzugriff des Servers auf die Dateien, weil der Inhalt der Datei vom Client über die Verbindung an den Server gesendet werden muss. Die Berechtigung FILE benötigen Sie hingegen zum Laden lokaler Dateien nicht.

Die Option LOCAL funktioniert nur, wenn ihre Verwendung sowohl am Client als auch am Server aktiviert wurde. Wenn beispielsweise mysqld mit --local-infile=0 gestartet wurde, funktioniert LOCAL nicht. Siehe auch Abschnitt 5.7.4, „Sicherheitsprobleme mit LOAD DATA LOCAL“.

Unter Unix können Sie, wenn LOAD DATA über eine Pipe lesen soll, die folgende Methode verwenden (hierbei laden wir das Listing des Verzeichnisses / in eine Tabelle):

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
find / -ls > /mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

Die Schlüsselwörter REPLACE und IGNORE steuern den Umgang mit eingegebenen Datensätzen, die Duplikate vorhandener Datensätze mit eindeutigen Schlüsselwerten darstellen.

Wenn Sie REPLACE angeben, ersetzt die Eingabe vorhandene Datensätze. Dies betrifft Datensätze, die denselben Wert bei einem Primärschlüssel oder einem eindeutigen Index wie die neuen Datensätze haben. Siehe auch Abschnitt 13.2.6, „REPLACE“.

Bei Angabe von IGNORE hingegen werden neue Datensätze, deren eindeutiger Schlüsselwert bereits einem vorhandenen Datensatz zugewiesen ist, ignoriert. Wenn Sie keine Option angeben, hängt das Verhalten davon ab, ob das Schlüsselwort LOCAL angegeben wird. Ohne LOCAL tritt ein Fehler auf, wenn ein doppelter Schlüsselwert gefunden wird; in diesem Fall wird der Rest der Textdatei ignoriert. Mit LOCAL ist das Standardverhalten so, als ob IGNORE angegeben worden wäre. Das liegt daran, dass der Server keine Möglichkeit hat, die Übertragung der Datei während des Vorgangs zu beenden.

Wenn Sie Fremdschlüssel-Constraints während des Ladevorgangs ignorieren wollen, können Sie eine SET FOREIGN_KEY_CHECKS=0-Anweisung vor der Ausführung von LOAD DATA absetzen.

Wenn Sie LOAD DATA INFILE auf eine leere MyISAM-Tabelle anwenden, werden alle nichteindeutigen Indizes in einer separaten Stapeloperation erstellt (wie bei REPAIR TABLE). Dies sollte LOAD DATA INFILE erheblich beschleunigen, wenn Sie viele Indizes haben. In einigen Extremfällen können Sie die Indizes noch schneller erstellen, indem Sie sie vor dem Einladen der Datei in die Tabelle mit ALTER TABLE ... DISABLE KEYS abschalten und sie nach Abschluss des Ladevorgangs mit ALTER TABLE ... ENABLE KEYS neu erstellen. Siehe auch Abschnitt 7.2.16, „Geschwindigkeit von INSERT-Anweisungen“.

LOAD DATA INFILE ist das Gegenstück zu SELECT ... INTO OUTFILE. (Siehe auch Abschnitt 13.2.7, „SELECT“.) Um Daten aus einer Tabelle in eine Datei zu schreiben, benutzen Sie SELECT ... INTO OUTFILE; wollen Sie die Datei wieder in eine Tabelle einlesen, dann verwenden Sie LOAD DATA INFILE. Die Syntax der Klauseln FIELDS und LINES ist bei beiden Anweisungen dieselbe. Beide sind optional, aber FIELDS muss LINES vorangehen, sofern beide angegeben sind.

Wenn Sie eine FIELDS-Klausel festlegen, ist auch jede einzelne Unterklausel (TERMINATED BY, [OPTIONALLY] ENCLOSED BY und ESCAPED BY) ebenfalls optional – Sie müssen lediglich mindestens eine dieser Unterklauseln angeben.

Wenn Sie keine FIELDS-Klausel angeben, entsprechen die Vorgabeeinstellungen dem Folgenden:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

Wenn Sie keine LINES-Klausel angeben, entsprechen die Vorgabeeinstellungen dem Folgenden:

LINES TERMINATED BY '\n' STARTING BY ''

Anders gesagt, die Standardwerte bewirken, dass LOAD DATA INFILE sich beim Einlesen wie folgt verhält:

Umgekehrt bewirken die Voreinstellungen, dass sich SELECT ... INTO OUTFILE beim Schreiben einer Ausgabe wie folgt verhält:

Der Backslash wird bei MySQL als Escape-Zeichen in Strings verwendet. Insofern müssen Sie, um FIELDS ESCAPED BY '\\' zu schreiben, zwei Backslashes angeben, damit der Wert als ein Backslash interpretiert wird.

Hinweis: Wenn Sie die Textdatei auf einem Windows-System erzeugt haben, müssen Sie unter Umständen LINES TERMINATED BY '\r\n' verwenden, um die Datei korrekt einzulesen, weil Windows-Programme normalerweise zwei Zeichen als Zeilenendzeichen verwenden. Einige Programme wie WordPad verwenden beim Schreiben von Dateien unter Umständen sogar \r als Zeilenendzeichen. Um solche Dateien zu lesen, verwenden Sie LINES TERMINATED BY '\r'.

Wenn alle Zeilen, die Sie einlesen wollen, ein gemeinsames Präfix aufweisen, das Sie ignorieren wollen, können Sie mit LINES STARTING BY 'prefix_string' das Präfix und alles, was davor steht, überspringen. Enthält eine Zeile das Präfix gar nicht, dann wird die gesamte Zeile übersprungen. Nehmen wir an, dass Sie folgende Anweisung absetzen:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

Nehmen wir ferner an, dass die Datendatei wie folgt aussieht:

xxx"abc",1
something xxx"def",2
"ghi",3

In diesem Fall werden als Ergebnis die Datensätze ("abc",1) und ("def",2) erzeugt. Der dritte Datensatz in der Datei wird übersprungen, weil er das Präfix nicht enthält.

Die Option IGNORE number LINES kann verwendet werden, um Zeilen am Anfang der Datei zu ignorieren. So können Sie etwa IGNORE 1 LINES angeben, um eine Kopfzeile zu überspringen, die etwa die Spaltennamen enthält:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

Wenn Sie SELECT ... INTO OUTFILE gemeinsam mit LOAD DATA INFILE verwenden, um Daten aus einer Datenbank in eine Datei zu schreiben und die Datei später wieder in die Datenbank einzulesen, dann müssen die Optionen der beiden Anweisungen zur Behandlung von Feldern und Zeilen einander entsprechen. Andernfalls kann LOAD DATA INFILE den Inhalt der Datei nicht korrekt einlesen. Angenommen, Sie schreiben mit SELECT ... INTO OUTFILE eine Datei mit Feldern, die kommagetrennt sind:

SELECT * INTO OUTFILE 'data.txt'
  FIELDS TERMINATED BY ','
  FROM table2;

Um diese kommagetrennte Datei wieder einzulesen, müssen Sie folgende Anweisung verwenden:

LOAD DATA INFILE 'data.txt' INTO TABLE table2
  FIELDS TERMINATED BY ',';

Haben Sie stattdessen probiert, die Datei mit der folgenden Anweisung einzulesen, dann ist dies nicht gelungen, da LOAD DATA INFILE angewiesen wird, nach Tabulatoren zwischen den Feldern zu suchen:

LOAD DATA INFILE 'data.txt' INTO TABLE table2
  FIELDS TERMINATED BY '\t';

Infolgedessen wird jede Eingabezeile wohl als separates Feld interpretiert werden.

Mit LOAD DATA INFILE können Sie Dateien lesen, die Sie aus externen Quellen erhalten haben. Beispielsweise exportieren viele Programme Daten als kommagetrennte Werte im so genannten CSV-Format (Comma-Separate Values). Hierbei werden die Felder durch Kommata voneinander getrennt und in doppelte Anführungszeichen gesetzt. Wenn Zeilen in einer solchen Datei durch Zeilenumbrüche abgeschlossen werden, veranschaulicht die folgende Anweisung die Optionen zur Feld- und Zeilenbehandlung, wie Sie sie zum Laden der Datei verwenden würden:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

Alle Optionen zur Feld- und Zeilenbehandlung können einen Leer-String ('') angeben. Wenn der String nicht leer ist, müssen die Werte FIELDS [OPTIONALLY] ENCLOSED BY und FIELDS ESCAPED BY genau ein Zeichen umfassen. Die Werte FIELDS TERMINATED BY, LINES STARTING BY und LINES TERMINATED BY können hingegen auch mehrere Zeichen enthalten. Um etwa Zeilen zu schreiben, die jeweils aus einem aus einer Absatzschaltung und einem Zeilenvorschub bestehenden Zeichenpaar bestehen, geben Sie die Klausel LINES TERMINATED BY '\r\n' an.

Um eine Datei mit Witzen zu lesen, die durch aus %% bestehenden Zeilen getrennt sind, können Sie Folgendes tun:

CREATE TABLE jokes
  (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  joke TEXT NOT NULL);
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
  FIELDS TERMINATED BY ''
  LINES TERMINATED BY '\n%%\n' (joke);

FIELDS [OPTIONALLY] ENCLOSED BY steuert die Anführungszeichen um die Felder. Bei der Ausgabe (SELECT ... INTO OUTFILE) werden, wenn Sie das Wort OPTIONALLY weglassen, alle Felder vom durch ENCLOSED BY angegebenen Zeichen umschlossen. Hier ein Beispiel für eine solche Ausgabe (mit dem Komma als Feldtrennzeichen):

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

Wenn Sie OPTIONALLY angeben, wird das ENCLOSED BY-Zeichen nur zum Umschließen von Werten in Spalten verwendet, die einen String-Datentyp haben (z. B. CHAR, BINARY, TEXT oder ENUM):

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

Beachten Sie, dass das Auftreten des ENCLOSED BY-Zeichens in einem Feldwert gekennzeichnet wird, indem man ihm das ESCAPED BY-Zeichen voranstellt. Ferner ist wichtig, dass es, wenn Sie einen leeren ESCAPED BY-Wert angeben, möglich ist, versehentlich eine Ausgabe zu erzeugen, die von LOAD DATA INFILE nicht korrekt eingelesen werden kann. Die obige Ausgabe beispielsweise würde wie folgt angezeigt werden, wenn das Escape-Zeichen leer wäre. Sie werden bemerken, dass das zweite Feld in der vierten Zeile ein auf das Anführungszeichen folgendes Komma enthält, welches das Feld abzuschließen scheint (was nicht zutrifft).

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20

Bei der Eingabe wird das ENCLOSED BY-Zeichen – sofern vorhanden – am Ende der jeweiligen Feldwerte entfernt. (Dies erfolgt unabhängig davon, ob OPTIONALLY angegeben ist oder nicht – OPTIONALLY hat keine Auswirkung auf die Eingabeinterpretation.) Instanzen des ENCLOSED BY-Zeichens, denen das ESCAPED BY-Zeichen vorangestellt ist, werden als Teil des aktuellen Feldwerts interpretiert.

Beginnt das Feld mit dem ENCLOSED BY-Zeichen, dann werden Instanzen dieses Zeichens nur dann als Feldtrennzeichen ausgewertet, wenn ihnen jeweils die TERMINATED BY-Sequenz für Feld oder Zeile folgt. Um Mehrdeutigkeiten zu vermeiden, können Fälle des ENCLOSED BY-Zeichens innerhalb eines Feldwerts verdoppelt werden; sie werden dann als einzelne Instanz des Zeichens ausgewertet. Wird also etwa ENCLOSED BY '"' angegeben, dann wird mit Anführungszeichen wie hier gezeigt verfahren:

"The ""BIG"" boss"  -> The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY steuert, wie Sonderzeichen geschrieben oder gelesen werden. Wenn das FIELDS ESCAPED BY-Zeichen nicht leer ist, dann wird es bei der Ausgabe als Präfix für die folgenden Zeichen verwendet:

Ist das FIELDS ESCAPED BY-Zeichen leer, dann werden keinerlei Zeichen gekennzeichnet und NULL wird als NULL (und nicht als \N) ausgegeben. Es ist wahrscheinlich nicht angeraten, ein leeres Escape-Zeichen anzugeben; dies gilt insbesondere dann, wenn Feldwerte in Ihren Daten eines der in der obigen Liste enthaltenen Zeichen enthalten.

Wenn das FIELDS ESCAPED BY-Zeichen nicht leer ist, werden Instanzen dieses Zeichens bei der Eingabe entfernt, und das nachfolgende Zeichen wird literal als Teil des Feldwerts verwendet. Ausnahmen sind die gekennzeichneten Zeichen ‘0’ oder ‘N’ (z. B. \0 oder \N, wenn das Escape-Zeichen ‘\’ ist). Diese Sequenzen werden als ASCII-NUL (Nullwertbyte) bzw. als NULL interpretiert. Die Regeln für den Umgang mit NULL werden im weiteren Verlauf dieses Abschnitts beschrieben.

Weitere Informationen zur ‘\’-Escape-Syntax finden Sie in Abschnitt 9.1, „Literale: wie Strings und Zahlen geschrieben werden“.

In bestimmten Fällen interagieren die Optionen zur Feld- und Zeilenbehandlung:

Die Handhabung von NULL-Werten variiert entsprechend den verwendeten FIELDS- und LINES-Optionen:

Manche Fälle werden von LOAD DATA INFILE nicht unterstützt:

Das folgende Beispiel lädt alle Spalten der Tabelle persondata:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

Standardmäßig wird, wenn keine Spaltenliste am Ende der LOAD DATA INFILE-Anweisung vorhanden ist, erwartet, dass Eingabezeilen ein Feld für jede Tabellenspalte enthalten. Wenn Sie nur einen Teil der Spalten einer Tabelle laden wollen, müssen Sie eine Spaltenliste angeben:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

Ferner müssen Sie eine Spaltenliste festlegen, wenn die Reihenfolge der Felder in der Eingabedatei sich von der Reihenfolge der Spalten in der Tabelle unterscheidet. Andernfalls kann MySQL nicht ermitteln, wie die Eingabefelder den Tabellenspalten zuzuordnen sind.

Die Spaltenliste kann entweder Spaltennamen oder Benutzervariablen enthalten. Bei Benutzervariablen gestattet Ihnen die SET-Klausel die Durchführung einer Werteumwandlung vor der Zuweisung des Ergebnisses zu den Spalten.

Benutzervariablen in der SET-Klausel können auf unterschiedliche Weise verwendet werden. Die folgenden Werte benutzen die erste Eingabespalte direkt für den Wert von t1.column1 und weisen die zweite Eingabespalte einer Benutzervariablen zu, an der eine Division durchgeführt wird, bevor sie als Wert für t1.column2 verwendet wird:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

Die SET-Klausel kann zur Übermittlung von Werten benutzt werden, die nicht der Eingabedatei entnommen wurden. Die folgende Anweisung weist column3 das aktuelle Datum und die Uhrzeit zu:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column3 = CURRENT_TIMESTAMP;

Sie können einen Eingabewert auch verwerfen, indem Sie ihn einer Benutzervariablen zuweisen, der Variablen aber keine Tabellenspalte zuordnen:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

Die Verwendung der Spalten-/Variablenliste und der SET-Klausel ist den folgenden Einschränkungen unterworfen:

Wenn eine Eingabezeile verarbeitet wird, unterteilt LOAD DATA sie in Felder und verwendet die Werte entsprechend der Spalten-/Variablenliste und der SET-Klausel, sofern diese vorhanden sind. Der Ergebnisdatensatz wird dann in die Tabelle eingefügt. Sind BEFORE INSERT- oder AFTER INSERT-Trigger für die Tabelle vorhanden, so werden diese vor bzw. nach dem Einfügen des Datensatzes aktiviert.

Hat eine Eingabezeile zu viele Felder, dann werden die überzähligen Felder ignoriert und die Anzahl der Warnungen wird erhöht.

Hat eine Eingabezeile zu wenig Felder, dann werden die Tabellenspalten, deren Eingabefelder fehlen, auf ihre Standardwerte gesetzt. Die Zuweisung von Standardwerten ist in Abschnitt 11.1.4, „Vorgabewerte von Datentypen“, beschrieben.

Ein leerer Feldwert wird anders interpretiert als ein fehlender Feldwert:

Dies sind dieselben Werte, die als Ergebnis ausgegeben werden, wenn Sie einem String, einem numerischen oder einem zeitbezogenen Wert in einer INSERT- oder UPDATE-Anweisung explizit den Leer-String zuweisen.

TIMESTAMP-Spalten werden nur dann auf die aktuellen Werte für Datum und Uhrzeit gesetzt, wenn ein NULL-Wert (d. h. \N) für die Spalte vorhanden ist oder der Standardwert der TIMESTAMP-Spalte der aktuelle Zeitstempel ist und in der Feldliste (sofern vorhanden) weggelassen wurde.

LOAD DATA INFILE betrachtet alle Eingaben als Strings, d. h., Sie können für ENUM- oder SET-Spalten numerische Werte nicht so eingeben wie von INSERT-Anweisungen her gewohnt. Alle ENUM- und SET-Werte müssen als Strings angegeben werden.

Wenn die LOAD DATA INFILE-Anweisung abgeschlossen ist, gibt sie einen Informations-String im folgenden Format zurück:

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

Wenn Sie die C-API verwenden, können Sie Informationen zur Anweisung durch einen Aufruf der Funktion mysql_info() ermitteln. Siehe auch Abschnitt 24.2.3.34, „mysql_info()“.

Warnungen treten unter denselben Umständen auf wie beim Einfügen von Werten mithilfe der INSERT-Anweisung (siehe Abschnitt 13.2.4, „INSERT“); der einzige Unterschied besteht darin, dass LOAD DATA INFILE auch dann Warnungen erzeugt, wenn zu wenig oder zu viel Felder im Eingabedatensatz vorhanden sind. Die Warnungen werden nirgends gespeichert; Sie können ihrer Anzahl lediglich entnehmen, ob alles geklappt hat oder nicht.

Mit SHOW WARNINGS erhalten Sie eine Liste der ersten max_error_count Warnungen mit Informationen dazu, was unter Umständen nicht geklappt hat. Siehe auch Abschnitt 13.5.4.25, „SHOW WARNINGS“.