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 id
s but both have name=ali
.
Solution 1:
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 Solution 3. (Not solution 2!)
Solution 2:
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)
Solution 3
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;
It will choose the ali
row which has the maximum id:
2 john
3 ali
Side Note: Please notice MySQL does not care what data you have in your table. It's the query itself that is ambiguous. So for example you may have even no records in the table and still see the only_full_group_by error.