mysql錯誤：[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated

標籤：ini   mys   gre   turn   rate   ons   rri   find   sam   

今天遷移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)

mysql錯誤：[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated