如何使用only_full_group_by修复查询组 [英] How to fix query group with 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屋!