Exporting Query Results from MySQL

10.14.1 Problem

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

10.14.2 Solution

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

10.14.3 Discussion

MySQL provides a SELECT ... INTO OUTFILE statement that exports a query result directly into a file on the server host. Another way to export a query, if you want to capture the result on the client host instead, 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.

10.14.4 Exporting with the SELECT ... INTO OUTFILE Statement

The syntax for this statement combines a regular SELECT with INTO OUTFILE filename at the end. 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. 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 '
';

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. 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 retrieve the file from that host. Otherwise, SELECT ... INTO OUTFILE likely will be of no value to you.
• Under Unix, the file is created world readable and is owned by the MySQL server. This means that although you'll be able to read the file, you may not be able to delete it.

10.14.5 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" -N cookbook > shells.txt

The -e option specifies the query to execute, and -N tells MySQL not to write the row of column names that normally precedes query output. The latter option was added in MySQL 3.22.20; if your version is older than that, you can achieve the same end by telling mysql to be "really silent" with the -ss option instead:

% mysql -e "SELECT account, shell FROM passwd" -ss cookbook > shells.txt

Note that NULL values are written 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 -N -e " your query here " db_name  | sed -e "s/ TAB /#/g" > output_file 

You can also use tr for this purpose, though the syntax may vary for different implementations of this utility. The command looks like this for Mac OS X or RedHat Linux:

% mysql -N -e " your query here " db_name  | tr "	" "#" > output_file 

The mysql commands just shown use -N or -ss to suppress column labels from appearing in the output. Under some circumstances, it may be useful to include the labels. (For example, they may be useful when importing the file later.) If so, omit the label-suppression 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.

10.14.6 See Also

Another way to export query results to a file on the client host is by using the mysql_to_text.pl utility described in Recipe 10.18. That program has options that allow you to specify the output format explicitly. To export a query result as an Excel spreadsheet or for use with FileMaker Pro, see Recipes Recipe 10.40 and Recipe 10.41.