1.21 Sending Query Output to a File or to a Program
1.21.1 Problem
You want to send
mysql output somewhere other than to your screen.
1.21.2 Solution
Redirect mysql's output or use a
pipe.
1.21.3 Discussion
mysql chooses its default output format according
to whether you run it interactively or non-interactively. Under
interactive use, mysql normally sends its output
to the terminal and writes query results using tabular format:
mysql> SELECT * FROM limbs;
+--------------+------+------+
| thing | legs | arms |
+--------------+------+------+
| human | 2 | 2 |
| insect | 6 | 0 |
| squid | 0 | 10 |
| octopus | 0 | 8 |
| fish | 0 | 0 |
| centipede | 100 | 0 |
| table | 4 | 0 |
| armchair | 4 | 2 |
| phonograph | 0 | 1 |
| tripod | 3 | 0 |
| Peg Leg Pete | 1 | 2 |
| space alien | NULL | NULL |
+--------------+------+------+
12 rows in set (0.00 sec)
In non-interactive mode
(that is, when either the input or output is redirected),
mysql writes output in tab-delimited format:
% echo "SELECT * FROM limbs" | mysql cookbook
thing legs arms
human 2 2
insect 6 0
squid 0 10
octopus 0 8
fish 0 0
centipede 100 0
table 4 0
armchair 4 2
phonograph 0 1
tripod 3 0
Peg Leg Pete 1 2
space alien NULL NULL
However, in either context, you can select any of
mysql's output formats by using
the appropriate command-line options. This section describes how to
send mysql output somewhere other than the
terminal. The next several sections discuss the various
mysql output formats and how to select them
explicitly according to your needs when the default format
isn't what you want.
To save output from mysql in a file, use your
shell's standard redirection capability:
% mysql cookbook > outputfile
However, if you try to run mysql interactively
with the output redirected, you won't be able to see
what you're typing, so generally in this case
you'll also take query input from a file (or another
program):
% mysql cookbook < inputfile > outputfile
You can also send query output to another program. For example, if
you want to mail query output to someone, you might do so like this:
% mysql cookbook < inputfile | mail paul
Note that because mysql runs non-interactively in
that context, it produces tab-delimited output, which the mail
recipient may find more difficult to read than tabular output. Recipe 1.22 shows how to fix this problem.
|
