Weiter Zurück [Inhalt] Online Suche im Handbuch

28.12 LOAD DATA INFILE

LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [OPTIONALLY] ENCLOSED BY "]
        [ESCAPED BY '\\' ]]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(col_name,...)]

Der Befehl LOAD DATA INFILE liest Reihen aus einer Textdatei in eine Tabelle mit einer sehr hohen Geschwindigkeit. Wenn das Schlüsselwort LOCAL angegeben wurde, dann wird die Datei von dem Client Host gelesen. Falls nicht, dann muß sich die Datei auf dem Server befinden. (LOCAL ist ab Version 3.22.6 verfügbar)

Viel einfacher ist es jedoch, mit Hilfe von Administrationswerkzeugen Datenbanken einzulesen oder zu konvertieren. Diese finden Sie im Kapitel Einsteiger Tutorial LINUX MySQL Server, nebenher gesagt....

Aus Sicherheitsgründen muß, wenn eine Textdatei auf dem Server eingelesen wird, diese entweder im Datenbankverzeichnis liegen, oder allgemein lesbar sein. Um also den Befehl LOAD DATA INFILE ausführen zu können, muß man die Datei Privilegien (file privilege) besitzen. Meh hierzu im Kapitel Datei Privilegien.

Der Befehl LOCAL wird ein wenig langsamer sein, als der direkte Zugriff des Servers, da die Daten erst über das Netzwerk übertragen werden müssen. Andererseits sind in diesem Falle keinerlei Privilegien notwendig.

Datenfiles können auch mit Hilfe des mysqlimport Utility eingelesen werden. Es arbeitet identisch zu dem Befehl LOAD DATA INFILE auf dem Server. Die LOCAL Option veranlaßt den Server, die Daten vom Client über das Netzwerk zu laden. Die Option --compress beim Befehl mysqlimport veranlaßt Client und Server, die Daten vor der Übermittlung zu komprimieren.

networks if the client and server support the compressed protocol.

Für das Auffinden der Dateien auf dem Server Host gelten folgende Regeln:

Folgendes Statement liest aus dem Datenbankverzeichnis für db1, nicht db2:
mysql> USE db1;
mysql> LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table; 

Die Schlüsselworte REPLACE und IGNORE kontrollieren das Handling der eingehenden Einträge, die exisiterende Einträge mit eindeutigen KEY VALUES kopieren. Wenn REPLACE angegeben wird, ersetzen neue Einträge alte, die dieselben KEY VALUES besitzen. Wenn IGNORE angegeben wird, dann werden diese Einträge übergangen. Ohne Angabe wird ein Fehler ausgegeben, wenn ein doppelter KEY VALUE auftritt.

Wenn Daten von einer lokalen Datei (auf dem Client) mit Hilfe des LOCAL Schlüsselwortes eingeladen werden, kann der Server die Übertragung der Daten nicht abbrechen, also handelt dieser, als wenn der Befehl zum Laden mit IGNORE gesterete worden wäre.

LOAD DATA INFILE ist das Gegenstück zu SELECT ... INTO OUTFILE. Siehe hierzu auch das Kapitel SELECT. Um Daten von einer Datenbank in eine Datei zu schreiben und wieder einzulesen, sind die Befehle SELECT ... INTO OUTFILE und LOAD DATA INFILE zu verwenden. Die Syntax für die Optionen FIELDS und LINES sind für beide Statements identisch und optional. Der Befehl FIELDS muß vor dem Befehl LINES stehen.

Zusammen mit dem Ausdruck FIELDS kann optional der Unterausdruck (TERMINATED BY, ENCOLSED BY und ESCAPED BY verwendet werden. Zumindest einer dieser Ausdrücke muß angewendet werden.

Wenn der Ausdruck FIELDS nicht angegeben wird, wird der Befehl so interpretiert:

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

Wenn kein LINES Ausdruck verwendet wird, wird folgender Befehl angenommen:

LINES TERMINATED BY '\n'

Anders gesagt, der Ausdruck LOAD DATA INFILE wird dann so ausgeführt:

Der Ausdruck SELECT ... INTO OUTFILE arbeitet folgendermaßen:

mysql> LOAD DATA INFILE "/tmp/file_name" into table test IGNORE 1 LINES;
Wenn SELECT ... INTO OUTFILE ausgeführt wird und direkt danach der Befehl LOAD DATA INFILE, müssen die Handling Optionen genau identisch sein, andern falls werden die Inhalte nicht korrekt interpretiert. Ein Beispiel:
mysql> SELECT * FROM table1 INTO OUTFILE 'data.txt'
           FIELDS TERMINATED BY ','
           FROM ...
Um eine durch Komma in den Feldern separierte Datei wieder einzuladen, muß also korrekterweise angegeben werden:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
           FIELDS TERMINATED BY ',';
Folgender Befehl würde unweigerlich scheitern:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
           FIELDS TERMINATED BY '\t';

Jede Zeile würde als einziges Feld interpretiert werden.

LOAD DATA INFILE kann benutzt werden, um Dateien von externen Quellen zu laden. Z.B. eine Datei im DBASE Format hat die Felder durch Kommas getrennt ,und diese in doppelte Anführungszeichen eingepackt. Wenn die Zeilen durch \newline getrennt sind, dann wird folgender Befehl die Datei einladen:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
           LINES TERMINATED BY '\n';
Jede der Felder oder Line Handling Optionen kann ein leeren String erkennen. Wenn dieser nicht leer ist, müssen die Werte für FIELDS [OPTIONALLY] ELCLOSED BY und FIELDS ESCAPED BY mehr als einen Buchstaben enthalten. Z.B. muß man, um eine Zeilen zu schreiben, die durch ein \cr terminiert sind, den Ausdruck TERMINATED BY '\r\n' verwenden. Beipiel:

"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 OPTIONALLY angegeben wird, wird der ENCLOSE BY Buchstabe nur benutzt, um CHAR und VARCHAR Felder zu trennen:

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
Sie sollten berücksichtigen, daß wenn immer der Ausdruck ENCLOSED BY Buchstabe in einem Feld vorkommt, diesem der ESCAPED BY Buchstabe vorangestellt werden muß. Beachten Sie hierzu das folgende Beispiel. Beispiel 4 (letzte Zeile) besitzt die richtige Syntax, auch wenn es so aussieht, als wenn das Anführungszeichen vor dem Komma das Feld beenden würde. Der Parser reagiert auf einen String, der sich aus den beiden ENCOSED BY und TERMINITATED BY Buchstaben zusammensetzt, mit einer Sonderbehandlung. Er schließt dann das vorhergehende Feld dann noch nicht ab, sondern merkt sich das Komma als Buchstabe, der in des String gehört.

Beispiel Zwei würde einen Fehler verursachen, weil der Parser das Komma als Trennfeld betrachen würde. Danach würden ein Gemisch aus String und Zahl folgen, welches zu einem Fehler führt. Bei Beispiel drei führt das zu einem Fehler, da ein Leerzeigen auf den ENCLOSED BY Buchstaben folgt. Hier endet für den Parser das Feld. Danach folgen keine reinen Zahlen mehr.


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
Hier ein Beispiel mit ENCLOSED BY:

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

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


mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
Es könne aber auch nur einige Spalten der Tabelle geladen werden:


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

Wenn eine Reihe zu wenig Felder hat, werden die Spalten, die leerbleiben, alle auf ihre Defaultwerte gesetzt, meist 0.

Alle Daten beim Einlesen werden als Strings behandelt. Es können also keine numerischen Werte für ENUM oder SET Spalten, so wie es mit dem INSERT Statment funktioniert. Alle ENUM und SET Werte müssen als Strings angegeben werden.


Weiter Zurück [Inhalt] Online Suche im Handbuch