为什么添加“*”会MySQL 查询会导致语法错误吗？

发布于 2024-12-12 04:45:36 字数 770 浏览 0 评论 0原文

在 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, * ... 失败，而 select, .* ... 和 select * 。 .. 和选择 *,; ... 成功了吗？

原文

In MySQL, this code works fine:

select f, blegg.* from blegg limit 1;

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

So why does this code cause a syntax error?

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

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

太傻旳人生 2024-12-19 04:45:37

可能是因为您选择同一字段两次。在以下查询中，

select name, * from <...>

* 将包含 name，因此您将再次显式指定 name。

这不是一个令人信服的论据，因为以下内容是有效的：

select name, name from <...>

以下内容也是有效的，

select name, users.* from users

两者都会多次选择同一字段。

更有可能的是，这只是 MySQL 的语法限制。

Possibly because you're selecting the same field twice. In the following query

select name, * from <...>

the * will include name, so you're explicitly specifying name a second time.

This isn't a convincing argument because the following is valid:

select name, name from <...>

and so is the following

select name, users.* from users

both of which will select the same field multiple times.

More likely it is simply a syntax limitation of MySQL.

回复收藏 0 原文

栖竹 2024-12-19 04:45:36

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

仅包含单个不合格的 * 的选择列表可用作
从所有表中选择所有列的简写：
从 t1 内连接 t2 中选择 * ...
tbl_name.* 可以用作
用于从指定表中选择所有列的限定简写：
从 t1 内连接 t2 中选择 t1.*, t2.* ...
使用不合格的*
与选择列表中的其他项目可能会产生解析错误。到
避免此问题，请使用合格的 tbl_name.* 引用
从 t1 中选择 AVG(分数), t1.* ...

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

除了 MySQL 之外，SQL-92 标准（旧的，但是可链接）说的是：

7.9 <查询规范>

         格式

         <查询规范> ::=
              SELECT [ <设置量词> ] <选择列表> <表表达式>

         <选择列表> ::=
                <星号>
              | <选择子列表> [ { <逗号>; <选择子列表> }...]

         <选择子列表> ::=
                <派生栏>
              | <限定符> <期间> <星号>

         <派生栏> ::= <值表达式> [ <作为子句> ]

         <作为子句> ::= [ AS ] <列名称>;

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

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.

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> can either be <asterisk> by itself or a "normal" select list.

回复收藏 0 原文