Linux运维笔记
我们一直在努力

MySQL报错:sql_mode=only_full_group_by

今天在执行select * from a group by cat_id;

的时候出现了如下的报错,好像说是查询列表中缺少了某些必要的条件。

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.goods_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

没事撩了下度娘,大概是5.7 sql_mode的关系

only_full_group_by :5.7使用了和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行,所以去掉就好。

用select @@sql_mode;查看下第一个就是only_full_group_by

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

解决方法:

所以只要把这个这个一行的字段,去掉了only_full_group_by以后,放在my.cnf配置文件的[mysqld]下

vim /etc/my.cnf

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

service mysqld restart

赞(0) 打赏

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址