为什么在MySQL查询中添加"*"会导致语法错误? [英] Why does adding '*' to a MySQL query cause a syntax error?

问题描述

为什么这会导致语法错误(MySQL 5)?

Why does this cause a syntax error (MySQL 5)?

mysql> select f, blegg.* from blegg limit 1;
+------+------+------+------+
| f    | f    | g    | h    |
+------+------+------+------+
|   17 |   17 |    2 |   17 |
+------+------+------+------+
1 row in set (0.00 sec)

mysql> 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> ...成功的地方失败?

I've looked through the manual but didn't really find anything. Why does select <field>, * ... fail where select <field>, <table>.* ... and select * ... and select *, <field> ... succeed?

推荐答案

MySQL手册在

The MySQL manual lays all this out pretty clearly in the section on 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.*可以用作 限定的速记以从命名表中选择所有列:

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 ...

使用不合格的* 选择列表中的其他项目可能会产生解析错误.到 为避免此问题，请使用合格的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

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

文档似乎表明*本身仅在特殊情况下才有效，因为它是选择列表中的唯一内容.但是，它仅表示将不合格的*与其他项目 一起使用可能会产生解析错误.

The documentation seems to indicate that * by itself is only valid in the special case where it's the only thing in the select list. However, it only says using an unqualified * with other items may produce a parse error.

除了MySQL， SQL-92标准(旧的，但可以链接)说了很多话:

Beyond MySQL, the SQL-92 standard (old, but linkable) says as much:

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> 或正常"选择列表.

<select list> can either be <asterisk> by itself or a "normal" select list.

这篇关于为什么在MySQL查询中添加"*"会导致语法错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！