MySQL 8.0-13.2.10.1 SELECT ... INTO Statement

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

SELECT ……INTO SELECT的形式允许查询结果存储在变量中或写入文件:

• SELECT ... INTO var_list selects column values and stores them into variables.

• SELECT ……INTO 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 OUTFILE将选中的行写入文件。可以指定列和行结束符以产生特定的输出格式。

• SELECT ... INTO DUMPFILE writes a single row to a file without any formatting.

• SELECT ……INTO DUMPFILE在不进行任何格式化的情况下将一行写入文件。

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

一个给定的SELECT语句最多可以包含一个INTO子句，尽管如SELECT语法描述(参见13.2.10节，“SELECT语句”)所示，INTO可以出现在不同的位置:

• Before FROM. Example:

  SELECT * INTO @myvar FROM t1;

• Before a trailing locking clause. Example:

• 在尾随锁定子句之前。例子

  SELECT * FROM t1 INTO @myvar FOR UPDATE;

• At the end of the SELECT. Example:在SELECT的末尾。例子:

  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 after FROM but not at the end of the SELECT produces a warning.

在MySQL 8.0.20中，语句末尾的INTO位置是受支持的，并且是首选位置。锁子句之前的位置在MySQL 8.0.20中已被弃用;希望在未来的MySQL版本中删除对它的支持。换句话说，INTO在FROM之后而不是在SELECT的末尾产生一个警告。

An 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 INTO within UNION statements; see Section 13.2.10.3, “UNION Clause”.

不应该在嵌套的SELECT中使用INTO子句，因为这样的SELECT必须将其结果返回到外部上下文。在UNION语句中使用INTO也有限制;见第13.2.10.3节“UNION条款”。

For the INTO var_list variant:

对于INTO var_list变量:

• var_list names 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 ... INTO var_list statement, only user-defined variables are permitted; see Section 13.6.4.2, “Local Variable Scope and Resolution”.)

• Var_list命名一个或多个变量的列表，每个变量可以是用户定义的变量、存储过程或函数参数，或存储的程序局部变量。(在一个准备好的SELECT…在var_list语句中，只允许用户定义的变量;请参见13.6.4.2节“局部变量作用域和分辨率”。)

• 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.

• 将选定的值分配给变量。变量的数量必须与列的数量匹配。查询应该返回单个行。如果查询没有返回任何行，则会出现错误代码1329的警告(没有数据)，变量值保持不变。如果查询返回多行，则发生错误1172 (Result包含不止一行)。如果语句可能检索多个行，则可以使用LIMIT 1将结果集限制为单个行。

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

INTO var_list can also be used with a TABLE statement, subject to these restrictions:

INTO var_list也可以和TABLE语句一起使用，有以下限制:

• The number of variables must match the number of columns in the table.

• 变量的数量必须与表中的列数匹配。

• If the table contains more than one row, you must use LIMIT 1 to limit the result set to a single row. LIMIT 1 must precede the INTO keyword.

• ：如果表包含多行，则必须使用LIMIT 1将结果集限制为单个行。LIMIT 1必须在INTO关键字之前。

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 a VALUES 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 to SET @x=2, @y=4, @z=8:

您还可以从values语句中选择值，该语句会将一行生成到一组用户变量中。在这种情况下，您必须使用表别名，并且必须将值列表中的每个值赋给一个变量。这里显示的两个语句中的每一个都等价于SET @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. See Section 9.4, “User-Defined Variables”.

用户变量名不区分大小写。参见第9.4节“用户定义的变量”。

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 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 modified. The character_set_filesystem system variable controls the interpretation of the file name.

选择……INTO OUTFILE 'file_name'形式的SELECT将选中的行写入文件。该文件是在服务器主机上创建的，因此必须具有file权限才能使用此语法。File_name不能是现有的文件，这样可以防止/etc/passwd和数据库表等文件被修改。character_set_filesystem系统变量控制文件名的解释。

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.

选择……INTO OUTFILE语句旨在启用将表转储到服务器主机上的文本文件。要在其他主机上创建结果文件，请使用SELECT…INTO OUTFILE通常是不合适的，因为没有办法相对于服务器主机文件系统写入文件的路径，除非可以使用服务器主机文件系统上的网络映射路径访问文件在远程主机上的位置

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

或者，如果MySQL客户端软件安装在远程主机上，您可以使用客户端命令，如MySQL -e "SELECT…"> file_name以在该主机上生成文件。

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.

选择……INTO OUTFILE是LOAD DATA的补充。将列值转换为character set子句中指定的字符集。如果没有这样的子句，则使用二进制字符集转储值。实际上，不存在字符集转换。如果结果集包含多个字符集中的列，则输出数据文件也是如此，并且可能无法正确地重新加载文件。
​

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. For information about the FIELDS and LINES clauses, including their default values and permissible values, see Section 13.2.7, “LOAD DATA Statement”.

语句export_options部分的语法由与LOAD DATA语句相同的FIELDS和LINES子句组成。有关FIELDS和LINES条款的信息，包括它们的默认值和允许值，请参见第13.2.7节“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:

FIELDS escapes BY控制如何编写特殊字符。如果FIELDS escape BY字符不为空，则在必要时使用它作为输出中以下字符之前的前缀，以避免歧义:

• 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.

必须对字段TERMINATED BY、ENCLOSED BY、escape BY或LINES TERMINATED BY字符进行转义，以便能够可靠地读取文件。ASCII NUL进行了转义，以使某些寻呼机更容易查看。

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

结果文件不需要符合SQL语法，因此不需要转义其他内容。

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.

如果FIELDS escape BY字符为空，则没有字符被转义，NULL将输出为NULL，而不是\N。指定空转义字符可能不是一个好主意，特别是当数据中的字段值包含刚才给出的列表中的任何字符时。

INTO OUTFILE can also be used with a TABLE 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 using ORDER BY and LIMIT; these clauses must precede INTO OUTFILE. TABLE ... INTO OUTFILE supports the same export_options as does SELECT ... 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:

当您想要将表的所有列转储到文本文件中时，INTO OUTFILE还可以与TABLE语句一起使用。在本例中，可以使用ORDER BY和LIMIT控制行的顺序和数量;这些子句必须放在INTO OUTFILE之前。表格INTO OUTFILE支持与SELECT相同的export_options…INTO OUTFILE，并且它在写入文件系统时也受到相同的限制。下面是这种说法的一个例子:

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…使用VALUES语句将值直接写入文件。下面是一个例子:

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.

必须使用表别名;也支持列别名，可以选择仅从所需列写入值。您还可以使用SELECT…支持的任何或所有导出选项。INTO OUTFILE以格式化文件的输出。

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

下面是一个使用逗号分隔值(CSV)格式生成文件的示例，该格式被许多程序使用:

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 file.

如果使用INTO DUMPFILE而不是INTO OUTFILE, MySQL只写入一行到文件中，没有任何列或行终止，也没有执行任何转义处理。这对于选择BLOB值并将其存储在文件中非常有用。

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 table_alias [LIMIT 1]. See Section 13.2.14, “VALUES Statement”.

TABLE还支持INTO DUMPFILE。如果表包含不止一行，还必须使用LIMIT 1将输出限制为一行。INTO DUMPFILE也可以与SELECT * FROM (VALUES ROW()[，…])AS table_alias [LIMIT 1]一起使用。参见13.2.14节“VALUES Statement”。

Note

Any file created by INTO OUTFILE or INTO DUMPFILE is owned by the operating system user under whose account mysqld runs. (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.

由INTO OUTFILE或INTO DUMPFILE创建的任何文件都由mysqld帐户下运行的操作系统用户拥有。(由于这个和其他原因，您不应该以root身份运行mysqld。)在MySQL 8.0.17中，创建文件的umask是0640;您必须有足够的访问权限来操作文件内容。在MySQL 8.0.17之前，umask是0666，该文件可以被服务器主机上的所有用户写入。

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.

如果secure_file_priv系统变量设置为非空目录名，则要写入的文件必须位于该目录中。

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 25.4.5, “Event Scheduler Status”.

在SELECT…对于作为事件调度程序执行的事件的一部分发生的语句，诊断消息(不仅是错误，还有警告)被写入错误日志，在Windows上则写入应用程序事件日志。有关更多信息，请参见25.4.5节“事件调度程序状态”。

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 the select_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 and select_into_disk_sync_delay. For more information, see the descriptions of these system variables.

在MySQL 8.0.22中，通过设置该版本中引入的select_into_disk_sync服务器系统变量，提供了对SELECT INTO OUTFILE和SELECT INTO DUMPFILE写入的输出文件的定期同步支持。输出缓冲区大小和可选延迟可以分别使用select_into_buffer_size和select_into_disk_sync_delay设置。有关更多信息，请参见这些系统变量的描述。