Database Reference
In-Depth Information
%
mysql -e "SELECT account, shell FROM passwd" --skip-column-names \
cookbook > shells.txt
The
-e
option specifies the statement to execute (see
Recipe 1.5
), and
--skip-column-
names
tells MySQL not to write the row of column names that normally precedes state‐
ment output (see
Recipe 1.7
).
Note that MySQL writes
NULL
values as the string “NULL”. Some postprocessing to
convert them may be needed, 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 postprocessing 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 varies for different implemen‐
tations 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 "\t" "#" >
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, if they will useful when importing the file later.) In that case,
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.
See Also
Another way to export query results to a file on the client host is to use the
mysql_to_text.pl
utility described in
Recipe 11.5
. That program has options that enable
you to specify the output format explicitly. To export a query result as an Excel spread‐
sheet or XML document, see Recipes
11.8
and
11.9
.
11.4. Importing and Exporting NULL Values
Problem
You need to represent
NULL
values in a datafile.
