在MySql中执行查询时，只有full_group_by相关的错误

小开

最佳答案

我只需要将group_id添加到GROUP BY。

当SELECTing一个不属于GROUP BY的列时，在组中该列可以有多个值，但在结果中只能为单个值留出空间。因此，需要精确地告诉数据库通常如何将这些多个值变成一个值。通常，这是通过COUNT()， SUM()， MAX()等聚合函数来完成的……我说通常是因为大多数其他流行的数据库系统都坚持这样做。然而，在5.7版本之前的MySQL中，默认行为更加宽容，因为它不会抱怨，然后任意选择任何价值!它还有一个ANY_VALUE()函数，如果你真的需要和以前一样的行为，可以用作这个问题的另一个解决方案。这种灵活性是有代价的，因为它是不确定的，所以除非你有很好的理由需要它，否则我不会推荐它。MySQL现在默认开启only_full_group_by设置是有原因的，所以最好习惯它，并使你的查询符合它。

那么为什么我的答案如此简单呢?我做了几个假设:

1) group_id是唯一的。看起来很合理，毕竟这是一个“ID”。

2) group_name也是唯一的。这可能不是一个合理的假设。如果不是这样，你有一些重复的group_names，然后你按照我的建议将group_id添加到GROUP BY中，你可能会发现你现在得到的结果比以前更多，因为具有相同名称的组现在在结果中有单独的行。对我来说，这比隐藏这些重复的组要好，因为数据库已经悄悄地任意选择了一个值!

当涉及多个表时，使用表名或别名限定所有列也是一种很好的实践……

SELECT
g.group_id AS 'value',
g.group_name AS 'text'
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id
WHERE g.active = 1
AND g.department_id = 1
AND g.manage_work_orders = 1
AND g.group_name != 'root'
AND g.group_name != 'superuser'
GROUP BY
g.group_name,
g.group_id
HAVING COUNT(d.user_id) > 0
ORDER BY g.group_name

小开

您可以尝试通过执行以下命令禁用only_full_group_by设置:

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

MySQL 8不接受NO_AUTO_CREATE_USER，因此需要删除它。

小开

如果您不想在当前查询中进行任何更改，那么请遵循以下步骤-

流浪汉SSH进你的盒子
类型:sudo vim /etc/mysql/my.cnf
滚动到文件底部，输入A进入插入模式

复制粘贴

[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

Type esc to exit input mode
Type :wq to save and close vim.
Type sudo service mysql restart to restart MySQL.

小开

你可以像其他答案中解释的那样关闭警告消息，或者你可以理解发生了什么并修复它。

从MySQL 5.7.5开始，默认的SQL模式只包含_full_group_by，这意味着当你对行进行分组，然后从组中选择一些东西时，你需要显式地说出哪一行，如果该选择是从其中做出的。

Mysql需要知道您要查找组中的哪一行，这为您提供了两个选项

你也可以将你想要的列添加到组语句group by rect.color, rect.value中，这在某些情况下可以是你想要的，否则将返回相同颜色的重复结果，这可能不是你想要的
你也可以使用mysql的聚合函数来指示你在组中寻找的行，如AVG() MIN() MAX() 完整的列表
最后，如果你确定组内的所有结果都是相同的，你可以使用ANY_VALUE()。医生

小开

这有助于我理解整个问题:

https://stackoverflow.com/a/20074634/1066234 < a href = " https://stackoverflow.com/a/20074634/1066234 " > < / >
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

下面是另一个问题查询的例子。

问题:

SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM `gameplay`
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
AND cookieid = #

通过在最后加上这个来解决:

  GROUP BY timestamp, userid, cookieid, questionid, answerid, elapsed, ipaddress

注意:查看PHP中的错误消息，它会告诉您问题出在哪里。

例子:

MySQL查询错误1140:在没有GROUP BY的聚合查询中，SELECT列表中的表达式#4包含非聚合列'db.game .timestamp';这与sql_mode=only_full_group_by不兼容-查询:SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal，用户id，时间戳，questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM gameplay WHERE时间戳>= DATE_SUB(NOW()，间隔1天) AND userid = 1

在本例中，GROUP BY中缺少表达式# 4。

小开

我使用Laravel 5.3, mysql 5.7.12，在Laravel homestead(0.5.0，我相信)

即使在显式地设置编辑/etc/mysql/my.cnf以反映:

[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

我仍然在接收错误。

我必须将config/database.php从true更改为false:

    'mysql' => [
'strict' => false, //behave like 5.6
//'strict' => true //behave like 5.7
],

进一步阅读:

< a href = " https://laracasts.com/discuss/channels/servers/set-set-sql-mode-on-homestead " > https://laracasts.com/discuss/channels/servers/set-set-sql-mode-on-homestead https://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2

小开

使用ANY_VALUE()引用未聚合的列。

SELECT name,           address , MAX(age) FROM t GROUP BY name; -- fails
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; -- works

从MySQL 5.7文档:

你可以在不禁用ONLY_FULL_GROUP_BY的情况下达到同样的效果通过使用ANY_VALUE()来引用未聚合的列
.．.

启用ONLY_FULL_GROUP_BY时，此查询可能无效，因为选择列表中的非聚合地址列没有在GROUP BY子句中命名:
SELECT name, address, MAX(age) FROM t GROUP BY name;
.．.

如果您知道，对于给定的数据集，每个名称值实际上唯一地决定了地址值，则地址实际上在功能上依赖于名称。要告诉MySQL接受查询，你可以使用ANY_VALUE()函数:
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

小开

mac:

1.复制默认的my-default.cnf到/etc/my.cnf

sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf

2.使用您喜欢的编辑器更改my.cnf中的sql_mode并将其设置为此

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3.重新启动MySQL服务器。

mysql.server restart

小开

对于localhost / wampserver 3，我们可以设置sql-mode = user_mode来消除这个错误:

click on wamp icon -> MySql -> MySql Setting -> sql-mode -> user_mode

然后重新启动wamp或apache

小开

很抱歉，没有使用准确的SQL

我使用这个查询来克服Mysql警告。

SELECT count(*) AS cnt, `regions_id`
FROM regionables
WHERE `regionable_id` = '115' OR `regionable_id` = '714'
GROUP BY `regions_id`
HAVING cnt > 1

注意我存在的关键

count(*) AS cnt

小开

如果你使用的是wamp 3.0.6或任何高于stable 2.5的版本，你可能会遇到这个问题，首先是sql的问题。您必须相应地命名字段。但是还有另一种解决方法。点击wamp的绿色图标。Mysql -> Mysql settings-> sql_mode->无。或者从控制台更改默认值。

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

小开

我会试着向你解释这个错误是关于什么的从MySQL 5.7.5开始，选项ONLY_FULL_GROUP_BY默认启用因此，根据SQL92标准和更早的标准:

不允许查询select list, HAVING条件，或ORDER BY列表引用既没有命名也没有聚合的列在GROUP BY子句中也不依赖于(唯一的 GROUP by columns

(阅读更多文档)

举个例子:

SELECT * FROM `users` GROUP BY `name`;

执行上述查询后，您将得到错误消息。

#1055 - SELECT列表的表达式#1不在GROUP BY子句中，并且包含未聚合的列'testsite.user. user. '我不是函数依赖于GROUP BY子句中的列;这是 sql_mode=only_full_group_by
.不兼容

为什么? 因为MySQL不能确切地理解，从分组记录中检索哪些特定的值，这就是要点 即假设你在users表中有这样的记录:

你将执行上面显示的无效查询你会得到如上所示的错误，因为，有3条记录名为John，这很好，但是，它们都有不同的email字段值因此，MySQL根本不知道在结果分组记录中返回哪一个

你可以通过修改你的查询来解决这个问题:

SELECT `name` FROM `users` GROUP BY `name`

此外，你可能想要添加更多的字段到SELECT节，但你不能这样做，如果他们没有聚合，但有拐杖你可以使用(但强烈不推荐):

SELECT ANY_VALUE(`id`), ANY_VALUE(`email`), `name` FROM `users` GROUP BY `name`

现在，你可能会问，为什么强烈不建议使用ANY_VALUE ? 因为MySQL不确切地知道要检索分组记录的值，并且通过使用这个函数，您要求它获取其中任何一个(在这种情况下，第一个名称为John的记录的电子邮件被获取) 确切地说，我想不出任何关于你为什么想要这种行为存在的想法如果你不明白我的意思，请阅读更多关于MySQL中的分组工作原理，这是非常简单的最后，这里还有一个更简单但有效的查询如果您希望根据可用年龄查询总用户计数，您可能希望写下这个查询

SELECT `age`, COUNT(`age`) FROM `users` GROUP BY `age`;

根据MySQL规则，这是完全有效的依此类推，

重要的是要确切地了解问题是什么，然后才写下解决方案

小开

增加/etc/mysql/my.cnf文件中的行(如下所述)

[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

对我来说很好。服务器版本:5.7.18-0ubuntu0.16.04.1 - (Ubuntu)

小开

如果使用Symfony时出现此错误，则使用原则查询构建器，并且如果此错误是由orderBy引起的:

注意select你想要groupBy的列，并使用addGroupBy而不是groupBy:

$query = $this->createQueryBuilder('smth')->addGroupBy('smth.mycolumn');

适用于Symfony3 -

小开

你可以在group_id中添加unique index;如果你确定group_id是唯一的。

它可以在不修改查询的情况下解决您的问题。

迟来的回答，但在回答中还没有提到。也许它应该完善现有的已经很全面的答案。至少，当我不得不分割一个包含太多字段的表时，它确实解决了我的问题。

小开

进入mysql或phpmyadmin，选择数据库然后简单地执行这个查询，它就可以工作了。它为我工作很好。

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

小开

我不得不在我的Ubuntu 18.04上编辑下面的文件:

/etc/mysql/mysql.conf.d/mysqld.cnf

与

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

而且

sudo service mysql restart

小开

上面的共识答案很好，但如果在修复my.cnf文件后在存储过程中运行查询时遇到问题，那么请再次尝试加载SPs。

我怀疑MySQL必须用默认的only_full_group_by集来编译sp。因此，即使我更改了my.cnf并重新启动mysqld，它对sp也没有影响，并且他们不断失败"选择列表不在GROUP BY子句中，并且包含非聚合列…它在功能上不依赖于GROUP BY子句中的列;这与sql_mode=only_full_group_by"不兼容。

重新加载sp必须导致它们现在被重新编译，只有_full_group_by被禁用。在那之后，它们似乎像预期的那样工作。