mysql5.6升级mysql5.7语法问题

1.升级后，原sql语句执行报错

mysql> select log_server,log_user,f6,tx1,log_time from log_exception where log_time between "2020-03-01 00:00:00" and "2020-03-10 23:59:59" AND 0=0 group by tx1 ORDER BY log_time DESC limit 0,50;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'beans_zombie.log_exception.log_server' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

2.查看sql_mode参数：

mysql> SELECT @@sql_mode;

 

 报错原因：对于group by聚合操作,如果在select中的列没有在group by中出现,那么这个SQL是不合法的,因为列不在group by从句中,所以设置了sql_mode=only_full_group_by的数据库,在使用group by时就会报错

3.解决方法：去掉ONLY_FULL_GROUP_BY参数

修改my.cnf文件，在[mysqld]配置内添加参数，重启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

或者

使用的是腾讯云实例，可以直接在控制台在线修改参数

 

4.重新连接mysql回话生效