在MySql中执行查询时与only_full_group_by相关的错误 [英] Error related to only_full_group_by when executing a query in MySql

问题描述

我升级了我的系统，并安装了MySql 5.7.9，并使用了我正在开发的Web应用程序的php。我有一个动态创建的查询，并在旧版本的MySql中运行时，它工作正常。自升级到5.7以后，我得到这个错误：

SELECT列表的表达式＃1不在GROUP BY子句中，并且包含
nonaggregated列'support_desk.mod_users_groups.group_id'是
在功能上不依赖于GROUP BY子句中的列;这是
与sql_mode = only_full_group_by不兼容

请注意Mysql 5.7的手册页，主题为服务器SQL模式。

这是该查询给我带来了麻烦：
$ b

  SELECT mod_users_groups.group_id AS'value'，
组名 '文本' 
起价mod_users_groups 
 LEFT JOIN mod_users_data ON mod_users_groups.group_id = mod_users_data.group_id 
，其中mod_users_groups.active = 1 
和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 
  

我在这个问题上做了一些Google搜索，但是我没有理解 only_full_group_by 足以找出我需要做什么来修复查询。我可以关闭 only_full_group_by 选项，还是有其他我需要做的？

让我知道如果你需要更多的信息。

解决方案

我只需将 group_id GROUP BY 。 $ b SELECT 不属于 GROUP BY 组中的该列可能有多个值，但结果中只有空间可用于单个值。因此，通常需要准确告知数据库 如何将这些多个值合并为一个值。通常，这是通过一个像 COUNT（）， SUM（）， MAX（）等...我通常说，因为大多数其他流行的数据库系统坚持这样做。但是，在5.7版以前的MySQL中，默认行为更加宽容，因为它不会抱怨，然后随意选择任何值！它还有一个 ANY_VALUE（）函数，如果您确实需要与以前相同的行为，可以将其用作此问题的另一个解决方案。这种灵活性的代价是非确定性的，所以我不会推荐它，除非你有很好的理由需要它。 MySQL现在默认开启了 only_full_group_by 设置，所以最好先适应它并让你的查询符合它。

那么为什么我上面的简单答案呢？我做了一些假设：

1） group_id 是唯一的。看起来合理，毕竟它是'ID'。

<2> group_name 也是唯一的。这可能不是一个合理的假设。如果情况并非如此，并且您有一些重复的 group_names ，然后您按照我的建议将 group_id 添加到 GROUP BY ，您可能会发现现在获得的结果比以前更多，因为具有相同名称的组现在将在结果中具有单独的行。对我来说，这会比隐藏这些重复的组更好，因为数据库已经悄悄地选择了一个值！

当涉及多个表时，使用表名或别名限定所有列也是一种很好的做法......

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！='超级用户'
GROUP BY
g.group_name，
g.group_id
HAVING COUNT（d.user_id）> 0
ORDER BY g.group_name


I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. I have a query that is dynamically created, and when run in older versions of MySql it works fine. Since upgrading to 5.7 I get this error:

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; this is incompatible with sql_mode=only_full_group_by

Note the Manual page for Mysql 5.7 on the topic of Server SQL Modes.

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. Can I just turn off the only_full_group_by option, or is there something else I need to do?

Let me know if you need more information.

解决方案

I would just add group_id to the GROUP BY.

When SELECTing 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. 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. 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! 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. 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.

So why my simple answer above? I've made a couple of assumptions:

1) the group_id is unique. Seems reasonable, it is an 'ID' after all.

2) the group_name is also unique. 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. 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

这篇关于在MySql中执行查询时与only_full_group_by相关的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！