MySQL verfügt über eine Funktion zum Exportieren einer Tabelle in die CSV-Datei. Ein CSV-Dateiformat ist ein durch Kommas getrennter Wert, den wir zum Austausch von Daten zwischen verschiedenen Anwendungen wie Microsoft Excel, Goole Docs und Open Office verwenden. Es ist nützlich, MySQL-Daten im CSV-Dateiformat zu haben, damit wir sie nach unseren Wünschen analysieren und formatieren können. Es handelt sich um eine Klartextdatei, die uns hilft, Daten sehr einfach zu exportieren.
MySQL Bietet eine einfache Möglichkeit zum Exportieren beliebiger Tabellen in CSV-Dateien, die sich auf dem Datenbankserver befinden. Wir müssen die folgenden Dinge sicherstellen, bevor wir MySQL-Daten exportieren:
- Der Prozess des MySQL-Servers verfügt über Lese-/Schreibzugriff auf den angegebenen (Ziel-)Ordner, der die CSV-Datei enthält.
- Die angegebene CSV-Datei sollte nicht im System vorhanden sein.
Um die Tabelle in eine CSV-Datei zu exportieren, verwenden wir die AUSWÄHLEN IN....OUTFILE Stellungnahme. Diese Aussage ist ein Kompliment der LADE DATEN Befehl, der zum Schreiben von Daten aus einer Tabelle und zum anschließenden Exportieren in ein bestimmtes Dateiformat auf dem Serverhost verwendet wird. Damit soll sichergestellt werden, dass wir über eine Dateiberechtigung zur Verwendung dieser Syntax verfügen.
SELECT column_lists INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Wir können diese Syntax auch mit einer Werteanweisung verwenden, um Daten direkt in eine Datei zu exportieren. Die folgende Aussage erklärt es deutlicher:
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Wenn wir exportieren wollen alle Tabellenspalten verwenden wir die folgende Syntax. Mit dieser Anweisung wird die Reihenfolge und Anzahl der Zeilen durch gesteuert SORTIEREN NACH Und GRENZE Klausel.
So lesen Sie die CSV-Datei in Java
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
Von Oben,
ZEILEN, DIE MIT ',' BEENDET WERDEN : Es wird verwendet, um die Zeilenzeilen in einer Datei anzugeben, die durch einen Kommaoperator abgeschlossen werden. Jede Zeile enthält die Daten jeder Spalte in der Datei.
Von „“ umschlossene Felder : Wird verwendet, um das Feld der Datei in doppelten Anführungszeichen anzugeben. Es verhindert die Werte, die Kommatrennzeichen enthalten. Wenn die Werte in doppelten Anführungszeichen stehen, erkennt es kein Komma als Trennzeichen.
Speicherort der exportierten Datei
Der Speicherort jeder exportierten Datei in MySQL wird in der Standardvariablen gespeichert secure_file_priv . Wir können den folgenden Befehl ausführen, um den Standardpfad einer exportierten Datei abzurufen.
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Nach der Ausführung wird das Ergebnis wie folgt ausgegeben, wo wir diesen Pfad sehen können: C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/ als Standardspeicherort für Dateien. Dieser Pfad wird zum Zeitpunkt der Ausführung eines Exportbefehls verwendet.
Wenn wir den Standard-Exportspeicherort der im angegebenen CSV-Datei ändern möchten secure_file_priv Variable, wir müssen die bearbeiten meine.ini Konfigurationsdatei. Auf der Windows-Plattform befindet sich diese Datei in diesem Pfad: C:ProgramDataMySQLMySQL Server X.Y .
Wenn wir MySQL-Daten exportieren möchten, müssen wir zunächst eine erstellen Datenbank mit mindestens einem Tisch . Wir werden diese Tabelle als Beispiel verwenden.
Wir können eine erstellen Datenbank und Tabelle indem Sie den folgenden Code in den von uns verwendeten Editoren ausführen:
CREATE DATABASE testdb; USE testdb; CREATE TABLE employee_detail ( ID int NOT NULL AUTO_INCREMENT, Name varchar(45) DEFAULT NULL, Email varchar(45) DEFAULT NULL, Phone varchar(15) DEFAULT NULL, City varchar(25) DEFAULT NULL, PRIMARY KEY (ID), UNIQUE KEY unique_email (Email), UNIQUE KEY index_name_phone (Name,Phone) ) INSERT INTO employee_detail ( Id, Name, Email, Phone, City) VALUES (1, 'Peter', '[email protected]', '49562959223', 'Texas'), (2, 'Suzi', '[email protected]', '70679834522', 'California'), (3, 'Joseph', '[email protected]', '09896765374', 'Alaska'), (4, 'Alex', '[email protected]', '97335737548', 'Los Angeles'), (5, 'Mark', '[email protected]', '78765645643', 'Washington'), (6, 'Stephen', '[email protected]', '986345793248', 'New York');
Wenn wir das ausführen WÄHLEN Anweisung sehen wir die folgende Ausgabe:
CSS-Kommentar
Exportieren Sie MySQL-Daten im CSV-Format mit der SELECT INTO ... OUTFILE-Anweisung
Um die Tabellendaten in eine CSV-Datei zu exportieren, müssen wir die Abfrage wie folgt ausführen:
SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Wir erhalten die folgende Ausgabe, in der wir sehen können, dass sechs Zeilen betroffen sind. Dies liegt daran, dass die angegebene Tabelle nur sechs Zeilen enthält.
Wenn wir dieselbe Anweisung erneut ausführen, erzeugt MySQL eine Fehlermeldung, die in der folgenden Ausgabe zu sehen ist:
Die Fehlermeldung teilt uns mit, dass der angegebene Dateiname bereits am angegebenen Speicherort vorhanden ist. Wenn wir also die neue CSV-Datei mit demselben Namen und Speicherort exportieren, kann sie nicht erstellt werden. Wir können dieses Problem beheben, indem wir entweder die vorhandene Datei am angegebenen Speicherort löschen oder den Dateinamen umbenennen, um sie am selben Ort zu erstellen.
Linux-Verknüpfungen
Wir können überprüfen, ob die CSV-Datei am angegebenen Speicherort erstellt wurde oder nicht, indem wir wie folgt zu einem bestimmten Pfad navigieren:
Wenn wir diese Datei öffnen, sieht sie wie im folgenden Bild aus:
Im Bild sehen wir, dass die numerischen Felder in Anführungszeichen stehen. Wir können diesen Stil durch Hinzufügen ändern OPTIONALLY-Klausel vor ENCLOSED BY :
SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Daten mit Spaltenüberschrift exportieren
Manchmal möchten wir Daten zusammen mit Spaltenüberschriften exportieren, um die Datei übersichtlich zu gestalten. Die exportierte Datei ist verständlicher, wenn die erste Zeile der CSV-Datei die Spaltenüberschriften enthält. Wir können die Spaltenüberschriften hinzufügen, indem wir die verwenden UNION ALLE Aussage wie folgt:
SELECT 'Id', 'Name', 'Email', 'Phone', 'City' UNION ALL SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS TERMINATED BY ';' ENCLOSED BY ''' ESCAPED BY ''' LINES TERMINATED BY ' ';
In dieser Abfrage können wir sehen, dass wir für jeden Spaltennamen eine Überschrift hinzugefügt haben. Wir können die Ausgabe überprüfen, indem wir zur angegebenen URL navigieren, deren erste Zeile die Überschrift für jede Spalte enthält:
Exportieren Sie die MySQL-Tabelle im CSV-Format
Mit MySQL OUTFILE können wir die Tabelle auch exportieren, ohne einen Spaltennamen anzugeben. Wir können die folgende Syntax verwenden, um eine Tabelle in ein CSV-Dateiformat zu exportieren:
TABLE employee_detail ORDER BY City LIMIT 1000 INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Wenn wir die obige Anweisung ausführen, erzeugt unser Befehlszeilentool das folgende Ergebnis. Das bedeutet, dass die angegebene Tabelle sechs Zeilen enthält, die exportiert wurden Employee_backup.csv Datei.
Umgang mit Nullwerten
Manchmal haben die Felder im Ergebnissatz NULL-Werte, dann enthält die Zieldatei (exportierter Dateityp) N statt NULL. Wir können dieses Problem beheben, indem wir den NULL-Wert durch ersetzen „nicht anwendbar (N/A)“ Verwendung der WENN NULL Funktion. Die folgende Aussage erklärt es deutlicher:
SELECT Name, Email, Phone, IFNULL(Phone, 'N/A') FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Exportieren Sie die Tabelle mit MySQL Workbench in das CSV-Format
Wenn wir zum Exportieren der CSV-Datei nicht auf den Datenbankserver zugreifen möchten, bietet MySQL eine andere Möglichkeit, nämlich die Verwendung von MySQL Workbench. Workbench ist ein GUI-Tool zum Arbeiten mit MySQL-Datenbanken ohne Verwendung eines Befehlszeilentools. Es ermöglicht uns, die Ergebnismenge einer Anweisung in ein CSV-Format in unserem lokalen System zu exportieren. Dazu müssen wir die folgenden Schritte ausführen:
Gitterstruktur
- Führen Sie die Anweisung/Abfrage aus und rufen Sie die Ergebnismenge ab.
- Klicken Sie dann im Ergebnisbereich auf 'Recordset in eine externe Datei exportieren' Möglichkeit. Das Recordset wird für die Ergebnismenge verwendet.
- Abschließend wird ein neues Dialogfeld angezeigt. Hier müssen wir einen Dateinamen und sein Format angeben. Nachdem Sie die Details ausgefüllt haben, klicken Sie auf Speichern Taste. Das folgende Bild erklärt es deutlicher:
Jetzt können wir das Ergebnis überprüfen, indem wir zum angegebenen Pfad navigieren.