MySQL 5.7及更高版本only_full_group_by [英] MySQL 5.7 & only_full_group_by
问题描述
因此，我更新了我的ubuntu框，并随mysql一起来到了导致厄运的光荣5.7:
So, I updated my ubuntu box and mysql came along for the ride to the glorious 5.7 that brings about doom:
[:error] [pid 9211] [client 0.0.0.0:1] PHP Fatal error:
Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation:
1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains
nonaggregated column 'a.Z'; this is incompatible with sql_mode=only_full_group_by
它所指的行是:
$s = $d->prepare("SELECT a.Z,a.Y,a.X,MAX(b.F) as `G` FROM `a`,`b` WHERE (a.P=:va1 && b.R:va2)");
拒绝选择列表，HAVING条件或ORDER BY列表所引用的查询，这些查询既未在GROUP BY子句中命名，也未在功能上依赖于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.
源: http://dev.mysql. com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by
所以，这是我的四个问题:
So, here are my 4 questions:
1-个非聚集列为a.Z，a.Y，a.X?
1- nonaggregated columns being a.Z, a.Y, a.X ?
2-在功能上取决于=?
2- functionally dependent on = ?
3-唯一确定的.从字面上看，列是(例如)P_Id int NOT NULL UNIQUE?
3- uniquely determined by. Literarily, columns that are (for example) P_Id int NOT NULL UNIQUE ?
4-尽管可以通过在a.Z，a.Y和a.X周围添加any_value()来解决此错误，但我仍然不明白为什么这样做.
我想，替代方法是按a.Z或/和a.Y或/和a.X进行分组.但是我该选哪三人呢?为什么?
4- Even though this error I can fix with adding any_value() around a.Z, a.Y and a.X I still don't understand why I'm doing so.
I imagine that the alternative would be to do a group by a.Z or/and a.Y or/and a.X. But which of those 3 do I pick in the group by and why?
此外，我不想禁用only_full_group_by.我想了解如何正确解决当前问题，最有可能的是随之而来的天文数字.
Also, I do not want to disable only_full_group_by. I would like to understand how to properly fix this current issue and most likely the astronomic number that will follow.
推荐答案
-
是的.非聚合列是不使用诸如
MAX，COUNT，SUM，GROUP_CONCAT等之类的聚合函数的任何列.
Yes. Non-aggregated columns are any column that don't use an aggregation function like
MAX,COUNT,SUM,GROUP_CONCAT, etc.
列a在功能上取决于列b.通常，这意味着b是表的唯一键，而a是该表中的其他列.
Column a is functionally dependent on column b if the value of b implies a particular value of a. This generally means that b is a unique key for the table, and a is some other column in that table.
唯一由决定的与功能依赖项相同.
Uniquely determined by is the same as functional dependency.
另一种方法是在GROUP BY列表中列出所有未聚合的列:GROUP BY a.Z, a.Y, a.X.
The alternative would be to list all the non-aggregated columns in the GROUP BY list: GROUP BY a.Z, a.Y, a.X.
所有这些的原因是，当您选择不在GROUP BY列表中的列时，它们将来自分组行中的任意行.这会导致许多常见错误.例如，一个常见的错误是写:
The reason for all this is that when you select columns that aren't in the GROUP BY list, they will come from arbitrary rows in the grouped rows. This leads to many common errors. For instance, a common mistake is to write:
SELECT user_id, MAX(timestamp), ip_address
FROM user_logins
GROUP BY user_id
，并期望ip_address包含每个用户的最新登录地址.但是它实际上将包含他们登录的地址的 any ，而不是包含MAX(timestamp)的行中的那个地址.请参见 SQL仅选择具有最大值的行在列上获取正确的方法.
and expect ip_address to contain the address of the most recent login for each user. But it will actually contain any of the addresses they logged in from, not the one from the row with MAX(timestamp). See SQL Select only rows with Max Value on a Column for the correct way to do that.
功能依赖异常通常对于连接很有用.
The functional dependency exception is typically useful with joins.
SELECT u.user_id, u.user_name, MAX(l.timestamp)
FROM users AS u
JOIN user_logins AS l ON u.user_id = l.user_id
GROUP BY u.user_id
由于user_id是users表的主键，因此它唯一地确定user_name，因此不必在GROUP BY中明确列出.
Since user_id is the primary key of the users table, it uniquely determines user_name, so it's not necessary to list that explicitly in GROUP BY.
这篇关于MySQL 5.7及更高版本only_full_group_by的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
