Summary of the use of ONLY_FULL_GROUP

In MySQL database management,ONLY_FULL_GROUP_BYis an important SQL pattern that directly affectsGROUP BYHow and result of statement execution. This article will start from the basic concept and analyze it step by stepONLY_FULL_GROUP_BYworking principle, application scenario and response strategy.

What is ONLY_FULL_GROUP_BY?

ONLY_FULL_GROUP_BYis an SQL pattern that requires useGROUP BYWhen a statement,SELECTList,HAVINGCondition orORDER BYThe columns in the clause must be part of the aggregate function (e.g.SUM(), COUNT()etc.) orGROUP BYThe column explicitly specified in the clause. This requirement ensuresGROUP BYThe results of the operation have clear semantics, i.e. the non-aggregated column values within each group are logically unique, or processed by an aggregation function to reduce ambiguity.

Why do I need ONLY_FULL_GROUP_BY?

Not enabledONLY_FULL_GROUP_BYIn the case of mode, MySQL allowsGROUP BYThe clause contains unaggregated non-grouped fields, which can lead to uncertain results. For example, consider the following query:

SELECT customer_id, product_id, SUM(quantity * price) AS total_amount
FROM orders
GROUP BY customer_id;

In this query,product_idNot included inGROUP BYIn the clause, no aggregate function is used, so its value will be uncertain, which may lead to inconsistency in the query results.

How ONLY_FULL_GROUP_BY works

When enabledONLY_FULL_GROUP_BYWhen in mode, MySQL checks eachGROUP BYQuery, make sure:

SELECTEach column in the list is either inGROUP BYIn clauses, they are either included in the aggregate function (e.g.SUM(), AVG(), MAX(), MIN(), COUNT()wait).
HAVINGEach column in the clause also needs to meet the above conditions.
ORDER BYAlthough the columns in the clause do not need to be directly involvedGROUP BYbut if they are not aggregate columns, their values will be based onGROUP BYThe first or random row in the result set (which depends on the internal implementation of MySQL), which can lead to uncertain results.

Handling the impact of ONLY_FULL_GROUP_BY

Identify the GROUP BY clause

The most direct way to deal with it isGROUP BYAll non-aggregated columns are explicitly specified in the clause. This way, even if enabledONLY_FULL_GROUP_BYMode, query can also be executed normally.

SELECT a, MAX(b), c FROM table GROUP BY a, c;

Using aggregation functions

Another approach is to use an aggregation function for non-aggregated columns to ensure consistency in the query results.

SELECT customer_id, ANY_VALUE(product_id), SUM(quantity * price) AS total_amount
FROM orders
GROUP BY customer_id;

In this query,ANY_VALUE(product_id)Select an arbitrary product ID from each customer's order, andSUM(quantity * price)Then calculate the total order amount for each customer.

Disable ONLY_FULL_GROUP_BY

If temporary or permanent disable is requiredONLY_FULL_GROUP_BYMode can be implemented by modifying SQL mode.

Temporary settings (session level)：

SET SESSION sql_mode='ONLY_FULL_GROUP_BY';

Or disable:

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Permanent settings (global level)：

In MySQL configuration files (e.g.or) Set in:

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Through understandingONLY_FULL_GROUP_BYHow it works and follows best practices, you can write SQL queries that are both efficient and reliable to better manage and analyze your data.

This is the article about the use of ONLY_FULL_GROUP_BY in MySQL. For more related contents of MySQL ONLY_FULL_GROUP_BY, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!