INTO form of
enables a query result to be stored in variables or written to a
SELECT ... INTOselects column values and stores them into variables.
SELECT ... INTO OUTFILEwrites the selected rows to a file. Column and line terminators can be specified to produce a specific output format.
SELECT ... INTO DUMPFILEwrites a single row to a file without any formatting.
SELECT statement can
contain at most one
INTO clause, although as
shown by the
description (see Section 13.2.9, “SELECT Statement”), the
INTO can appear in different positions:
SELECT * INTO @myvar FROM t1;
Before a trailing locking clause. Example:
SELECT * FROM t1 INTO @myvar FOR UPDATE;
INTO clause should not be used in a nested
SELECT because such a
SELECT must return its result to
the outer context. There are also constraints on the use of
UNION statements; see
Section 22.214.171.124, “UNION Clause”.
var_listnames a list of one or more variables, each of which can be a user-defined variable, stored procedure or function parameter, or stored program local variable. (Within a prepared
SELECT ... INTOstatement, only user-defined variables are permitted; see Section 126.96.36.199, “Local Variable Scope and Resolution”.)
The selected values are assigned to the variables. The number of variables must match the number of columns. The query should return a single row. If the query returns no rows, a warning with error code 1329 occurs (
No data), and the variable values remain unchanged. If the query returns multiple rows, error 1172 occurs (
Result consisted of more than one row). If it is possible that the statement may retrieve multiple rows, you can use
LIMIT 1to limit the result set to a single row.
SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;
User variable names are not case-sensitive. See Section 9.4, “User-Defined Variables”.
SELECT ... INTO
OUTFILE ' form of
SELECT writes the selected rows
to a file. The file is created on the server host, so you must
FILE privilege to use
file_name cannot be an
existing file, which among other things prevents files such as
/etc/passwd and database tables from being
variable controls the interpretation of the file name.
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
Alternatively, if the MySQL client software is installed on the
remote host, you can use a client command such as
-e "SELECT ..." >
to generate the
file on that host.
SELECT ... INTO
OUTFILE is the complement of
DATA. Column values are written converted to the
character set specified in the
clause. If no such clause is present, values are dumped using
binary character set. In effect, there is
no character set conversion. If a result set contains columns in
several character sets, so does the output data file and it may
not be possible to reload the file correctly.
The syntax for the
part of the statement consists of the same
that are used with the
statement. For information about the
LINES clauses, including their default
values and permissible values, see Section 13.2.6, “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
FIELDS ESCAPED BYcharacter
FIELDS [OPTIONALLY] ENCLOSED BYcharacter
The first character of the
FIELDS TERMINATED BYand
LINES TERMINATED BYvalues
NUL(the zero-valued byte; what is actually written following the escape character is ASCII
0, not a zero-valued byte)
FIELDS TERMINATED BY,
ESCAPED BY, or
TERMINATED BY characters must be
escaped so that you can read the file back in reliably. ASCII
NUL is escaped to make it easier to view with
The resulting file need not conform to SQL syntax, so nothing else need be escaped.
FIELDS ESCAPED BY character is empty,
no characters are escaped and
NULL is output
\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.
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 of
INTO 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 a
BLOB value and storing it in a
Any file created by
INTO OUTFILE or
INTO DUMPFILE is writable by all users on
the server host. The reason for this is that the MySQL server
cannot create a file that is owned by anyone other than the
user under whose account it is running. (You should
never run mysqld as
root for this and other reasons.) The file
thus must be world-writable so that you can manipulate its
system variable is set to a nonempty directory name, the file
to be written must be located in that directory.
In the context of
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 23.4.5, “Event Scheduler Status”.