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.
- Find the configuration file / etc / my.cnf (or mysql-server.cnf in the associated folder)
- 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’
- 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!