Mysql5.7使用group by出错
在mysql5.7里面的配置有一个ONLY_FULL_GROUP_BY导致禁用了GROUP BY
解决办法:
查看sql_mode
select @@sql_mode
发现有ONLY_FULL_GROUP_BY,此时需要去掉它
有两种解决方法
1.set global
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set session
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
2.打开配置文件/etc/mysql/mysql.conf.d 下的mysqld.cnf
设置[mysqld],然后重启mysql
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION