php 没有 GROUP BY 的聚合查询

Aggregated query without GROUP BY

提问by Rust

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:

这个查询在我的旧机器上似乎很完美。但是，在我装有 MySQL 5.7.14 和 PHP 5.6.25 的新机器上，它似乎抛出了一个错误：

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

致命错误：未捕获的异常“PDOException”，消息为“SQLSTATE[42000]：语法错误或访问冲突：1140 在没有 GROUP BY 的聚合查询中，SELECT 列表的表达式 #1 包含非聚合列“pixel_perfect.users.id”；这与 C:\wamp64\www 中的 sql_mode=only_full_group_by' 不兼容

Here is what my query looks like:

这是我的查询的样子：

$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.

为什么我现在收到这个错误，我该怎么做才能轻松解决它。

回答by JNevill

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

一个变化是在5.7版本十岁上下做它现在，默认情况下，拒绝它你可以将使用功能（查询sum，avg，max中等等）SELECT条款，不能把非聚合领域中的GROUP BY条款。这种行为是所有其他 RDBMS 的重要组成部分，MySQL 终于加入了。

You have two options:

您有两个选择：

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

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

Option 2 would look something like:

选项 2 看起来像：

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

回答by ahcyT

Its a little late but I just ran into this error.

有点晚了，但我刚刚遇到了这个错误。

This command might be useful for anyone else who runs into the same error

此命令可能对遇到相同错误的其他人有用

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

More information about this can be found at Table Plusand other links quoted above by JNevill.

更多关于这方面的信息可以在Table Plus和JNevill上面引用的其他链接中找到。

Hope it helps someone else.

希望它可以帮助别人。

回答by Taurean Wooley

Easiest answer, in config/database.php make sure to set strict => true to strict => false for the mysql settings.

最简单的答案，在 config/database.php 中确保将 mysql 设置的 strict => true 设置为 strict => false。

This will allow for less than strict queries at a cost of security for normal well-formed sql calls (still not 100% secure), but will allow for the use of other sql calls that could be in-secure if written improperly.

这将允许不严格的查询，以牺牲正常格式良好的 sql 调用的安全性为代价（仍然不是 100% 安全），但将允许使用其他可能不安全的 sql 调用，如果编写不当。

回答by Rajeshwar Dhayalan

Change ur SQL mode to default.. it will execute without error The SQL mode defines the syntax of the query. If you using ONLY_FULLY_GROUPBY you have to write a query with group by for all aggregator functions

将您的 SQL 模式更改为默认值.. 它将执行而不会出错 SQL 模式定义了查询的语法。如果您使用 ONLY_FULLY_GROUPBY，则必须为所有聚合器函数编写带有 group by 的查询