mysql错误:[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggr...

今天迁移django数据库的时候,跑程序的时候出现这样的错误:

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.
PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

没遇到过这样的问题,所以我把它记录下来,免得以后再次遇到。

先直接上解决办法吧,原理分析在后面,愿意看就看,不愿意这有解决办法,绝对管用。
""修改sql_mode的值,去掉ONLY_FULL_GROUP_BY""

进入你的mysql:

mysql -uxxx -pxxxxx

执行命令:

set @@global.sql_mode        ='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

这样就好了。

现在说一下原理,首先我是怎么出这个问题了呢,是我升级了自己的mysql到5.7,然后原来5.6的一些旧语法就出了这样或者那样的问题,mysql5.7.5后,ONLY_FULL_GROUP_BY 默认为真,那么此时select中的字段必须出现在group by中,所以显而易见,旧新版本的问题。

官方文档在此:
A new function, ANY_VALUE(), is available that can be used to force
MySQL to accept queries that it thinks should be rejected with
ONLY_FULL_GROUP_BY enabled.
The function return value and type are
the same as the return value and type of its argument, but the
function result is not checked for the ONLY_FULL_GROUP_BY SQL mode.
If you find that having ONLY_FULL_GROUP_BY enabled causes queries for existing applications to be rejected, either of these actions should restore operation:
If it is possible to modify an offending query, do so, either so that
nondeterministic nonaggregated columns are functionally dependent on
GROUP BY columns, or by referring to nonaggregated columns using
ANY_VALUE().
If it is not possible to modify an offending query (for example, if
it is generated by a third-party application), set the sql_mode
system variable at server startup to not enable ONLY_FULL_GROUP_BY.
For more information about SQL modes and GROUP BY queries, see Server SQL Modes, and MySQL Handling of GROUP BY. (Bug #18486310)

posted on 2018-04-10 13:59 Kilichko 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/Yemilice/p/8778471.html

、和[3]引用的内容都是关于MySQL报错1055的问题。这个错误是由于在使用ORDER BY子句时,ORDER BY子句中的列没有在GROUP BY子句中,并且包含了不是在GROUP BY子句中的列,这与sql_mode=only_full_group_by选项不兼容所导致的。换句话说,这个错误是由于使用了不合法的ORDER BY子句。 要解决这个问题,可以有以下几种方法: 1. 修改SQL查询语句,确保ORDER BY子句中的列都在GROUP BY子句中,并且不包含不在GROUP BY子句中的列。这样可以确保查询语句符合sql_mode=only_full_group_by选项的要求。 2. 修改MySQL配置文件,将sql_mode选项中的only_full_group_by选项去掉。这样可以关闭只允许全面GROUP BY的模式,但需要注意这可能会导致其他潜在的问题。 3. 升级MySQL版本到5.7.5或更高的版本。从MySQL 5.7.5开始,sql_mode=only_full_group_by成为默认设置,不再允许ORDER BY子句中的非聚合列。 请根据具体情况选择适合的解决方法来解决[Err] 1055的问题。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [MySQL异常:[Err] 1055Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nona...](https://download.csdn.net/download/weixin_38742656/13685329)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 33.333333333333336%"] - *2* [[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated...](https://blog.csdn.net/miachen520/article/details/122390084)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 33.333333333333336%"] - *3* [[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains](https://blog.csdn.net/qq_43514711/article/details/122041247)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 33.333333333333336%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值