Mysql8 group by 报错解决方法及启动报错

错误信息：
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

问题解决。