MySQL-Zertifizierung, Kapitel 15 ohne 15.3.2

15.1 Import- and Export Operations

Eine wichtiger Aspekt in jeder Anwendung, die Datensätze verwaltet, sind Import- und Export-Funktionen. MySQL unterstützt diese Funktionen natürlich auch. Hier gibt es zwei Möglichkeiten:

1.) Im MySQL-Client (MySQL-Prompt)
Wenn man über den MySQL-Client mit dem MySQL-Server verbunden ist, kann man mit LOAD DATA INFILE Daten importieren und mit SELECT ... INTO OUTFILE Daten exportieren.

2.) Über die Kommandozeile
MySQL bietet die Programme mysqlimport und mysqldump an, um Daten aus einer Datenbank zu importieren und exportieren.

15.2.1 Importing Data with LOAD DATA INFILE

Mit dem Befehl LOAD DATA INFILE können Datensätze von einer externen Datei in eine Tabelle importiert werden.

LOAD DATA INFILE 'dateiname' INTO TABLE tbl01;

Der Dateiname muss in Hochkommata geschrieben werden. Windows-Anwender müssen aufpassen, wenn ein absoluter Pfad angegeben wird. '\' interpretiert MySQL als Steuerzeichen, so dass man für den Pfad entweder / oder \\ verwenden muss:

# Windows-Anwender müssen die Pfadangabe anpassen
LOAD DATA INFILE 'C:/data/bck-01-02-2009.sql' INTO TABLE tbl01;
LOAD DATA INFILE 'C:\\data\\bck-01-02-2009.sql' INTO TABLE tbl01;

Die Syntax von LOAD DATA INFILE in der Übersicht:

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,...)]

15.2.1.1 Specifying the Data File Location

Standardmäßig sucht MySQL die zu importierende Datei auf dem Serverhost, wenn mit LOAD DATA INFILE Datensätze importiert werden sollen. Wenn sich die Daten aber nicht auf dem Server, sondern auf dem Client befinden, muss der Befehl um LOCAL erweitert werden:

LOAD DATA INFILE = Datei auf dem Server-Host importieren
LOAD DATA LOCAL INFILE = Datei auf dem Client-Host importieren

Wichtig: Verzeichnisangabe bei LOAD DATA INFILE
Wenn eine relative Pfadangabe erfolgt, so sucht MySQL in dem Datenverzeichnis der Standard-Datenbank von MySQL. Wenn die Standard-Datenbank beispielsweise default lautet, so hieße das entsprechende Verzeichnis /var/mysql/data/default/. Ausgehend von diesem Verzeichnis sucht MySQL die Daten:

# Drei Möglichkeiten
LOAD DATA INFILE '/var/mysql/data/default/backup.txt' INTO TABLE tbl01;
LOAD DATA INFILE 'backup.txt' INTO TABLE tbl01;
LOAD DATA INFILE './default/backup.txt' INTO TABLE tbl01;

Wichtig: Verzeichnisangabe bei LOAD DATA LOCAL INFILE
Bei einer relativen Pfadangabe für LOAD DATA LOCAL INFILE wird nicht von dem Standard-Datenbank-Verzeichnis ausgegangen, sondern von dem Verzeichnis, wo das Programm gestartet wurde.

15.2.1.2 Skipping Data File Lines

Mit den Schlüsselwörtern IGNORE x LINES können die ersten x-Zeilen ignoriert werden:

# Die ersten 20 Zeilen werden ignoriert
LOAD DATA INFILE 'backup.txt' INTO TABLE tbl01 IGNORE 20 LINES;

15.2.1.3 Skipping Data File Lines

Wenn man mit LOAD DATA INFILE Datensätze importiert, geht man davon aus, dass die Zeilen komplett und die Spalten in der richtigen Reihenfolge sind. Wenn zuviele Spalten pro Zeile in der Datei existieren, werden die überflüssigen Spalten verworfen. Enthalten die Zeilen zu wenige Spalten, so werden die Datensätze (ähnlich beim INSERT-Befehl) mit den Standardwerten aufgefüllt.

Stellen wir uns folgendes Szenario vor: Wir haben eine Tabelle mit den Spalten vorname und name. Die zu importierende Datei enthält Zeilen, in denen die Spalten vertauscht sind. Man kann den LOAD DATA INFILE Befehl nun folgendermaßen formulieren, um die Daten in richtiger Reihenfolge zu importieren:

# Spalten explizit angeben
LOAD DATA INFILE 'backup.txt' INTO TABLE tbl01 (name, vorname);

15.2.1.4 Skipping or Transforming Column Values

Es ist möglich, gewisse Spalten nicht zu importieren. Dazu kann man so genannte Benutzervariablen definieren. Wenn man beispielsweise eine Spalte nicht importieren möchte, gibt man für die Spalte einen beliebigen Namen beginnend mit einem @ an, beispielsweise @skip an. Das @ sagt MySQL, dass es sich um eine Variable bedeutet. Wenn man jetzt keinen SET-Statement angibt, werden die Daten nicht übernommen, sprich sie werden nicht importiert.

# Die ertse spalte nicht importieren
LOAD DATA INFILE 'backup.txt'
INTO TABLE tbl01 (@skip, name, vorname, alter);

Man kann aber in Verbindung mir SET auch nützliche Operationen durchführen. Dazu setzt man in der Spaltenangabe bei der entsprechende Spalte eine Benutzervariable und verarbeitet diese dann nach der SET-Anweisung weiter:

# Die erste Spalte wird verworfen,
# Vorname und Nachname werden in eine Spalte zusammengefügt
LOAD DATA INFILE 'backup.txt'
INTO TABLE tbl01 (@skip, @name, @vorname, alter)
SET name=CONCAT(@vorname,' '@nachname);

# Das Alter verdoppeln
LOAD DATA INFILE 'backup.txt'
INTO TABLE tbl01 (id, name, vorname, @var1)
SET alter=@var1*2;

15.2.1.7 Privileges Required for LOAD DATA INFILE

Für die Anweisung LOAD DATA INFILE benötigt man mindestens INSERT-Rechte. LOAD DATA INFILE in Verbindung mit REPLACE erfordert auch noch die DELETE-Rechte, denn vorhandene Datensätze werden dann gelöscht und durch die neuen ersetzt.

15.2.1.8 Efficiency of LOAD DATA INFILE

LOAD DATA INFILE ist effizienter als INSERT-Statements, da weniger Overhead produziert wird. Das gilt für Import-Dateien, die sowohl lokal auf dem Server liegen oder über das Netzwerk transportiert werden müssen.

15.2.2 Exporting Data with SELECT ... INTO OUTFILE

INTO OUTFILE wird dazu genutzt, um Datensätze zu exportieren. Der Standard-Speicherort ist das Verzeichnis der Standard-Datenbank. Unter Gentoo Linux wäre das ein Verzeichnis in /var/lib/mysql. Wie bei LOAD DATA INFILE müssen bei INTO OUTFILE die zu schreibenden Dateien in ' ' gesetzt werden. Ohne weitere Angabe speichert INTO OUTFILE die Daten in einem vorbestimmten Format, nämlich mit Tabs separiert. Folgendes Beispiel soll die Funktionsweise verdeutlichen.

mysql> SELECT * FROM logging;
+-------+------+
| name | logs |
+-------+------+
| peter | 1 |
| hans | 3 |
| anja | 1 |
| meik | 1 |
+-------+------+
4 rows in set (0.00 sec)

mysql> SELECT * INTO OUTFILE 'backup.txt' FROM logging;
Query OK, 4 rows affected (0.00 sec)

# cd /var/lib/mysql/test/
# cat backup.txt
peter 1
hans 3
anja 1
meik 1

15.2.3 Data File Format Specifiers

Im vorher gezeigten Beispiel wurden Daten in dem Standard-Format exportiert, so dass die einzelnen Datensätze beispielsweise durch Tabs getrennt und nicht mit Hochkommata eingebunden wurden. Oftmals ist es aber notwendig, die Ausgabe anzupassen. In diesem Fall ist es möglich, die Anweisungen LOAD DATA INFILE und INTO OUTFILE um die Schlüsselwörter TERMINATED BY, ENCLOSED BY, ESCAPED BY, LINES TERMINATED BY zu erweitern.

# Syntax
FIELDS
TERMINATED BY 'string'
ENCLOSED BY 'char'
ESCAPED BY 'char'
'string'

# Wenn man keine Angaben macht, werden diese
# Standardwerte genutzt
FIELDS
TERMINATED BY '\t'
ENCLOSED BY ''
ESCAPED BY '\\'

Die LINE-Sequenz bezieht sich auf das Ende der Zeile. Dieses ist ganz wichtig, denn mit LINES TERMINATED BY wird eine neue Zeile und somit auch ein neuer Datensatz angezeigt. Hier gibt es bei Windows- und UNIX-Systemen unterschiedliche Konventionen. Jeder hat wahrscheinlich schon einmal das Problem gehabt, dass eine UNIX-Textdatei im Windows Notepad aus nur einer einzigen Zeile besteht. Grund dafür sind die unterschiedlichen "line terminators".

#  \n steht für Newline, wird unter UNIX verwendet
LINES TERMINATED BY \n

# \r steht für Carriage return
LINES TERMINATED BY \r

# Für Windows muss man beides angeben
LINES TERMINATED BY \n\r

In folgendem Beispiel soll die Funktionsweise näher erläutert werden:

# Beispiel: Ausgabe als CSV-Datei
SELECT * INTO OUTFILE 'backup.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM logging;
Query OK, 4 rows affected (0.00 sec)

# cat backup.txt
"peter","1"
"hans","3"
"anja","1"
"meik","1"

Hier eine Übersicht über die wichtigsten Squenzen:

\N = NULL Value
\0 = NUL (zero) byte
\b = Backspace
\n = Newline
\r = Carriage return
\s = Space
\t = Tab
\' = Single quote
\" = Double quote
\\ = Backslash

15.2.4 Importing and Exporting NULL Values

NULL steht in MySQL bekanntlich für "Wert nicht vorhanden" oder "Wert unbekannt". Beim Ex-/Import mit LOAD DATA INFILE werden Nullwerte als \N deklariert. Exportiert man also Datensätze aus einer Tabelle, welches NULL-Werte sind, so werden diese in der Datei als \N gespeichert.

15.3.1 Importing Data with mysqlimport

Was man mit LOAD DATA INFILE in dem MySQL-Client machen kann, erledigt mysqlimport über die Shell bzw. Eingabeaufforderung. Somit kann man mithilfe von Skripten den Import automatisieren oder einfach den Import ohne den MySQL-Client durchführen.

# Syntax von MySQLImport
mysqlimport [options] db_name textfile1 textfile2 ...

# Wichtige Optionen
--lines-terminated-by=name
Lines in the i.file are terminated by ...
--fields-terminated-by=name
Fields in the textfile are terminated by ...
--fields-enclosed-by=name
Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=name
Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=name

Wichtig: mysqlimport ist nicht mit dem Import mithilfe von mysql zu verwechseln und keine Dump-Files importieren, die mit mysqldump erstellt wurden!

Hier einige Beispiele:

# Import von backup.txt in die DB db_namen.
# Angabe des Zeichens für das Zeilenende
mysqlimport --lines-terminated-by="\r\n" db_namen backup.txt

# Angabe, dass die einzelnen Werte mit " umklammert sind
mysqlimport --fields-enclosed-by='"' db_namen backup.txt

# Angabe, dass die einzelnen Werte mit , beendet werden
mysqlimport --fields-terminated-by=, db_namen backup.txt

Was passiert bei doppelten Einträgen?

# Doppelte Werte werden verworfen
mysqlimport --ignore db_namen backup.txt

# Doppelte Werte werden durch die neuen Werte ersetzt/überschrieben
mysqlimport --replace db_namen backup.txt