10.14 Exporting Query Results from MySQL
10.14.1 Problem
You want to export the result of a query
from MySQL into a file or another program.
10.14.2 Solution
Use the SELECT ...
INTO OUTFILE statement or
redirect the output of the mysql program.
10.14.3 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.
10.14.4 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 should indicate where you want the file to be written on the
server host. 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
retrieve the file from that host. Otherwise,
SELECT ... INTO
OUTFILE likely will be of no value to you.
Under Unix, the file is created world readable and is owned by the
MySQL server. This means that although you'll be
able to read the file, you may not be able to delete it.
10.14.5 Using the mysql Client to Export Data
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" -N cookbook > shells.txt
The
-e option specifies the query to execute,
and
-N tells MySQL not to write the row of
column names that normally precedes query output. The latter option
was added in MySQL 3.22.20; if your version is older than that, you
can achieve the same end by telling
mysql to be "really
silent" with the -ss option
instead:
% mysql -e "SELECT account, shell FROM passwd" -ss cookbook > shells.txt
Note that NULL values are written 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 -N -e " your query here " db_name | sed -e "s/ TAB /#/g" > output_file
You can also use tr for this purpose, though the
syntax may vary for different implementations of this utility. The
command looks like this for Mac OS X or RedHat Linux:
% mysql -N -e " your query here " db_name | tr "\t" "#" > output_file
The mysql commands just shown use
-N or -ss to suppress column labels
from appearing in the output. Under some circumstances, it may be
useful to include the labels. (For example, they may be useful when
importing the file later.) If so, omit the label-suppression 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.
10.14.6 See Also
Another way to export query results to a file on the client host is
by using the mysql_to_text.pl utility described
in Recipe 10.18. That program has options that allow
you to specify the output format explicitly. To export a query result
as an Excel spreadsheet or for use with FileMaker Pro, see Recipes
Recipe 10.40 and Recipe 10.41.
| 
No comments:
Post a Comment