- A+
所属分类:未分类
原因
之前的MySQL5.6升级到MySQL5.7后,执行查询语句的时候,报错,如下:
getDefContactByUserId exception: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'coolcloud.fpci.value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
登录到mysql查询一下sql_mode,如下:
mysql> select @@sql_mode;
+------------------------------------------------+
| @@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 |
+------------------------------------------------+
原因就是默认设置了
sql_mode=only_full_group_by
only_full_group_by :使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行,其实这个配置目前个人感觉和distinct差不多的,所以去掉就好
解决
mysql配置文件中加入如下配置:
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后,再看看
mysql> select @@sql_mode; +---------------------------------------------+ | @@sql_mode | +---------------------------------------------+ | 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 (0.00 sec)
问题解决
- 安卓客户端下载
- 微信扫一扫
- 微信公众号
- 微信公众号扫一扫