9.2 The SELECT … INTO OUTFILE Statement

A SELECT statement normally creates a result set that the server returns to the client. For example, when you issue a SELECT using the mysql client, the server returns the result and mysql writes it in tabular format when run interactively or in tab-delimited format when run in batch mode.

A variation on SELECT syntax adds an INTO OUTFILE clause. This form of SELECT writes the result set directly into a file and thus is the complement of LOAD DATA INFILE. To use SELECT in this way, place the INTO OUTFILE clause before the FROM clause. For example, to write the contents of the Country table into a file named Country.txt, issue this statement:

SELECT * INTO OUTFILE 'Country.txt' FROM Country;

The name of the file indicates the location where you want to write it. MySQL interprets the pathname using the same rules that apply to LOAD DATA INFILE for files located on the server host. For example, given the statement just shown, the server writes the file into the database directory of the default database.

Use of INTO OUTFILE in a SELECT statement results in several operational differences compared to when it isn't used:

• The output produced by a SELECT … INTO OUTFILE statement never leaves the server host. Instead of sending the result over the network to the client, the server writes it to a file on the server host. The output file must not already exist. This prevents files from being overwritten, either accidentally or maliciously.

• Because the statement causes the server to write a new file on the server host, you must have the FILE privilege.

• The file is created with permissions that make it owned by the MySQL server but world-readable.

• The output is written as a set of columns, one line per row selected by the statement. By default, data values are delimited by tab characters and lines are terminated with newlines, but you can control the output format by adding format specifiers after the filename, as described in section 9.3, "Datafile Format Specifiers."

The location and manner in which SELECT … INTO OUTFILE creates the file has several implications:

• If you want to access the file directly, you must have a login account on the server host or be otherwise able to access files on that host somehow. For some purposes, this limitation might not be a problem. For example, you don't need to access the file yourself to reload it later with LOAD DATA INFILE because the MySQL server can read it for you.

• Because the file is world-readable, anyone who has filesystem access on the server host can read it. You probably don't want to use SELECT … INTO OUTFILE to write files that contain sensitive information, unless perhaps you're the only person with access to the machine.

• Because the file is owned by the MySQL server, you might not be able to remove it after you're done with it. It might be necessary to coordinate with the server administrator to arrange for removal of the file.