only_full_group_by模式的好处是什么? [英] What are the benefits of only_full_group_by mode?
问题描述
我更新了mysql，然后从MySQL版本5.6.17转到版本5.7.14
I updated mysql and I went from MySQL Version 5.6.17 to version 5.7.14
由于我的SQL查询出错
Since I have errors on my sql queries
实际上，我的许多查询如下:
Indeed, many of my queries look like this:
SELECT count (id) as nbr, lic from prep WHERE key = '18'
我有这个错误:
1140-在没有GROUP BY的聚合查询中，SELECT列表的表达式#2包含非聚合列'operator.preparation.orig_lic'； 这与sql_mode = only_full_group_by
1140 - In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'operator.preparation.orig_lic'; this is incompatible with sql_mode=only_full_group_by
经过研究，我了解到Mysql 5.7.14默认情况下会激活ONLY_FULL_GROUP_BY
After some research, I learn that Mysql 5.7.14 activates ONLY_FULL_GROUP_BY by default
为什么默认启用它?
最佳的解决方案(性能)是什么?禁用ONLY_FULL_GROUP_BY或在查询中添加分组依据"?
What is the best solution (for performance)? Disable ONLY_FULL_GROUP_BY or add a 'group by' on my query?
谢谢
推荐答案
最佳"解决方案是做正确的事情，并通过添加group by来修复查询，而不是覆盖抛出的错误.如果用ONLY_FULL_GROUP_BY覆盖该错误，则您遇到的错误将消失，但是这样做可能会导致两个新错误:
The "best" solution is to do the correct thing and fix your query by adding a group by, rather than override the error being thrown. If you override the error with ONLY_FULL_GROUP_BY the error your experiencing will go away but you'll likely experience two new errors as a result of doing so:
-
包含非聚合值的聚合值的意外结果，这是您的错误试图避免的问题.
Unexpected results of including an aggregated value with non-aggregated values, the problem your error is trying to prevent.
无法在其他环境上执行查询.如果您需要切换设置或将代码提供给不使用此数据库的其他人，查询将再次引发错误.如果您习惯于改写错误或其他错误，则您的代码可能对其他人不可用，并严重削弱了代码的用处.
Inability to execute your query on other environments. If you ever need to switch settings or give your code to someone else not using this database, the query will throw the error again. If you get into a habit of overriding the error or other errors, your code could become unusable to others and severely cripple the usefulness of it.
通常，如果收到错误，请修复它，而不仅仅是告诉编译器/优化器忽略它.
In general, if you are receiving an error, fix it rather than just telling the compiler/optimizer to ignore it.
这篇关于only_full_group_by模式的好处是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
