15.2.13.1 SELECT ... INTO Statement (original) (raw)

15.2.13.1 SELECT ... INTO Statement

The SELECT ... INTO form of SELECT enables a query result to be stored in variables or written to a file:

A given SELECT statement can contain at most one INTO clause, although as shown by the SELECT syntax description (see Section 15.2.13, “SELECT Statement”), theINTO can appear in different positions:

SELECT * INTO @myvar FROM t1;  
SELECT * FROM t1 INTO @myvar FOR UPDATE;  
SELECT * FROM t1 FOR UPDATE INTO @myvar;

The INTO position at the end of the statement is supported as of MySQL 8.0.20, and is the preferred position. The position before a locking clause is deprecated as of MySQL 8.0.20; expect support for it to be removed in a future version of MySQL. In other words, INTO afterFROM but not at the end of theSELECT produces a warning.

An INTO clause should not be used in a nestedSELECT because such aSELECT must return its result to the outer context. There are also constraints on the use ofINTO withinUNION statements; seeSection 15.2.18, “UNION Clause”.

For the INTO_`varlist`_ variant:

SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;

INTO _`varlist`_ can also be used with a TABLE statement, subject to these restrictions:

An example of such a statement is shown here:

TABLE employees ORDER BY lname DESC LIMIT 1
    INTO @id, @fname, @lname, @hired, @separated, @job_code, @store_id;

You can also select values from aVALUES statement that generates a single row into a set of user variables. In this case, you must employ a table alias, and you must assign each value from the value list to a variable. Each of the two statements shown here is equivalent toSET @x=2, @y=4, @z=8:

SELECT * FROM (VALUES ROW(2,4,8)) AS t INTO @x,@y,@z;

SELECT * FROM (VALUES ROW(2,4,8)) AS t(a,b,c) INTO @x,@y,@z;

User variable names are not case-sensitive. SeeSection 11.4, “User-Defined Variables”.

The SELECT ... INTO OUTFILE 'file_name' form ofSELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. filename cannot be an existing file, which among other things prevents files such as/etc/passwd and database tables from being modified. Thecharacter_set_filesystem system variable controls the interpretation of the file name.

The SELECT ... INTO OUTFILE statement is intended to enable dumping a table to a text file on the server host. To create the resulting file on some other host,SELECT ... INTO OUTFILE normally is unsuitable because there is no way to write a path to the file relative to the server host file system, unless the location of the file on the remote host can be accessed using a network-mapped path on the server host file system.

Alternatively, if the MySQL client software is installed on the remote host, you can use a client command such as mysql -e "SELECT ..." >_`filename`_ to generate the file on that host.

SELECT ... INTO OUTFILE is the complement of LOAD DATA. Column values are written converted to the character set specified in the CHARACTER SET clause. If no such clause is present, values are dumped using the binary character set. In effect, there is no character set conversion. If a result set contains columns in several character sets, so is the output data file, and it may not be possible to reload the file correctly.

The syntax for the exportoptions part of the statement consists of the sameFIELDS and LINES clauses that are used with the LOAD DATA statement. For more detailed information about theFIELDS and LINES clauses, including their default values and permissible values, seeSection 15.2.9, “LOAD DATA Statement”.

FIELDS ESCAPED BY controls how to write special characters. If the FIELDS ESCAPED BY character is not empty, it is used when necessary to avoid ambiguity as a prefix that precedes following characters on output:

The FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, or LINES TERMINATED BY characters must be escaped so that you can read the file back in reliably. ASCIINUL is escaped to make it easier to view with some pagers.

The resulting file need not conform to SQL syntax, so nothing else need be escaped.

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

INTO OUTFILE can also be used with aTABLE statement when you want to dump all columns of a table into a text file. In this case, the ordering and number of rows can be controlled usingORDER BY and LIMIT; these clauses must precede INTO OUTFILE.TABLE ... INTO OUTFILE supports the same_exportoptions_ as doesSELECT ... INTO OUTFILE, and it is subject to the same restrictions on writing to the file system. An example of such a statement is shown here:

TABLE employees ORDER BY lname LIMIT 1000
    INTO OUTFILE '/tmp/employee_data_1.txt'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY '\'
    LINES TERMINATED BY '\n';

You can also use SELECT ... INTO OUTFILE with a VALUES statement to write values directly into a file. An example is shown here:

SELECT * FROM (VALUES ROW(1,2,3),ROW(4,5,6),ROW(7,8,9)) AS t
    INTO OUTFILE '/tmp/select-values.txt';

You must use a table alias; column aliases are also supported, and can optionally be used to write values only from desired columns. You can also use any or all of the export options supported by SELECT ... INTO OUTFILE to format the output to the file.

Here is an example that produces a file in the comma-separated values (CSV) format used by many programs:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

If you use INTO DUMPFILE instead ofINTO OUTFILE, MySQL writes only one row into the file, without any column or line termination and without performing any escape processing. This is useful for selecting aBLOB value and storing it in a file.

TABLE also supports INTO DUMPFILE. If the table contains more than one row, you must also use LIMIT 1 to limit the output to a single row. INTO DUMPFILE can also be used with SELECT * FROM (VALUES ROW()[, ...]) AS_`tablealias`_ [LIMIT 1]. SeeSection 15.2.19, “VALUES Statement”.

Note

Any file created by INTO OUTFILE orINTO DUMPFILE is owned by the operating system user under whose account mysqld runs. (You should never runmysqld as root for this and other reasons.) As of MySQL 8.0.17, the umask for file creation is 0640; you must have sufficient access privileges to manipulate the file contents. Prior to MySQL 8.0.17, the umask is 0666 and the file is writable by all users on the server host.

If the secure_file_priv system variable is set to a nonempty directory name, the file to be written must be located in that directory.

In the context ofSELECT ... INTO statements that occur as part of events executed by the Event Scheduler, diagnostics messages (not only errors, but also warnings) are written to the error log, and, on Windows, to the application event log. For additional information, see Section 27.4.5, “Event Scheduler Status”.

As of MySQL 8.0.22, support is provided for periodic synchronization of output files written to by SELECT INTO OUTFILE and SELECT INTO DUMPFILE, enabled by setting theselect_into_disk_sync server system variable introduced in that version. Output buffer size and optional delay can be set using, respectively,select_into_buffer_size andselect_into_disk_sync_delay. For more information, see the descriptions of these system variables.