1.22 Selecting Tabular or Tab-Delimited Query Output Format

1.22.1 Problem

mysql produces tabular output when you want tab-delimited output, or vice versa.

1.22.2 Solution

Select the desired format explicitly with the appropriate command-line option.

1.22.3 Discussion

When you use mysql non-interactively (such as to read queries from a file or to send results into a pipe), it writes output in tab-delimited format by default. Sometimes it's desirable to produce tabular output instead. For example, if you want to print or mail query results, tab-delimited output doesn't look very nice. Use the -t (or --table) option to produce tabular output that is more readable:

% mysql -t cookbook <  inputfile  | lpr 
% mysql -t cookbook <  inputfile  | mail paul 

The inverse operation is to produce batch (tab-delimited) output in interactive mode. To do this, use -B or --batch.

1.23 Specifying Arbitrary Output Column Delimiters

1.23.1 Problem

You want mysql to produce query output using a delimiter other than tab.

1.23.2 Solution

Postprocess mysql's output.

1.23.3 Discussion

In non-interactive mode, mysql separates output columns with tabs and there is no option for specifying the output delimiter. Under some circumstances, it may be desirable to produce output that uses a different delimiter. Suppose you want to create an output file for use by a program that expects values to be separated by colon characters (:) rather than tabs. Under Unix, you can convert tabs to arbitrary delimiters by using utilities such as tr and sed. For example, to change tabs to colons, any of the following commands would work (TAB indicates where you type a tab character).

The syntax for some versions of tr may be different; consult your local documentation. Also, some shells use the tab character for special purposes such as filename completion. For such shells, type a literal tab into the command by preceding it with Ctrl-V.

% mysql cookbook <  inputfile   | sed -e "s/ TAB /:/g" >  outputfile 
% mysql cookbook <  inputfile   | tr " TAB " ":" >  outputfile 
% mysql cookbook <  inputfile   | tr "\011" ":" >  outputfile 

sed is more powerful than tr because it understands regular expressions and allows multiple substitutions. This is useful when you want to produce output in something like comma-separated values (CSV) format, which requires three substitutions:

• Escape any quote characters that appear in the data by doubling them so that when you use the resulting CSV file, they won't be taken as column delimiters.

• Change the tabs to commas.

• Surround column values with quotes.

sed allows all three subsitutions to be performed in a single command:

% mysql cookbook <  inputfile   \ 
    | sed -e 's/"/""/g' -e 's/ TAB /","/g' -e 's/^/"/' -e 's/$/"/' >  outputfile 

That's fairly cryptic, to say the least. You can achieve the same result with other languages that may be easier to read. Here's a short Perl script that does the same thing as the sed command (it converts tab-delimited input to CSV output), and includes comments to document how it works:

#! /usr/bin/perl -w
while (<>)              # read next input line
{
    s/"/""/g;           # double any quotes within column values
    s/\t/","/g;         # put `","' between column values
    s/^/"/;             # add `"' before the first value
    s/$/"/;             # add `"' after the last value
    print;              # print the result
}
exit (0);

If you name the script csv.pl, you can use it like this:

% mysql cookbook <  inputfile   | csv.pl >  outputfile 

If you run the command under a version of Windows that doesn't know how to associate .pl files with Perl, it may be necessary to invoke Perl explicitly:

C:\> mysql cookbook <  inputfile   | perl csv.pl >  outputfile 

Perl may be more suitable if you need a cross-platform solution, because it runs under both Unix and Windows. tr and sed normally are unavailable under Windows.

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.

1.20.1 Problem

Query output zooms off the top of your screen before you can see it.

1.20.2 Solution

Tell mysql to display output a page at a time, or run mysql in a window that allows scrollback.

1.20.3 Discussion

If a query produces many lines of output, normally they just scroll right off the top of the screen. To prevent this, tell mysql to present output a page at a time by specifying the --pager option. --pager=program tells mysql to use a specific program as your pag

The --pager option is not available under Windows.

% mysql --pager=/usr/bin/less

--pager by itself tells mysql to use your default pager, as specified in your PAGER environment variable:

% mysql --pager

If your PAGER variable isn't set, you must either define it or use the first form of the command to specify a pager program explicitly. 

Within a mysql session, you can turn paging on and off using \P and \n. \P without an argument enables paging using the program specified in your PAGER variable. \P with an argument enables paging using the argument as the name of the paging program:

mysql> \P
PAGER set to /bin/more
mysql> \P /usr/bin/less
PAGER set to /usr/bin/less
mysql> \n
PAGER set to stdout

Output paging was introduced in MySQL 3.23.28.

Another way to deal with long result sets is to use a terminal program that allows you to scroll back through previous output. Programs such as xterm for the X Window System, Terminal for Mac OS X, MacSSH or BetterTelnet for Mac OS, or Telnet for Windows allow you to set the number of output lines saved in the scrollback buffer. Under Windows NT, 2000, or XP, you can set up a DOS window that allows scrollback using the following procedure:

Now you should be able to launch the shortcut to get a scrollable DOS window that allows output produced by commands in that window to be retrieved by using the scrollbar.

1.18.1 Problem

You want to specify a query directly on the command line for mysql to execute.

1.18.2 Solution

mysql can read a query from its argument list. Use the -e (or --execute) option to specify a query on the command line.

1.18.3 Discussion

For example, to find out how many records are in the limbs table, run this command:

% mysql -e "SELECT COUNT(*) FROM limbs" cookbook
+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+

To run multiple queries with the -e option, separate them with semicolons:

% mysql -e "SELECT COUNT(*) FROM limbs;SELECT NOW( )" cookbook
+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+
+---------------------+
| NOW( )               |
+---------------------+
| 2001-07-04 10:42:22 |
+---------------------+

1.18.4 See Also

By default, results generated by queries that are specified with -e are displayed in tabular format if output goes to the terminal, and in tab-delimited format otherwise. To produce a different output style