Link to the original text:He Xiaodong’s blog
In MySQL version 5.7 or above
GROUP BYThe following error occurs
[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_byThe constraint of MySQL is: you did
ORDER BYYou need to be assured
SELECTAre all in the
ORDER BYHowever, the actual demand is not so simple.
Temporary modification only_ FULL_ GROUP_ By mode
Find out the existing patterns
Get rid of the inside
ONLY_FULL_GROUP_BYMode, then set back
set @@ global.sql_ Mode = the found value, remove only_ FULL_ GROUP_ BY "
Modify only in the configuration file_ FULL_ GROUP_ By mode
sql_ Mode = remove only from the found value_ FULL_ GROUP_ BY "
With the help of and_ The value() function ignores columns that are not participating in the grouping
have access to
ANY_VALUE()References non aggregated columns without having to disable them
ONLY_FULL_GROUP_BYThe same effect can be obtained.
mysql> SELECT name, MAX(age) FROM t; ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mydb.t.name'; this is incompatible with sql_mode=only_full_group_by
With the help of
ANY_VALUE()Change it to this:
SELECT ANY_VALUE(name), MAX(age) FROM t;
The official documentation tip: this can be useful if you don’t care at all which value to choose for each group for the non aggregated column. And
COUNT()Different functions like this,
ANY_VALUE()Is not an aggregate function. It only serves to suppress uncertainty testing.