MySQL error - this is incompatible with SQL_ mode=only_ full_ group_ By perfect solution

Procedural apes are fearless 2022-02-13 05:19:34 阅读数:49

mysql error incompatible sql_ sql

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
 Insert picture description here

3.sql sentence
1) Inquire about group by Field of ( normal )

SELECT USER_ID FROM t_iov_help_feedback GROUP BY USER_ID;

 Insert picture description here

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

 Insert picture description here

2) Check not group by Field of ( Report errors )
 Insert picture description here

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();

 Insert picture description here

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

  1. see sql_mode The sentence of
select @@GLOBAL.sql_mode;

 Insert picture description here

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;

 Insert picture description here

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’;

 Insert picture description here

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 :

 Insert picture description here

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 .
 Insert picture description here

 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

 Insert picture description here

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

 Insert picture description here

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

copyright:author[Procedural apes are fearless],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130519315098.html