MySQL error -this is incompatible with sql_mode=only_full_group_by Perfect solution

Project scenario ：
occasionally , Database duplicate data encountered , Data needs to be grouped , And take out one of them to show , It needs to be used group by sentence .
however , If mysql It's a high version , When executed group by when ,select The field of does not belong to group by Of the ,sql The statement will report an error . The error information is as follows ：

Expression #1 of SELECT list is not in GROUP BY clause and contains
nonaggregated column ‘ Database name . Table name . Field name ’ which is not functionally dependent
on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by

Problem description ：
1. Table structure

CREATE TABLE `t_iov_help_feedback` (
`ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT ' Primary key ID',
`USER_ID` INT(255) DEFAULT NULL COMMENT ' user ID',
`problems` VARCHAR(255) DEFAULT NULL COMMENT ' Problem description ',
`last_updated_date` DATETIME DEFAULT NULL COMMENT ' Last update time ',
PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

2. Table data

3.sql sentence
1） Inquire about group by Field of （ normal ）

SELECT USER_ID FROM t_iov_help_feedback GROUP BY USER_ID;

SELECT MAX(ID),USER_ID FROM t_iov_help_feedback GROUP BY USER_ID;

2） Check not group by Field of （ Report errors ）

What do you mean by reporting an error ？
One sentence summary ：“ Error code 1055 And sql_mode = only_full_group_by Are not compatible ”
translate ：

“ Error code ：1055.SELECT Expression for list ＃1 be not in GROUP
BY clause , And contains non aggregate Columns ’test.t_iov_help_feedback.ID’, It is functionally independent of GROUP BY Column in clause ;
This is related to sql_mode = only_full_group_by Are not compatible ”

Cause analysis ：

One 、 Principle level This mistake happened in mysql 5.7.5 Problems with version and above ： mysql
5.7.5 Version above default sql Configuration is :sql_mode=“ONLY_FULL_GROUP_BY”, This configuration is strictly enforced "SQL92 standard ". A lot from 5.6 Upgrade to 5.7 when , For grammatical compatibility , Most will choose to adjust sql_mode, To keep up with 5.6 Agreement , To be as compatible as possible with the program .

Two 、sql level stay sql Execution time , The reason for this , In a nutshell ： Because it's on ONLY_FULL_GROUP_BY Set up , If select
The field of is not in group by in , also select
The field of does not use the aggregate function （SUM,AVG,MAX,MIN etc. ） Words , So this one sql The inquiry is made by mysql Considered illegal , Report error …

Verify that this reason ：

1. Query the database version

SELECT VERSION();

You can see , My database version here is ：8.0.16, Greater than 5.7.5 了

2. see sql_mode The sentence of

select @@GLOBAL.sql_mode;

The value of the query is ：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

You can see ,sql_mode Open the only_full_group_by attribute

Solution ：

Solution 1 ： Using functions ANY_VALUE() Contains error reporting fields

Change the above error reporting statement to ：

SELECT ANY_VALUE(ID),USER_ID,ANY_VALUE(problems),ANY_VALUE(last_updated_date) FROM t_iov_help_feedback GROUP BY USER_ID;

You can see , The results can be queried normally , Change the alias of the query field as needed .

ANY_VALUE() Function description ：

MySQL Yes any_value(field) function , Its main function is to inhibit ONLY_FULL_GROUP_BY Value rejected .
such sql Whether it's in ONLY_FULL_GROUP_BY Whether the mode is off or on, it can be executed normally , Don't be mysql Refuse .
any_value() The specified column value of the first data in the data divided into the same group will be selected as the return data .

There is an official introduction , Address ：https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

Solution 2 ： adopt sql The statement is temporarily modified sql_mode

Get rid of ONLY_FULL_GROUP_BY, Reset value

SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Above is the change of the overall situation sql_mode, Valid for new database . For existing databases , You need to execute... Under the corresponding database ：

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

problem ：

 restart mysql After the database service ,ONLY_FULL_GROUP_BY There will be , So this is only temporary .

Solution three ： Permanently modify through the configuration file sql_mode

mysql Installed on the server and installed locally , The way to modify the configuration file is a little different .

1、Linux Next, modify the configuration file

1） Log in MySQL
Use command mysql -u username -p Log in , Then enter the password , Input SQL：

show variables like ‘%sql_mode’;

2） edit my.cnf file

The file address is usually in ：/etc/my.cnf,/etc/mysql/my.cnf
Use vim Command edit file , I do not know! vim How to use the command , You can refer to my other article ：Linux Use in vi Tool for text editing
find sql-mode The location of , Get rid of ONLY_FULL_GROUP_BY
And then restart MySQL;
yes , we have my.cnf There may not be sql-mode, Need to add ：

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Be careful to add [mysqld] below , If you join other places , The restart does not take effect , The details are as follows ：

3） Restart after successful modification MySQL service

service mysql restart

After restart , Login again mysql, Input SQL：show variables like ‘%sql_mode’; without ONLY_FULL_GROUP_BY, It means it has been successful .

 If not , So just keep STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION that will do

The additional content is ：

sql-mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

2、window Next, modify the configuration file

1） find mysql The installation directory , Open it directly in Notepad my.ini file

2） edit my.cnf file , stay [mysql] Additional content under the label

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1

3） restart mysql service

remarks ：
Some on the Internet offer sql_mode Value , But it leads to restart mysql The service failed to start

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

At this time , Only need to sql_mode In the value “NO_AUTO_CREATE_USER” This attribute can be removed .

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Link to the original text ：https://blog.csdn.net/u012660464/article/details/113977173