MySQL Cookbook, 2nd Edition

Exporting Query Results from MySQL

Problem

You want to export the result of a query from MySQL into a file or another program.

Solution

Use the SELECT ... INTO OUTFILE statement, or redirect the output of the mysql program.

Discussion

MySQL provides a SELECT ... INTO OUTFILE statement that exports a query result directly into a file on the server host. If you want to capture the result on the client host instead, another way to export a query is to redirect the output of the mysql program. These methods have different strengths and weaknesses, so you should get to know them both and apply whichever one best suits a given situation.

Exporting with the SELECT ... INTO OUTFILE statement

The syntax for this statement combines a regular SELECT with INTO OUTFILE filename. The default output format is the same as for LOAD DATA, so the following statement exports the passwd table into /tmp/passwd.txt as a tab-delimited, linefeed-terminated file:

mysql>SELECT * FROM passwd INTO OUTFILE '/tmp/passwd.txt';

You can change the output format using options similar to those used with LOAD DATA that indicate how to quote and delimit columns and records. For example, to export the passwd table in CSV format with CRLF-terminated lines, use this statement:

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

SELECT ... INTOOUTFILE has the following properties:

• The output file is created directly by the MySQL server, so the filename ...