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

时间:2023-03-09 16:21:27
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)