在mysql中执行查询时,仅与\u full \u group \u by相关的错误

ckx4rj1h  于 2021-07-24  发布在  Java
关注(0)|答案(15)|浏览(243)

我已经升级了我的系统,并安装了mysql 5.7.9与php的一个web应用程序,我正在工作。我有一个动态创建的查询,当在mysql的旧版本中运行时,它可以正常工作。升级到5.7后,出现以下错误:
select列表的表达式#1不在group by子句中,它包含未聚合的列“support_desk.mod_users_groups.group_id”,该列在功能上不依赖于group by子句中的列;这与sql\u mode=only\u full\u group by不兼容
请注意MySQL5.7的手册页中关于服务器sql模式的主题。
这是给我带来麻烦的问题:

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 或者我还需要做什么?
如果你需要更多的信息,请告诉我。

uujelgoq

uujelgoq1#

我只想补充一句 group_idGROUP BY .
什么时候 SELECT 删除不属于 GROUP BY 组中该列可以有多个值,但结果中只有一个值有空间。因此,通常需要确切地告诉数据库如何将多个值转换为一个值。通常,这是通过聚合函数完成的,如 COUNT() , SUM() , MAX() 等。。。我这样说通常是因为大多数其他流行的数据库系统都坚持这样做。然而,在5.7版本之前的mysql中,默认行为更为宽容,因为它不会抱怨然后任意选择任何值!它还有一个 ANY_VALUE() 如果你真的需要和以前一样的行为,这个函数可以作为这个问题的另一个解决方案。这种灵活性是有代价的,因为它是不确定的,所以除非你有很好的理由需要它,否则我不会推荐它。mysql现在正在打开 only_full_group_by 默认设置是有充分理由的,所以最好习惯它并使您的查询符合它。
那么,为什么我的简单答案上面?我做了几个假设:
1) 那个 group_id 是独一无二的。似乎有道理,毕竟是个“身份证”。
2) 那个 group_name 也是独一无二的。这可能不是一个合理的假设。如果不是这样,你有一些复制品 group_names 然后你按照我的建议 group_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
35g0bw71

35g0bw712#

您可以尝试禁用 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 所以这需要去掉。

6jjcrrmo

6jjcrrmo3#

您可以按照其他答案中的说明关闭警告消息,也可以了解发生了什么并进行修复。
从MySQL5.7.5开始,默认的sql模式只包括\u full \u group \u,这意味着当您对行进行分组,然后从该组中选择某个内容时,您需要显式地说应该从哪一行进行选择。

mysql需要知道您要查找的是组中的哪一行,这给了您两个选择
也可以将所需的列添加到group语句中 group by rect.color, rect.value 在某些情况下,它可能是您想要的,否则将返回具有相同颜色的重复结果,而您可能不想要
您还可以使用mysql的聚合函数来指示要在组中查找的行,如 AVG() MIN() MAX() 完整列表
最后你可以用 ANY_VALUE() 如果您确定组内的所有结果都相同。博士

kmbjn2e3

kmbjn2e34#

如果不想对当前查询进行任何更改,请按照以下步骤操作-
把流浪汉塞进你的箱子里
类型: 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。

ddarikpa

ddarikpa5#

使用 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

来自MySQL5.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;
qqrboqgw

qqrboqgw6#

我会尽力向你解释这个错误的原因。
从MySQL5.7.5开始,选择 ONLY_FULL_GROUP_BY 默认情况下启用。
因此,根据标准sql92和更早版本:
不允许select list、having condition或order by list引用既不在group by子句中命名,也不在功能上依赖于group by列(由group by列唯一确定)的未聚合列的查询
(在文档中阅读更多)
例如:

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

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

1055-select列表的表达式#1不在group by子句中,并且包含未聚合的列“testsite.user.id”,该列在功能上不依赖于group by子句中的列;这与sql\u mode=only\u full\u group by不兼容

为什么?
因为mysql不完全理解,从分组记录中检索哪些特定值,这就是重点。
i、 假设你的电脑里有这些记录 users 表格:

您将执行上面显示的无效查询。
您将得到上面显示的错误,因为有3条记录的名称为 John ,这是很好的,但是,他们都有不同的 email 字段值。
所以,mysql根本不知道在结果分组记录中返回哪一个。
您可以通过如下方式更改查询来解决此问题:

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

此外,您可能希望添加更多字段以选择节,但如果这些字段未聚合,则无法这样做,但您可以使用拐杖(但高度不建议):

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规则,这是完全有效的。
等等。
重要的是要弄清楚问题到底是什么,然后才写下解决办法。

xv8emn3q

xv8emn3q7#

我在laravelhomestead上使用了laravel5.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-homesteadhttpshttp://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2

pxyaymoc

pxyaymoc8#

转到mysql或phpmyadmin并选择database,然后简单地执行这个查询,它就可以工作了。对我来说很好。

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

sg3maiej9#

如果您使用的是WAMP3.0.6或Stable2.5以外的任何更高版本,您可能会遇到这个问题,首先问题是sql。必须相应地命名字段。但还有另一种方法可以解决这个问题。点击wamp的绿色图标。mysql->mysql设置->sql\u模式->无。也可以从控制台更改默认值。

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';
kpbwa7wx

kpbwa7wx10#

在文件:/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)

wnavrhmk

wnavrhmk11#

对于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\模式,并将其设置为

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
kupeojn6

kupeojn612#

这有助于我理解整个问题:
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 list的表达式#4包含未聚合的列'db.gameplay.timestamp';这与sql不兼容。mode=only\u full\u group\u by-query:选择count(*)作为尝试,sum(appeased)作为elapsedtotal,userid,timestamp,questionid,answerid,sum(correct)作为正确,appeased,ipaddress from gameplay where timestamp>=date\u sub(now(),interval 1 day)和userid=1
在这种情况下,在by组中表达4缺失。

rjjhvcjd

rjjhvcjd13#

对于localhost/wampserver 3,我们可以设置sql mode=user\u mode来删除此错误:

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

然后重新启动wamp或apache

hmae6n7t

hmae6n7t14#

您可以添加 unique indexgroup_id ; 如果你确定 group_id 是独一无二的。
它可以在不修改查询的情况下解决您的问题。
迟来的答复,但答复中尚未提及。也许它应该完成已经全面的答案。当我不得不拆分一个字段太多的表时,它至少解决了我的问题。

x6yk4ghg

x6yk4ghg15#

如果您在symfony使用条令查询生成器时遇到此错误,并且

相关问题