SELECT - Exporting results

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SELECT [flags] {*|columns|expression}[, ...]
[INTO OUTFILE '/path/filename'
  [FIELDS TERMINATED BY 'character']
  [FIELDS ENCLOSED BY 'character']
  [ESCAPED BY 'character' ]
  [LINES [STARTING BY 'character'] [TERMINATED BY 'character']]
|INTO DUMPFILE '/path/filename'
|INTO 'variable'[, ...]
[FOR UPDATE|LOCK IN SHARE MODE]]
FROM table[, ...]
[WHERE condition]
[other clauses] [options]

Explanation

The INTO clause is used to export data from a SELECT statement to an external text file or a variable. Only the results will be exported, not the column names or other information.

Various clauses set delimiter and control characters in the output:

  • ESCAPED BY
  • Character used to escape special characters in the output. The default is a backslash.

  • FIELDS ENCLOSED BY
  • Character to use before and after each field. By default, no character is used.

  • FIELDS TERMINATED BY
  • Character with which to separate fields. The default is a tab.

  • LINES STARTING BY
  • Character used to start each lines. By default, no character is used.

  • LINES TERMINATED BY
  • Character used to end each line. The default is a newline character.

FILE privilege is necessary to use the INTO clause of the SELECT statement. This statement and clause combination is in essence the counterpart of the LOAD DATA INFILE statement. See the explanation of that statement for more details on the options for this clause.

Examples

Here is an example of this clause and these options:

SELECT * FROM employees
INTO OUTFILE '/tmp/employees.txt'
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '
'
ESCAPED BY '\';

The text file created by this SQL statement will contain a separate line for each row selected. Each field will end with a vertical bar. Any special characters (e.g., an apostrophe) will be preceded by a backslash. Because a backslash is an escape character within an SQL statement, two backslashes are needed in the ESCAPE BY clause, because the first escapes the second. To import the resulting data text file, use the LOAD DATA INFILE statement.

The second syntax uses the clause INTO DUMPFILE and exports only one row into an external text file. It does not allow any field or line terminators like the INTO OUTFILE clause. Here is an example of its use:

SELECT photograph
INTO DUMPFILE '/tmp/bobs_picture.jpeg'
FROM employees
WHERE emp_id = '1827';

This statement exports the contents of the photograph column for an employee's record. It's a BLOB type column and contains an image file. The result of the exported file is a complete and usable image file.

You can also use the INTO clause to store a value in a user variable or a system variable for reuse. Here's an example:

SET @sales = 0;

SELECT SUM(total_order) AS Sales
INTO @sales
FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE());

This example creates the user variable @sales. Then we calculate the total sales for the current year and stores it into that variable for reuse in subsequent statements in the session.