Mysql升级5.7.10后GROUP BY语句出错解决方法

MYSQL升级5.7.10后GROUP BY语句出错解决方法

安装Mysql5.7.X版本数据库后，运行原先项目，执行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 

原因是sql_model=only_full_group_by限制了，导致在以往MYSQL版本中能正常查询的SQL，在5.7不能用了，ONLY_FULL_GROUP_BY的设定，将不允许查询字段包括非聚集列。

解决方法

• 修改sql_mode

修改/etc/my.cnf
在[mysqld] 下面加上

sql_mode=

• 执行重启mysql命令

启动mysql服务

systemctl start mysqld.service

停止mysql服务

systemctl stop mysqld.service

重启mysql服务

systemctl restart mysqld.service