MySQueaL Resources

SELECT - Exporting results Statement

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.

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.