今天执行SQL语句等后面都会报一个错误:
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
看了很多教程以后发现可以使用命令解决这个问题;
1、查看sql_mode的值
使用命令
select @@sql_mode;
或者
show variables like 'sql_mode';
可以查看到sql_mode的值为:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
可以发现sql_mode里面有个值为ONLY_FULL_GROUP_BY
就是这个值报错的
2.修改sql_mode的值
把刚才得到的sql_mode的值去掉ONLY_FULL_GROUP_BY执行以下命令;(sql_mode等号后面就是刚才去掉ONLY_FULL_GROUP_BY的值哦)
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
执行后再查看一下sql_mode的值里面ONLY_FULL_GROUP_BY有没有去掉哦;
有问题请多多指教,谢谢,一起共同进步。