MySQL solves only_ FULL_ GROUP_ Several methods of by error

Time:2020-12-7

Link to the original text:He Xiaodong’s blog

In MySQL version 5.7 or aboveORDER BYperhapsGROUP 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 didGROUP BYandORDER BYYou need to be assuredSELECTAre all in theGROUP BYandORDER BYHowever, the actual demand is not so simple.

Temporary modification only_ FULL_ GROUP_ By mode

Find out the existing patterns


select @@global.sql_mode;

Get rid of the insideONLY_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

findmy.iniFile, modifysql_modevalue

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.

For example:


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 ofANY_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. AndSUM()orCOUNT()Different functions like this,ANY_VALUE()Is not an aggregate function. It only serves to suppress uncertainty testing.

Reference link:MySQL Handling of GROUP BY

Last but not leastAlicloud’s full range of products / SMS packages special purchase the best choice for small and medium-sized enterprises to go to the cloud and Alibaba cloud internal coupons

Recommended Today

Swift advanced (XV) extension

The extension in swift is somewhat similar to the category in OC Extension can beenumeration、structural morphology、class、agreementAdd new features□ you can add methods, calculation attributes, subscripts, (convenient) initializers, nested types, protocols, etc What extensions can’t do:□ original functions cannot be overwritten□ you cannot add storage attributes or add attribute observers to existing attributes□ cannot add parent […]