Mysql的ONLY_FULL_GROUP_BY

  今天在测试环境遇到一个问题,本地测试是没有问题,在测试环境sql报错了: nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'dt.vDateTime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by;

  刚开始觉得很奇怪,本地测试的时候没有问题,为什么在测试环境就sql报错了,后来想到leader说过的sql严格模式。百度了一下报错信息,在这里纪录一下。

  ONLY_FULL_GROUP_BY是MySQL提供的一个sql_mode,通过这个sql_mode来提供SQL语句GROUP BY合法性的检查,在MySQL的sql_mode没有ONLY_FULL_GROUP_BY语义时。一条select语句,MySQL允许target list中输出的表达式是除聚集函数或group by column以外的表达式。ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在ONLY_FULL_GROUP_BY模式下,target list中的值要么是来自于聚集函数的结果,要么是来自于group by list中的表达式的值

mysql> select id+1 as a from tt group by a order by id+1;
+------+
| a    |
+------+
|    2 |
|    3 |
+------+
2 rows in set (0.00 sec)

   mysql允许target list中对于非聚集函数的alias column被group by、having condition以及order by语句引用(version 5.7中允许having condition引用alias column,version 5.6不支持having condition引用alias column),从上面两条语句可以看出,group by和order by中引用了alias column。

  我的sql报错是因为我的order by并没有出现在group by list中,而且也不是聚合修饰的;

select a1 from table group by a1 order by a2

 纪录一下网上找到的一些解决方案:

1.命令行输入:

set @@GLOBAL.sql_mode='';

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

默认关掉ONLY_FULL_GROUP_BY!

这样只是session级别的,在mysql重启后,还是会有ONLY_FULL_GROUP_BY;

2.改my.ini 配置(如果你们mysql 没有这个文件,就把my-default.ini 改成my.ini,我这个版本就是没有my.ini配置问题)

在 [mysqld]和[mysql]下添加

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE

 

posted @ 2019-10-23 23:57  EasilyAi  阅读(6399)  评论(0编辑  收藏