SELECT
Syntax
1088
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.
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”
.
The
SELECT ... INTO OUTFILE 'file_name'
form of
SELECT
writes the selected rows
to a file. The file is created on the server host, so you must have the
FILE
[577]
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. As of MySQL 5.0.19, the
character_set_filesystem
[442]
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 ..." > file_name
to generate the file on the client host.
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 INFILE
. Column 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 INFILE
statement. See
Section 13.2.6, “
LOAD DATA
INFILE
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
character
• The
FIELDS [OPTIONALLY] ENCLOSED BY
character
• The first character of the
FIELDS TERMINATED BY
and
LINES TERMINATED BY
values
• ASCII
NUL
(the zero-valued byte; what is actually written following the escape character is ASCII “
0
”,
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.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...