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.
Another way to export a query, if you want to capture the result on
the client host instead, 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 at the
end. 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. 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 ... INTO
OUTFILE has the following properties:
The output file is created directly by the MySQL server, so the filename ...