报错信息
报错信息及语句如下
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函数将需要查询的结果包裹，这样就不会报错（不清楚原因）
查询结果显示如下：文章来源：https://www.toymoban.com/news/detail-465140.html
文章来源地址https://www.toymoban.com/news/detail-465140.html
到了这里，关于MySQL报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column whic的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
