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

2021-11-20 00:00:00 sql group-by mysql mysql-error-1055

我已经升级了我的系统并为我正在开发的 Web 应用程序安装了带有 php 的 MySql 5.7.9.我有一个动态创建的查询，当在旧版本的 MySql 中运行时，它工作正常.升级到 5.7 后，我收到此错误:

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:

SELECT 列表的表达式 #1 不在 GROUP BY 子句中并且包含非聚合列support_desk.mod_users_groups.group_id"是在功能上不依赖于 GROUP BY 子句中的列；这是与 sql_mode=only_full_group_by 不兼容

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

注意关于服务器SQL模式.

这是给我带来麻烦的查询:

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

我对这个问题进行了一些谷歌搜索，但我对 only_full_group_by 的了解不足以弄清楚我需要做什么来修复查询.我可以关闭 only_full_group_by 选项，还是我需要做其他事情?

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.

推荐答案

我只想将 group_id 添加到 GROUP BY.

I would just add group_id to the GROUP BY.

当 SELECT 不属于 GROUP BY 的列时，组内该列可能有多个值，但只有一个空间结果中的单个值.因此，数据库通常需要被告知如何将这些多个值变成一个值.通常，这是通过一个聚合函数来完成的，比如 COUNT()、SUM()、MAX() 等等......我说通常因为大多数其他流行的数据库系统都坚持这一点.但是，在 MySQL 5.7 之前的版本中，默认行为更加宽容，因为它不会抱怨然后任意选择任何值！它还有一个 ANY_VALUE() 函数，如果你真的需要和以前一样的行为，它可以用作这个问题的另一种解决方案.这种灵活性是有代价的，因为它是不确定的，所以除非你有充分的理由需要它，否则我不会推荐它.MySQL 现在默认打开 only_full_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) group_id 是唯一的.看起来有道理，毕竟是一个ID".

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

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

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

相关文章