MySQL 8.0-13.2.10 SELECT Statement

最新推荐文章于 2023-11-25 23:09:59 发布

KING BOMB

最新推荐文章于 2023-11-25 23:09:59 发布

阅读量412

点赞数

分类专栏： Mysql 文章标签： mysql sql 数据库

原文链接：https://dev.mysql.com/doc/refman/8.0/en/select.html

版权

Mysql 专栏收录该内容

142 篇文章 2 订阅

订阅专栏

13.2.10.1 SELECT ... INTO Statement

13.2.10.2 JOIN Clause

13.2.10.3 UNION Clause

13.2.10.4 Parenthesized Query Expressions

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

SELECT is used to retrieve rows selected from one or more tables, and can include UNION statements and subqueries. See Section 13.2.10.3, “UNION Clause”, and Section 13.2.11, “Subqueries”. A SELECT statement can start with a WITH clause to define common table expressions accessible within the SELECT. See Section 13.2.15, “WITH (Common Table Expressions)”.

SELECT用于检索从一个或多个表中选择的行，可以包括UNION语句和子查询。参见第13.2.10.3节“UNION子句”和第13.2.11节“子查询”。SELECT语句可以以with子句开始，以定义在SELECT中可访问的公共表表达式。参见13.2.15节“WITH(公共表表达式)”。

The most commonly used clauses of SELECT statements are these:

SELECT语句中最常用的子句有:

Each select_expr indicates a column that you want to retrieve. There must be at least one select_expr.
每个select_expr指示要检索的列。必须至少有一个select_expr。
table_references indicates the table or tables from which to retrieve rows. Its syntax is described in Section 13.2.10.2, “JOIN Clause”.
Table_references表示要从中检索行的一个或多个表。
SELECT supports explicit partition selection using the PARTITION clause with a list of partitions or subpartitions (or both) following the name of the table in a table_reference (see Section 13.2.10.2, “JOIN Clause”). In this case, rows are selected only from the partitions listed, and any other partitions of the table are ignored. For more information and examples, see Section 24.5, “Partition Selection”.
SELECT支持显式分区选择，使用partition子句，在table_reference(参见13.2.10.2节，“JOIN子句”)的表名后面有一个分区或子分区列表(或两者都有)。在这种情况下，只从列出的分区中选择行，而忽略表中的任何其他分区。
The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause. WHERE子句(如果给出)指示行必须满足哪些条件才能被选中。Where_condition是一个表达式，对于要选择的每一行计算结果都为true。如果没有WHERE子句，该语句将选择所有行。

In the WHERE expression, you can use any of the functions and operators that MySQL supports, except for aggregate (group) functions. See Section 9.5, “Expressions”, and Chapter 12, Functions and Operators. 在WHERE表达式中，可以使用MySQL支持的任何函数和操作符，聚合(组)函数除外。

SELECT can also be used to retrieve rows computed without reference to any table.

还可以使用SELECT检索不引用任何表的计算行。

For example:

mysql> SELECT 1 + 1;
        -> 2

You are permitted to specify DUAL as a dummy table name in situations where no tables are referenced:

在没有引用表的情况下，允许指定DUAL作为虚拟表名:

mysql> SELECT 1 + 1 FROM DUAL;
        -> 2

DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.

DUAL纯粹是为了方便那些所有SELECT语句都应该有FROM和可能的其他子句的人。MySQL可能会忽略子句。如果没有表被引用，MySQL不需要FROM DUAL。

In general, clauses used must be given in exactly the order shown in the syntax description. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. The INTO clause, if present, can appear in any position indicated by the syntax description, but within a given statement can appear only once, not in multiple positions. For more information about INTO, see Section 13.2.10.1, “SELECT ... INTO Statement”.

一般来说，所使用的子句必须按照语法描述中显示的顺序给出。例如，HAVING子句必须在任何GROUP BY子句之后，在任何ORDER BY子句之前。如果存在INTO子句，则可以出现在语法描述所指示的任何位置，但在给定的语句中只能出现一次，不能出现多个位置。

The list of select_expr terms comprises the select list that indicates which columns to retrieve. Terms specify a column or expression or can use *-shorthand:

select_expr术语列表由选择列表组成，该列表指示要检索哪些列。Terms指定列或表达式，也可以使用*-简写:

A select list consisting only of a single unqualified * can be used as shorthand to select all columns from all tables: 只包含一个非限定*的选择列表可以用作从所有表中选择所有列的简写:
```
SELECT * FROM t1 INNER JOIN t2 ...
```
tbl_name.* can be used as a qualified shorthand to select all columns from the named table: tbl_name。*可以作为一个合格的简写从指定的表中选择所有列:
```
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...
```
If a table has invisible columns, * and tbl_name.* do not include them. To be included, invisible columns must be referenced explicitly. 如果表有不可见的列，*和tbl_name。*不包括他们。要包含不可见列，必须显式引用不可见列。
Use of an unqualified * with other items in the select list may produce a parse error. To avoid this problem, use a qualified tbl_name.* reference: 对选择列表中的其他项使用不限定的*可能会产生解析错误。要避免这个问题，请使用限定的tbl_name。*参考:
```
SELECT AVG(score), t1.* FROM t1 ...
```

The following list provides additional information about other SELECT clauses:

下面的列表提供了关于其他SELECT子句的额外信息:

A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. For example: 可以使用AS alias_name给select_expr一个别名。别名用作表达式的列名，可以在GROUP BY、ORDER BY或HAVING子句中使用。例如:
```
SELECT CONCAT(last_name,', ',first_name) AS full_name
  FROM mytable ORDER BY full_name;
```
The AS keyword is optional when aliasing a select_expr with an identifier. The preceding example could have been written like this: 当用标识符对select_expr进行别名时，AS关键字是可选的。前面的例子可以这样写:
```
SELECT CONCAT(last_name,', ',first_name) full_name
  FROM mytable ORDER BY full_name;
```
However, because the AS is optional, a subtle problem can occur if you forget the comma between two select_expr expressions: MySQL interprets the second as an alias name. For example, in the following statement, columnb is treated as an alias name: 但是，由于AS是可选的，如果忘记了两个select_expr表达式之间的逗号，就会出现一个微妙的问题:MySQL将第二个解释为一个别名。例如，在下面的语句中，columnb被当作别名处理:
```
SELECT columna columnb FROM mytable;
```
For this reason, it is good practice to be in the habit of using AS explicitly when specifying column aliases. 因此，在指定列别名时，最好习惯显式地使用AS。

It is not permissible to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section B.3.4.4, “Problems with Column Aliases”. 不允许在WHERE子句中引用列别名，因为在执行WHERE子句时，可能还不能确定列值。
The FROM table_references clause indicates the table or tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see Section 13.2.10.2, “JOIN Clause”. For each table specified, you can optionally specify an alias. FROM table_references子句指示要从中检索行的一个或多个表。如果指定多个表，则执行连接。有关连接语法的信息，请参见13.2.10.2节“join子句”。对于指定的每个表，您可以选择指定别名。
```
tbl_name [[AS] alias] [index_hint]
```
The use of index hints provides the optimizer with information about how to choose indexes during query processing. For a description of the syntax for specifying these hints, see Section 8.9.4, “Index Hints”. 索引提示的使用为优化器提供了关于在查询处理期间如何选择索引的信息。有关指定这些提示的语法描述，请参见8.9.4节“索引提示”。

You can use SET max_seeks_for_key=value as an alternative way to force MySQL to prefer key scans instead of table scans. See Section 5.1.8, “Server System Variables”. 您可以使用SET max_seeks_for_key=value作为一种替代方法来强制MySQL选择键扫描而不是表扫描。参见第5.1.8节“服务器系统变量”。
You can refer to a table within the default database as tbl_name, or as db_name.tbl_name to specify a database explicitly. You can refer to a column as col_name, tbl_name.col_name, or db_name.tbl_name.col_name. You need not specify a tbl_name or db_name.tbl_name prefix for a column reference unless the reference would be ambiguous. See Section 9.2.2, “Identifier Qualifiers”, for examples of ambiguity that require the more explicit column reference forms.
您可以将默认数据库中的表引用为tbl_name或db_name。Tbl_name以显式指定数据库。您可以将列引用为col_name, tbl_name。col_name或db_name.tbl_name.col_name。您不需要指定tbl_name或db_name。列引用的Tbl_name前缀，除非该引用具有二义性。参见第9.2.2节“标识符限定符”，以了解需要更明确列引用形式的歧义。
A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name. These statements are equivalent: 表引用可以使用tbl_name AS alias_name或tbl_name alias_name别名。这些语句是等价的:
```
SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
  WHERE t1.name = t2.name;

SELECT t1.name, t2.salary FROM employee t1, info t2
  WHERE t1.name = t2.name;
```
Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1: 为输出选择的列可以在ORDER BY和GROUP BY子句中使用列名、列名或列位引用。列位置为整数，以1开头:
```
SELECT college, region, seed FROM tournament
  ORDER BY region, seed;

SELECT college, region AS r, seed AS s FROM tournament
  ORDER BY r, s;

SELECT college, region, seed FROM tournament
  ORDER BY 2, 3;
```
To sort in reverse order, add the DESC (descending) keyword to the name of the column in the ORDER BY clause that you are sorting by. The default is ascending order; this can be specified explicitly using the ASC keyword. 要按相反的顺序排序，在要排序的order BY子句中的列的名称中添加DESC(降序)关键字。默认是升序;这可以使用ASC关键字显式指定。

If ORDER BY occurs within a parenthesized query expression and also is applied in the outer query, the results are undefined and may change in a future version of MySQL. 如果ORDER BY出现在带圆括号的查询表达式中，并且也应用在外部查询中，则结果是未定义的，可能会在MySQL的未来版本中更改。

Use of column positions is deprecated because the syntax has been removed from the SQL standard. 不建议使用列位置，因为该语法已从SQL标准中删除。
Prior to MySQL 8.0.13, MySQL supported a nonstandard syntax extension that permitted explicit ASC or DESC designators for GROUP BY columns. MySQL 8.0.12 and later supports ORDER BY with grouping functions so that use of this extension is no longer necessary. (Bug #86312, Bug #26073525) This also means you can sort on an arbitrary column or columns when using GROUP BY, like this: 在MySQL 8.0.13之前，MySQL支持一个非标准语法扩展，允许显式的ASC或DESC指定GROUP BY列。MySQL 8.0.12和更高版本支持ORDER BY与分组功能，所以使用这个扩展不再是必要的。(Bug #86312, Bug #26073525)这也意味着你可以在使用GROUP BY时对任意列进行排序，就像这样:
```
SELECT a, b, COUNT(c) AS t FROM test_table GROUP BY a,b ORDER BY a,t DESC;
```
As of MySQL 8.0.13, the GROUP BY extension is no longer supported: ASC or DESC designators for GROUP BY columns are not permitted. 从MySQL 8.0.13开始，GROUP BY扩展不再被支持:不允许使用GROUP BY列的ASC或DESC指定符。
When you use ORDER BY or GROUP BY to sort a column in a SELECT, the server sorts values using only the initial number of bytes indicated by the max_sort_length system variable.
：当您使用ORDER BY或GROUP BY对SELECT中的列进行排序时，服务器仅使用max_sort_length系统变量指定的初始字节数对值进行排序。
MySQL extends the use of GROUP BY to permit selecting fields that are not mentioned in the GROUP BY clause. If you are not getting the results that you expect from your query, please read the description of GROUP BY found in Section 12.20, “Aggregate Functions”.
MySQL扩展了GROUP BY的使用，允许选择GROUP BY子句中没有提到的字段。如果您没有从您的查询中得到您期望的结果，请阅读第12.20节“聚合函数”中GROUP BY的描述。
GROUP BY permits a WITH ROLLUP modifier. See Section 12.20.2, “GROUP BY Modifiers”.GROUP BY允许WITH ROLLUP修饰符。参见第12.20.2节“GROUP BY Modifiers”。

Previously, it was not permitted to use ORDER BY in a query having a WITH ROLLUP modifier. This restriction is lifted as of MySQL 8.0.12. See Section 12.20.2, “GROUP BY Modifiers”.以前，不允许在具有WITH ROLLUP修饰符的查询中使用ORDER BY。这个限制在MySQL 8.0.12中被取消。参见第12.20.2节“GROUP BY Modifiers”。
The HAVING clause, like the WHERE clause, specifies selection conditions. The WHERE clause specifies conditions on columns in the select list, but cannot refer to aggregate functions. The HAVING clause specifies conditions on groups, typically formed by the GROUP BY clause. The query result includes only groups satisfying the HAVING conditions. (If no GROUP BY is present, all rows implicitly form a single aggregate group.) 与WHERE子句一样，HAVING子句指定选择条件。WHERE子句指定选择列表中的列的条件，但不能引用聚合函数。HAVING子句指定组的条件，通常由GROUP by子句构成。查询结果只包括满足HAVING条件的组。(如果没有GROUP BY，则所有行隐式组成一个聚合组。)

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.) HAVING子句几乎是最后应用的，就在条目发送到客户端之前，没有进行任何优化。(LIMIT是在HAVING之后。)

The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well. SQL标准要求HAVING必须仅引用GROUP BY子句中的列或聚合函数中使用的列。然而，MySQL支持这种行为的扩展，并允许HAVING引用SELECT列表中的列和外部子查询中的列。

If the HAVING clause refers to a column that is ambiguous, a warning occurs. In the following statement, col2 is ambiguous because it is used as both an alias and a column name: 如果HAVING子句引用的列不明确，则会出现警告。在下面的语句中，col2是二义性的，因为它同时用作别名和列名:
```
SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
```
Preference is given to standard SQL behavior, so if a HAVING column name is used both in GROUP BY and as an aliased column in the select column list, preference is given to the column in the GROUP BY column. 优先考虑标准SQL行为，因此，如果在GROUP BY中使用HAVING列名，并在选择列列表中作为别名列使用，则优先考虑GROUP BY列中的列。
Do not use HAVING for items that should be in the WHERE clause. For example, do not write the following: 不要对应该在WHERE子句中的项使用HAVING。例如，不要写以下内容:
```
SELECT col_name FROM tbl_name HAVING col_name > 0;
```
Write this instead:
```
SELECT col_name FROM tbl_name WHERE col_name > 0;
```
The HAVING clause can refer to aggregate functions, which the WHERE clause cannot: HAVING子句可以引用聚合函数，而WHERE子句不能:
```
SELECT user, MAX(salary) FROM users
  GROUP BY user HAVING MAX(salary) > 10;
```
(This did not work in some older versions of MySQL.) (这在一些旧版本的MySQL中不起作用。)
MySQL permits duplicate column names. That is, there can be more than one select_expr with the same name. This is an extension to standard SQL. Because MySQL also permits GROUP BY and HAVING to refer to select_expr values, this can result in an ambiguity: MySQL允许重复列名。也就是说，可以有多个具有相同名称的select_expr。这是对标准SQL的扩展。因为MySQL也允许GROUP BY和HAVING引用select_expr值，这可能会导致歧义:
```
SELECT 12 AS a, a FROM t GROUP BY a;
```
In that statement, both columns have the name a. To ensure that the correct column is used for grouping, use different names for each select_expr. 在该语句中，两个列都有名称a。为了确保使用正确的列进行分组，请为每个select_expr使用不同的名称。
The WINDOW clause, if present, defines named windows that can be referred to by window functions. For details, see Section 12.21.4, “Named Windows”. WINDOW子句(如果存在)定义了可被窗口函数引用的已命名窗口。详细信息请参见12.21.4章节“Named Windows”。
MySQL resolves unqualified column or alias references in ORDER BY clauses by searching in the select_expr values, then in the columns of the tables in the FROM clause. For GROUP BY or HAVING clauses, it searches the FROM clause before searching in the select_expr values. (For GROUP BY and HAVING, this differs from the pre-MySQL 5.0 behavior that used the same rules as for ORDER BY.)
MySQL解析ORDER BY子句中的未限定列或别名引用时，首先搜索select_expr值，然后搜索FROM子句中表的列。对于GROUP BY或HAVING子句，它在搜索select_expr值之前搜索FROM子句。(对于GROUP BY和HAVING，这与使用ORDER BY相同规则的mysql 5.0之前的行为不同。)
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions: LIMIT子句可用于约束SELECT语句返回的行数。LIMIT接受一个或两个数值参数，它们必须都是非负整数常量，但有以下例外:
- Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.
- 在预处理语句中，可以使用?占位符标记。
- Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.
- 在存储的程序中，可以使用整数值例程参数或局部变量指定LIMIT参数。
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1): 对于两个参数，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。第一行的偏移量是0(不是1):
```
SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15
```
To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:要检索从某个偏移量到结果集末尾的所有行，可以使用较大的数字作为第二个参数。该语句检索从第96行到最后一行的所有行:
```
SELECT * FROM tbl LIMIT 95,18446744073709551615;
```
With one argument, the value specifies the number of rows to return from the beginning of the result set:使用一个参数，该值指定从结果集的开头返回的行数:
```
SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows
```
In other words, LIMIT row_count is equivalent to LIMIT 0, row_count. 换句话说，LIMIT row_count等价于LIMIT 0, row_count。

For prepared statements, you can use placeholders. The following statements return one row from the tbl table: 对于预处理语句，可以使用占位符。下面的语句从tbl表中返回一行:
```
SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;
```
The following statements return the second to sixth rows from the tbl table: 下面的语句返回tbl表中的第二到第六行:
```
SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;
```
For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax. 为了与PostgreSQL兼容，MySQL还支持LIMIT row_count OFFSET OFFSET语法。

If LIMIT occurs within a parenthesized query expression and also is applied in the outer query, the results are undefined and may change in a future version of MySQL. 如果LIMIT出现在带圆括号的查询表达式中，并且也应用在外部查询中，则结果是未定义的，并且可能在MySQL的未来版本中更改。
The SELECT ... INTO form of SELECT enables the query result to be written to a file or stored in variables. For more information, see Section 13.2.10.1, “SELECT ... INTO Statement”.
选择……以SELECT的形式将查询结果写入文件或存储在变量中。更多信息，请参见13.2.10.1节“SELECT…声明”。
If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction. 如果将FOR UPDATE与使用页锁或行锁的存储引擎一起使用，则查询检查的行将被写锁，直到当前事务结束。

You cannot use FOR UPDATE as part of the SELECT in a statement such as CREATE TABLE new_table SELECT ... FROM old_table .... (If you attempt to do so, the statement is rejected with the error Can't update table 'old_table' while 'new_table' is being created.) 不能在CREATE TABLE new_table SELECT…语句中使用FOR UPDATE作为SELECT的一部分。从old_table…(如果你尝试这样做，语句会被拒绝，错误是:当'new_table'正在创建时，不能更新表'old_table'。)

FOR SHARE and LOCK IN SHARE MODE set shared locks that permit other transactions to read the examined rows but not to update or delete them. FOR SHARE and LOCK IN SHARE MODE are equivalent. However, FOR SHARE, like FOR UPDATE, supports NOWAIT, SKIP LOCKED, and OF tbl_name options. FOR SHARE is a replacement for LOCK IN SHARE MODE, but LOCK IN SHARE MODE remains available for backward compatibility. FOR SHARE和LOCK IN SHARE MODE设置共享锁，允许其他事务读取检查的行，但不允许更新或删除它们。FOR SHARE和LOCK IN SHARE MODE是等价的。然而，与FOR UPDATE一样，FOR SHARE也支持NOWAIT、SKIP LOCKED和OF tbl_name选项。FOR SHARE是LOCK IN SHARE模式的替代品，但是LOCK IN SHARE模式仍然可以向后兼容。

NOWAIT causes a FOR UPDATE or FOR SHARE query to execute immediately, returning an error if a row lock cannot be obtained due to a lock held by another transaction. NOWAIT导致立即执行FOR UPDATE或FOR SHARE查询，如果由于另一个事务持有的锁而无法获得行锁，则返回一个错误。

SKIP LOCKED causes a FOR UPDATE or FOR SHARE query to execute immediately, excluding rows from the result set that are locked by another transaction. SKIP LOCKED导致立即执行FOR UPDATE或FOR SHARE查询，排除结果集中被另一个事务锁定的行。

NOWAIT and SKIP LOCKED options are unsafe for statement-based replication. NOWAIT和SKIP LOCKED选项对于基于语句的复制是不安全的。

Note

Queries that skip locked rows return an inconsistent view of the data. SKIP LOCKED is therefore not suitable for general transactional work. However, it may be used to avoid lock contention when multiple sessions access the same queue-like table. 跳过锁定行的查询将返回不一致的数据视图。因此，SKIP LOCKED不适用于一般的事务性工作。但是，当多个会话访问同一类队列表时，可以使用它来避免锁争用。

OF tbl_name applies FOR UPDATE and FOR SHARE queries to named tables. For example:OF tbl_name对命名表应用FOR UPDATE和FOR SHARE查询。例如:
```
SELECT * FROM t1, t2 FOR SHARE OF t1 FOR UPDATE OF t2;
```
All tables referenced by the query block are locked when OF tbl_name is omitted. Consequently, using a locking clause without OF tbl_name in combination with another locking clause returns an error. Specifying the same table in multiple locking clauses returns an error. If an alias is specified as the table name in the SELECT statement, a locking clause may only use the alias. If the SELECT statement does not specify an alias explicitly, the locking clause may only specify the actual table name. 当OF tbl_name被省略时，查询块引用的所有表都被锁定。因此，将不带OF tbl_name的锁定子句与另一个锁定子句结合使用将返回错误。在多个锁定子句中指定同一个表将返回错误。如果在SELECT语句中指定了一个别名作为表名，那么锁定子句只能使用这个别名。如果SELECT语句没有显式地指定别名，锁定子句只能指定实际的表名。

For more information about FOR UPDATE and FOR SHARE, see Section 15.7.2.4, “Locking Reads”. For additional information about NOWAIT and SKIP LOCKED options, see Locking Read Concurrency with NOWAIT and SKIP LOCKED.

Following the SELECT keyword, you can use a number of modifiers that affect the operation of the statement. HIGH_PRIORITY, STRAIGHT_JOIN, and modifiers beginning with SQL_ are MySQL extensions to standard SQL.

在SELECT关键字之后，可以使用许多修饰符来影响语句的操作。HIGH_PRIORITY、STRAIGHT_JOIN和以SQL_开头的修饰符是对标准SQL的MySQL扩展。

The ALL and DISTINCT modifiers specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both modifiers. DISTINCTROW is a synonym for DISTINCT. ALL和DISTINCT修饰符指定是否应该返回重复的行。ALL(默认值)指定应该返回所有匹配的行，包括重复的行。DISTINCT指定从结果集中删除重复的行。同时指定这两个修饰符是错误的。DISTINCTROW是DISTINCT的同义词。

In MySQL 8.0.12 and later, DISTINCT can be used with a query that also uses WITH ROLLUP. (Bug #87450, Bug #26640100)
HIGH_PRIORITY gives the SELECT higher priority than a statement that updates a table. You should use this only for queries that are very fast and must be done at once. A SELECT HIGH_PRIORITY query that is issued while the table is locked for reading runs even if there is an update statement waiting for the table to be free. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE). HIGH_PRIORITY为SELECT提供比更新表的语句更高的优先级。您应该只在速度非常快且必须立即执行的查询中使用此方法。SELECT HIGH_PRIORITY查询是在表被锁定读取时发出的，即使有一个更新语句等待表被释放。这只影响只使用表级锁的存储引擎(如MyISAM、MEMORY和MERGE)。

HIGH_PRIORITY cannot be used with SELECT statements that are part of a UNION.
STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause. You can use this to speed up a query if the optimizer joins the tables in nonoptimal order. STRAIGHT_JOIN also can be used in the table_references list. See Section 13.2.10.2, “JOIN Clause”. STRAIGHT_JOIN强制优化器按照FROM子句中列出的顺序连接表。如果优化器以非最佳顺序联接表，则可以使用此方法来加快查询速度。也可以在table_references列表中使用直线连接。参见第13.2.10.2节“JOIN子句”。

STRAIGHT_JOIN does not apply to any table that the optimizer treats as a const or system table. Such a table produces a single row, is read during the optimization phase of query execution, and references to its columns are replaced with the appropriate column values before query execution proceeds. These tables appear first in the query plan displayed by EXPLAIN. See Section 8.8.1, “Optimizing Queries with EXPLAIN”. This exception may not apply to const or system tables that are used on the NULL-complemented side of an outer join (that is, the right-side table of a LEFT JOIN or the left-side table of a RIGHT JOIN. 对于优化器视为const或系统表的任何表，都不适用STRAIGHT_JOIN。这样的表生成一行，在查询执行的优化阶段读取该行，并在查询执行之前用适当的列值替换对其列的引用。这些表首先出现在EXPLAIN显示的查询计划中。参见8.8.1节“使用EXPLAIN优化查询”。此异常不适用于外部连接(即左连接的右表或左表)的空补侧使用的const表或系统表
SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively. For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if they are created, and prefers sorting to using a temporary table with a key on the GROUP BY elements. For SQL_SMALL_RESULT, MySQL uses in-memory temporary tables to store the resulting table instead of using sorting. This should not normally be needed.
SQL_BIG_RESULT或SQL_SMALL_RESULT可与GROUP BY或DISTINCT一起使用，分别告诉优化器结果集有很多行或很小。对于SQL_BIG_RESULT, MySQL直接使用基于磁盘的临时表(如果创建了这些表)，并且喜欢排序，而不是使用GROUP BY元素上带有键的临时表。对于SQL_SMALL_RESULT, MySQL使用内存中的临时表来存储结果表，而不是使用排序。这通常是不需要的。
SQL_BUFFER_RESULT forces the result to be put into a temporary table. This helps MySQL free the table locks early and helps in cases where it takes a long time to send the result set to the client. This modifier can be used only for top-level SELECT statements, not for subqueries or following UNION.
SQL_BUFFER_RESULT强制将结果放入临时表中。这有助于MySQL尽早释放表锁，并有助于在需要很长时间将结果集发送到客户机的情况下。此修饰符只能用于顶级SELECT语句，而不能用于子查询或跟随UNION。
SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in the result set, disregarding any LIMIT clause. The number of rows can then be retrieved with SELECT FOUND_ROWS(). See Section 12.16, “Information Functions”. SQL_CALC_FOUND_ROWS告诉MySQL计算结果集中有多少行，忽略任何LIMIT子句。然后可以使用SELECT FOUND_ROWS()检索行数。参见第12.16节“信息功能”。

Note

The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17; expect them to be removed in a future version of MySQL. See the FOUND_ROWS() description for information about an alternative strategy. SQL_CALC_FOUND_ROWS查询修饰符和伴随的FOUND_ROWS()函数在MySQL 8.0.17中已弃用;希望在未来的MySQL版本中删除它们。有关替代策略的信息，请参阅FOUND_ROWS()描述。
The SQL_CACHE and SQL_NO_CACHE modifiers were used with the query cache prior to MySQL 8.0. The query cache was removed in MySQL 8.0. The SQL_CACHE modifier was removed as well. SQL_NO_CACHE is deprecated, and has no effect; expect it to be removed in a future MySQL release.
SQL_CACHE和SQL_NO_CACHE修饰符在MySQL 8.0之前用于查询缓存。查询缓存在MySQL 8.0中被移除。SQL_CACHE修饰符也被删除了。SQL_NO_CACHE已弃用，且不起作用;希望在未来的MySQL版本中删除它。