Mysql5.7版本sql错误:this is incompatible with sql_mode=only_full_group_by

数据库,MySQL

2018-03-16

174

0

目录


错误信息

最近将测试环境做了迁移,发现在执行某一条未作任何改动的sql时,出现如下错误:

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hmp-uat.trm.startTime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hmp-uat.trm.startTime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个错误的意思:选择的列没有包含在GROUP BY子句中,而且包含了非聚合列,它在功能上不依赖于GROUP BY子句的列;这与sql mode=only_full_group_by完全不兼容。

简单来说,就是查出来的字段必须是需要group by的这些字段中的一部分(隐约记得ORACLE是必须符合这个规则的)。

 

原因分析

上边的是MyBatis抛出的异常信息,单独执行SQL错误就是红色的部分。未作迁移之前执行的好好的SQL,迁移后就不能正常执行了,做的事情就是换了MySQL版本,估计应该是MySQL版本导致的。原环境上mysql版本为5.6.x,新环境上的mysql我安装的是5.7.21。

 

版本查询

工具使用的navicat,查询MySQL版本:

在链接上右键,打开命令行,或者直接快捷键F6,输入如下命令:

select version();

结果如下:

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.21-0ubuntu0.16.04.1 |
+-------------------------+
1 row in set

mysql> 

sql_mode查询

同样,在命令行输入如下命令:

select @@GLOBAL.sql_mode;

结果如下:

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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

mysql> 

可以看到,当前数据库sql_mode确实包括了ONLY_FULL_GROUP_BY。开启了这个模式,那么group by和select的字段就会严格匹配。

sql测试

接下来在5.7数据库中做一个简单的测试:

select id, name from t_hotel group by name

如上,按照name分组,但是我需要查出来id和name两个字段,结果是抛出异常:

[SQL]select id, name from t_hotel group by name

[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hmp-uat.t_hotel.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

正确的SQL写法应该是这样:

select id, name from t_hotel group by id, name

需要查询出来的列表id和name也必须作为分组的字段。

那么,这样在写SQL时限制较多,而且原来的应用需要改动,修改业务逻辑不太现实。最后的办法是,将该选项关闭。

解决办法

重新设置sql_mode,来关闭这个选项。

临时修改

在navicate命令窗口,键入如下命令来修改:

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';

其实就是去掉了ONLY_FULL_GROUP_BY,在查询sql_mode可以看到设置成功。

但是这个方法如果重启了mysql,设置就会失效,最后的办法是永久关闭。

永久修改

打开/etc/mysql/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 mysql restart

再次执行sql,可以看到可以成功执行,问题解决。

表名大小区分

另外,mysql5.7默认也是开启了表明的大小写区分的,也可以通过修改配置文件来关闭,具体如下:

打开/etc/mysql/my.cnf配置文件,在mysqld节点下添加如下配置:

lower_case_table_names=1

1代表不区分大小写,0代表区分,改完后,重启mysql。

总结

mysql5.7默认开启了很多功能,如果是数据库升级,需要关闭一些选项,例如lower_case_table_names和ONLY_FULL_GROUP_BY,肯定还有其他问题,在测试过程中慢慢发现吧。


前一篇:Spring事务管理四:声明式事务
后一篇:MindManager2016版本下载,带正版激活码

belonk

轻轻地我走了,正如我轻轻地来,我挥一挥衣袖,不带走一片云彩