Geek Answers Handbook

Check mysql GROUP BY

Suppose I have a table with the following contents:

mysql> select * from test; +----+------+ | id | val | +----+------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | | 5 | 2 | | 6 | 2 | | 7 | 2 | | 8 | 2 | +----+------+ 8 rows in set (0.00 sec) mysql>

Now I run an erroneous SQL query with a group by clause and without any aggregation in the id column and get the wrong results:

 mysql> select id, val from test group by val; +----+------+ | id | val | +----+------+ | 1 | 1 | | 5 | 2 | +----+------+ 2 rows in set (0.00 sec) mysql>

Can the mysql client or perhaps some other tool check this request and give an error or warning when using group by without aggregation?

+4
source share
2 answers

Yes you can do this:

To disable the MySQL GROUP BY extension, enable ONLY_FULL_GROUP_BY SQL.

 mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';

See the documentation here . In addition, this section on server modules can help.

+7
source

By default, SQL ONLY_FULL_GROUP_BY mode is disabled. And precisely for this reason, your request was executed without any exceptions. If you do not want this behavior to ONLY_FULL_GROUP_BY mode,

SQL mode setting

  • You can set the default SQL mode by running mysqld with the -sql-mode = "mode" parameter or using the sql-mode = "mode in my.cnf (Unix operating systems) or my.ini (Windows). Mode is a list of different modes separated by commas (",").
  • You can change the SQL mode at run time by using the SET [GLOBAL|SESSION] sql_mode='modes' statement to set the system sql_mode value. ex:
    SET sql_mode = 'ONLY_FULL_GROUP_BY'
+1
source

More articles:


All Articles