Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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. 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.

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 '\r\n';

SELECT ... INTO OUTFILE has the following properties:

  • The output file is created directly by the MySQL server, so the filename ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata