When MySQL grouping, encounter ONLY_FULL_GROUP_BY error report problem and solution

When MySQL grouping, I encountered an ONLY_FULL_GROUP_BY error problem

1. The root cause of ONLY_FULL_GROUP_BY error

MySQL 5.7 and above are enabled by defaultsql_mode=only_full_group_byStrict mode.

This mode mandates:

Non-aggregated fields in SELECTMust appear inGROUP BYin clause;
All non-aggregated fieldsNeed to pass an aggregate function (e.g.MAX、MIN、SUM) processing, or explicitly declare the grouping basis.

Trigger scenario example：

SELECT name, age, SUM(sales) FROM orders GROUP BY name;

likeageNot appearing inGROUP BYMySQL cannot determine how to be the same without using the aggregate functionnameThe differenceageThe value returns the result, resulting in an error.

2. Solution

1. Adjust SQL statements

Scheme 1: Add missing fields to GROUP BY

Add non-aggregated fields in all SELECTs to group conditions:

SELECT name, age, SUM(sales) FROM orders GROUP BY name, age;

Applicable scenarios: It is necessary to group accurately by multiple fields, but it may lead to an increase in the grouping dimension and affect performance.

Solution 2: Use an aggregate function to wrap non-grouped fields

passMAX()、MIN()orANY_VALUE()Processing fields:

SELECT name, MAX(age) AS latest_age, SUM(sales) FROM orders GROUP BY name;

ANY_VALUE(age)A value is randomly selected from the grouping, suitable for scenarios where exact values are not required.

Scheme 3: Use subquery or temporary tables

Split the complex logic into subqueries and process them in steps:

WITH grouped_data AS (  
  SELECT name, SUM(sales) AS total_sales  
  FROM orders  
  GROUP BY name  
)  
SELECT , , g.total_sales  
FROM grouped_data g  
JOIN orders o ON  = ;

2. Temporarily or permanently close ONLY_FULL_GROUP_BY

Temporarily disabled (session level)：

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE...';  -- Remove ONLY_FULL_GROUP_BY

Permanently banned(Configuration file needs to be modified):

[mysqld]  
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE...  -- Remove ONLY_FULL_GROUP_BY

risk: May cause unpredictable query results and are only recommended for use in test environments.

3. Use the ANY_VALUE() function (recommended)

Starting with MySQL 5.7.5, availableANY_VALUE()Explicit suppression errors:

SELECT name, ANY_VALUE(age), SUM(sales) FROM orders GROUP BY name;

This function returns any value from the grouping and is suitable for business scenarios where exact values are not required.

3. Suggestions to avoid problems

Follow strict model：

EnableONLY_FULL_GROUP_BYIt can improve data accuracy and avoid unpredictable query results.
Developers are mandatory to write SQL to the specification to ensure that all non-aggregated fields are handled explicitly.

Optimize query design：

AvoidSELECTIntroduce unnecessary fields to reduce ambiguity.
Preferential use of aggregate functions or subqueries to process complex logic.

Index optimization：

forGROUP BYAdd indexes to the fields and association conditions involved to improve performance.

Code review and testing：

Check in code reviewGROUP BYThe normativeness of the statement.
Enable strict mode in the test environment to expose problems in advance.

Summarize

The fundamental contradiction：ONLY_FULL_GROUP_BYThe pattern ensures data consistency through strictness, but developers need to follow SQL standards.

Best Practices：

Preferentially solve problems by adjusting SQL statements (such as aggregate functions, subqueries);
Strict mode is only temporarily disabled when necessary, and be used with caution in the production environment;
useANY_VALUE()As a supplement to flexibility, it is necessary to evaluate the accuracy requirements of the business scenario.

By the above approaches, a balance between compatibility, performance and data accuracy can be achieved.

The above is personal experience. I hope you can give you a reference and I hope you can support me more.