MySQL Error number: 3087; Symbol: ER_WRONG_FIELD_WITH_GROUP_V2; SQLSTATE: HY000 报错 故障修复 远程处理

文档解释

Error number: 3087; Symbol: ER_WRONG_FIELD_WITH_GROUP_V2; SQLSTATE: HY000

Message: Expression #%u of %s is not in GROUP BY clause and contains nonaggregated column ‘%s’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Error 3087 (ER_WRONG_FIELD_WITH_GROUP_V2) is a MySQL database server error which indicates that a column cannot be used in a GROUP BY or any other kind of grouping operation because it is not part of the GROUP BY list. In such cases, the database server will generate a query execution error and stop the query from executing further.

Error 3087 (ER_WRONG_FIELD_WITH_GROUP_V2): Explanation and Common Usage

The error occurs when the database server detects an attempt to include a column in a GROUP BY clause, but that column is not specified in the GROUP BY clause. It usually occurs when a programmer attempts to group data based solely on a column which cannot be used in this manner. For example, a SELECT statement which includes a column without specifying it in the GROUP BY clause:

SELECT name, count(*) FROM persons WHERE age > 16 GROUP BY name;

In the above example, a GROUP BY clause has been specified, however the “name” column has not been specified in the GROUP BY clause. This will cause the database server to throw Error 3087 (ER_WRONG_FIELD_WITH_GROUP_V2).

Error 3087 (ER_WRONG_FIELD_WITH_GROUP_V2): Solutions

The solution to Error 3087 is to include the columns which are used in the GROUP BY clause in the SELECT statement. So, in the above example, the correct query should be

SELECT name, age, count(*) FROM persons WHERE age > 16 GROUP BY name, age;

This will ensure that the “name” and “age” columns are included in the GROUP BY clause, thus avoiding the error.

Additionally, the HAVING clause can also be used to filter the grouped data based on a specific condition. The HAVING clause can be used in conjunction with the GROUP BY clause to restrict the groups that are returned as part of the query. So, in the above example the query could be modified as follows:

SELECT name, age, count(*) FROM persons WHERE age > 16 GROUP BY name, age HAVING count(*) > 1;

This would return only groups which have more than one entry.

In conclusion, Error 3087 (ER_WRONG_FIELD_WITH_GROUP_V2) indicates that a column is being used as part of a grouping operation but has not been specified in the GROUP BY clause. The solution is to include the columns which are being used as part of the grouping operation in the SELECT statement and, if necessary, apply a filter with the HAVING clause. Following these steps will ensure that the query can execute with no errors.