SQL_MODE=only_full_group_by: SELECT列表中不在GROUP BY子句中且包含非聚合列....不兼容

Question

我在 Windows PC 上使用 MySQL 5.7.13 和 WAMP 服务器

我的问题是在执行此查询时

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

我总是遇到这样的错误

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

您能告诉我最好的解决方案吗？

我需要这样的结果

+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
| 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 |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+

Answer 1

MySql引擎中有一个系统变量ONLY_FULL_GROUP_BY。

从Mysql版本5.7.5开始：ONLY_FULL_GROUP_BY默认启用SQL模式

版本 5.7.5 之前：默认情况下未启用 ONLY_FULL_GROUP_BY。

如果启用了 ONLY_FULL_GROUP_BY SQL 模式（从版本 5.7.5 开始默认启用），MySQL 将拒绝选择列表、HAVING 条件或ORDER BY 列表指的是非聚合列，这些列既未在 GROUP BY 子句中命名，也不在功能上依赖于它们。

要解决问题，请使用任意一种解决方案（以下 3 种中的一种）

(1) PHPMyAdmin

禁用：ONLY_FULL_GROUP_BY 模式

如果您使用 phpMyAdmin，请更改 sql_mode 设置，如下面的屏幕截图所示。

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

或

(2) SQL/命令提示符

禁用：通过运行以下命令来禁用ONLY_FULL_GROUP_BY模式。

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

或

(3) 不要使用SELECT *

请勿禁用ONLY_FULL_GROUP_BY模式，但是

在 SELECT 查询中使用相关列。相关意味着列，它们要么出现在 group by 子句中，要么出现在具有聚合函数的列（MAX、MIN、SUM，<代码>COUNT等）

---

重要说明

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

因此，您应该在配置文件中进行设置（例如 [mysqld] 部分中的 /etc/mysql/my.cnf），以便更改保留在MySQL重启后的效果：

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

变量名称：sql_mode OR sql-mode

从值中删除单词 ONLY_FULL_GROUP_BY 并保存文件。

注意：如果您在配置文件中没有找到sql_mode变量，请在文件末尾插入以下两行

[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

Answer 2

这个

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

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

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

或者仅包含 SELECT 语句指定的 GROUP BY 子句中的所有列。 sql_mode 可以保持启用状态。

谢谢...:-)

---

更新日期：2023 年 7 月 14 日

更改 SQL 模式是一种解决方案，但结构化查询语言的最佳实践仍然是避免选择所有 (SELECT * ...) 列，而是在分组列上使用聚合器函数，如@Tim Biegeleisen 在下面的答案中提到的https://stackoverflow.com/a/41887524/3602846