MySQL 错误代码：1055 与 sql_mode=only_full_group_by 不兼容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36207042/
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
Error Code: 1055 incompatible with sql_mode=only_full_group_by
提问by Frydaddy07
I have been having issues switching to an offline version of the Lahman SQL baseball database. I was using a terminal embed into an EDX course. This command runs fine on the web terminal:
我在切换到 Lahman SQL 棒球数据库的离线版本时遇到了问题。我正在使用嵌入到 EDX 课程中的终端。此命令在 Web 终端上运行良好：
SELECT concat(m.nameFirst,concat(" ",m.nameLast)) as Player,
p.IPOuts/3 as IP,
p.W,p.L,p.H,p.BB,p.ER,p.SV,p.SO as K,
p.IPOuts+p.W*5+p.SV+p.SO-p.BB-p.L-p.H as PTS,
p.yearID as Year
FROM Pitching p
Inner Join Master m
ON p.playerID=m.playerID
WHERE p.yearID=2014 AND p.IPOuts>=50
GROUP BY m.playerID
ORDER BY PTS DESC;
Which is running SQL 5.5.46, but when I use my offline version running 5.7.10 I get the following error code:
它运行的是 SQL 5.5.46，但是当我使用运行 5.7.10 的离线版本时，我收到以下错误代码：
Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stats.m.nameFirst' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
错误代码：1055。SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“stats.m.nameFirst”，它在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by 不兼容
I've been reading a lot of solutions to people's problems, but they haven't helped in this case. That's never happened before, so I think this is either super obvious or maybe I'm getting ok at coding. Anyway, anyone know how to fix this?
我已经阅读了很多解决人们问题的方法，但在这种情况下它们并没有帮助。这以前从未发生过，所以我认为这要么非常明显，要么我在编码方面做得很好。无论如何，有人知道如何解决这个问题吗？
回答by White Feather
In 5.7 the sqlmode is set by default to:
在 5.7 中，sqlmode 默认设置为：
ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
To remove the clause ONLY_FULL_GROUP_BY you can do this:
要删除 ONLY_FULL_GROUP_BY 子句，您可以执行以下操作：
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
This supposed you need to make that GROUP BY with non aggregated columns.
这假设您需要使用非聚合列制作该 GROUP BY。
Regards
问候
回答by Anis
The accepted solution above didn't work for me on version 5.7.9, for osx10.9 (x86_64).
上面接受的解决方案在版本上对我不起作用5.7.9, for osx10.9 (x86_64)。
Then the following worked -
然后以下工作 -
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';
回答by phil
For other use cases: You don't necessarily have to disable ONLY_FULL_GROUP_BYGiven a case like this, According to mysql docs, "This query is invalid if name is not a primary key of t or a unique NOT NULL column. In this case, no functional dependency can be inferred and an error occurs:"
对于其他用例：您不一定要禁用ONLY_FULL_GROUP_BY鉴于这样的情况，根据 mysql 文档，“如果 name 不是 t 的主键或唯一的 NOT NULL 列，则此查询无效。在这种情况下，没有可以推断出函数依赖并发生错误：”
SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by
Instead you can use this ANY_VALUE('my_column_name') my_column_nameQuoting the mysql docs, "In this case, MySQL ignores the nondeterminism of address values within each name group and accepts the query."
Use ANY_VALUE() to refer to address:
相反，您可以使用此ANY_VALUE('my_column_name') my_column_name引用 mysql 文档，“在这种情况下，MySQL 忽略每个名称组中地址值的不确定性并接受查询。” 使用 ANY_VALUE() 来引用地址：
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
回答by Kesha Viveki
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
** your query **
This will resolve your problem.
这将解决您的问题。
回答by Anushil Kumar
You can set the variables in mysql:
您可以在 mysql 中设置变量：
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';
Remember NO_AUTO_CREATE_USER will not work with mysql 8.
请记住 NO_AUTO_CREATE_USER 不适用于 mysql 8。
If that doesn't work just do:
如果这不起作用，请执行以下操作：
mysql > set sql_mode = ''
回答by Mikku Berkin
If you do as the picked answer, the @sql_mode may be like this—
如果你按照选择的答案去做，@sql_mode 可能是这样的——
',STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'.
there's a comma in front of the 'STRICT_TRANS_TABLES' string.
'STRICT_TRANS_TABLES' 字符串前面有一个逗号。
Just execute this—
执行这个——
set @@sql_mode =
'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
it works.
有用。
Also, you can try following exp,
另外，您可以尝试以下 exp，
SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,','');
I didn't test it, but I guess it may works.
我没有测试它，但我想它可能有效。
