mysql8.0 official website: processing group by

1. Initial conditions

Now there is a table with two pairs of name = pants

2. Phenomenon

Execute the following sql to get the data grouped by name: GROUP BY name

SELECT name,author,SUM(price) FROM  `t_book`
GROUP BY name

1055 exception occurred while executing sql

SELECT name,author,SUM(price) FROM  `t_book`
GROUP BY name
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t_book.author' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
> time: 0.03s

According to the error prompt, execute the following statement: group by name, author, and the query will not report an error

SELECT name,author,SUM(price) FROM  `t_book`
GROUP BY name ,author

Query results:

          However, this query has deviated from our original logic. We want GROUP BY name, and only two pieces of data should appear in the result. Instead of the three results after GROUP BY name and author, what should I do?

 

3. Solution

 

① : close sql_mode only_full_group_by mode

          only_full_group_by requires that the non function column after select must be filled after group by, so we only need to turn off only_ full_ group_ The closing mode is divided into temporary closing and permanent closing

• Temporary shutdown: temporary shutdown is divided into Session level and GLOBAL level
  • Session: only the current session settings are closed. It will become invalid after mysql is restarted
  • GLOBAL: GLOBAL settings are turned off and will become invalid after mysql is restarted
• Permanently close: permanently close only_full_group_by mode. This method needs to be modified in the mysql configuration file and then restarted.
  1. Find the configuration file / etc / my.cnf (or mysql-server.cnf in the associated folder)
  2. Append SQL after [mysqld] in the above file_ mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’
  3. After saving the configuration file, restart Mysql.

Next, I'll show you how to close and open Session level sql_mode.only_full_group_by attribute!

View sql_mode attribute

• View Session level: SELECT @@sql_mode;
• View GLOBAL level: select @@GLOBAL.sql_mode;

View Session level sql_mode attribute, and as a result, you can see with only_ FULL_ GROUP_ The by attribute, so our GROUP BY name will report 1055 exceptions

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Next, close the Session level sql_mode attribute

// Close session level
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

// Turn off GLOBAL level
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Select @ @ SQL again_ mode; Query discovery SQL_ The value of mode has been deleted ONLY_FULL_GROUP_BY property

STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Then execute the following sql again

SELECT name,author,SUM(price) FROM  `t_book`
GROUP BY name 

Execution result: two records are returned according to our idea

Note: in the query result, the author attribute after select only returns the first data in the original data table because it does not use the aggregate function. Please pay attention when using it! The original data are as follows

         

② : use ANY_VALUE() suppress only_ FULL_ GROUP_ Impact of by

          If you don't want to close mysql ONLY_FULL_GROUP_BY global setting. If you only want the current sql to ignore its impact, you can use ANY_VALUE(cloum) ignore only_ FULL_ GROUP_ See the official document for the impact of by!

mysql8.0 official website: ANY_VALUE(cloum) can suppress only_ FULL_ GROUP_ Impact of by

Only has been set above_ FULL_ GROUP_ The by property is closed. Next, open it:

 // Open ` ONLY_FULL_GROUP_BY ` attribute
 SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

Then execute the above sql, and 1055 exception will be reported

SELECT name,author,SUM(price) FROM  `t_book`
GROUP BY name 

Use ANY_VALUE() ignore only_ FULL_ GROUP_ Impact of by:

SELECT name,ANY_VALUE(author),SUM(price) FROM  `t_book`
GROUP BY name

The results are as follows:

Normal results!