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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.