What are the benefits of only_full_group_by mode?

by

only_full_group_by = on tells MySQL engine: Do not apply GROUP BY when you have doubt about what results to show and throw an error. Only apply it if Group By specifically tells you what to do. i.e. when the Group By is full and perfect!

only_full_group_by = off tells MySQL engine: always apply GROUP BY and if you have doubt about what results to choose, just pick one randomly!

You don’t have to turn it off if you use GROUP BY properly!

Example:

Table: users

 id   |  name
----------------
  1      ali
  2      john
  3      ali

When you use GROUP BY on the name column:

SELECT * FROM users GROUP BY name;

There are two possible results:

  1      ali
  2      john

OR

  2      john
  3      ali

MYSQL does not know what result to choose! Because there are different ids but both have name=ali.

Solution1:

only selecting the name field:

SELECT name FROM users GROUP BY name;

result:

  ali
  john

This is a perfect solution. removing columns that makes GROUP BY confused. This means you know what you’re doing. Usually, you do not need
those columns, but if you need them, go to Solution3!

Solution2:

Turning off only_full_group_by. MYSQL will pick one of the two possible results RANDOMLY!! (It’s ok if you do not really care what id it will choose, but remember to turn it on immediately after your query to prevent unexpected behaviors in future groupBys)

Solution3

Use an Aggregate function like MIN(), MAX() to help MYSQL to decide what it must choose.

For example:

SELECT MAX(id), name FROM users GROUP BY name;

result:

  2      john     
  3      ali

It will choose the ali row which has the maximum id.

More Related Contents:

  1. INSERT INTO SELECT statement with more columns which is not in another table
  2. multiple query same table but in different columns mysql
  3. How can I make SQL case sensitive string comparison on MySQL?
  4. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  5. 1052: Column ‘id’ in field list is ambiguous
  6. How to split the name string in mysql?
  7. How to do a batch insert in MySQL
  8. Count the occurrences of DISTINCT values
  9. Why does MySQL allow “group by” queries WITHOUT aggregate functions?
  10. Cumulative sum over a set of rows in mysql
  11. How to set a maximum execution time for a mysql query?
  12. Set NOW() as Default Value for datetime datatype?
  13. Add Auto-Increment ID to existing table?
  14. MySQL SELECT DISTINCT multiple columns
  15. How to copy a row and insert in same table with a autoincrement field in MySQL?
  16. SQL DELETE with JOIN another table for WHERE condition
  17. mysql trigger stored trigger is already used by statement which invoked stored trigger
  18. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  19. Using group by on two fields and count in SQL
  20. Can MySQL convert a stored UTC time to local timezone?
  21. Whats the fastest way to lookup big tables for points within radius MySQL (latitude longitude)
  22. mysql update query with sub query
  23. What is the difference between the backtick and the square bracket in SQL statements?
  24. MySQL CONCAT returns NULL if any field contain NULL
  25. Does Order of Fields of Multi-Column Index in MySQL Matter
  26. Integrity constraint violation: 1452 Cannot add or update a child row:
  27. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  28. What does SQL Select symbol || mean?
  29. MySQL optimizing INSERT speed being slowed down because of indices
  30. What does it mean by select 1 from table?

Leave a Comment