在MySQL8中使用group by引发的问题：1 of SELECT list is not in GROUP BY clause

出现的问题

当在SQL中使用group by时可能会出现如下问题：

> 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
           

解决办法

尝试解决，一般是在my.cnf文件下的[mysqld]中加入如下内容：

sql_mode='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'

           

然后重启数据库，可能会启不来，原因是MYSQL8以上已经取消了NO_AUTO_CREATE_USER，sql_mode中不能包含这个，将其去掉再次启动MySQL即可。