life will change

[紀錄]ubuntu 18.04 mysql 8.0 移除 sql_mode 中的 only_full_group_by

發表於 分類於 Disqus：
文章字數： 2.2k 所需閱讀時間 ≈ 2 分鐘

於ubuntu 18.04 mysql 8.0 移除
sql_mode 中的 only_full_group_by

某天在開發isthereanyclothes這個專案時在DBeaver上先寫Query看看沒想到跑出

SQL Error [1055] [42000]: Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column '' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT 列表中的第二個表達式不在 GROUP BY 的子句中，同時它也不是聚合函數；這與 sql 模式：ONLY_FULL_GROUP_BY 不相容。

sql_mode=only_full_group_by

  1. 先找官方說明看看

5.1.10 Server SQL Modes 其中提到了

ONLY_FULL_GROUP_BY Reject 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 (uniquely determined by) GROUP BY columns.

ONLY_FULL_GROUP_BY
如果Queries中 SELECT 中的列、HAVING 語句或者ORDER BY子句的列，沒有在GROUP BY子句中命名的非聚合列出現而且也非功能上的依賴GROUP BY列，那麼這個SQL是不合法

ONLY_FULL_GROUP_BY是MySQL提供的一個sql_mode，通過這個sql_mode來提供SQL語句GROUP BY合法性的檢查，在MySQL的sql_mode是非ONLY_FULL_GROUP_BY語義時。
所以語法上比較嚴謹

基於對於SQL的掌握的還沒有很熟悉，我決定先拿掉ONLY_FULL_GROUP_BY來方便的開發如果今後有相對的影響會再想文章，權衡比較

  1. SET sql_mode

我先以 

1
SELECT @@sql_mode

來查詢目前Mysql的sql_mode，得到

1
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

看來目前是有開啟ONLY_FULL_GROUP_BY模式的，嘗試拿掉他

1
2
SET sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

更改完後Query可以正常執行了

  1. 更改Mysql config

之後我一個無聊看到Mysql uptime太久把它重啟，OK 設定又跑回來了，所以這次要改就改完，爬了一堆文後看到這篇有如救星一般

mysql 8.0 配置文件my.cnf中文注解(CSDN連結)

先回到我的Linode vps裡面/etc底下先找找看有沒有my字樣的檔案

沒有

然後我進到/etc/mysql資料夾裡面
conf.d my.cnf my.cnf.fallback mysql.cnf conf.d
先用vi編輯器到my.cnf中察看

1
2
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

這邊可以看到的是my.cnf會從conf.d跟mysql.conf.d抓取config資料
接下來進去mysql.conf.d裡面有一個mysqld.cnf看來就是我們目標資料了
用vi編輯器到mysqld.cnf中察看

1
2
3
4
5
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

BINGO 找到了
最後我在檔案中加入

1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

然後重啟Mysql後再用SELECT @@sql_mode檢查 OK

參考自:

mysql 8.0 配置文件my.cnf中文注解(CSDN連結)

踩坑系列：MySql only_full_group_by配置，竟導致所有應用報錯