MySQL之[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause问题

一时失言乱红尘 2023-02-11 10:23 1阅读 0赞

## 问题提出 ##

MySQL数据表查询语句：

select * from t_user where sex = 0 group by home_address having count(id)>0;

提示如下错误信息：

[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'spb-db.t_user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

t\_user表的定义如下：

create table t_user
    (
       id           bigint auto_increment  primary key,
       created_by   varchar(255) null,
       created_time datetime     null,
       updated_by   varchar(255) null,
       updated_time datetime     null,
       version      bigint       null,
       age          int          null,
       home_address varchar(255) null,
       name         varchar(255) not null,
       phone        varchar(255) null,
       sex          int          null,
       birth_day    date         null,
       constraint my_index_name
           unique (name)
    )

## MySQL信息 ##

在MySQL命令行或者控制台输入指令：

> select @@version

输出： 8.0.19

> select @@global.sql\_mode

输出：

> ONLY\_FULL\_GROUP\_BY,STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_ENGINE\_SUBSTITUTION

原因分析：  
MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY\_FULL\_GROUP\_BY SQL模式（默认情况下），MySQL将拒绝选择列表。  
参考文档： http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html\#sql-mode-setting  
在文档指出：ONLY\_FULL\_GROUP\_BY的设定，将不允许查询字段包括非聚集列。  
5.7.5之前，MySQL没有检测到功能依赖关系，默认情况下不启ONLY\_FULL\_GROUP\_BY.

## only\_full\_group\_by ##

only\_full\_group\_by：select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行，其实这个配置和distinct差不多的.  
例如在默认开启only\_full\_group\_by设置下：

select home_address, count(id) from t_user where sex = 0 group by home_address having count(id)>0;

其中home\_address为group 分组中的column。 id使用了聚集函数count，所以它们是有效的。

## 关闭这个设置 ##

在MySQL命令行或控制台执行指令：

set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

检查一下是否设置成功：

select @@global.sql_mode

看看ONLY\_FULL\_GROUP\_BY是否消失。

MySQL之[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause问题

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Mysql问题：[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause

相关 mysql错误-1055 - Expression #1 of SELECT list is not in GROUP BY clause 解决方案

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

相关 mysql：报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonag

相关 MySQL之[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause问题

相关 mysql遇见Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre的问题

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

相关 mysql遇见Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre的问题

相关 MySQL：错误Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate