项目场景:
linux系统下的MySQL,在Navicat上运行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 临时方案:在数据库中或Navicat中输入执行(重启数据库后失效)
set @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
2 永久方案:修改my.cnf 配置文件(mac系统则是my.cof)
找不到配置文件的可以参考我的博客
(25条消息) 从零搭建阿里云服务器(Linux安装MySQL5.7)图文详解_阿里云linux安装mysql_Ricardo · M · YUAN的博客-CSDN博客
在配置文件的末尾加上这段代码:
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
修改完成之后,wq保存退出
最后别忘了重启数据库,让配置生效。