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

Question

我已经升级了我的系统，并为我正在处理的一个web应用程序安装了带有php的MySQL5.7.9。我有一个动态创建的查询，当运行在旧版本的MySql中时，它可以正常工作。自从升级到5.7后,我遇到以下错误：

SELECT列表的表达式#1不在GROUP BY子句中，并且包含非聚合列'SUPPORT_DESK.MOD_USERS_GROUPS.GROUP_ID'，该列在功能上不依赖于GROUP BY子句中的列；这与sql_mode=only_full_group_by不兼容

请注意关于服务器SQL模式主题的MySQL5.7手册页面。

这是给我带来麻烦的问题：

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

我对这个问题进行了一些搜索，但我对only_full_group_by的理解还不够，无法确定需要做什么来修复查询。我可以只关闭only_full_group_by选项吗，还是需要做其他事情？

如果你需要更多的信息请告诉我。

Answer 1

我只需将group_id添加到组by中。

当selecting不属于分组依据的列时，该列在组中可能有多个值，但在结果中只有一个值的空间。因此，数据库通常需要知道如何将多个值转换为一个值。通常，这是通过聚合函数来完成的，如count()、sum()、max()等。我说通常是因为大多数其他流行的数据库系统坚持这样做。然而，在5.7版本之前的MySQL，默认行为更加宽容，因为它不会抱怨，然后任意选择任何值！它还有一个any_value()函数，如果您真的需要与以前相同的行为，它可以用作这个问题的另一个解决方案。这种灵活性是有代价的，因为它是非确定性的，所以我不会推荐它，除非您有非常好的理由需要它。MySQL现在在默认情况下打开only_full_group_by设置，原因很充分，所以最好习惯它并使您的查询符合它。

那么为什么我上面简单的回答呢？我做了几个假设：

1）group_id是唯一的。看起来很合理，毕竟它是一个“id”。

2）group_name也是唯一的。这可能不是一个合理的假设。如果情况并非如此，并且您有一些重复的group_names并且您然后按照我的建议将group_id添加到组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 != 'superuser' 
GROUP BY 
  g.group_name, 
  g.group_id 
HAVING COUNT(d.user_id) > 0 
ORDER BY g.group_name