How to Disable the ONLY_FULL_GROUP_BY Mode in MySQL

Last updated December 6th, 2023 00:18

MySQL is a popular relational database management system widely used in the field of IT. MySQL offers the ability to work with data using the SQL language, which is used for data manipulation and various operations. These operations include data insertion, updating, reading, and deletion. In this article, we will explore how to disable the ONLY_FULL_GROUP_BY mode in MySQL. This can be achieved by using a command in the connection script.

How to Disable the ONLY_FULL_GROUP_BY Mode in MySQL

The ONLY_FULL_GROUP_BY mode in MySQL affects the behavior of SQL queries. To understand what this mode does, let’s first familiarize ourselves with the concept of “GROUP BY” in conjunction with aggregate functions such as “SUM,” “COUNT,” or “AVG.”

By utilizing the GROUP BY clause, we are able to categorize the rows in the query results according to the value of a particular column. This enables us to subsequently execute aggregate functions on these grouped data. An illustrative scenario would involve a table named “Employees,” containing pertinent details about employees, including their respective salaries. In this case, the application of GROUP BY facilitates the computation of the total salary for each department.


This is where the ONLY_FULL_GROUP_BY mode comes into play. When this mode is enabled, MySQL requires that all columns in the SELECT statement either be part of the GROUP BY clause. Alternatively, they should be part of an aggregate function. This means we cannot select columns that are not in the GROUP BY clause and are not part of any aggregate function.

The reason for enabling this mode is to ensure data consistency. Without it, we could unintentionally obtain incorrect results. For example, if we select a column that is not part of the GROUP BY clause, MySQL could return a random value from the given group of rows. This can happen when the column is also not included in any aggregate function. ONLY_FULL_GROUP_BY provides greater control over query results and protects us from potential inaccuracies.

How to Disable the ONLY_FULL_GROUP_BY Mode in MySQL Using a Connection Script

If we want to disable the SQL mode ONLY_FULL_GROUP_BY, we can utilize a connection script and add a line to the connection definition. The command looks as follows:

					mysqli_query($connect, "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))");

Overall, the connection script can look like this:

					$connect = mysqli_connect( "server", "login", "password", "database" );
mysqli_query($connect, "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))" );

This command will replace the current SQL mode setting and aremove ONLY_FULL_GROUP_BY from it. This allows executing queries that do not comply with this restriction. However, you must be aware that disabling this mode can result in a loss of query result accuracy. Therefore, it is important to consider the consequences of disabling this mode. It is also crucial to thoroughly test your queries and application before actually disabling ONLY_FULL_GROUP_BY.


In conclusion, the SQL mode ONLY_FULL_GROUP_BY is an important feature in MySQL that helps maintain data consistency and protects against potential errors in query results. Disabling this feature should always be carefully considered and tested.

The website is created with care for the included information. I strive to provide high-quality and useful content that helps or inspires others. If you are satisfied with my work and would like to support me, you can do so through simple options.

Byl pro Vás tento článek užitečný?

Klikni na počet hvězd pro hlasování.

Průměrné hodnocení. 0 / 5. Počet hlasování: 0

Zatím nehodnoceno! Buďte první

Jak užitečný vidíte tento článek.

Sledujte mě na sociálních médiích.

Je mi líto, že pro Vás nebyl článek užitečný.

Jak mohu vylepšit článek?

Řekněte mi, jak jej mohu zlepšit.


Subscribe to the Newsletter

Stay informed! Join our newsletter subscription and be the first to receive the latest information directly to your email inbox. Follow updates, exclusive events, and inspiring content, all delivered straight to your email.

Upozornit na
0 Komentáře/ů
Vložené zpětné vazby.
Zobrazit všechny komentáře.

Pokud mi chcete napsat rychlou zprávu, využije, prosím, níže uvedený
kontaktní formulář. Děkuji.

Další Kontaktní údaje