最近mysql数据库版本5.7执行SQL语句的时候,出现错[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by;
1.临时方案
cmd,登录mysql(执行mysql -uroot -p…),成功后
一.查看sql_mode
select @@sql_mode;
查询结果为:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
二、去掉ONLY_FULL_GROUP_BY,重新设置值:
set @@sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;
ok,此时零时生效,关闭cmd窗口后失效
2.永久方案
打开mysql安装目录,找到my.ini配置文件,在文件最后(其他位置应该也可以)添加:
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
保存后,CMD,重启mysql : 先执行 net stop mysql 再执行net start mysql 至此,再执行SQL问题消失。