错误信息:
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘tests.a.status’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql从5.7升级到mysql8之后,对于select后查询的字段(非聚合函数,如sum,count等)未出现在group by中
解决方式: 修改配置文件去除ONLY_FULL_GROUP_BY
环境: Centos7
find / -name my.cnf
找到配置文件,去掉sql_mode中的 ONLY_FULL_GROUP_BY
systemctl restart mysqld.service
重启mysql服务
记一个坑:
启动时报错
Job for mysqld.service failed because the control process exited with error code. See “systemctl status mysqld.service” and “journalctl -xe” for details
排查问题:
find / -name mysqld.log
查看mysql启动日志
/usr/sbin/mysqld: Error while setting value ‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’ to ‘sql_mode’.
sql_mode加载时异常
原因及解决:
MYSQL8以上已经取消了NO_AUTO_CREATE_USER,sql_mode中不能包含这个,删掉
systemctl start mysqld.service
问题解决。