在MySql中执行查询时与only_full_group_by相关的错误

最新推荐文章于 2023-12-15 13:52:58 发布

w36680130

最新推荐文章于 2023-12-15 13:52:58 发布

阅读量356

点赞数

文章标签： mysql sql group-by mysql-error-1055

原文链接：https://oldbug.net/q/2J8uk/Error-related-to-only-full-group-by-when-executing-a-query-in-MySql

版权

I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. 我已经升级了系统，并为正在使用的Web应用程序安装了带有PHP的MySql 5.7.9。 I have a query that is dynamically created, and when run in older versions of MySql it works fine. 我有一个动态创建的查询，当在旧版本的MySql中运行时，它运行良好。 Since upgrading to 5.7 I get this error: 自升级到5.7以来，出现此错误：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'support_desk.mod_users_groups.group_id' which is not functionally dependent on columns in GROUP BY clause; SELECT列表的表达式＃1不在GROUP BY子句中，并且包含未聚合的列'support_desk.mod_users_groups.group_id'，该列在功能上不依赖于GROUP BY子句中的列； this is incompatible with sql_mode=only_full_group_by 这与sql_mode = only_full_group_by不兼容

Note the Manual page for Mysql 5.7 on the topic of Server SQL Modes . 请注意有关服务器SQL模式主题的Mysql 5.7手册页。

This is the query that is giving me trouble: 这是给我带来麻烦的查询：

SELECT mod_users_groups.group_id AS 'value', 
       group_name AS 'text' 
FROM mod_users_groups
LEFT JOIN mod_users_data ON mod_users_groups.group_id = mod_users_data.group_id 
WHERE  mod_users_groups.active = 1 
  AND mod_users_groups.department_id = 1 
  AND mod_users_groups.manage_work_orders = 1 
  AND group_name != 'root' 
  AND group_name != 'superuser' 
GROUP BY group_name 
HAVING COUNT(`user_id`) > 0 
ORDER BY group_name

I did some googling on the issue, but I don't understand only_full_group_by enough to figure out what I need to do to fix the query. 我在这个问题上做了一些谷歌搜索，但是我对only_full_group_by理解不足以弄清楚我需要做什么来解决查询。 Can I just turn off the only_full_group_by option, or is there something else I need to do? 我可以仅关闭only_full_group_by选项，还是需要做其他事情？

Let me know if you need more information. 如果您需要更多信息，请与我们联系。

#1楼

参考：https://stackoom.com/question/2J8uk/在MySql中执行查询时与only-full-group-by相关的错误

#2楼

I would just add group_id to the GROUP BY . 我只是将group_id添加到GROUP BY 。

When SELECT ing a column that is not part of the GROUP BY there could be multiple values for that column within the groups, but there will only be space for a single value in the results. 当SELECT不属于GROUP BY列时，组中该列可能有多个值，但结果中只有一个值的空间。 So, the database usually needs to be told exactly how to make those multiple values into one value. 因此，通常需要准确告知数据库如何将多个值变成一个值。 Commonly, this is done with an aggregate function like COUNT() , SUM() , MAX() etc... I say usually because most other popular database systems insist on this. 通常，这是通过诸如COUNT() ， SUM() ， MAX()等聚合函数来完成的。我之所以这样说是因为大多数其他流行的数据库系统都坚持这样做。 However, in MySQL prior to version 5.7 the default behaviour has been more forgiving because it will not complain and then arbitrarily choose any value ! 但是，在版本5.7之前的MySQL中，默认行为更为宽容，因为它不会抱怨，然后可以任意选择任何值 ！ It also has an ANY_VALUE() function that could be used as another solution to this question if you really needed the same behaviour as before. 如果您确实需要与以前相同的行为，它还具有ANY_VALUE()函数，可以用作该问题的另一个解决方案。 This flexibility comes at a cost because it is non-deterministic, so I would not recommend it unless you have a very good reason for needing it. 这种灵活性要付出一定的代价，因为它是不确定性的，因此除非您有充分的理由需要它，否则我不建议您这样做。 MySQL are now turning on the only_full_group_by setting by default for good reasons, so it's best to get used to it and make your queries comply with it. 由于种种原因，MySQL现在默认情况下默认启用了only_full_group_by设置，因此最好习惯它并使查询符合它。

So why my simple answer above? 那么，为什么我上面的简单答案呢？ I've made a couple of assumptions: 我做了两个假设：

1) the group_id is unique. 1） group_id是唯一的。 Seems reasonable, it is an 'ID' after all. 似乎合理，毕竟它是一个“ ID”。

2) the group_name is also unique. 2） group_name也是唯一的。 This may not be such a reasonable assumption. 这可能不是一个合理的假设。 If this is not the case and you have some duplicate group_names and you then follow my advice to add group_id to the GROUP BY , you may find that you now get more results than before because the groups with the same name will now have separate rows in the results. 如果不是这种情况，并且您有一些重复的group_names ，然后按照我的建议将group_id添加到GROUP BY ，您可能会发现现在获得的结果比以前更多，因为同名的组现在将在其中具有单独的行。结果。 To me, this would be better than having these duplicate groups hidden because the database has quietly selected a value arbitrarily! 对我来说，这比隐藏这些重复的组更好，因为数据库已悄悄地选择了一个值！

It's also good practice to qualify all the columns with their table name or alias when there's more than one table involved... 当涉及多个表时，最好用表名或别名来限定所有列。

SELECT 
  g.group_id AS 'value', 
  g.group_name AS 'text' 
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id 
WHERE g.active = 1 
  AND g.department_id = 1 
  AND g.manage_work_orders = 1 
  AND g.group_name != 'root' 
  AND g.group_name != 'superuser' 
GROUP BY 
  g.group_name, 
  g.group_id 
HAVING COUNT(d.user_id) > 0 
ORDER BY g.group_name

#3楼

You can try to disable the only_full_group_by setting by executing the following: 您可以通过执行以下操作来尝试禁用only_full_group_by设置：

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

MySQL 8 does not accept NO_AUTO_CREATE_USER so that needs to be removed. MySQL 8不接受NO_AUTO_CREATE_USER因此需要删除它。

#4楼

If you don't want to make any changes in your current query then follow the below steps - 如果您不想在当前查询中进行任何更改，请按照以下步骤操作-

vagrant ssh into your box 流浪者ssh放入您的盒子
Type: sudo vim /etc/mysql/my.cnf 键入： sudo vim /etc/mysql/my.cnf
Scroll to the bottom of file and type A to enter insert mode 滚动到文件底部，然后键入A进入插入模式

Copy and paste 复制和粘贴

 [mysqld] sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Type esc to exit input mode 键入esc退出输入模式
Type :wq to save and close vim. 输入:wq保存并关闭vim。
Type sudo service mysql restart to restart MySQL. 输入sudo service mysql restart重启MySQL。

#5楼

you can turn off the warning message as explained in the other answers or you can understand what's happening and fix it. 您可以按照其他答案中的说明关闭警告消息，也可以了解发生的情况并进行修复。

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY which means when you are grouping rows and then selecting something out of that groups, you need to explicitly say which row should that selection be made from. 从MySQL 5.7.5开始，默认的SQL模式包括ONLY_FULL_GROUP_BY ，这意味着当您将行分组然后从该组中选择某项时，您需要明确说出应该从哪一行进行选择。

Mysql needs to know which row in the group you're looking for, which gives you two options Mysql需要知道您要查找的组中的哪一行，这给了您两个选择

You can also add the column you want to the group statement group by rect.color, rect.value which can be what you want in some cases otherwise would return duplicate results with the same color which you may not want 您还可以group by rect.color, rect.value将所需的列添加到group语句group by rect.color, rect.value在某些情况下group by rect.color, rect.value可能是您想要的列，否则将返回重复的结果，并且颜色可能与您不希望的相同
you could also use aggregate functions of mysql to indicate which row you are looking for inside the groups like AVG() MIN() MAX() complete list 您还可以使用mysql的聚合函数来指示要在组中查找的行，例如AVG() MIN() MAX() 完整列表
AND finally you can use ANY_VALUE() if you are sure that all the results inside the group are the same. 最后，如果您确定组内的所有结果都相同，则可以使用ANY_VALUE() 。 doc doc

#6楼

This is what helped me to understand the entire issue: 这就是帮助我了解整个问题的原因：

And in the following another example of a problematic query. 在下面的另一个有问题查询的示例中。

Problematic: 有问题的：

SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM `gameplay`
                        WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
                        AND cookieid = #

Solved by adding this to the end: 通过将其添加到末尾来解决：

  GROUP BY timestamp, userid, cookieid, questionid, answerid, elapsed, ipaddress

Note: See the error message in PHP, it tells you where the problem lies. 注意：请参阅PHP中的错误消息，它告诉您问题出在哪里。

Example: 例：

MySQL query error 1140: In aggregated query without GROUP BY, expression #4 of SELECT list contains nonaggregated column 'db.gameplay.timestamp'; MySQL查询错误1140：在没有GROUP BY的聚合查询中，SELECT列表的表达式＃4包含非聚合列'db.gameplay.timestamp'； this is incompatible with sql_mode=only_full_group_by - Query: SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM gameplay WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND userid = 1 这与sql_mode = only_full_group_by不兼容-查询：SELECT COUNT（*）作为尝试，SUM（elapsed）作为经过，total，userid，timestamp，questionid，answerid，SUM（正确）为正确，经过，ipaddress from gameWhere时间戳> = DATE_SUB （现在（），间隔1天）和用户ID = 1

In this case, expression #4 was missing in the GROUP BY. 在这种情况下，GROUP BY中缺少表达式＃4 。

w36680130

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
在MySql中执行查询时与only_full_group_by相关的错误

I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am workin
复制链接

扫一扫