mysql错误:[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated
今天迁移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)
最新文章
- 【Windows编程】系列第七篇:Menubar的创建和使用
- atitit 英文与中文与阿拉伯文的简化解决方案.docx
- Servlet Filter
- POJ2743Mobile Computing[DFS 状态压缩]
- Android任务和返回栈完全解析,细数那些你所不知道的细节
- zepto源码--核心方法6(显示隐藏)--学习笔记
- IOS 7 Study - UIDatePicker
- 关于 error: LNK1123: failure during conversion to COFF: file invalid or corrupt 错误的解决方案【Qt】【 VS2010】
- Filter 配置多个URL-PATTERN
- 道格拉斯—普克(Douglas一Peukcer)节点抽稀算法
- 2048 Puzzle游戏攻略
- ubuntu16.04+eigen3安装
- Error: Your project contains C++ files but it is not using a supported native build system
- JVM初探- 使用堆外内存减少Full GC
- python day06
- ubutu16.04 安装Tenda u12无线网卡驱动
- SUID、SGID详解
- 开源的许可证GPL、LGPL、BSD、Apache 2.0
- POST请求测试地址
- 第14章:MongoDB-聚合操作--聚合管道
热门文章
- jmeter3.x的jtx文件解析
- Linux 线程浅析
- 《linux 内核全然剖析》 mktime.c
- Solidworks如何圆周阵列
- &;lt;&;lt;Python基础教程&;gt;&;gt;学习笔记 | 第04章 | 字典
- {}在javascript与(python,java)中的含义区别
- 如何让<;input type=";text"; />;中的文字居中
- HTML字体对应word字体
- Android NDK开发常见错误
- 敏捷DoD完毕定义的多种形态