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.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net