您现在的位置是:首页 > 程序 > mysql网站首页mysql
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ'
MySQL升级5.7以后经常会出现这种错误:[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and co...
MySQL升级5.7以后经常会出现这种错误:
[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
虽然不影响SQL执行,但看起来总是不爽!
既然是版本的问题,那就查询一下配置看看具体的情况,命令行连接上数据库,输入命令:
select @@sql_mode;
你会看到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
其中ONLY_FULL_GROUP_BY就是造成这个错误的罪魁祸首了,
对于group by聚合操作,如果在select中的列没有在group by中出现,那么这个SQL是不合法的,因为列不在group by从句中,所以设置了sql_mode=only_full_group_by的数据库,在使用group by时就会报错,
既然知道了问题,那么修改这个配置就可以了,找到MySQL的配置文件,Windows系统配置文件为安装MySQL目录下的my.ini文件,linux系统是/etc/my.cnf文件,查询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'
需要注意的一点是一定要添加在[mysqld]配置内,这样添加完后重启mysql才会生效,
重启mysql:
service mysqld restart;
刷新页面报错信息消失成功解决,再次连接上数据库查看sql_mode配置:
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
相关文章
文章评论 (0)
- 这篇文章还没有收到评论,赶紧来抢沙发吧~