GROUP BY clause and contains nonaggregated column ‘product.id’ which is not functionally dependent

在执行sql语句时,如果用了group by会提示以下错误

```
select id,product_type,count(*) from product group by product_type

> 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'product.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
> 时间: 0s
```

什么原因

>MySql从5.7版本开始默认开启only_full_group_by规则,规则核心原则如下,没有遵循原则的sql会被认为是不合法的sql。

```
1、order by后面的列必须是在select后面存在的。

2、 select、having或order by后面存在的非聚合列必须全部在group by中存在
```

解决方法

```

show variables like '%sql_mode%';`

以下为mysql显示

variable_name value

sql_mode ONLY_FULL_GROUP_BY,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';

show variables like '%sql_mode%';

select id,product_type,count(*) from hl_product group by product_type;

```

或者在mysql.ini中加入

`sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'`

然后重启mysql服务器

    A+
发布日期:2022年09月02日  所属分类:未分类

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: