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

Error related to only_full_group_by when executing a query in MySql

提问by Dan Bemowski

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:

我已经升级了我的系统，并为我正在处理的 Web 应用程序安装了带有 php 的 MySql 5.7.9。我有一个动态创建的查询，当在旧版本的 MySql 中运行时，它工作正常。升级到 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; this is incompatible with sql_mode=only_full_group_by

SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“support_desk.mod_users_groups.group_id”，该列在功能上不依赖于 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_byenough to figure out what I need to do to fix the query. Can I just turn off the only_full_group_byoption, or is there something else I need to do?

我对这个问题做了一些谷歌搜索，但我不明白only_full_group_by我需要做什么来修复查询。我可以关闭该only_full_group_by选项，还是我需要做其他事情？

Let me know if you need more information.

如果您需要更多信息，请与我们联系。

采纳答案by davmos

I would just add group_idto the GROUP BY.

我只想添加group_id到GROUP BY.

When SELECTing a column that is not part of the GROUP BYthere 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 usuallyneeds 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 usuallybecause 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_bysetting by default for good reasons, so it's best to get used to it and make your queries comply with it.

当SELECTing 不属于的GROUP BY列时，组内该列可能有多个值，但结果中只有一个值的空间。因此，通常需要准确地告诉数据库如何将这些多个值变成一个值。通常，这是与像一个聚合函数中完成COUNT()，SUM()，MAX()等...我说一般，因为大多数其他流行的数据库系统坚持这一点。但是，在 MySQL 5.7 之前的版本中，默认行为更加宽容，因为它不会抱怨然后随意选择任何值！它还有一个ANY_VALUE()如果您确实需要与以前相同的行为，则可以用作此问题的另一种解决方案的函数。这种灵活性是有代价的，因为它是不确定的，所以除非你有充分的理由需要它，否则我不会推荐它。MySQL 现在only_full_group_by出于充分的理由默认启用该设置，因此最好习惯它并使您的查询符合它。

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

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

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

1)group_id是独一无二的。看似合理，毕竟是“ID”。

2) the group_nameis also unique. This may not be such a reasonable assumption. If this is not the case and you have some duplicate group_namesand you then follow my advice to add group_idto 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!

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

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

回答by WeiYuan

You can try to disable the only_full_group_bysetting 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_USERso that needs to be removed.

MySQL 8 不接受，NO_AUTO_CREATE_USER因此需要删除。

回答by azerafati

you can turn off the warning message as explained in the other answers or you can understand what's happeningand fix it.

您可以按照其他答案中的说明关闭警告消息，或者您可以了解正在发生的事情并修复它。

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BYwhich means when you are grouping rows and then selecting something out of that groups, you need to explicitly say which rowshould 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.valuewhich can be what you want in some cases otherwise would return duplicate results with the same color which you may not want
• you could also use aggregate functionsof mysql to indicate which row you are looking for inside the groups like AVG()MIN()MAX()complete list
• AND finally you can use ANY_VALUE()if you are sure that all the results inside the group are the same. doc

• 您还可以将您想要的列添加到 group 语句中group by rect.color, rect.value，这在某些情况下可能是您想要的，否则会返回您可能不想要的相同颜色的重复结果
• 您还可以使用mysql 的聚合函数来指示您在组内查找哪一行，如AVG()MIN()MAX()完整列表
• 最后，ANY_VALUE()如果您确定组内的所有结果都相同，则可以使用。文档

回答by Ajai

If you don't want to make any changes in your current query then follow the below steps -

如果您不想对当前查询进行任何更改，请按照以下步骤操作 -

1. vagrant ssh into your box
2. Type: sudo vim /etc/mysql/my.cnf
3. Scroll to the bottom of file and type Ato enter insert mode

4. 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
   

5. Type escto exit input mode

6. Type :wqto save and close vim.
7. Type sudo service mysql restartto restart MySQL.

1. vagrant ssh 进入你的盒子
2. 类型： sudo vim /etc/mysql/my.cnf
3. 滚动到文件底部并输入A以进入插入模式

4. 复制和粘贴

   [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
   

5. 键入esc退出输入模式

6. 键入:wq以保存并关闭 vim。
7. 键入sudo service mysql restart以重新启动 MySQL。

回答by Italo Borssatto

Use ANY_VALUE()to refer to the nonaggregated column.

使用ANY_VALUE()来指代非聚集列。

SELECT name,           address , MAX(age) FROM t GROUP BY name; -- fails
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; -- works

From MySQL 5.7 docs:

来自MySQL 5.7 文档：

You can achieve the same effect without disabling ONLY_FULL_GROUP_BYby using ANY_VALUE()to refer to the nonaggregated column.

...

This query might be invalid with ONLY_FULL_GROUP_BYenabled because the nonaggregated address column in the select list is not named in the GROUP BYclause:

SELECT name, address, MAX(age) FROM t GROUP BY name;

...

If you know that, for a given data set, each name value in fact uniquely determines the address value, address is effectively functionally dependent on name. To tell MySQL to accept the query, you can use the ANY_VALUE()function:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

您可以在不禁ONLY_FULL_GROUP_BY用的情况下通过使用ANY_VALUE()来引用非聚合列来实现相同的效果。

...

此查询在ONLY_FULL_GROUP_BY启用时可能无效，因为选择列表中的非聚合地址列未在GROUP BY子句中命名：

SELECT name, address, MAX(age) FROM t GROUP BY name;

...

如果您知道，对于给定的数据集，每个名称值实际上唯一地确定地址值，则地址在功能上实际上依赖于名称。要告诉 MySQL 接受查询，您可以使用以下ANY_VALUE()函数：

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

回答by Abraham Tugalov

I will try to explain you what this error is about.
Starting from MySQL 5.7.5, option ONLY_FULL_GROUP_BYis enabled by default.
Thus, according to standart SQL92 and earlier:

我将尝试向您解释此错误的含义。
从 MySQL 5.7.5 开始ONLY_FULL_GROUP_BY，默认启用选项。
因此，根据标准 SQL92 及更早版本：

does not permit 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 (uniquely determined by) GROUP BY columns

不允许查询的选择列表、HAVING 条件或 ORDER BY 列表引用非聚合列，这些列既不在 GROUP BY 子句中命名，也不在功能上依赖于（唯一确定的）GROUP BY 列

(read more in docs)

（在文档中阅读更多内容）

So, for example:

因此，例如：

SELECT * FROM `users` GROUP BY `name`;

You will get error message after executing query above.

执行上述查询后，您将收到错误消息。

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testsite.user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

#1055 - SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“testsite.user.id”，它在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by 不兼容

Why?
Because MySQL dont exactly understand, what certain values from grouped records to retrieve, and this is the point.

为什么？
因为 MySQL 并不完全明白，从分组记录中检索哪些某些值，这就是重点。

I.E. lets say you have this records in your userstable:

IE 可以说你的users表中有这些记录：

And you will execute invalid query showen above.
And you will get error shown above, because, there is 3 records with name John, and it is nice, but, all of them have different emailfield values.
So, MySQL simply don't understand which of them to return in resulting grouped record.

您将执行上面显示的无效查询。
你会得到上面显示的错误，因为有 3 条记录带有 name John，这很好，但是，它们都有不同的email字段值。
所以，MySQL 根本不明白在结果分组记录中返回哪些。

You can fix this issue, by simply changing your query like this:

您可以通过简单地更改查询来解决此问题：

SELECT `name` FROM `users` GROUP BY `name`

Also, you may want to add more fields to SELECT section, but you cant do that, if they are not aggregated, but there is crutch you could use (but highly not reccomended):

此外，您可能希望向 SELECT 部分添加更多字段，但如果它们未聚合，则不能这样做，但是您可以使用拐杖（但强烈不推荐）：

SELECT ANY_VALUE(`id`), ANY_VALUE(`email`), `name` FROM `users` GROUP BY `name`

Now, you may ask, why using ANY_VALUEis highly not recommended?
Because MySQL don't exactly know what value of grouped records to retrieve, and by using this function, you asking it to fetch any of them (in this case, email of first record with name = John was fetched).
Exactly I cant come up with any ideas on why you would want this behaviour to exist.

Please, if you dont understand me, read more about how grouping in MySQL works, it is very simple.

现在，您可能会问，为什么ANY_VALUE强烈不推荐使用？
因为 MySQL 并不确切知道要检索的分组记录的值，并且通过使用此函数，您要求它获取其中的任何一个（在这种情况下，获取了 name = John 的第一条记录的电子邮件）。
确切地说，我无法想出您为什么希望这种行为存在的任何想法。

请，如果您不明白我的意思，请阅读更多关于 MySQL 中分组如何工作的信息，它非常简单。

And by the end, here is one more simple, yet valid query.
If you want to query total users count according to available ages, you may want to write down this query

最后，这是一个更简单但有效的查询。
如果你想根据可用年龄查询总用户数，你可能需要写下这个查询

SELECT `age`, COUNT(`age`) FROM `users` GROUP BY `age`;

Which is fully valid, according to MySQL rules.
And so on.

It is important to understand what exactly the problem is and only then write down the solution.

根据 MySQL 规则，这是完全有效的。
等等。

重要的是要了解问题究竟是什么，然后才写下解决方案。

回答by Ryan

I am using Laravel 5.3, mysql 5.7.12, on laravel homestead (0.5.0, I believe)

我在 Laravel Homestead 上使用 Laravel 5.3、mysql 5.7.12（我相信是 0.5.0）

Even after explicitly setting editing /etc/mysql/my.cnfto reflect:

即使在明确设置编辑/etc/mysql/my.cnf以反映之后：

[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

I was still receiving the error.

我仍然收到错误。

I had to change config/database.phpfrom trueto false:

我不得不config/database.php从true改为false：

    'mysql' => [
        'strict' => false, //behave like 5.6
        //'strict' => true //behave like 5.7
    ], 

Further reading:

进一步阅读：

https://laracasts.com/discuss/channels/servers/set-set-sql-mode-on-homesteadhttps://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2

https://laracasts.com/discuss/channels/servers/set-set-sql-mode-on-homestead https://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel -5-2

回答by PiyaChakraborty

If you are using wamp 3.0.6 or any upper version other than the stable 2.5 you might face this issue, firstly the issue is with sql . you have to name the fields accordingly. but there is another way by which you can solve it. click on green icon of wamp. mysql->mysql settings-> sql_mode->none. or from console you can change the default values.

如果您使用 wamp 3.0.6 或稳定 2.5 以外的任何更高版本，您可能会遇到此问题，首先是 sql 问题。您必须相应地命名字段。但是还有另一种方法可以解决它。单击 wamp 的绿色图标。mysql->mysql 设置-> sql_mode->无。或者从控制台您可以更改默认值。

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';

回答by Jagdeep Singh

Addition of lines (mention below) in file : /etc/mysql/my.cnf

在文件中添加行（下面提到）：/etc/mysql/my.cnf

[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

Work fine for me. Server version: 5.7.18-0ubuntu0.16.04.1 - (Ubuntu)

对我来说很好。服务器版本：5.7.18-0ubuntu0.16.04.1 - (Ubuntu)

回答by Anil Gupta

Go to mysql or phpmyadmin and select database then simply execute this query and it will work. Its working fine for me.

转到 mysql 或 phpmyadmin 并选择数据库，然后只需执行此查询即可。它对我来说工作正常。

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