Importing and Exporting Data - MySQL - page 384

Exporting using the SELECT ... INTO OUTFILE statement
The syntax for this statement combines a regular SELECT with INTO OUTFILE
file_name . 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';
To change the output format, use 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 (created earlier in Recipe 11.1 ) 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 these properties:
• The output file is created directly by the MySQL server, so the filename should
indicate where to write the file on the server host. The file location is determined
using the same rules as for LOAD DATA without LOCAL , as described in Recipe 11.1 .
(There is no LOCAL version of the statement analogous to the LOCAL version of LOAD
DATA .)
• You must have the MySQL FILE privilege to execute the SELECT … INTO OUTFILE
statement.
• The output file must not already exist. (This prevents MySQL from overwriting
files that may be important.)
• You should have a login account on the server host or some way to access files on
that host. SELECT … INTO OUTFILE is of no value to you if you cannot retrieve the
output file.
• Under Unix, the file is created world readable and is owned by the account used for
running the MySQL server. This means that although you can read the file, you
may not be able to delete it unless you can log in using that account.
Exporting using the mysql client program
Because SELECT … INTO OUTFILE writes the datafile on the server host, you cannot use
it unless your MySQL account has the FILE privilege. To export data into a local file
owned by yourself, use another strategy. If all you require is tab-delimited output, do a
“poor-man's export” by executing a SELECT statement with the mysql program and re‐
directing the output to a file. That way you can write query results into a file on your
local host without the FILE privilege. Here's an example that exports the login name and
command interpreter columns from the passwd table: