MySQL sql_mode=only_full_group_by 錯誤

Walking發表於2019-06-28
MySql
因為新裝mysql5.7，今天在本地環境用到sql的group by語法。
[10501] PDOException in Connection.php line 687
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'blog.categories.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
一直在尋找之前的環境也可以執行，後來網上查了一下據庫版本為5.7以上的版本,預設是開啟了 only_full_group_by 模式的，但開啟這個模式後，原先的 group by 語句就報錯。
Your MySQL connection id is 4
Server version: 5.7.24 MySQL Community Server (GPL)

我檢視了一下配置看看具體的情況

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

其中ONLY_FULL_GROUP_BY就是造成這個錯誤的原因,

對於group by聚合操作,如果在select中的列沒有在group by中出現,那麼這個SQL是不合法的,因為列不在group by從句中,所以設定了sql_mode=only_full_group_by的資料庫,在使用group by時就會報錯。

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';
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
或者到my.cnf配置檔案修改

[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

最後修改完配置,重啟mysql
service mysqld restart;

就可以解決這個問題

Walking

相關文章