没有GROUP BY的汇总查询 [英] Aggregated query without GROUP BY

问题描述

此查询似乎在我的旧计算机上可以完美运行.但是，在装有MySQL 5.7.14和PHP 5.6.25的新机器上，似乎抛出了错误:

This query seems to work perfect on my older machine. However, on my new machine with MySQL 5.7.14 and PHP 5.6.25 it seems to throw an error:

致命错误:带有消息的未捕获异常'PDOException' 'SQLSTATE [42000]:语法错误或访问冲突:总计1140 没有GROUP BY的查询，SELECT列表的表达式#1包含 未汇总的列'pixel_perfect.users.id';这是不兼容的 在C:\ wamp64 \ www

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'pixel_perfect.users.id'; this is incompatible with sql_mode=only_full_group_by' in C:\wamp64\www

这是我的查询内容:

$sql="SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email LIMIT 1";

$stmt=$db->prepare($sql);
$stmt->bindValue(':email', $email);
$stmt->execute();

为什么现在我会收到此错误，我该怎么做才能不费吹灰之力地解决它.

Why am I getting this error now and what do I do to resolve it painlessly.

推荐答案

A 版本5.7-ish 中进行了更改，默认情况下，该版本现在将拒绝使用功能(sum，avg，max等)进行聚合的查询.在SELECT子句中，并且无法将未聚合的字段放在GROUP BY子句中.这种行为是所有其他RDBMS不可或缺的部分，而MySQL最终也加入了进来.

A change was made in version 5.7-ish where it will now, by default, reject queries in which you aggregate using a function (sum, avg, max, etc.) in the SELECT clause and fail to put the non-aggregated fields in the GROUP BY clause. This behavior is part and parcel to every other RDBMS and MySQL is finally jumping on board.

您有两个选择:

1. 您可以将MySQL设置更改为默认的旧行为，以允许像这样不太好的查询.可以在此处
找到信息
2. 您可以解决您的查询

1. You can change the MySQL settings to default to the old behavior to allow not-so-great queries like this. Information can be found here
2. You can fix your query

选项2类似于:

SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email GROUP BY id, password LIMIT 1

这篇关于没有GROUP BY的汇总查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！