MySQL SELECT 列表不在 GROUP BY 子句中并且包含非聚合列......与 sql_mode=only_full_group_by 不兼容

SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

提问by Dhanu K

AM using MySQL 5.7.13 on my windows PC with WAMP Server

我在带有 WAMP 服务器的 Windows PC 上使用 MySQL 5.7.13

Here my Problem is While executing this query

这里我的问题是执行此查询时

SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
      `status` = 'Active'
GROUP BY `proof_type`

Am getting always error like this

我总是得到这样的错误

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.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 子句中，并且包含非聚合列“returntr_prod.tbl_customer_pod_uploads.id”，该列在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by 不兼容

Can you please tell me the best solution...

你能告诉我最好的解决方案吗...

I need Result like

我需要像这样的结果

+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
| id | user_id | load_id | bill_id | latitude | langitude | proof_type | document_type | file_name    | is_private | status | createdon           | updatedon           |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
|  1 |       1 | 78      | 1       | 21.1212  | 21.5454   |          1 |             1 | id_Card.docx |          0 | Active | 2017-01-27 11:30:11 | 2017-01-27 11:30:14 |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+

回答by Dhanu K

This

这个

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.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 子句中，并且包含非聚合列“returntr_prod.tbl_customer_pod_uploads.id”，该列在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by 不兼容

will be simply solved by changing the sql mode in MySQL by this command,

将通过此命令更改 MySQL 中的 sql 模式来简单解决，

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

This too works for me.. I used this, because in my project there are many Queries like this so I just changed this sql mode to only_full_group_by

这也适用于我..我使用了这个，因为在我的项目中有很多这样的查询，所以我只是将这个 sql 模式更改为 only_full_group_by

Thank You... :-)

谢谢你... ：-）

回答by Tim Biegeleisen

When MySQL's only_full_group_bymode is turned on, it means that strict ANSI SQL rules will apply when using GROUP BY. With regard to your query, this means that if you GROUP BYof the proof_typecolumn, then you can only select two things:

当 MySQL 的only_full_group_by模式打开时，意味着使用GROUP BY. 关于您的查询，这意味着如果您GROUP BY是该proof_type列，那么您只能选择两件事：

• the proof_typecolumn, or
• aggregates of any other column

• 该proof_type列或
• 任何其他列的聚合

By "aggregates" of other columns, I mean using an aggregate function such as MIN(), MAX(), or AVG()with another column. So in your case the following query would be valid:

由其他列的“集合”，我的意思是使用聚合功能，诸如MIN()，MAX()或AVG()与另一列。因此，在您的情况下，以下查询将有效：

SELECT proof_type,
       MAX(id) AS max_id,
       MAX(some_col),
       MIN(some_other_col)
FROM tbl_customer_pod_uploads
WHERE load_id = '78' AND
      status = 'Active'
GROUP BY proof_type

The vast majority of MySQL GROUP BYquestions which I see on SO have strict mode turned off, so the query is running, but with incorrect results. In your case, the query won't run at all, forcing you to think about what you really want to do.

GROUP BY我在 SO 上看到的绝大多数 MySQL问题都关闭了严格模式，因此查询正在运行，但结果不正确。在你的情况下，查询根本不会运行，迫使你考虑你真正想要做什么。

Note: The ANSI SQL extends what is allowed to be selected in GROUP BYby also including columns which are functionally dependenton the column(s) being selected. An example of functional dependency would be grouping by a primary key column in a table. Since the primary key is guaranteed to be unique for every record, therefore the value of any other column would also be determined. MySQL is one of the databases which allows for this (SQL Server and Oracle do not AFAIK).

注意：ANSI SQL 扩展了允许选择的内容GROUP BY，还包括功能上依赖于所选列的列。函数依赖的一个例子是按表中的主键列分组。由于主键保证对每条记录都是唯一的，因此任何其他列的值也将被确定。MySQL 是允许这样做的数据库之一（SQL Server 和 Oracle 没有 AFAIK）。

回答by Rakesh Soni

Use any one solution

使用任何一种解决方案

(1) PHPMyAdmin

(1) PHPMyAdmin

if you are using phpMyAdmin then change the "sql_mode" setting as mentionedthe in below screenshot.

如果您使用的是 phpMyAdmin，则更改“ sql_mode”设置，如下面的屏幕截图中所述。

Edit "sql mode" variable and remove the "ONLY_FULL_GROUP_BY" text from the value

编辑“sql mode”变量并从值中删除“ONLY_FULL_GROUP_BY”文本

(2) SQL/Command promptRun the below command.

(2) SQL/命令提示符运行以下命令。

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

(3) Don't use SELECT *

(3)不要使用SELECT *

Use relevant column in SELECT query. relevant means columns, which are either coming in "group by" clause or column with the aggregate function (MAX, MIN, SUM, COUNT etc)

在 SELECT 查询中使用相关列。相关意味着列，它们要么来自“group by”子句，要么来自具有聚合函数的列（MAX、MIN、SUM、COUNT 等）

---

---

Important note

重要的提示

Changes made by using point(1) OR point(2) will not set it PERMANENTLY, and it will revert after every restart.

使用 point(1) OR point(2) 所做的更改不会永久设置它，并且每次重新启动后都会恢复。

So you should set this in your config file (e.g. /etc/mysql/my.cnf in the [mysqld] section), so that the changes remain in effect after MySQL restart:

所以你应该在你的配置文件中设置它（例如 [mysqld] 部分中的 /etc/mysql/my.cnf ），以便更改在 MySQL 重启后仍然有效：

Config File: /etc/mysql/my.cnf

配置文件：/etc/mysql/my.cnf

Variable name: sql_mode ORsql-mode

变量名：sql_mode ORsql-mode

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

回答by Anand Huded

Below method solved my problem:

下面的方法解决了我的问题：

In ubuntu

在 ubuntu 中

Type: sudo vi /etc/mysql/my.cnf

类型： sudo vi /etc/mysql/my.cnf

type A to enter insert mode

输入A进入插入模式

In the last line paste below two line code:

在最后一行粘贴下面两行代码：

[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

Type esc to exit input mode

输入 esc 退出输入模式

Type :wq to save and close vim.

Type sudo service mysql restartto restart MySQL.

键入sudo service mysql restart以重新启动 MySQL。

回答by ElliotMok

For the query to be legal in SQL92, the name column must be omitted from the select list or named in the GROUP BY clause.

SQL99 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between name and custid, the query is legal. This would be the case, for example, were custid a primary key of customers.

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.

via MySQL :: MySQL 5.7 Reference Manual :: 12.19.3 MySQL Handling of GROUP BY

要使查询在 SQL92 中合法，名称列必须从选择列表中省略或在 GROUP BY 子句中命名。

如果它们在功能上依赖于 GROUP BY 列，则 SQL99 及更高版本允许每个可选功能 T301 的此类非聚合：如果 name 和 custid 之间存在这种关系，则查询是合法的。例如，如果是客户的主键，就会出现这种情况。

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

通过MySQL :: MySQL 5.7 参考手册 :: 12.19.3 MySQL 处理 GROUP BY

You can solve it by changing the sql mode with this command:

您可以通过使用以下命令更改 sql 模式来解决它：

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

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

and ... remember to reconnect the database!!!

还有……记得重新连接数据库！！！

回答by Gullu Mutullu

In Ubuntu

在 Ubuntu 中

Step 1:

第1步：

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Step 2: Go to last line and add the following

第 2 步：转到最后一行并添加以下内容

sql_mode = ""

Step 3: Save

第 3 步：保存

Step 4: Restart mysql server.

第四步：重启mysql服务器。

回答by fajr abd el ali

go to the phpmyadmin and open the console and execute this request

转到 phpmyadmin 并打开控制台并执行此请求

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

回答by Mr_Bull

Search for "SQL mode" if you are using PhpMyAdmin and take off the value: ONLY_FULL_GROUP_BY, just did and it okay.

如果您使用的是 PhpMyAdmin，请搜索“SQL 模式”并去掉值：ONLY_FULL_GROUP_BY, just did and it 没问题。

回答by PrafulPravin

1. Login to phpMyAdmin
2. Navigate to : Server: localhost:3306 and do not select any database
3. Click on variables from the top menu
4. Search for "sql mode" and edit the corresponding value to : NO_ENGINE_SUBSTITUTION

1. 登录 phpMyAdmin
2. 导航到：Server: localhost:3306 并且不要选择任何数据库
3. 单击顶部菜单中的变量
4. 搜索“sql mode”并将对应的值编辑为：NO_ENGINE_SUBSTITUTION

That's all.

就这样。

I did this in my Ec2 and it worked like charm.

我在我的 Ec2 中做到了这一点，它的效果非常好。