在MySql中执行查询时与only_full_group_by相关的错误-Java 学习之路

286

我已经升级了我的系统，并为我正在处理的Web应用程序安装了MySql 5.7.9和php . 我有一个动态创建的查询，并且在旧版本的MySql中运行时，它可以正常工作 . 自升级到5.7后，我收到此错误：

SELECT列表的表达式＃1不在GROUP BY子句中，并且包含非聚合列'support_desk.mod_users_groups.group_id'，它在功能上不依赖于GROUP BY子句中的列;这与sql_mode = only_full_group_by不兼容

请注意有关Server SQL Modes主题的Mysql 5.7的Manual页面 .

这是给我带来麻烦的查询：

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

我在这个问题上做了一些谷歌搜索，但我不明白 only_full_group_by 足以弄清楚我需要做些什么来修复查询 . 我可以关闭 only_full_group_by 选项，还是还有其他我需要做的事情？

如果您需要更多信息，请与我们联系 .

15 回答

4
我只想将 group_id 添加到 GROUP BY .

当 SELECT 不属于 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
```
回复于 2024-05-19T06:57:03+08:00

您可以尝试通过执行以下操作来禁用 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 因此需要删除 .

回复于 2024-05-19T06:57:03+08:00

259
你可以按照其他答案中的说明关闭警告信息，或者你可以理解 what's happening 并修复它 .

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY这意味着当您对行进行分组然后从该组中选择某些内容时，您需要 explicitly say which row 来进行选择 .

Mysql需要知道您正在寻找的组中的哪一行，这为您提供了两个选项
- 您还可以将您想要的列添加到组语句 group by rect.color, rect.value ，这可能是您想要的，但在某些情况下会返回您可能不需要的相同颜色的重复结果
- 你也可以使用 aggregate functions 的mysql来指示你在组中寻找哪一行，如 AVG() MIN() MAX() complete list
- 最后，如果您确定组内的所有结果都相同，则可以使用 ANY_VALUE() . doc
回复于 2024-05-19T06:57:03+08:00
34
如果您不想在当前查询中进行任何更改，请按照以下步骤操作 -
- vagrant 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
```
- 输入 esc 退出输入模式
- 键入 :wq 以保存并关闭vim .
- 键入 sudo service mysql restart 以重启MySQL .
回复于 2024-05-19T06:57:03+08:00
0

Use ANY_VALUE() to refer to the nonaggregated column.

来自MySQL 5.7 docs：

通过使用ANY_VALUE（）来引用非聚合列，可以在不禁用ONLY_FULL_GROUP_BY的情况下实现相同的效果 . ...启用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;

回复于 2024-05-19T06:57:03+08:00
138
我正在使用Laravel 5.3，mysql 5.7.12laravel宅基地（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
    ],
```
进一步阅读：

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
回复于 2024-05-19T06:57:03+08:00
247
我会试着解释一下这个错误是什么 .
从MySQL 5.7.5开始，默认情况下启用选项 ONLY_FULL_GROUP_BY .
因此，根据标准SQL92和更早版本：

不允许查询选择列表，HAVING条件或ORDER BY列表引用既未在GROUP BY子句中命名的非聚合列，也不允许在功能上依赖于（由GROUP BY列唯一确定）

（read more in docs）

所以，例如：
```
SELECT * FROM `users` GROUP BY `name`;
```
执行上述查询后，您将收到错误消息 .

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

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

I.E.假设您在 users 表中有此记录：

并且您将执行上面的无效查询showen .
并且你会得到上面显示的错误，因为有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规则，这完全有效 .
等等 .

重要的是要了解问题到底是什么，然后记下解决方案 .
回复于 2024-05-19T06:57:03+08:00
7
如果您正在使用wamp 3.0.6或除稳定版2.5之外的任何高级版本，您可能会遇到此问题，首先问题是sql . 你必须相应地命名字段 . 但还有另一种方法可以解决它 . 点击wamp的绿色图标 . mysql-> mysql settings-> 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';
```
回复于 2024-05-19T06:57:03+08:00

对于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.Restart MySQL服务器 .

mysql.server restart

回复于 2024-05-19T06:57:03+08:00

8
在文件中添加行（如下所述）：/ 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）
回复于 2024-05-19T06:57:03+08:00
40
对于localhost / wampserver 3，我们可以设置sql-mode = user_mode来删除此错误：
```
click on wamp icon -> MySql -> MySql Setting -> sql-mode -> user_mode
```
然后重启wamp或apache
回复于 2024-05-19T06:57:03+08:00
1
这有助于我理解整个问题：
- https://stackoverflow.com/a/20074634/1066234
- https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
并在下面的另一个有问题的查询示例中 .

Problematic:
```
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 = #
```
Solved by adding this to the end:
```
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不兼容 - 查询：SELECT COUNT（*）作为尝试，SUM（已用）为elapsedtotal，userid，timestamp，questionid，answerid，SUM（正确）为正确，已过去，ipaddress FROM gameplay WHERE timestamp> = DATE_SUB （NOW（），INTERVAL 1 DAY）和userid = 1

在这种情况下，GROUP BY中缺少 expression #4 .
回复于 2024-05-19T06:57:03+08:00
239

您可以将 unique index 添加到 group_id ;如果你确定 group_id 是唯一的 .

It can solve your case without modifying the query .

答案很晚，但答案中还没有提到 . 也许它应该完成已经全面的答案 . 至少它确实解决了我的情况，当我不得不拆分一个有太多字段的表 .

回复于 2024-05-19T06:57:03+08:00
2
如果 Symfony 使用 doctrine query builder 时出现此错误，并且此错误是由 orderBy 引起的：

注意 select 你要 groupBy 的列，并使用 addGroupBy 而不是 groupBy ：
```
$query = $this->createQueryBuilder('smth')->addGroupBy('smth.mycolumn');
```
适用于 Symfony3 -
回复于 2024-05-19T06:57:03+08:00

抱歉不使用您的确切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

回复于 2024-05-19T06:57:03+08:00

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

15 回答

相关问题