MySQL 8.0-13.2.11.1 The Subquery as Scalar Operand(子查询作为标量操作数)

In its simplest form, a subquery is a scalar subquery that returns a single value. A scalar subquery is a simple operand, and you can use it almost anywhere a single column value or literal is legal, and you can expect it to have those characteristics that all operands have: a data type, a length, an indication that it can be NULL, and so on. For example:

在其最简单的形式中，子查询是返回单个值的标量子查询。标量子查询是一个简单的操作数,你几乎可以在任何地方使用它一个列值或文字是合法的,你可以期望它有这些特征,所有操作数:一个数据类型,长度,表明它可以为空,等等。例如:

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
INSERT INTO t1 VALUES(100, 'abcde');
SELECT (SELECT s2 FROM t1);

The subquery in this SELECT returns a single value ('abcde') that has a data type of CHAR, a length of 5, a character set and collation equal to the defaults in effect at CREATE TABLE time, and an indication that the value in the column can be NULL. Nullability of the value selected by a scalar subquery is not copied because if the subquery result is empty, the result is NULL. For the subquery just shown, if t1 were empty, the result would be NULL even though s2 is NOT NULL.

这个SELECT中的子查询返回一个值('abcde')，该值的数据类型为CHAR，长度为5，字符集和排序规则等于CREATE TABLE时的默认值，并指示列中的值可以为NULL。不复制标量子查询选择的值的空性，因为如果子查询结果为空，则结果为NULL。对于刚才显示的子查询，如果t1为空，结果将是NULL，即使s2不是NULL。

There are a few contexts in which a scalar subquery cannot be used. If a statement permits only a literal value, you cannot use a subquery. For example, LIMIT requires literal integer arguments, and LOAD DATA requires a literal string file name. You cannot use subqueries to supply these values.

在一些上下文中不能使用标量子查询。如果语句只允许文字值，则不能使用子查询。例如，LIMIT需要文字整型参数，而LOAD DATA需要文字字符串文件名。不能使用子查询来提供这些值。

When you see examples in the following sections that contain the rather spartan construct (SELECT column1 FROM t1), imagine that your own code contains much more diverse and complex constructions.

当您在下面的小节中看到包含相当简单的构造(SELECT column1 FROM t1)的示例时，请假设您自己的代码包含更多样化和复杂的构造。

Suppose that we make two tables:

假设我们制作了两个表:

CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);

Then perform a SELECT:

SELECT (SELECT s1 FROM t2) FROM t1;

The result is 2 because there is a row in t2 containing a column s1 that has a value of 2.

In MySQL 8.0.19 and later, the preceding query can also be written like this, using TABLE:

结果是2，因为t2中有一行包含值为2的列s1。
在MySQL 8.0.19及更高版本中，前面的查询也可以这样写，使用TABLE:

SELECT (TABLE t2) FROM t1;

A scalar subquery can be part of an expression, but remember the parentheses, even if the subquery is an operand that provides an argument for a function. For example:

标量子查询可以是表达式的一部分，但请记住括号，即使子查询是为函数提供参数的操作数。例如:

SELECT UPPER((SELECT s1 FROM t1)) FROM t2;

The same result can be obtained in MySQL 8.0.19 and later using SELECT UPPER((TABLE t1)) FROM t2.

在MySQL 8.0.19及以后的版本中使用SELECT UPPER((TABLE t1)) FROM t2也可以得到相同的结果。