MySQL Cookbook, 2nd Edition

Sending Query Output to a File or to a Program

Problem

You want mysql output to go somewhere other than your screen.

Solution

Redirect mysql’s output, or use a pipe .

Discussion

mysql chooses its default output format according to whether you run it interactively or noninteractively. 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 noninteractive 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 using the appropriate command option. 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

If you 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 statement input from a file (or another program):

%mysql cookbook < 
               inputfile 
               > 
               outputfile

You can also send statement 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 noninteractively in that context, it produces tab-delimited output, which the mail recipient may find more difficult to read than tabular output. Selecting Tabular or Tab-Delimited Query Output Format shows how to fix this problem.