使用 ActiveRecord 時遭遇 only_full_group_by 的錯誤

前言

MySql 版本 5.7 以上都會自己預設開啟這個 ONLY_FULL_GROUP_BY。這個設定的目的主要是提高 SQL 查詢的準確性和一致性，避免導致一些人為疏失或其他預期外的錯誤發生。

舉例來說就是明明 SELECT 了全部的欄位，在做 GROUP BY 的時候卻只有部分欄位，其實是不符合 SQL 的標準。

防止非預期的結果

假設我們有一個名為employees的表，資料如下：

id	name	department	salary
1	Alice	Sales	50000
2	Bob	Sales	60000
3	Carol	HR	55000
4	David	HR	52000

現在，考慮以下查詢：

SELECT department, name, AVG(salary)
FROM employees
GROUP BY department;

在沒有 ONLY_FULL_GROUP_BY 模式的情況下，這個查詢可能會執行，但結果是不確定的。為什麼？

這時候問題來了

如果你要取用name，那資料庫應該返回哪個name？

啟用 ONLY_FULL_GROUP_BY 後，MySQL 會拒絕執行這個查詢，並給出一個錯誤，提示name列應該要麼包含在 GROUP BY 子句中，要麼使用聚合函數。

一個正確的查詢可能是：

SELECT department, GROUP_CONCAT(name), AVG(salary)
FROM employees
GROUP BY department;

或者：

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

這樣，查詢結果就是明確和可預測的，避免了可能的歧義和錯誤。

通過強制執行這種規則，ONLY_FULL_GROUP_BY 模式幫助開發者寫出更清晰、更可靠的 SQL 查詢，減少了因不明確的 GROUP BY 操作而導致的潛在錯誤。

好了，廢話這麼多，該進入正題了。

除了把語法通通修好的方法外，我們可能需要的是趕快解決這問題，並且可以繼續向下開發的解決辦法。

其實就把這設定給關了就行了

出現這個錯誤後繼續往下追查至 mysql，進入 mysql cli 之後輸入下面語法查看

SELECT @@sql_mode;

我的環境輸入後會出現下面這樣的畫面：

可以看到確實出現了 ONLY_FULL_GROUP_BY 的設定，也就是這個設定導致很多語法都無法『正常』執行。因此我們需要把它關閉。

如果是 homebrew 安裝的 mysql@5.7 路徑，需編輯/opt/homebrew/etc/my.cnf 這個設定檔。並在裡面加入你需要開啟的 sql_mode。否則用下語法的方式每次重啟 mysql 都會恢復到預設設定。

準備把下面這段設定貼到 config 中

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

貼上去後畫面如下（注意要在 mysqld) 的區塊下：

接著重新啟動你的 mysql

brew services restart mysql@5.7

完成！