【Mysql】异常报错解决 “this is incompatible with sql_mode=only_full_group_by”

项目场景：

将jar包部署至linix运行，运行时提示 ：  com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException 异常报错

问题描述

 MySQLSyntaxErrorException 异常报错内容如下：

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
Expression #11 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'springcloud.comment_user.nikename' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatibcle with sql_mode=only_full_group_by

原因分析：

这个异常表示在 MySQL 查询语句执行时，SELECT 列表中的一些字段未在 GROUP BY 子句中出现，可能会导致输出结果不一致或者不正确。

在设置 sql_mode 为 only_full_group_by 的情况下，MySQL 数据库会禁止此类型的查询。

在 MySQL 5.7.5 版本之后，默认SQL模式中包含了ONLY_FULL_GROUP_BY选项，要求在GROUP BY子句中的所有列都必须出现在SELECT语句中，并且不能含有聚合函数的列

解决方案：

tip：推荐使用第三种方案(解决流程采用第三种解决方案)

1. 将 SELECT 子句中的所有字段都添加到 GROUP BY 子句中。如下：

   SELECT nikename, COUNT(*) FROM comment_user GROUP BY nikename;

2. 对于非聚合函数的字段，可以使用 MySQL 中的聚合函数对其进行聚合，即将其放入 SELECT 子句中，并附加相应的聚合函数操作。如下：

   SELECT MAX(nikename), COUNT(*) FROM comment_user GROUP BY user_id;

3. 可以修改 sql_mode，将 only_full_group_by 模式关闭。如下：

   SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

请注意，开启 only_full_group_by 模式是为了减少 SQL 查询的歧义性和错误率，如果您不确定修改 SQL_MODE 的影响，请谨慎和慎重考虑。同时，建议在编写 SQL 查询语句时注

意 GROUP BY 子句的书写规则，避免出现此类异常。

解决流程：

一、临时解决

 1. 进入mysql 修改（此修改为临时性，重启mysql将失效）

mysql -uroot -p

 2. 设置sql_mode @GLOBAL.sql_mode 和 @SESSION.sql_mode

 set @@GLOBAL.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

 set @@SESSION.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

 3. 退出 quit; 服务器异常报错解决。当一旦 service mysqld restart; 重启mysql，此设置将失效，异常报错依旧存在。

二、永久解决

修改Linux系统中MySQL配置文件/etc/my.cnf，可按如下步骤进行：

 1. 进入MySQL配置文件所在的目录，如/etc/目录。可以使用以下命令进入目录：

cd /etc/

 2. 打开my.cnf文件，可以使用vim编辑器打开：

vim my.cnf

 3. 编辑my.cnf文件，在最后一行添加以下内容：

tip：别忘记添加[mysqld]哦，否则是不生效的。

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

 4.  保存并关闭my.cnf文件。在 Vim 编辑器中，可以按 Esc 键然后输入 :wq 来保存并退出。

 5. 重新启动MySQL服务，以使更改生效。可以使用以下命令来重启MySQL服务：

sudo systemctl restart mysqld.service

 6.查看结果，进入Mysql执行如下指令：

 SELECT @@SESSION.sql_mode;

 SELECT @@GLOBAL.sql_mode;

 运行结果如下时，MySQL配置文件中的sql_mode参数值已被修改，MySQL将不再对GROUP BY   查询产生 “Expression #11 of SELECT list is not in GROUP BY clause” 异常。

mysql> SELECT @@SESSION.sql_mode;
+----------------------------------------------------------------------------------------------------+
| @@SESSION.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> 
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> 

代码007普通

打赏 收藏 海报 链接