Rechercher dans le manuel MySQL
13.2.10.1 SELECT ... INTO Syntax
The SELECT ...
INTO
form of SELECT
enables a query result to be stored in variables or written to a
file:
SELECT ... INTO
selects column values and stores them into variables.var_list
SELECT ... INTO OUTFILE
writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format.SELECT ... INTO DUMPFILE
writes a single row to a file without any formatting.
The SELECT
syntax description
(see Section 13.2.10, “SELECT Syntax”) shows the INTO
clause near the end of the statement. It is also possible to use
INTO
immediately following the
select_expr
list.
An INTO
clause should not be used in a nested
SELECT
because such a
SELECT
must return its result to
the outer context.
The INTO
clause can name a list of one or
more variables, which can be user-defined variables, stored
procedure or function parameters, or stored program local
variables. (Within a prepared SELECT ... INTO
OUTFILE
statement, only user-defined variables are
permitted;see Section 13.6.4.2, “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
1
to limit the result set to a single row.
User variable names are not case-sensitive. See Section 9.4, “User-Defined Variables”.
The SELECT ... INTO
OUTFILE '
form of
file_name
'SELECT
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. file_name
cannot be an
existing file, which among other things prevents files such as
/etc/passwd
and database tables from being
destroyed. The
character_set_filesystem
system
variable controls the interpretation of the file name.
The SELECT ... INTO
OUTFILE
statement is intended primarily to let you
very quickly dump a table to a text file on the server machine.
If you want to create the resulting file on some other host than
the server host, you normally cannot use
SELECT ... INTO
OUTFILE
since there is no way to write a path to the
file relative to the server host's file system.
However, if the MySQL client software is installed on the remote
machine, you can instead use a client command such as
mysql -e "SELECT ..." >
to generate the
file on the client host.
file_name
It is also possible to create the resulting file on a different host other than the server host, if the location of the file on the remote host can be accessed using a network-mapped path on the server's file system. In this case, the presence of mysql (or some other MySQL client program) is not required on the target 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, the output data file will as well and
you may not be able to reload the file correctly.
The syntax for the export_options
part of the statement consists of the same
FIELDS
and LINES
clauses
that are used with the LOAD DATA
statement. See Section 13.2.7, “LOAD DATA Syntax”, for information
about the FIELDS
and LINES
clauses, including their default values and permissible values.
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 ESCAPED BY
characterThe
FIELDS [OPTIONALLY] ENCLOSED BY
characterThe first character of the
FIELDS TERMINATED BY
andLINES TERMINATED BY
valuesASCII
NUL
(the zero-valued byte; what is actually written following the escape character is ASCII0
, not a zero-valued byte)
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. ASCII
NUL
is escaped to make it easier to view with
some pagers.
The resulting file does not have to 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.
Here is an example that produces a file in the comma-separated values (CSV) format used by many programs:
- 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 if you want to
store a BLOB
value in a file.
Any file created by INTO OUTFILE
or
INTO DUMPFILE
is owned by the operating
system user under whose account mysqld run.
(You should never run
mysqld 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 of
SELECT ...
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 24.4.5, “Event Scheduler Status”.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-select-into.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.