MySQL 8.0-13.2.10.4 Parenthesized Query Expressions

最新推荐文章于 2025-11-22 04:58:29 发布

翻译最新推荐文章于 2025-11-22 04:58:29 发布 · 237 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://dev.mysql.com/doc/refman/8.0/en/parenthesized-query-expressions.html

文章标签：

#mysql #elasticsearch #数据库

Mysql 专栏收录该内容

142 篇文章

订阅专栏

本文详细介绍了MySQL 8.0.22及以上版本中括号查询表达式的用法，包括基本结构、UNION操作与ORDER BY、LIMIT的结合，以及注意事项。掌握如何在查询中嵌套和强制执行这些子句，提升SQL查询的灵活性。

parenthesized_query_expression:
    ( query_expression [order_by_clause] [limit_clause] )
      [order_by_clause]
      [limit_clause]
      [into_clause]

query_expression:
    query_block [UNION query_block [UNION query_block ...]]
      [order_by_clause]
      [limit_clause]
      [into_clause]

query_block:
    SELECT ...             (see Section 13.2.10, “SELECT Statement”)

order_by_clause:
    ORDER BY as for SELECT (see Section 13.2.10, “SELECT Statement”)

limit_clause:
    LIMIT as for SELECT    (see Section 13.2.10, “SELECT Statement”)

into_clause:
    INTO as for SELECT     (see Section 13.2.10, “SELECT Statement”)

MySQL 8.0.22 and higher supports parenthesized query expressions according to the preceding syntax. At its simplest, a parenthesized query expression contains a single SELECT and no following optional clauses:

MySQL 8.0.22及更高版本支持用括号括起来的查询表达式。最简单的是，带圆括号的查询表达式包含一个SELECT，没有以下可选子句:

(SELECT 1);
(SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'mysql');

A parenthesized query expression can also contain a UNION comprising multiple SELECT statements, and end with any or all of the optional clauses:

带圆括号的查询表达式还可以包含由多个SELECT语句组成的UNION，并以任意或所有可选子句结尾:

mysql> (SELECT 1 AS result UNION SELECT 2);
+--------+
| result |
+--------+
|      1 |
|      2 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2) LIMIT 1;
+--------+
| result |
+--------+
|      1 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2) LIMIT 1 OFFSET 1;
+--------+
| result |
+--------+
|      2 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2)
       ORDER BY result DESC LIMIT 1;
+--------+
| result |
+--------+
|      2 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2)
       ORDER BY result DESC LIMIT 1 OFFSET 1;
+--------+
| result |
+--------+
|      1 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 3 UNION SELECT 2)
       ORDER BY result LIMIT 1 OFFSET 1 INTO @var;
mysql> SELECT @var;
+------+
| @var |
+------+
|    2 |
+------+

Parenthesized query expressions are also used as query expressions, so a query expression, usually composed of query blocks, may also consist of parenthesized query expressions:

带圆括号的查询表达式也可以用作查询表达式，所以通常由查询块组成的查询表达式也可以由带圆括号的查询表达式组成:

(SELECT * FROM t1 ORDER BY a) UNION (SELECT * FROM t2 ORDER BY b) ORDER BY z;

Query blocks may have trailing ORDER BY and LIMIT clauses, which are applied before the outer UNION and ORDER BY and LIMIT.

查询块可能有尾随的ORDER BY和LIMIT子句，这些子句应用于外部UNION和ORDER BY和LIMIT之前。

You cannot have a query block with a trailing ORDER BY or LIMIT, without wrapping it in parentheses, but parentheses may be used for enforcement in various ways:

你不能有一个尾随ORDER BY或LIMIT的查询块，而不将其用圆括号括起来，但是圆括号可以以各种方式用于强制执行:

To enforce LIMIT on each query block: 对每个查询块执行LIMIT:
```
(SELECT 1 LIMIT 1) UNION (SELECT 2 LIMIT 1);
```
To enforce LIMIT on both query blocks and the entire query expression: 要对查询块和整个查询表达式执行LIMIT:
```
(SELECT 1 LIMIT 1) UNION (SELECT 2 LIMIT 1) LIMIT 1;
```
To enforce LIMIT on the entire query expression (with no parentheses):要对整个查询表达式强制LIMIT(不带括号):
```
SELECT 1 UNION SELECT 2 LIMIT 1;
```
Hybrid enforcement: LIMIT on the first query block and on the entire query expression: 混合执行:限制第一个查询块和整个查询表达式:
```
(SELECT 1 LIMIT 1) UNION SELECT 2 LIMIT 1;
```

The syntax described in this section is subject to certain restrictions:

本节描述的语法有一定的限制:

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. The same is true if LIMIT occurs within a parenthesized query expression and also is applied in the outer query.
如果ORDER BY出现在带圆括号的查询表达式中，并且也应用在外部查询中，则结果是未定义的，可能会在MySQL的未来版本中更改。如果LIMIT出现在带圆括号的查询表达式中，并且也应用于外部查询，则也是如此
A trailing INTO clause for a query expression is not permitted if there is another INTO clause inside parentheses.
如果括号中有另一个INTO子句，则查询表达式的末尾不允许使用INTO子句。
Parenthesized query expressions do not permit multiple levels of ORDER BY or LIMIT operations. For example:

带括号的查询表达式不允许多级ORDER BY或LIMIT操作。例如:

mysql> (SELECT 'a' UNION SELECT 'b' LIMIT 1) LIMIT 2;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'parenthesized
query expression with more than one external level of ORDER/LIMIT operations'