MySQL Server Blog

Ich möchte einen weniger bekannten MySQL-SQL-Befehl hervorheben, analysieren, wie er funktioniert und einige der Entscheidungen verstehen, die bei seiner Verwendung getroffen werden müssen.

Lassen Sie uns mit den Grundlagen beginnen: Das Referenzhandbuch.

Es hat eine Menge über LOAD DATA zu sagen. Aber wir werden uns auf LOAD DATA INFILE konzentrieren. In Kurzform hat es diese Syntax:

LOAD DATA

INFILE 'file_name'

INTO TABLE tbl_name
...)]


ENCLOSED BY 'char']

]


]

...)]
...]

OK, dies ist also ein Befehl, um die Daten in einer Textdatei in eine Tabelle zu laden.

Lassen Sie es uns ausprobieren.

Lassen Sie uns die folgende Tabellendefinition verwenden:
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT);

Und die folgende Datendatei (data.txt):

1,1
2,2
3,3
4,4

Aber … Hmm.

Das ist also ein SQL-Befehl, der auf einem Server-Computer von einem Client-Computer ausgeführt wird. Ich weiß, wie ich die Tabelle erstellen kann. Aber wo lege ich meine Datendatei ab?

Zurück zum Referenzhandbuch:

Wenn LOCAL nicht angegeben ist, muss sich die Datei auf dem Server-Host befinden und wird direkt vom Server gelesen.

OK, also muss ich data.txt auf meinem Server ablegen. Versuchen wir es mal:

mysql> create table t1 (a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected (0.05 sec)

mysql> select @@datadir;
+--------------------------------------------+
| @@datadir |
+--------------------------------------------+
| /home/gkodinov/work/mysql-8.0/bld/datadir/ |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> exit;
Bye

~/work/mysql-8.0/bld/mysql-test: mkdir /home/gkodinov/work/mysql-8.0/bld/datadir/textdata/

~/work/mysql-8.0/bld/mysql-test: cat > /home/gkodinov/work/mysql-8.0/bld/datadir/textdata/data.txt
1,1
2,2
3,3
4,4
~/work/mysql-8.0/bld/mysql-test: mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16-debug Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> load data infile 'data/data.txt' into table t1 fields terminated by ',';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Hmm !

mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)

Dieser NULL-Wert (bedeutet „keinen Dateizugriff erlauben“) verhindert, dass der Server auf eine beliebige lokale Datei im Dateisystem zugreifen kann. OKAY. Das scheint eine vernünftige Voreinstellung zu sein.

Nun lassen wir ihn der Fortsetzung halber auf Dateien im Unterverzeichnis data des Datenverzeichnisses zugreifen, indem wir folgendes hinzufügen:

--secure-file-priv=/home/gkodinov/work/mysql-8.0/bld/datadir/textdata

zum mysqld.

Zweiter Versuch:

mysql> select @@datadir,@@secure_file_priv\G
*************************** 1. row ***************************
@@datadir: /home/gkodinov/work/mysql-8.0/bld/datadir/
@@secure_file_priv: /export/home/tmp/gkodinov/work/mysql-8.0/bld/datadir/textdata/
1 row in set (0.00 sec)

mysql> load data infile 'textdata/data.txt' into table t1 fields terminated by ',';
ERROR 1261 (01000): Row 1 doesn't contain data for all columns

Hmm, sieht so aus, als ob wir den Dateizugriffszaun überwunden haben. Aber, wie der Server richtig bemerkt hat, haben wir 3 Spalten in der Tabelle und nur 2 Spalten für jede Zeile in data.txt. Lassen Sie uns auch das beheben:

mysql> load data infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | NULL |
| 2 | 2 | NULL |
| 3 | 3 | NULL |
| 4 | 4 | NULL |
+---+------+------+
4 rows in set (0.00 sec)

Perfekt!

Wie Sie sehen können, funktioniert LOAD DATA INFILE ziemlich ähnlich wie das Importieren von Textdaten in eine MS Excel-Tabelle. Und es hat eine ziemlich vernünftige Standardkonfiguration, um zu verhindern, dass Leute zufällige Daten über das Dateisystem des Servers lesen. Sie müssen Ihre Daten nur in einem geeigneten Verzeichnis des Server-Rechners haben, und schon können Sie sie importieren.

Die Vorteile, sich diese zusätzliche Mühe zu machen, liegen vor allem darin, dass Sie nur eine einzige INSERT-Anweisung parsen müssen und sie dann einfach so oft ausführen, wie es Zeilen gibt.

So weit so gut. Aber … hmmm … haben Sie Ihre Daten auf dem Server-Rechner? Damit Sie sie importieren und wieder im Datenverzeichnis auf demselben Server-Rechner ablegen können?

Das mag bei kleinen Dateien funktionieren. Aber wenn Sie viele, viele Daten haben, dann ist es aus Platzgründen vielleicht gar nicht möglich, zwei verschiedene Kopien davon auf demselben Computer zu haben.

Aber wie Sie sich von früher erinnern, gibt es zwei Computer, die an der Ausführung eines SQL-Befehls beteiligt sind: der Server-Computer und der Client-Computer. Was wäre, wenn wir den Dateispeicherplatz des letzteren nutzen und die Daten irgendwie über die Client/Server-Verbindung zum ersteren streamen könnten (zusammen mit dem SQL-Befehl selbst)?

Das ist genau das, was der optionale LOCAL-Modifikator tut!

Aber hey, das würde eine Netzwerkprotokoll-Erweiterung erfordern: Sie haben eine Menge zusätzlicher Daten zu senden. Schauen wir mal, wie das auf dem Draht aussieht. Dazu könnten wir natürlich Wireshark oder ein anderes Tool zur Analyse von Netzwerkdaten verwenden. Aber zum Glück müssen wir das nicht.

Die Drahtformate des mysql-Client/Server-Protokolls sind in der Doxygen-Dokumentation des Servers dokumentiert. Sie hat das folgende Diagramm:

Im Grunde funktioniert es also so:

1. Der Client sendet den COM_QUERY, um den SQL-Befehl auszuführen, wie er es normalerweise tut.
   • In unserem Fall ist das „LOAD DATA LOCAL INFILE datafile/data.txt“
2. Anstatt ein Resultset oder einen Status zu senden, sendet der Server eine spezielle Anfrage (0xFB) zusammen mit dem Dateinamen der Datei, die der Client lesen und senden soll
   • In unserem Fall ist dies „datafile/data.txt“
3. Der Client beginnt, den Inhalt der vom Server angeforderten Datei zu streamen, gefolgt von einem leeren (null langen) Paket, um das Ende der Datei zu kennzeichnen
4. Der Server führt den Befehl aus und verwendet die gesendeten Daten und fährt fort, wie er es normalerweise mit jedem anderen DML-Befehl tun würde und sendet ein OK- (oder ein ERR-) Paket, um das Ergebnis der Ausführung zu kennzeichnen.

Gut! Aber … Moment …

Warum muss der Server einen Dateinamen an den Client senden, um daraus zu lesen? Hat er nicht einfach einen SQL-Befehl mit demselben Dateinamen bereitgestellt?

Ja, das hat er. Aber leider (oder auch nicht) ist SQL nicht die am einfachsten zu parsende Sprache. Um den Dateinamen zu kennen, muss der Client lokal einen vollwertigen Parser für den MySQL-SQL-Dialekt haben, der *synchron* mit dem entsprechenden Parser auf dem Server ist. Knifflig !

Deshalb versucht der Client, das ohnehin vom Server durchgeführte Parsing zu nutzen und erwartet, dass dieser das empfangene Statement parst und das Bit liefert, das der Client braucht: den Dateinamen zum Lesen.

OK, verschieben wir unsere Datendatei auf den Client-Rechner und probieren das Ganze mal aus:

mysql> load data local infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);
ERROR 1148 (42000): The used command is not allowed with this MySQL version

Was?

Die nicht sehr freundliche Fehlermeldung besagt, dass LOAD DATA LOCAL INFILE deaktiviert ist. Aber warum?

RefMan hilft wieder (über -local-infile):

Diese Variable steuert die serverseitige LOCAL-Fähigkeit für LOAD DATA-Anweisungen. Je nach Einstellung von local_infile verweigert oder erlaubt der Server das Laden von lokalen Daten durch Clients, die LOCAL auf der Client-Seite aktiviert haben.

Und die Voreinstellung dieser Variable ist seit 8.0.2 OFF.

OK, lassen Sie uns dies aktivieren:

mysql> select @@local_infile;

+----------------+
| @@local_infile |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)

mysql> load data local infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);
ERROR 1148 (42000): The used command is not allowed with this MySQL version

Hmm, funktioniert immer noch nicht ?! Was ist los?!

Erinnern Sie sich an die obige Erklärung zum Netzwerkaustausch? Der Client erhält vom Server den Namen einer Datei, aus der er lesen soll. Der Client muss also selbst entscheiden, ob er dem Server und dem von ihm gesendeten Dateinamen vertrauen will.

Diese Situation wird in dieser Referenzhandbuchseite sehr gut erklärt. Dort wird auch erwähnt, dass der Client signalisieren muss, dass er dem Server und der Verbindung zu ihm vertraut, indem er eine zusätzliche Option (oder mysql_options()-Option) hinzufügt: -local-infile.

Lassen Sie uns das mal ausprobieren:

~/work/mysql-8.0/bld/datadir: ../runtime_output_directory/mysql --local-infile=1 <your options here>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16-debug Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> load data local infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

Nett! Endlich !

Ok, also lassen Sie uns rekapitulieren, was nötig ist, damit LOAD DATA LOCAL INFILE funktioniert:

1. Sie müssen dem Server sagen, dass er es für seine Clients aktivieren soll, indem Sie -local-infile auf dem Server angeben.
2. Sie müssen Ihren mysql-Client anweisen, es für alle SQL-Anweisungen zu aktivieren. Oder Sie verwalten es auf einer feineren Basis (z. B. pro Verbindung), wenn Sie die C-API verwenden.
3. Und natürlich müssen Sie die richtige Datei haben und die richtigen Importoptionen angeben.

Ich weiß, es ist eine große Aufgabe und eine starke Einschränkung der Benutzbarkeit. Aber es ist es auf jeden Fall wert, wenn man bedenkt, was für eine Magie es ist, einen Haufen Textdaten über die Leitung zu übertragen und sie in eine relationale Tabelle auf dem Server zu importieren. Und das alles mit einem einzigen Befehl.

Danke, dass Sie MySQL benutzen!“