Java 應(yīng)用連接 MySQL 8 數(shù)據(jù)庫(kù)遇到以下問題：
2020-07-13 15:28:58 WARN 14972 --- [xec-9] i.SqlExceptionHelper137 : SQL Error: 1055, SQLState: 42000
2020-07-13 15:28:58 ERROR 14972 --- [xec-9] i.SqlExceptionHelper142 : (conn=10930) Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
2020-07-13 15:28:58 ERROR 14972 --- [xec-9] ller.aop.ValidateAop64 : could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
分析：
ONLY_FULL_GROUP_BY 的意思是針對(duì) GROUP BY 聚合操作，如果 SELECT 中的列沒有在 GROUP BY 中出現(xiàn)甚垦，那么這個(gè) SQL 是不合法的，因?yàn)榱胁辉?GROUP BY 從句中。
解決方案一：
使用 MySQL 客戶端查詢 sql_mode砌庄，發(fā)現(xiàn)包含 ONLY_FULL_GROUP_BY封拧，使用 SET 命令更新，然后再次查詢發(fā)現(xiàn) ONLY_FULL_GROUP_BY 已不存在硫狞。
mysql> SELECT @@global.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SET @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.sql_mode;
+----------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+----------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> USE DB_NAME
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
注意：
-
SET @@global.sql_mode修改了全局sql_mode信轿，只對(duì)新建的數(shù)據(jù)庫(kù)生效，對(duì)于已經(jīng)存在的數(shù)據(jù)庫(kù)残吩，需要使用USE命令先切換到具體數(shù)據(jù)庫(kù)财忽，然后執(zhí)行SET sql_mode命令； - 這種方案在 MySQL 服務(wù)重啟后會(huì)失效泣侮，即重啟后
ONLY_FULL_GROUP_BY還會(huì)出現(xiàn)即彪。
解決方案二：
修改 MySQL 配置文件，Windows 操作系統(tǒng)中為 my.ini 文件旁瘫，Linux 操作系統(tǒng)中使用 RPM 安裝包安裝后配置文件位于 /etc/my.cnf祖凫。
在 [mysqld] 添加：
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
重啟 MySQL 服務(wù)。
