MySQL #1055 - SELECT 列表的表达式不在 GROUP BY 子句中，并且包含与 sql_mode=only_full_group_by 不兼容的非聚合列

#1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by

提问by Miloud BAKTETE

my Query :

我的查询：

select libelle,credit_initial,disponible_v,sum(montant) as montant 
FROM fiche,annee,type where type.id_type=annee.id_type and annee.id_annee=fiche.id_annee 
and annee = year(current_timestamp) GROUP BY libelle order by libelle asc

• Mysql 5.7.9 runs the query without any problem but mysql 5.7.12 display the error above Could you please help me with that

• Mysql 5.7.9 运行查询没有任何问题，但 mysql 5.7.12 显示上述错误你能帮我吗

回答by Miloud BAKTETE

I had a struggle getting this to work i've tested it and it's working on lamp server mysql version 5.12

我很难让它工作我已经测试过它并且它在灯服务器 mysql 版本 5.12 上工作

So, steps to success:

所以，成功的步骤：

1. sudo vim /etc/mysql/conf.d/mysql.cnf

2. Scroll to the bottom of file 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

1. sudo vim /etc/mysql/conf.d/mysql.cnf

2. 滚动到文件底部复制并粘贴

   [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

to the bottom of the file

到文件底部

1. save and exit input mode
2. sudo service mysql restartto restart MySQL.

3. 保存并退出输入模式
4. sudo service mysql restart重新启动 MySQL。

Done!

完毕！

回答by Henry

This worked for me:

这对我有用：

mysql -u root -p
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

You might need sudo for the first step:

第一步您可能需要 sudo：

sudo mysql -u root -p

回答by gr1zzly be4r

You need to specify all of the columns that you're not using for an aggregation function in your GROUP BYclause like this:

您需要在GROUP BY子句中指定所有不用于聚合函数的列，如下所示：

select libelle,credit_initial,disponible_v,sum(montant) as montant 
FROM fiche,annee,type where type.id_type=annee.id_type and annee.id_annee=fiche.id_annee 
and annee = year(current_timestamp) GROUP BY libelle,credit_initial,disponible_v order by libelle asc

The full_group_bymode basically makes you write more idiomatic SQL. You can turn off this setting if you'd like. There are different ways to do this that are outlined in the MySQL Documentation. Here's MySQL's definition of what I said above:

该full_group_by模式基本上可以让您编写更地道的 SQL。如果您愿意，可以关闭此设置。MySQL 文档中概述了执行此操作的不同方法。这是 MySQL 对我上面所说的定义：

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects 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 them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

MySQL 5.7.5 及更高版本实现了功能依赖检测。如果 ONLY_FULL_GROUP_BY SQL 模式被启用（默认情况下），MySQL 拒绝查询，其中选择列表、HAVING 条件或 ORDER BY 列表引用非聚合列，这些列既不在 GROUP BY 子句中命名，也不在功能上依赖它们. （5.7.5之前，MySQL不检测函数依赖，ONLY_FULL_GROUP_BY默认不开启。5.7.5之前的行为描述，参见MySQL 5.6参考手册。）

You're getting the error because you're on a version < 5.7.5

您收到错误是因为您使用的是 < 5.7.5 的版本

回答by Anil Gupta

You can disable sql_mode=only_full_group_byby some command you can try this by terminal or MySql IDE

您可以 通过某些命令禁用 sql_mode=only_full_group_by您可以通过终端或 MySql IDE 尝试此操作

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

I have tried this and worked for me. Thanks :)

我已经尝试过这个并为我工作。谢谢 ：）

回答by Tauqueer Hassan

Solution 1: Remove ONLY_FULL_GROUP_BY from mysql console

解决方案 1：从 mysql 控制台中删除 ONLY_FULL_GROUP_BY

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

you can read more here

你可以在这里阅读更多

Solution 2: Remove ONLY_FULL_GROUP_BY from phpmyadmin

解决方案 2：从 phpmyadmin 中删除 ONLY_FULL_GROUP_BY

Open phpmyadmin & select localhost
Click on menu Variables & scroll down for sql mode
Click on edit button to change the values & remove ONLY_FULL_GROUP_BY & click on save. 

回答by DRapp

You have to aggregate by anything NOT INthe group byclause.

你必须通过什么聚合NOT IN的group by条款。

So,there are two options...Add Credit_Initialand Disponible_vto the group by

因此，有两个选项...将 Credit_Initial和Disponible_v添加到group by

OR

或者

Change them to MAX( Credit_Initial ) as Credit_Initial, MAX( Disponible_v ) as Disponible_vif you know the values are constant anyhow and have no other impact.

MAX( Credit_Initial ) as Credit_Initial, MAX( Disponible_v ) as Disponible_v如果您知道这些值无论如何都是恒定的并且没有其他影响，请将它们更改为。

回答by scaisEdge

Base ond defualt config of 5.7.5 ONLY_FULL_GROUP_BY You should use all the not aggregate column in your group by

基于 5.7.5 ONLY_FULL_GROUP_BY 的默认配置您应该使用组中的所有非聚合列

select libelle,credit_initial,disponible_v,sum(montant) as montant 
FROM fiche,annee,type 
where type.id_type=annee.id_type 
and annee.id_annee=fiche.id_annee 
and annee = year(current_timestamp) 
GROUP BY libelle,credit_initial,disponible_v order by libelle asc

回答by rust

So let's fully understand, Let's say you have a query which works in localhost but does not in production mode, This is because in MySQL 5.7 and abovethey decided to activate the sql_mode=only_full_group_byby default, basically it is a strict mode which prevents you to select non aggregated fields.

所以让我们充分理解，假设您有一个在 localhost 中工作但不在生产模式下的查询，这是因为MySQL 5.7 and above他们决定sql_mode=only_full_group_by默认激活，基本上它是一种严格模式，可防止您选择非聚合字段。

Here's the query (works in local but not in production mode) :

这是查询（在本地工作但不在生产模式下）：

SELECT post.*, YEAR(created_at) as year
FROM post
GROUP BY year

SELECT post.id, YEAR(created_at) as year  // This will generate an error since there are many ids
FROM post
GROUP BY year

To verify if the sql_mode=only_full_group_byis activated for, you should execute the following query :

要验证 是否sql_mode=only_full_group_by已激活，您应该执行以下查询：

SELECT @@sql_mode;   //localhost

Output: IGNORE_SPACE, STRICT_TRANS, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION(If you don't see it, it means it is deactivated)

输出：（IGNORE_SPACE, STRICT_TRANS, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION如果你没有看到它，这意味着它已被停用）

But if try in production mode, or somewhere where it gives you the error it should be activated:

但是如果在生产模式下尝试，或者在它给你错误的地方尝试它应该被激活：

SELECT @@sql_mode;   //production

Output:ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO...And it's ONLY_FULL_GROUP_BYwe're looking for here.

输出：ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO...这就是ONLY_FULL_GROUP_BY我们在这里寻找的。

Otherwise, if you are using phpMyAdminthen go to -> Variablesand search for sql_mode

否则，如果您使用的是phpMyAdmin，则转到 ->Variables并搜索sql_mode

Let's take our previous example and adapt it to it :

让我们以前面的例子为例并对其进行调整：

SELECT MIN(post.id), YEAR(created_at) as year  //Here we are solving the problem with MIN()
FROM post
GROUP BY year

And the same for MAX()

同样对于 MAX()

And if we want all the IDs, we're going to need:

如果我们想要所有的 ID，我们将需要：

SELECT GROUP_CONCAT(post.id SEPARATOR ','), YEAR(created_at) as year
FROM post
GROUP BY year

or another newly added function:

或另一个新添加的功能：

SELECT ANY_VALUE(post.id), YEAR(created_at) as year 
FROM post
GROUP BY year

?? ANY_VALUE does not exist for MariaDB

?? MariaDB 不存在 ANY_VALUE

And If you want all the fields, then you could use the same:

如果你想要所有的字段，那么你可以使用相同的：

SELECT ANY_VALUE(post.id), ANY_VALUE(post.slug), ANY_VALUE(post.content) YEAR(created_at) as year 
FROM post
GROUP BY year

? To deactivate the sql_mode=only_full_group_bythen you'll need to execute this query:

? 要停用sql_mode=only_full_group_by然后您需要执行此查询：

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

Sorry for the novel, hope it helps.

对不起小说，希望对你有帮助。

回答by Atul

Same thing is happened with 8.0+ versions as well. By default in 8.0+ version it is "enabled" by default. Here is the link official document reference

8.0+ 版本也发生了同样的事情。默认情况下，在 8.0+ 版本中它默认为“启用”。这是链接官方文档参考

In case of 5.6+, 5.7+ versions, the property "ONLY_FULL_GROUP_BY" is disabled by default.

对于 5.6+、5.7+ 版本，默认情况下禁用属性“ONLY_FULL_GROUP_BY”。

To disabled it, follow the same steps suggested by @Miloud BAKTETE

要禁用它，请按照@Miloud BAKTETE 建议的相同步骤操作

回答by bilal

just go to the window bottom tray click on wamp icon ,click mysql->my.ini,then there is option ;sql-mode="" uncomment this make it like sql-mode="" and restart wamp worked for me

只需转到窗口底部托盘单击 wamp 图标，单击 mysql->my.ini，然后有选项 ;sql-mode="" 取消注释这使它像 sql-mode="" 并重新启动 wamp 为我工作