最近,在mysql使用group_by时报sql_mode错误,报错信息如下:
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column '' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方法如下:
1.mysql中执行命令;
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';
2.mysql中执行命令:
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';
此种方法的问题是:
重启后下次就失效了,有解决办法说在mysql配置文件中直接修改sql_mode配置
不过我这没有生效,ubuntu16中配置文件位于/etc/mysql/mysql.conf.d/mysqld.cnf