MySQL 语法错误或访问冲突：SELECT 列表的 1055 表达式 #8 不在 GROUP BY 子句中并且包含非聚合列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36228836/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Syntax error or access violation: 1055 Expression #8 of SELECT list is not in GROUP BY clause and contains nonaggregated column
提问by weristsam
i tried the CakePHP 3.x "Bookmaker Tutorial" and i followed the instruction step by step. Unfortunately, at the end of the first chapter i get the attached error:
我尝试了 CakePHP 3.x “Bookmaker Tutorial”，我一步一步地按照说明进行操作。不幸的是，在第一章结束时，我收到了附加的错误：
Error: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #8 of SELECT list
is not in GROUP BY clause and contains nonaggregated column 'wis.Tags.id' which is not
functionally dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by
Furthermore, i get the information to check my "BookmarksTags" table but i do not have to creat one before. I little bit confused.
此外，我获得了检查我的“BookmarksTags”表的信息，但我之前不必创建一个。我有点困惑。
Please try correcting the issue for the following table aliases:
BookmarksTags
I already google my problem and i found information to update the "my.cnf" with a extra line. i already try but nothing happed. I also check the spelling and downloaded the "bookmarker-tutorial" from github but i still get this error above.
我已经用谷歌搜索了我的问题，我找到了用额外行更新“my.cnf”的信息。我已经尝试过，但什么也没发生。我还检查了拼写并从 github 下载了“书签教程”，但我仍然收到上述错误。
I use MySQL 5.7.11 and PHP 5.6.14.
我使用 MySQL 5.7.11 和 PHP 5.6.14。
回答by tadman
This is a new thing in MySQL 5.7 and is a warning that your query is ambiguous.
这是 MySQL 5.7 中的一个新东西，它警告您的查询不明确。
Consider the following table:
考虑下表：
id | name | age | points
--------------------------------------------
1 Bob 21 1
2 James 14 1
3 Bob 21 3
4 James 14 2
5 Casey 17 3
If you did the following query:
如果您进行了以下查询：
SELECT name, age, SUM(points) FROM scores GROUP BY name
Then the namecolumn is used for grouping. Note that agemay have multiple values, so it's "non-aggregated". You need to do something to collapse down those values.
然后该name列用于分组。请注意，它age可能有多个值，因此它是“非聚合的”。你需要做一些事情来折叠这些值。
The behaviour in 5.6 and previous was to just pick the first one depending on sort order, though this was sometimes unpredictable and would fail. In 5.7 they're preventing you from doing it in the first place.
5.6 及更早版本中的行为只是根据排序顺序选择第一个，尽管这有时是不可预测的并且会失败。在 5.7 中，他们首先阻止您这样做。
The solution here is to group on that as well, or to apply an aggregate operator like MIN()to it.
这里的解决方案是对其进行分组，或者对其应用聚合运算符MIN()。
回答by Fendi Setiawan
I'm using Laravel 5.4 and facing same problem.
I try to set strictinto falseon config/database.phpand it works.
我正在使用 Laravel 5.4 并面临同样的问题。
我尝试设置strict为falseonconfig/database.php并且它有效。
'connections' => [
'mysql' => [
'strict' => false,
]
]
However it's better to edit sql query than suppress it's warning.
然而，最好是编辑 sql 查询而不是抑制它的警告。
回答by Jaya Parwani
This may be the issue related to newer version of MySQL as you said you are using MySQL 5.7.11 So to solve this issue for all your sites see this
这可能是与较新版本的 MySQL 相关的问题，因为您说您使用的是 MySQL 5.7.11 所以要为您的所有站点解决此问题，请参阅此内容
the answer by community to add the line below
社区的答案添加下面的行
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
in /etc/mysql/my.cnf file & saving it; and after restarting the server
在 /etc/mysql/my.cnf 文件中并保存它；并在重新启动服务器后
sudo service mysql restart
Would do the work
会做这项工作
回答by Remka
Just stumbled on the same problem.
A simple way to make the tutorial "work" without changing the query could be to reset sql_modein config/app.php:
只是偶然发现了同样的问题。
一个简单的方法，使教程“工作”，而不更改查询可以重置sql_mode在config/app.php：
'Datasources' => [
'default' => [
'className' => 'Cake\Database\Connection',
'driver' => 'Cake\Database\Driver\Mysql',
'persistent' => false,
'host' => 'localhost',
...
'init' => ["SET sql_mode = ''"],
],
While this should makes things work, it is probably not recommended in real life cases.
虽然这应该可以使事情奏效，但在现实生活中可能不推荐这样做。
回答by Tahi Reu
In addition to tadman's comment, for tutorial you are following, you should distinct your sql select query by both Bookmarks.id and BookmarksTags.tag_id, instead of just Bookmarks.id
除了 tadman 的评论，对于您正在关注的教程，您应该通过 Bookmarks.id 和 BookmarksTags.tag_id 来区分 sql select 查询，而不仅仅是 Bookmarks.id
To do that, in BookmarksTable.php file, line
为此，在 BookmarksTable.php 文件中，行
->distinct(['Bookmarks.id'])
should look like
应该看起来像
->distinct(['Bookmarks.id', 'BookmarksTags.tag_id'])
