我已经升级了我的系统,并为我正在开发的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不兼容

请注意Mysql 5.7的手册页中关于Server SQL Modes的主题。

这个问题让我很困扰:

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选项,或者还有其他需要做的事情?


当前回答

可以通过以下命令禁用only_full_group_by setting。

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,所以需要删除。

其他回答

如果您在Symfony使用doctrine查询构建器时出现此错误,并且此错误是由orderBy引起的:

注意选择你想要groupBy的列,使用addGroupBy而不是groupBy:

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

工作在Symfony3 -

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

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

如果你使用的是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列表,HAVING条件, 或ORDER BY列表引用既没有命名也没有聚合的列 在GROUP BY子句中也不依赖于(唯一的 按列分组

(在文档中阅读更多)

举个例子:

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

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

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

为什么? 因为MySQL不能确切地理解,从分组记录中检索哪些特定值,这就是问题所在。

例如,假设你在用户表中有这样的记录:

您将执行上面显示的无效查询。 你会得到如上所示的错误,因为,有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不确切地知道要检索分组记录的值,并且通过使用这个函数,您要求它获取其中任何一个(在本例中,获取了name = John的第一条记录的电子邮件)。 确切地说,我想不出你为什么想要这种行为存在。 如果你不明白我的意思,请阅读更多关于如何在MySQL中分组工作,这是非常简单的。

最后,这里有一个更简单但有效的查询。 如果希望根据可用年龄查询用户总数,可能需要写下这个查询

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

根据MySQL规则,这是完全有效的。 等等。 重要的是要了解问题到底是什么,然后才写下解决方案。

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

从MySQL 5.7.5开始,默认的SQL模式只包括ONLY_FULL_GROUP_BY,这意味着当你对行进行分组,然后从组中选择一些东西时,你需要显式地说应该从哪一行进行选择。

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

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