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