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.
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 ' ';
SELECT
... INTO
OUTFILE
has the following
properties:
The output file is created directly by the MySQL server,
so the filename should indicate where you want the file to be
written on the server host. The location for the file is
determined using the same rules as for LOAD
DATA
without LOCAL
, as described in Specifying the Datafile Location. 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
statement.
The output file must not already exist. (This prevents MySQL from clobbering 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
will be 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’ll be able to read the file, you may not be able to delete it.
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, you must use some other strategy. If all you require is
tab-delimited output, you can do a “poor-man’s export”
by executing a SELECT
statement
with the mysql program and
redirecting 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 created earlier in
this chapter:
%mysql -e "SELECT account, shell FROM passwd" --skip-column-names
cookbook > shells.txt
The -e
option specifies the statement to
execute, and
--skip-column-names
tells MySQL not
to write the row of column names that normally precedes statement
output (Recipes and ).
Note that MySQL writes NULL
values as the string “NULL”. Some sort of
postprocessing may be necessary to convert them, depending on what
you want to do with the output file.
It’s possible to produce output in formats other than
tab-delimited by sending the query result into a post-processing
filter that converts tabs to something else. For example, to use
hash marks as delimiters, convert all tabs to #
characters
(TAB
indicates where you type a tab
character in the command):
%mysql --skip-column-names -e "
your statement here
"
db_name
| sed -e "s/
TAB
/#/g" >
output_file
You can also use tr for this purpose, although the syntax may vary for different implementations of this utility. For Mac OS X or Linux, the command looks like this:
%mysql --skip-column-names -e "
your statement here
"
db_name
| tr " " "#" >
output_file
The mysql commands just
shown use --skip-column-names
to suppress column
labels from appearing in the output. Under some circumstances, it
may be useful to include the labels. (For example, they might be
useful when importing the file later.) If so, omit the
--skip-column-names
option from the command. In
this respect, exporting query results with mysql is more flexible than SELECT
... INTO
OUTFILE
because the latter cannot produce
output that includes column labels.
Another way to export query results to a file on the client host is to use the mysql_to_text.pl utility described in Writing Your Own Export Programs. That program has options that enable you to specify the output format explicitly. To export a query result as an Excel spreadsheet, see Exchanging Data Between MySQL and Microsoft Excel.
3.15.144.170