openshell 的个人博客

一天很长,但十年很短。

Open Source, Open Mind,
Open Sight, Open Future!
  menu
110 文章
5051 浏览
0 当前访客
ღゝ◡╹)ノ❤️

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column XXX sql_mode=only_full_group_by

执行SQL报错

select s_id,avg(s_score)
from score

1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'school.score.s_id'; this is incompatible with sql_mode=only_full_group_by
原因是MySQL7.0之后更加严格的遵守标准了,所以之前的写法是不符合规范的。
解决办法

  1. 需修改mysql配置文件,通过手动添加sql_mode的方式强制指定不需要ONLY_FULL_GROUP_BY属性,
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  1. 对需要select的字段加上group by子句
select s_id,avg(s_score)
from score
group by s_id

标题: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column XXX sql_mode=only_full_group_by
作者:openshell
地址:http://solo.caiqz.cn/articles/2020/06/08/1591615347343.html