MYSQL gruop by Error: this is incompatible with sql_mode=only_full_group_by

The following error is reported when searching or inserting data in the mysql tool:

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

the reason:

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, and then the columns to be retrieved are determined. The columns to be retrieved can only be selected among the columns participating in the grouping.

My current Mysql version 5.7.17,

Look again at ONLY_FULL_GROUP_BY means: 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 The column must appear after the group by, otherwise an error will be reported, or this field appears in the aggregate function.

View the mysql version command: select version();

View the sql_model parameter command:

SELECT @@GLOBAL.sql_mode;

SELECT @@SESSION.sql_mode;

Find:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The first item turns ONLY_FULL_GROUP_BY by default,

Solution:

1. Select only the columns that appear behind the group by, or add aggregate functions to the columns; (not recommended)

2. Command line input:

set @@GLOBAL.sql_mode=”;

set sql_mode =’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

Turn off ONLY_FULL_GROUP_BY by default!

Use the tool to select at this time

SELECT @@sql_mode;

SELECT @@GLOBAL.sql_mode;

Found that ONLY_FULL_GROUP_BY does not exist anymore, it feels OK. But if you restart the Mysql service, you will find that ONLY_FULL_GROUP_BY will still exist

If you want to completely solve this problem, you have to change the my.ini configuration (if your mysql does not have this file, change my-default.ini to my.ini, my version does not have the my.ini configuration problem)

Add under [mysqld] and [mysql]

sql_mode =’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

ProgrammerAH

Programmer Guide, Tips and Tutorial

MYSQL gruop by Error: this is incompatible with sql_mode=only_full_group_by

Read More:

Leave a Reply Cancel reply