MySQL :: MySQL 8.4 参考手册 :: 15.2.19 值声明

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 15 条 SQL 语句 / 15.2 数据操作语句 / 15.2.19 值声明

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

15.2.19 VALUES 语句15.2.19 VALUES Statement

VALUES 是一个 DML 语句，返回一组或多行作为表。换句话说，它是一个表值构造函数，同时也作为独立的 SQL 语句运行。VALUES is a DML statement which returns a set of one or more rows as a table. In other words, it is a table value constructor which also functions as a standalone SQL statement.

VALUES row_constructor_list [ORDER BY column_designator] [LIMIT number]

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

value_list:
    value[, value][, ...]

column_designator:
    column_index

VALUES 语句由 VALUES面跟一个或多个行构造函数列表组成，中间用逗号分隔。行构造子由 ROW() 行构造子子句组成，括号内包含一个或多个标量值的值列表。一个值可以是任何 MySQL 数据类型的文字，也可以是解析为标量值的表达式。The VALUES statement consists of the VALUES keyword followed by a list of one or more row constructors, separated by commas. A row constructor consists of the ROW() row constructor clause with a value list of one or more scalar values enclosed in the parentheses. A value can be a literal of any MySQL data type or an expression that resolves to a scalar value.

ROW() 不能为空（但每个提供的标量值都可以是 NULL每个 ROW() 在同一 VALUES 语句的值列表必须有相同数量的值。ROW() cannot be empty (but each of the supplied scalar values can be NULL). Each ROW() in the same VALUES statement must have the same number of values in its value list.

DEFAULT 关键字不被 VALUES，导致语法错误，除非用于提供 INSERT 语句。The DEFAULT keyword is not supported by VALUES and causes a syntax error, except when it is used to supply values in an INSERT statement.

VALUES 是一个表：The output of VALUES is a table:

mysql> VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |       -2 |        3 |
|        5 |        7 |        9 |
|        4 |        6 |        8 |
+----------+----------+----------+
3 rows in set (0.00 sec)

表的列输出 VALUES 的隐式列名为 column_0，column_1，column_2，依此类推，始终以 0。这一事实可以用可选的 ORDER BY，就像该子句与 SELECT 语句，如图所示：The columns of the table output from VALUES have the implicitly named columns column_0, column_1, column_2, and so on, always beginning with 0. This fact can be used to order the rows by column using an optional ORDER BY clause in the same way that this clause works with a SELECT statement, as shown here:

mysql> VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8) ORDER BY column_1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |       -2 |        3 |
|        4 |        6 |        8 |
|        5 |        7 |        9 |
+----------+----------+----------+
3 rows in set (0.00 sec)

VALUES 语句还支持 LIMIT 子句，用于限制输出行数。VALUES statement also supports a LIMIT clause for limiting the number of rows in the output.

VALUES 语句对列值的数据类型是宽容的;你可以在同一列内混合类型，如图所示：The VALUES statement is permissive regarding data types of column values; you can mix types within the same column, as shown here:

mysql> VALUES ROW("q", 42, '2019-12-18'),
    ->     ROW(23, "abc", 98.6),
    ->     ROW(27.0002, "Mary Smith", '{"a": 10, "b": 25}');
+----------+------------+--------------------+
| column_0 | column_1   | column_2           |
+----------+------------+--------------------+
| q        | 42         | 2019-12-18         |
| 23       | abc        | 98.6               |
| 27.0002  | Mary Smith | {"a": 10, "b": 25} |
+----------+------------+--------------------+
3 rows in set (0.00 sec)

Important

VALUES 与一个或多个 ROW() 作为表值构造子;尽管它也可以用于在 INSERT 或 REPLACE 语句，不要将其与同样用于此目的的 VALUES。你也不应将其与 VALUES()，后者指代列值 INSERT ... ON DUPLICATE KEY UPDATE时。VALUES with one or more instances of ROW() acts as a table value constructor; although it can be used to supply values in an INSERT or REPLACE statement, do not confuse it with the VALUES keyword that is also used for this purpose. You should also not confuse it with the VALUES() function that refers to column values in INSERT ... ON DUPLICATE KEY UPDATE.

你还应注意，ROW() 是行值构造子（参见第 15.2.15.5 节，“行子查询”），而 VALUES ROW() 是表值构造器;两者不能互换使用。You should also bear in mind that ROW() is a row value constructor (see Section 15.2.15.5, “Row Subqueries”), whereas VALUES ROW() is a table value constructor; the two cannot be used interchangeably.

VALUES 可以在许多情况下使用，比如 SELECT包括此处列出的：VALUES can be used in many cases where you could employ SELECT, including those listed here:

With UNION如图所示：With UNION, as shown here:

mysql> SELECT 1,2 UNION SELECT 10,15;
+----+----+
| 1  | 2  |
+----+----+
|  1 |  2 |
| 10 | 15 |
+----+----+
2 rows in set (0.00 sec)

mysql> VALUES ROW(1,2) UNION VALUES ROW(10,15);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 |        2 |
|       10 |       15 |
+----------+----------+
2 rows in set (0.00 sec)

你可以将多行构造表合并，比如这样：You can union together constructed tables having more than one row, like this:

mysql> VALUES ROW(1,2), ROW(3,4), ROW(5,6)
     >     UNION VALUES ROW(10,15),ROW(20,25);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 |        2 |
|        3 |        4 |
|        5 |        6 |
|       10 |       15 |
|       20 |       25 |
+----------+----------+
5 rows in set (0.00 sec)

你也可以（通常更倾向地）省略 UNION并使用一个 VALUES 语句，比如这样：You can also (and it is usually preferable to) omit UNION altogether in such cases and use a single VALUES statement, like this:

mysql> VALUES ROW(1,2), ROW(3,4), ROW(5,6), ROW(10,15), ROW(20,25);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 |        2 |
|        3 |        4 |
|        5 |        6 |
|       10 |       15 |
|       20 |       25 |
+----------+----------+

VALUES 也可以用于SELECT 语句，TABLE 语句，或两者兼有。VALUES can also be used in unions with SELECT statements, TABLE statements, or both.

构造表在 UNION 必须包含相同的列数，就像你使用 SELECT。更多示例请参见第 15.2.18 节，”UNION Clause“。The constructed tables in the UNION must contain the same number of columns, just as if you were using SELECT. See Section 15.2.18, “UNION Clause”, for further examples.

您可以使用EXCEPT以及INTERSECT设置为VALUES in much the same way as UNION, as shown here:You can use EXCEPT and INTERSECT with VALUES in much the same way as UNION, as shown here:

mysql> VALUES ROW(1,2), ROW(3,4), ROW(5,6)
    ->   INTERSECT 
    -> VALUES ROW(10,15), ROW(20,25), ROW(3,4);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        3 |        4 |
+----------+----------+
1 row in set (0.00 sec)
 
mysql> VALUES ROW(1,2), ROW(3,4), ROW(5,6)
    ->   EXCEPT 
    -> VALUES ROW(10,15), ROW(20,25), ROW(3,4);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        1 |        2 |
|        5 |        6 |
+----------+----------+
2 rows in set (0.00 sec)

参见 Section 15.2.4， “EXCEPT Clause”，第 15.2.8 节，“INTERSECT Clause”，更多信息。See Section 15.2.4, “EXCEPT Clause”, and Section 15.2.8, “INTERSECT Clause”, for more information.

在 join 中。更多信息和示例请参见第 15.2.13.2 节，“JOIN Clause”。In joins. See Section 15.2.13.2, “JOIN Clause”, for more information and examples.
In place of VALUES() in an INSERT或者REPLACE statement, in which case its semantics differ slightly from what is described here. See Section 15.2.7, “INSERT Statement”, for details.In place of VALUES() in an INSERT or REPLACE statement, in which case its semantics differ slightly from what is described here. See Section 15.2.7, “INSERT Statement”, for details.
在 CREATE TABLE ... SELECT 和 CREATE VIEW ... SELECT。请参阅这些语句的描述以获取更多信息和示例。In place of the source table in CREATE TABLE ... SELECT and CREATE VIEW ... SELECT. See the descriptions of these statements for more information and examples.

首页上一级下一节