为什么给定的语法在mysql中有效?

时间:2021-10-31 15:42:08

In another answer I've spotted a weird syntax:

在另一个答案中,我发现了一个奇怪的语法:

(SELECT * FROM `articles` 
 WHERE date >= UNIX_TIMESTAMP(DATE(NOW() - INTERVAL 30 DAY))
 ORDER BY `views` DESC 
 LIMIT 20
) ORDER by `views` ASC

which was executed by mysql well though.

虽然很好地执行了mysql。

Why I think it should fail:

为什么我认为它应该失败:

  1. The subquery doesn't have alias
  2. 子查询没有别名

  3. The whole query lacks SELECT clause
  4. 整个查询缺少SELECT子句

I find it unexpected to run and don't have an explanation why it works.

我发现运行意外,并没有解释为什么它的工作原理。

It does not fit the grammar defined on https://dev.mysql.com/doc/refman/5.5/en/select.html

它不符合https://dev.mysql.com/doc/refman/5.5/en/select.html上定义的语法

So, why is it valid? Any references?

那么,为什么它有效?任何参考?

2 个解决方案

#1


6  

It's the alternative UNION syntax with a final ORDER BY.

它是具有最终ORDER BY的替代UNION语法。

This is what such a union between two selects looks like:

这就是两个选择之间的这种联合看起来像:

(SELECT ...)
UNION
(SELECT ...) ORDER BY ... LIMIT ...

And this is what such a union between one select looks like:

这就是一个选择之间的这种联合看起来像:

(SELECT ...) ORDER BY ... LIMIT ...

Not related to subqueries at all.

根本与子查询无关。

This isn't documented in MySQL, but is obvious from the grammar:

这在MySQL中没有记载,但从语法中可以明显看出:

top_level_select_init:
        SELECT_SYM
        {
            Lex->sql_command= SQLCOM_SELECT;
        }
        select_init2
        | '(' select_paren ')' union_opt
        ;


/* Need select_init2 for subselects. */
union_select_init:
        SELECT_SYM select_init2
        | '(' select_paren ')' union_opt
        ;

...

union_opt:
        /* Empty */ { $$= 0; }
        | union_list { $$= 1; }
        | union_order_or_limit { $$= 1; }
        ;

#2


1  

The syntax is useful when you want to sort the end result of the UNION.

当您想要对UNION的最终结果进行排序时,语法很有用。

The following would sort only the last SELECT:

以下将仅排序最后一个SELECT:

SELECT …
UNION
SELECT …
UNION
SELECT … ORDER BY views

But this would sort the whole result:

但这会对整个结果进行排序:

(SELECT …)
UNION
(SELECT …)
UNION
(SELECT …) ORDER BY views

You're doing something like this query, but you've got just one SELECT.

你正在做这样的查询,但你只有一个SELECT。

#1


6  

It's the alternative UNION syntax with a final ORDER BY.

它是具有最终ORDER BY的替代UNION语法。

This is what such a union between two selects looks like:

这就是两个选择之间的这种联合看起来像:

(SELECT ...)
UNION
(SELECT ...) ORDER BY ... LIMIT ...

And this is what such a union between one select looks like:

这就是一个选择之间的这种联合看起来像:

(SELECT ...) ORDER BY ... LIMIT ...

Not related to subqueries at all.

根本与子查询无关。

This isn't documented in MySQL, but is obvious from the grammar:

这在MySQL中没有记载,但从语法中可以明显看出:

top_level_select_init:
        SELECT_SYM
        {
            Lex->sql_command= SQLCOM_SELECT;
        }
        select_init2
        | '(' select_paren ')' union_opt
        ;


/* Need select_init2 for subselects. */
union_select_init:
        SELECT_SYM select_init2
        | '(' select_paren ')' union_opt
        ;

...

union_opt:
        /* Empty */ { $$= 0; }
        | union_list { $$= 1; }
        | union_order_or_limit { $$= 1; }
        ;

#2


1  

The syntax is useful when you want to sort the end result of the UNION.

当您想要对UNION的最终结果进行排序时,语法很有用。

The following would sort only the last SELECT:

以下将仅排序最后一个SELECT:

SELECT …
UNION
SELECT …
UNION
SELECT … ORDER BY views

But this would sort the whole result:

但这会对整个结果进行排序:

(SELECT …)
UNION
(SELECT …)
UNION
(SELECT …) ORDER BY views

You're doing something like this query, but you've got just one SELECT.

你正在做这样的查询,但你只有一个SELECT。