mysql - 为什么将 '*' 添加到 MySQL 查询会导致语法错误？

Question

在 MySQL 中，这段代码工作正常:

select f, blegg.* from blegg limit 1;

+------+------+------+------+
| f    | f    | g    | h    |
+------+------+------+------+
|   17 |   17 |    2 |   17 |
+------+------+------+------+
1 row in set (0.00 sec)

那么为什么这段代码会导致语法错误呢？

select f, * from blegg limit 1;

-- * is unqualified
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '* 
from blegg limit 1' at line 1

我已经查看了手册，但没有真正找到任何东西。为什么 select <field>, * ...失败的地方 select <field>, <table>.* ...和 select * ...和 select *, <field> ...成功了吗？

Answer 1

MySQL 手册在 SELECT syntax 部分中非常清楚地列出了所有这些内容:

• 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:

  SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...
  

• 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

  SELECT AVG(score), t1.* FROM t1 ...
  

文档似乎表明 * by itself 仅在特殊情况下有效，即它是选择列表中唯一的东西。但是，它只说使用不合格的 *与其他项目可能产生解析错误。

超越 MySQL，SQL-92 standard (旧的，但可链接)说得一样多:

7.9  <query specification>

         Format

         <query specification> ::=
              SELECT [ <set quantifier> ] <select list> <table expression>

         <select list> ::=
                <asterisk>
              | <select sublist> [ { <comma> <select sublist> }... ]

         <select sublist> ::=
                <derived column>
              | <qualifier> <period> <asterisk>

         <derived column> ::= <value expression> [ <as clause> ]

         <as clause> ::= [ AS ] <column name>

<select list>可以是 <asterisk>本身或一个“普通”选择列表。