this is incompatible with sql_mo
2020-07-13 本文已影响0人
又语
Java 应用连接 MySQL 8 数据库遇到以下问题:
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
的意思是针对 GROUP BY
聚合操作,如果 SELECT
中的列没有在 GROUP BY
中出现,那么这个 SQL 是不合法的,因为列不在 GROUP BY
从句中。
解决方案一:
使用 MySQL 客户端查询 sql_mode
,发现包含 ONLY_FULL_GROUP_BY
,使用 SET
命令更新,然后再次查询发现 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
,只对新建的数据库生效,对于已经存在的数据库,需要使用USE
命令先切换到具体数据库,然后执行SET sql_mode
命令; - 这种方案在 MySQL 服务重启后会失效,即重启后
ONLY_FULL_GROUP_BY
还会出现。
解决方案二:
修改 MySQL 配置文件,Windows 操作系统中为 my.ini
文件,Linux 操作系统中使用 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 服务。