MySQL报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column whic

ゝ一世哀愁。 2024-03-31 09:35 27阅读 0赞

### 报错信息 ###

报错信息及语句如下

![在这里插入图片描述][0e5029705a46433fb5137b4e4053ddc1.png]

SELECT i.sku_id sku_id,a.attr_id attr_id,a.attr_name attr_name, a.attr_value
    from pms_sku_info i
    LEFT JOIN pms_sku_sale_attr_value a on a.sku_id = i.sku_id
    where i.spu_id = 3
    GROUP BY a.attr_value;

### 报错原因 ###

**字面翻译：** SELECT 列表的表达式 \#1 不在 GROUP BY 子句中，并且包含非聚合列“grades.order\_id” 它在功能上不依赖于 GROUP BY 子句中的列； 这与 sql\_mode=only\_full\_group\_by 不兼容

使用GROUP BY 语句违背了 sql\_mode=only\_full\_group\_by。因为mysql版本5.7之后默认的模式是 `ONLY_FULL_GROUP_BY`

查看官方文档，发现从 MySQL 5.7.5 开始，默认 SQL 模式包括 ONLY\_FULL\_GROUP\_BY。 （在 5.7.5 之前，MySQL 不检测函数依赖，并且默认不启用 ONLY\_FULL\_GROUP\_BY）这可能会导致一些sql语句失效

### 解决办法 ###

#### 1 临时 ####

执行 SQL ：查询模式

select @@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

可以看到我们的 sql\_mode 中含有 ONLY\_FULL\_GROUP\_BY，因此只要将它去掉即可

执行命令：

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

**注意：**  
需要注意的是，该修改仅在该次（窗口）有效，只是临时修改

#### 2 永久 ####

Windows 用户

编辑 mysql 配置文件 my.ini，在尾部添加以下内容，重新启动 mysql 即可：

[mysqld] 
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Mac/Linux 用户

需要编辑 /etc/my.cnf 文件，在尾部添加以下内容，重新启动 mysql 即可：

[mysqld] 
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

如果该文件不存在，可以新建一个，以下为初始内容：

# Example MySQL config file for medium systems.  
      #  
      # This is for a system with little memory (32M - 64M) where MySQL plays  
      # an important part, or systems up to 128M where MySQL is used together with  
      # other programs (such as a web server)  
      #  
      # MySQL programs look for option files in a set of  
      # locations which depend on the deployment platform.  
      # You can copy this option file to one of those  
      # locations. For information about these locations, see:  
      # http://dev.mysql.com/doc/mysql/en/option-files.html  
      #  
      # In this file, you can use all long options that a program supports.  
      # If you want to know which options a program supports, run the program  
      # with the "--help" option.  
      # The following options will be passed to all MySQL clients  
      [client]
      default-character-set=utf8
      #password   = your_password  
      port        = 3306  
      socket      = /tmp/mysql.sock   
      # Here follows entries for some specific programs  
      # The MySQL server  
      [mysqld]
      character-set-server=utf8
      init_connect='SET NAMES utf8
      port        = 3306  
      socket      = /tmp/mysql.sock  
      skip-external-locking  
      key_buffer_size = 16M  
      max_allowed_packet = 1M  
      table_open_cache = 64  
      sort_buffer_size = 512K  
      net_buffer_length = 8K  
      read_buffer_size = 256K  
      read_rnd_buffer_size = 512K  
      myisam_sort_buffer_size = 8M  
      character-set-server=utf8  
      init_connect='SET NAMES utf8' 
    # Don't listen on a TCP/IP port at all. This can be a security enhancement,  
    # if all processes that need to connect to mysqld run on the same host.  
    # All interaction with mysqld must be made via Unix sockets or named pipes.  
    # Note that using this option without enabling named pipes on Windows  
    # (via the "enable-named-pipe" option) will render mysqld useless!  
    #   
    #skip-networking  
      
      # Replication Master Server (default)  
      # binary logging is required for replication  
      log-bin=mysql-bin  
        
        # binary logging format - mixed recommended  
        binlog_format=mixed

#### 3 自用 ####

或者可以像我这样使用

SELECT GROUP_CONCAT( i.sku_id) sku_id,
    GROUP_CONCAT(DISTINCT a.attr_id) attr_id,
    GROUP_CONCAT(DISTINCT a.attr_name) attr_name, a.attr_value
    from pms_sku_info i
    LEFT JOIN pms_sku_sale_attr_value a on a.sku_id = i.sku_id
    where i.spu_id = 3
    GROUP BY a.attr_value;

使用 `GROUP_CONCAT`函数将需要查询的结果包裹，这样就不会报错（不清楚原因）

查询结果显示如下：

![在这里插入图片描述][c52f4af89ee0477cb48829d32bc24b54.png]

[0e5029705a46433fb5137b4e4053ddc1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/5c64c786bd7b428a8c3ac5ae0a5a13ea.png
[c52f4af89ee0477cb48829d32bc24b54.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/b7feed9c8d2946b4905f49e4d5acd1ae.png

MySQL报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column whic

发表评论取消回复

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

相关阅读

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

相关 MySQL 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

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

相关 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'inform

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

相关 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