在 MySql 中执行查询时与only_full_group_by相关的错误

我只会添加到.group_idGROUP BY

当 ping 不属于 的列时，组中该列可能有多个值，但结果中只有一个值的空间。因此，通常需要确切地告诉数据库如何将这些多个值转换为一个值。通常，这是通过聚合函数（如 、 等）完成的......我之所以说通常，是因为大多数其他流行的数据库系统都坚持这一点。但是，在 MySQL 5.7 版本之前，默认行为更加宽容，因为它不会抱怨然后任意选择任何值！它还具有一个功能，如果您确实需要与以前相同的行为，则可以用作此问题的另一种解决方案。这种灵活性是有代价的，因为它是不确定的，所以我不推荐它，除非你有很好的理由需要它。MySQL现在默认打开该设置是有充分理由的，因此最好习惯它并使您的查询符合它。SELECTGROUP BYCOUNT()SUM()MAX()ANY_VALUE()only_full_group_by

那么，为什么我上面的简单答案呢？我做了几个假设：

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

2）也是独一无二的。这可能不是一个合理的假设。如果不是这种情况，并且您有一些重复项，然后按照我的建议添加到 中，您可能会发现您现在得到的结果比以前更多，因为具有相同名称的组现在将在结果中具有单独的行。对我来说，这比隐藏这些重复的组要好，因为数据库已经悄悄地任意选择了一个值！group_namegroup_namesgroup_idGROUP 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

如果您不想对当前查询进行任何更改，请按照以下步骤操作 -

1. 流浪 ssh 进入您的盒子
2. 类型：sudo vim /etc/mysql/my.cnf
3. 滚动到文件底部并键入以进入插入模式A

4. 复制和粘贴

   [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. 键入退出输入模式esc

6. 键入以保存并关闭 vim。:wq
7. 键入以重新启动 MySQL。sudo service mysql restart

您可以按照其他答案中的说明关闭警告消息，也可以了解正在发生的事情并修复它。

从MySQL 5.7.5开始，默认的SQL模式包括ONLY_FULL_GROUP_BY这意味着当您对行进行分组，然后从该组中选择某些内容时，您需要明确说明应该从哪一行进行选择。

例如，给定以下记录：

我们可以要求MySQL按颜色字段进行分组，从而为我们提供以下组：

但是，如果我们要求每个组的值字段，我们会得到一个错误，因为每个组都有很多行带有值字段。

或者，以图像形式：

MySQL需要知道你要查找的组中的哪一行，这给了你三个选项

• 将所需的列添加到组语句中，在某些情况下，这可能是您想要的，但否则会返回具有相同颜色的重复结果，而您可能不需要group by rect.color, rect.value
• 使用 MySQL 的聚合函数来指示您在组（如 、 、 或AVG()MIN()MAX()FIRST()
• 如果确定组内的所有结果都相同，请使用 ANY_VALUE（）。

这就是帮助我理解整个问题的原因：

1. https://stackoverflow.com/a/20074634/1066234
2. 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.gameplay.timestamp”;这与 sql_mode=only_full_group_by 不兼容 - 查询：选择 COUNT（*） 作为尝试次数，SUM（elapsed） 作为 elapsedtotal、userid、timestamp、questionid、answerid、SUM（correct） 作为正确、已用完、ipaddress FROM 游戏玩法 其中时间戳 >= DATE_SUB（NOW（），间隔 1 天） AND 用户 ID = 1

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

我在 laravel homestead 上使用 Laravel 5.3、mysql 5.7.12（我相信是 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.phptruefalse

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

延伸阅读：

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_BYANY_VALUE()

...

启用此查询可能无效，因为选择列表中的非聚合地址列未在子句中命名：ONLY_FULL_GROUP_BYGROUP 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 或稳定版 2.5 以外的任何更高版本，您可能会遇到此问题，首先问题出在 sql 上。您必须相应地命名字段。但是还有另一种方法可以解决它。单击 Wamp 的绿色图标。mysql->mysql 设置-> sql_mode->none。或者从控制台中，您可以更改默认值。

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 开始，选项默认启用。
因此，根据标准 SQL92 及更早版本：ONLY_FULL_GROUP_BY

不允许选择列表、HAVING 条件、 或 ORDER BY 列表是指未命名的非聚合列 在 GROUP BY 子句中，也不依赖于 （唯一 确定者） GROUP BY 列

(在文档中阅读更多内容)

因此，例如：

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

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

#1055 - SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“testsite.user.id”，它不是 在功能上依赖于 GROUP BY 子句中的列;这是 与 sql_mode=only_full_group_by 不兼容

为什么？
因为MySQL并不完全理解，从分组记录中检索哪些某些值，这就是重点。

即假设您的表中有以下记录：users

您将执行上面显示的无效查询。
你会得到上面显示的错误，因为，有 3 条带有名称的记录，这很好，但是，它们都有不同的字段值。
因此，MySQL根本不了解在生成的分组记录中返回哪些内容。Johnemail

只需按如下方式更改查询即可解决此问题：

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

此外，您可能希望向 SELECT 部分添加更多字段，但如果它们未聚合，则无法这样做，但您可以使用拐杖（但强烈建议）：

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

现在，你可能会问，为什么强烈不建议使用？
因为MySQL并不完全知道要检索的分组记录的值，并且通过使用此函数，您要求它获取其中的任何一条（在本例中，获取了名称为= John的第一条记录的电子邮件）。
确切地说，我想不出任何关于你为什么希望这种行为存在的想法。

拜托，如果你不理解我，请阅读更多关于MySQL中的分组是如何工作的，它非常简单。ANY_VALUE

最后，这里还有一个简单但有效的查询。
如果要根据可用年龄查询用户总数，则可能需要记下此查询

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）

如果您在使用 doctrine 查询生成器的 Symfony 中遇到此错误，并且此错误是由 orderBy 引起的：

注意你想要的列，并使用代替：selectgroupByaddGroupBygroupBy

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

在 Symfony3 上工作 -

您可以将 a 添加到 ;如果您确定这是独一无二的。unique indexgroup_idgroup_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 文件后在存储过程中运行查询时遇到问题，请尝试再次加载 SP。

我怀疑 MySQL 一定是用最初设置的默认only_full_group_by编译了 SP。因此，即使我更改了 my.cnf 并重新启动了 mysqld，它对 SP 也没有影响，并且它们一直失败，并显示“SELECT 列表不在 GROUP BY 子句中并且包含非聚合列......在功能上不依赖于 GROUP BY 子句中的列;这与 sql_mode=only_full_group_by“不兼容。

重新加载 SP 必须导致它们现在被重新编译并禁用only_full_group_by。在那之后，它们似乎按预期工作。