Recipe 10.13. Exporting Query Results from MySQL

Problem

You want to export the result of a query from MySQL into a file or another program.

Solution

Use the SELECT ... INTO OUTFILE statement, or redirect the output of the mysql program.

Discussion

MySQL provides a SELECT ... INTO OUTFILE statement that exports a query result directly into a file on the server host. If you want to capture the result on the client host instead, another way to export a query is to redirect the output of the mysql program. These methods have different strengths and weaknesses, so you should get to know them both and apply whichever one best suits a given situation.

Exporting with the SELECT ... INTO OUTFILE statement

The syntax for this statement combines a regular SELECT with INTO OUTFILE filename. The default output format is the same as for LOAD DATA, so the following statement exports the passwd table into /tmp/passwd.txt as a tab-delimited, linefeed-terminated file:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/passwd.txt';

You can change the output format using options similar to those used with LOAD DATA that indicate how to quote and delimit columns and records. For example, to export the passwd table in CSV format with CRLF-terminated lines, use this statement:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/passwd.txt'     -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'     -> LINES TERMINATED BY '\r\n';

SELECT ... INTO OUTFILE has the following properties:

The output file is created directly by the MySQL server, so the filename should indicate where you want the file to be written on the server host. The location for the file is determined using the same rules as for LOAD DATA without LOCAL, as described in Section 10.2. There is no LOCAL version of the statement analogous to the LOCAL version of LOAD DATA.
You must have the MySQL FILE privilege to execute the SELECT ... INTO statement.
The output file must not already exist. (This prevents MySQL from clobbering files that may be important.)
You should have a login account on the server host or some way to access files on that host. SELECT ... INTO OUTFILE will be of no value to you if you cannot retrieve the output file.
Under Unix, the file is created world readable and is owned by the account used for running the MySQL server. This means that although you'll be able to read the file, you may not be able to delete it.

Using the mysql client to export data

Because SELECT ... INTO OUTFILE writes the datafile on the server host, you cannot use it unless your MySQL account has the FILE privilege. To export data into a local file, you must use some other strategy. If all you require is tab-delimited output, you can do a "poor-man's export" by executing a SELECT statement with the mysql program and redirecting the output to a file. That way you can write query results into a file on your local host without the FILE privilege. Here's an example that exports the login name and command interpreter columns from the passwd table created earlier in this chapter:

% mysql -e "SELECT account, shell FROM passwd" --skip-column-names \                   cookbook > shells.txt

The -e option specifies the statement to execute, and --skip-column-names tells MySQL not to write the row of column names that normally precedes statement output (Recipes Section 1.14 and Section 1.21).

Note that MySQL writes NULL values as the string "NULL". Some sort of postprocessing may be necessary to convert them, 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 post-processing 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 may vary for different implementations 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, they might be useful when importing the file later.) If so, 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.