[Solved] MYSQL Command gruop by Error: this is incompatible with sql_mode=only_full_group_by

Error Messages:
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘student.name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Check the version of MYSQL

select version();

View the Content in SQL_mode

select @@GLOBAL.sql_mode;

Reason:

The only_full_group_by option is enabled by default in mysql5.7 and above. My version is mysql5.7.27

Take a look at the syntax of group by:

select select the column in the group + aggregate function from the table name group by grouped column

From the perspective of grammatical format, the grouping is first established, and then the columns to be retrieved are determined. The columns to be retrieved can only be selected from the columns participating in the grouping.

My current Mysql version 5.7.27,

Let’s look at the meaning of ONLY_FULL_GROUP_BY: For the GROUP BY aggregation operation, if the column in the SELECT does not appear in the GROUP BY, then this SQL is illegal, because the column is not in the GROUP BY clause, that is to say, it is detected List

It must appear after the group by, otherwise an error will be reported, or this field appears in the aggregate function.

 

There are two solutions. The first is to temporarily solve the problem, which will reappear when the database is restarted, and the second is to modify the MySQL configuration file

Windows solution:

Method 1:
Delete ONLY_FULL_GROUP_BY in the queried sql_mode, it will be invalid after restart

set @@GLOBAL.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Method 2:
find your MySQL installation path first

select @@basedir;

Then go to the folder and find your my Ini configuration file, modify the content in [mysqld], add the following content, then exit the database and restart MySQL in the service

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Linux solution:

Method 1:
Delete ONLY_FULL_GROUP_BY in the queried sql_mode, it will be invalid after restart

set @@GLOBAL.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Method 2:
The configuration file of mysql on linux is not called my.ini, the configuration file is in /etc/my.cnf

vim /etc/my.cnf

Modify the content in [mysqld], add the following content, and then restart mysql

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Restart command:

service mysql restart

Similar Posts: