通过语句抛出错误教程IS不

发布于 2025-01-31 02:54:41 字数 263 浏览 2 评论 0原文

您好，我正在通过SQL Joins上的教程，下面的此语句抛出“ sql_mode = belly_full_group_by”错误。我直接从教程及其为他工作中复制了此内容。我只需要在终端中禁用此模式吗？

    SELECT *
    FROM customers
    JOIN orders
            ON customers.id = orders.customer_id
    GROUP BY orders.customer_id;

原文

Hello im going through a tutorial on SQL joins and this statement below is throwing "sql_mode=only_full_group_by" error. I copied this directly from the tutorial and its working for him. Do I just need to disable this mode in the terminal?

    SELECT *
    FROM customers
    JOIN orders
            ON customers.id = orders.customer_id
    GROUP BY orders.customer_id;

分享到QQ

分享到微博

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

发布评论

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

如梦初醒的夏天 2025-02-07 02:54:41

如果启用了唯一_FULL_GROUP_BY_BY SQL模式（默认情况下是），则MySQL拒绝select> select> select list，具有条件或代码>按列表订购，请参阅子句 group中未命名的非聚集列，也不在功能上取决于它们。 [1]

您可以禁用此功能，但是让它启用确实很有意义。考虑以下情况：

id | type
---|-----
1  | foo
2  | foo
3  | bar

如果您使用类似的查询，by Type从表组中选择 *，您希望输出是什么？也许您期望两行，foo和bar，但是您期望在id> id列中，即foo记录？我们应该只选择最早的记录吗？最新记录？随机记录？这是错误告诉您的 - 您必须明确说明数据库引擎为您选择。

因此，在您的示例中，您需要考虑所得数据的外观。也许不是选择 *您想要选择客户

。没有唯一的_FULL_GROUP_BY已启用，或者他们使用的是5.7.5的MySQL版本。在这种情况下，服务器可以自由从每个组中选择任何值，这可能不是您想要的。

最后，如果您希望自己的控制台以相同的方式行事，则可以在控制台中通过：

mysql＆gt;设置global sql_mode =（select替换（@@ sql_mode，'ally_full_group_by'，''））;

[1] https://dev.mysql.com/doc/refman/5.7/en/group-bybyhandling.html

If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. [1]

You can disable this, but it really makes sense to leave it enabled. Think about the following case:

id | type
---|-----
1  | foo
2  | foo
3  | bar

If you used a similar query to yours, select * from table group by type, what would you expect the output to be? Perhaps you expect two rows, foo and bar, but what do you expect to be in the id column, namely for the foo record? Should we just choose the earliest record? The latest record? A random record? This is what the error is telling you - instead of having the database engine choose for you, you must be explicit.

So, in your example, you need to think about what the resulting data should look like. Maybe instead of select * you want select customers.name, count(*) ..., etc.

The reason this works for the tutorial you're following is either they don't have only_full_group_by enabled or they're using a MySql version older than 5.7.5 where this was introduced. In this case, the server is free to choose any value from each group, which is probably not what you want.

Finally, if you want your console to behave in the same way your tutorial is behaving, you can disable ONLY_FULL_GROUP_BY in the console by:

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

[1] https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

回复收藏 0 原文

~没有更多了~