如何使用only_full_group_by修复查询组 [英] How to fix query group with only_full_group_by

查看:194
本文介绍了如何使用only_full_group_by修复查询组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个基本的键值表,其中包含每个用户的一些数据.使用更新的mysql进行分组依据时,sql_mode设置为only_full_group_by(新的默认值).当我尝试运行此简单查询时:

I have a basic key-value table, that has some data in it for each user. With the updated mysql it has the sql_mode set to only_full_group_by (new default) when you do a group by. When I try to run this simple query:

select * from user_features
where user_id = 1
group by feature_key

我收到以下错误:

SQL错误(1055):SELECT列表的表达式#1不在GROUP BY子句中,并且包含未聚合的列'date.user_features.user_id',该列在功能上不依赖于GROUP BY子句中的列;这与sql_mode = only_full_group_by

SQL Error (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'date.user_features.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

使用此示例数据,我想基于feature_key进行分组(一旦修复了组错误,我将添加group_concat).

With this example data, I want to group based on the feature_key (I will add a group_concat once the group error is fixed).

| user_id | feature_key | feature_value |
+---------+-------------+---------------+
| 1       | color       | red           |
+---------+-------------+---------------+
| 1       | age         | 15            |
+---------+-------------+---------------+
| 1       | color       | blue          |
+---------+-------------+---------------+

该表如下所示:

CREATE TABLE `user_features` (
  `user_id` int(10) unsigned NOT NULL,
  `feature_key` varchar(50) NOT NULL,
  `feature_value` varchar(50) NOT NULL,
  UNIQUE KEY `user_id_feature_key_feature_value` (`user_id`,`feature_key`,`feature_value`)
)

我可以运行哪些查询来解决此问题,或者需要添加什么索引?

What query can I run to fix this or what index(es) do I need to add?

推荐答案

这是MySQL用户的常见错误.在MySQL 5.7中,默认情况下,数据库强制执行大多数其他SQL数据库多年来执行的标准语义.

This is a common error for MySQL users. In MySQL 5.7, by default the database enforces the standard semantics that most other SQL databases have been enforcing for years.

规则是选择列表中的每一列都必须是以下其中之一:

The rule is that every column in your select-list must be one of:

  • 在GROUP BY子句中命名;也就是说,这就是您要分组的内容.
  • 在诸如MIN,MAX(),SUM(),GROUP_CONCAT()等聚合函数中.
  • 在功能上取决于您要分组的列(这是MySQL对标准SQL行为的扩展,而其他SQL数据库不一定支持此功能.)

在您的查询中(我将展开您的SELECT *):

In your query (I'll expand your SELECT *):

select user_id, feature_key, feature_value from user_features
where user_id = 1
group by feature_key

您正在按feature_key进行分组,但这意味着其他列不符合我上述的规则.

You are grouping by feature_key, but this means the other columns don't comply with the rules I described above.

这是一种解决方法:

select MAX(user_id), feature_key, GROUP_CONCAT(feature_value)
from user_features
where user_id = 1
group by feature_key

使用MAX(user_id)似乎是多余的,因为基于WHERE子句条件只能有一个值.但是也没有害处. MIN(user_id)也可以.

It might seem redundant to use MAX(user_id) since there is only one value possible based on the WHERE clause condition. But there's no harm either. MIN(user_id) would also work.

另请参阅我过去对相同错误的回答:

See also my past answers on this same error:

  • https://stackoverflow.com/a/47701851/20860
  • https://stackoverflow.com/a/38705647/20860

这篇关于如何使用only_full_group_by修复查询组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
相关文章
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆