MySQL5.7:of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

ゝ一纸荒年。 2023-10-11 12:24 11阅读 0赞

安装了mysql5.7，用group by 查询时抛出如下异常：

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

#### 原因： ####

MySQL 5.7.5和up实现了对功能依赖的检测。如果启用了only\_full\_group\_by SQL模式(在默认情况下是这样)，那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。(在5.7.5之前，MySQL没有检测到功能依赖项，only\_full\_group\_by在默认情况下是不启用的。关于前5.7.5行为的描述，请参阅MySQL 5.6参考手册。)  
  
  
进入mysql中，执行以下个命令，可以查看 sql\_mode 的相关内容：

mysql> SHOW SESSION VARIABLES;
    mysql> SHOW GLOBAL VARIABLES;

查看本机的sql\_mode设置：

mysql> select @@sql_mode;

可见session和global 的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  
![在这里插入图片描述][20190923154033419.png]

####  only\_full\_group\_by说明： ####

only\_full\_group\_by ： 使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行，其实这个配置目前个人感觉和distinct差不多的，所以去掉就好

#### 官网摘抄： ####

官网：ONLY\_FULL\_GROUP\_BY  
Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

As of MySQL 5.7.5, the default SQL mode includes ONLY\_FULL\_GROUP\_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY\_FULL\_GROUP\_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Before MySQL 5.7.5, enabling ONLY\_FULL\_GROUP\_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. As of MySQL 5.7.5, this restriction is lifted so that the HAVING clause can refer to aliases regardless of whether ONLY\_FULL\_GROUP\_BY is enabled.

### 解决： ###

###### 方法1：执行以下两个命令： ######

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    
    mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

这两个命令的作用： 去掉 sql\_mode 的 ONLY\_FULL\_GROUP\_BY

###### 方法2：更改配置文件： ######

去你mysql的安装目录中找到mysql配置文件（若系统是Linux，比如Ubuntu16.04的，在/etc/mysql/mysql.conf.d/mysqld.cnf 中并没有sql\_mode这个配置，所以直接加上就好，如果是其他Windows系统有得修改就不用添加了）

mysql 配置信息读取顺序。

①ps aux|grep mysql|grep ‘my.cnf’

②mysql –help|grep ‘my.cnf’

/etc/my.cnf, /etc/mysql/my.cnf, /usr/local/etc/my.cnf, ~/.my.cnf 这些就是mysql默认会搜寻my.cnf的目录，顺序排前的优先。mysql按照上面的顺序加载配置文件，后面的配置项会覆盖前面的。

如果没有该文件可以自定义一个文件。然后回默认读取配置中的内容）  
查看你需要修改的是哪个配置文件。我只有/etc/my.cnf 只修改这个文件即可

配置文件my.cnf通常会分成好几部分，如\[client\]，\[mysqld\], \[mysql\]等等。MySQL程序通常是读取与它同名的分段部分，例如服务器mysqld通常读取\[mysqld\]分段下的相关配置项。如果配置项位置不正确，该配置是不会生效的

参考：https://stackoverflow.com/questions/37951742/1055-expression-of-select-list-is-not-in-group-by-clause-and-contains-nonaggr

这个语句没试过，先记录：

set @@sql\_mode=‘STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION’;  
去掉ONLY\_FULL\_GROUP\_BY即可正常执行sql.

##### 补充说明 #####

1、 以上sql执行修改之后要先断开sql的session连接才能生效，也就是说如果是在生产中，要将项目重启后才能正常。  
     2、windows上msyql服务重新开启后，会将sql的设置清除，需要去配置文件中配置，我的参考配置如下：

[mysql]
    
    #设置mysql客户端默认字符集
    
    default-character-set=utf8mb4
    
    [mysqld]
    
    #配置sqlmode便面系统升级导致的问题
    sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
    
    #设置3306端口
    
    port = 3306 
    
    #设置mysql的安装目录
    
    basedir=E:\DevTools\mysql-5.7.25-winx64
    
    #设置mysql数据库的数据的存放目录
    
    datadir=E:\DevTools\mysql-5.7.25-winx64\data
    
    #允许最大连接数
    
    max_connections=200
    
    #服务端使用的字符集默认为8比特编码的latin1字符集
    
    character-set-server=utf8
    
    #创建新表时将使用的默认存储引擎
    
    explicit_defaults_for_timestamp=true
    
    default-storage-engine=INNODB

更改配置文件后，重启Mysql服务进入Mysql输入命令 `select @@sql_mode;`可看到已经更改了mysql\_mode  
![在这里插入图片描述][20190923154919834.png]

[20190923154033419.png]: https://img-blog.csdnimg.cn/20190923154033419.png
[20190923154919834.png]: https://img-blog.csdnimg.cn/20190923154919834.png

MySQL5.7:of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

发表评论取消回复

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

相关阅读

相关 MySQL Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column异常处理

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

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

相关 MySQL5.7:of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

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

相关 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 nonaggregated column 异常解决

相关 MySQL错误SELECT list is not in GROUP BY clause and contains nonaggregated column解决”

相关 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘sss.mon

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