The SELECT INTO OUTFILE SQL statement is actually a variant of the SELECT query. It is used when you want to direct query output to a text file. This file can then be opened by a spreadsheet application, or imported into another database like Microsoft Access, Oracle, or any other software that supports delimitation. Its general syntax format follows:
SELECT [select options go here] INTO {OUTFILE | DUMPFILE} filename
EXPORT_OPTIONS
FROM table_references [additional select options go here]
Key Command Options of SELECT INTO OUTFILE
- OUTFILE: Selecting this option causes the query result to be output to the text file. The formatting of the query result is dependent upon how the EXPORT OPTIONS are set.
- DUMPFILE: Selecting this option over OUTFILE results in the query results being written as a single line, omitting column or line terminations. This is useful when exporting binary data such as a graphic or a Word file. Keep in mind that you cannot choose OUTFILE when exporting a binary file, or the file will be corrupted. Also, note that a DUMPFILE query must target a single row; combining output from two binary files doesn’t make any sense, and an error will be returned if you attempt it.
- EXPORT OPTIONS: The export options determine how the table fields and lines will be delimited in the outfile. Their syntax and rules match exactly those used in LOAD DATA INFILE.
Important Tips About usage of SELECT INTO OUTFILE
- If a target file path is not specified, the directory of the present database is used.
- The executing user must possess the selection privilege (SELECT_PRIV) for the target table(s).
- If a target file path is specified, the MySQL daemon owner must possess adequate privileges to write to the target directory.
- One unexpected side effect of this process is that it leaves the target file world-readable and -writeable. Therefore, if you’re scripting the backup process, you’ll probably want to change the file permissions programmatically once the query has completed.
- The query will fail if the target text file already exists.
- Export options cannot be included if the target text file is a dump file.
SELECT INTO OUTFILE - Simple Data Export Example
Suppose you want to export books data to a tab-delimited text file, consisting of lines delimited by newline characters:
SELECT * INTO OUTFILE "/backup/books/allbooks.txt"
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
FROM books;