MySQL-Daten exportieren

In MySQL können Sie dieSELECT...INTO OUTFILE-Anweisung verwenden, um Daten einfach in eine Textdatei zu exportieren.


Verwenden der SELECT ... INTO OUTFILE-Anweisung zum Exportieren von Daten

SELECT...INTO OUTFILEist die Syntax in MySQL, die verwendet wird, um Abfrageergebnisse in eine Datei zu exportieren.

SELECT...INTO OUTFILEErmöglicht es Ihnen, die Abfrageergebnisse in eine Textdatei zu schreiben. Grundlegende Verwendung:

SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;

Parameter description:

  • column1, column2, ...: Die auszuwählenden Spalten.
  • 'file_path': Geben Sie den Pfad und den Namen der Ausgabedatei an.
  • your_table: Die abzufragende Tabelle.
  • your_conditions: Die Abfragebedingung.

Das folgende ist ein einfaches Beispiel:

Beispiel

SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;

In der obigen SQL-Anweisung haben wir die Spalten id, name und email aus der Tabelle users ausgewählt und die Ergebnisse in die Datei /tmp/user_data.csv geschrieben. FIELDS TERMINATED BY ',' legt das Trennzeichen zwischen Spalten (Komma) fest, und LINES TERMINATED BY '\n' legt das Trennzeichen zwischen Zeilen (Zeilenumbruch) fest.

Beachten Sie, dass zum Ausführen vonSELECT...INTO OUTFILEdie entsprechende Berechtigung erforderlich ist, und das Verzeichnis der Ausgabedatei muss ein Ort sein, an dem der MySQL-Server schreiben kann.

Im folgenden Beispiel exportieren wir die Daten aus der Tabelle example_tbl in die Datei /tmp/example.txt:

mysql> SELECT * FROM example_tbl 
    -> INTO OUTFILE '/tmp/example.txt';

Sie können Befehlsoptionen verwenden, um das spezifische Format für die Datenausgabe festzulegen. Das folgende Beispiel exportiert Daten im CSV-Format:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/example.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

Im folgenden Beispiel wird eine Datei mit durch Kommas getrennten Werten erzeugt. Dieses Format kann von vielen Programmen verwendet werden.

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

Die Anweisung SELECT ... INTO OUTFILE hat die folgenden Eigenschaften:

  • LOAD DATA INFILE ist die umgekehrte Operation von SELECT ... INTO OUTFILE, wobei die SELECT-Syntax verwendet wird. Um Daten aus einer Datenbank in eine Datei zu schreiben, verwenden Sie SELECT ... INTO OUTFILE; um die Datei zurück in die Datenbank zu lesen, verwenden Sie LOAD DATA INFILE.
  • Ein SELECT in der Form SELECT ... INTO OUTFILE 'file_name' kann die ausgewählten Zeilen in eine Datei schreiben. Die Datei wird auf dem Serverhost erstellt, daher müssen Sie das FILE-Privileg besitzen, um diese Syntax zu verwenden.
  • Die Ausgabe darf keine vorhandene Datei sein. Dadurch wird verhindert, dass Dateidaten manipuliert werden.
  • Sie benötigen ein Konto, das beim Server angemeldet ist, um die Datei abzurufen. Andernfalls hat SELECT ... INTO OUTFILE keine Wirkung.
  • Unter UNIX ist die Datei nach ihrer Erstellung lesbar, wobei die Berechtigungen dem MySQL-Server gehören. Das bedeutet, dass Sie die Datei zwar lesen, aber möglicherweise nicht löschen können.

mysqldump: Exportieren von Tabellen als Rohdaten

mysqldump ist ein von MySQL bereitgestelltes Kommandozeilenwerkzeug zum Sichern und Exportieren von Datenbanken.

mysqldumpEs ist ein Hilfsprogramm, das von mysql zum Dumpen von Datenbanken verwendet wird. Es erzeugt hauptsächlich ein SQL-Skript, das die Befehle enthält, die zum Neuerstellen der Datenbank von Grund auf erforderlich sind, wie CREATE TABLE, INSERT usw.

Um Daten mitmysqldumpzu exportieren, müssen Sie die Option--tabverwenden, um das Verzeichnis anzugeben, in dem die exportierten Dateien gespeichert werden. Dieses Ziel muss beschreibbar sein.

Grundlegende Verwendung von mysqldump:

mysqldump -u username -p password -h hostname database_name > output_file.sql

Parameter description:

  • -u: MySQL-Benutzernamen angeben.
  • -p: Zur Eingabe des Passworts auffordern.
  • -h: MySQL-Hostnamen angeben.
  • database_name: Der Name der zu exportierenden Datenbank.
  • output_file.sql: Die Datei, in der die exportierten Daten gespeichert werden.

Das folgende Beispiel exportiert die Tabelle example_tbl in das Verzeichnis /tmp:

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp EXAMPLE example_tbl
password ******

mysqldump-Beispiel

1. Die gesamte Datenbank exportieren

Exportieren Sie die Datenbank mydatabase in die Datei mydatabase_backup.sql:

mysqldump -u root -p mydatabase > mydatabase_backup.sql

2. Eine bestimmte Tabelle exportieren

Wenn Sie nur eine bestimmte Tabelle aus der Datenbank exportieren möchten, können Sie den folgenden Befehl verwenden:

mysqldump -u username -p password -h hostname database_name table_name > output_file.sql

Oder:

mysqldump -u root -p mydatabase mytable > mytable_backup.sql

3. Die Datenbankstruktur exportieren

Wenn Sie nur die Datenbankstruktur exportieren möchten, ohne die Daten einzuschließen, können Sie die--no-data Option verwenden:

mysqldump -u username -p password -h hostname --no-data database_name > output_file.sql

4. Eine komprimierte Datei exportieren

Sie können die exportierten Daten komprimieren, um die Dateigröße zu reduzieren. Zum Beispiel mit gzip:

mysqldump -u username -p password -h hostname database_name | gzip > output_file.sql.gz

Daten im SQL-Format exportieren

Exportieren Sie Daten im SQL-Format in eine angegebene Datei, wie unten gezeigt:

$ mysqldump -u root -p EXAMPLE example_tbl > dump.txt
password ******

Der Inhalt der durch den obigen Befehl erstellten Datei lautet wie folgt:

-- MySQL dump 8.23
--
-- Host: localhost    Database: EXAMPLE
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `example_tbl`
--

CREATE TABLE example_tbl (
  example_id int(11) NOT NULL auto_increment,
  example_title varchar(100) NOT NULL default '',
  example_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (example_id),
  UNIQUE KEY AUTHOR_INDEX (example_author)
) TYPE=MyISAM;

--
-- Dumping data for table `example_tbl`
--

INSERT INTO example_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO example_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO example_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

Wenn Sie die Daten der gesamten Datenbank exportieren müssen, können Sie den folgenden Befehl verwenden:

$ mysqldump -u root -p EXAMPLE > database_dump.txt
password ******

Wenn Sie alle Datenbanken sichern müssen, können Sie den folgenden Befehl verwenden:

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

Die Option --all-databases wurde in MySQL 3.23.12 und späteren Versionen hinzugefügt.

Diese Methode kann verwendet werden, um eine Datenbanksicherungsstrategie umzusetzen.


Tabellen und Datenbanken auf andere Hosts kopieren

Wenn Sie Daten auf einen anderen MySQL-Server kopieren müssen, können Sie den Datenbanknamen und die Tabelle im mysqldump-Befehl angeben.

Führen Sie den folgenden Befehl auf dem Quellhost aus, um die Daten in die Datei dump.txt zu sichern:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

Wenn die gesamte Datenbank gesichert wird, müssen keine spezifischen Tabellennamen verwendet werden.

Wenn Sie die gesicherte Datenbank in den MySQL-Server importieren müssen, können Sie den folgenden Befehl verwenden. Bei Verwendung des folgenden Befehls müssen Sie bestätigen, dass die Datenbank bereits erstellt wurde:

$ mysql -u root -p database_name < dump.txt
password *****

Sie können auch den folgenden Befehl verwenden, um die exportierten Daten direkt in einen Remote-Server zu importieren, aber bitte stellen Sie sicher, dass die beiden Server verbunden sind und aufeinander zugreifen können:

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

Der obige Befehl verwendet eine Pipe, um die exportierten Daten in den angegebenen Remote-Host zu importieren.

Andere Erweiterungen