编程知识 cdmana.com

Group by syntax of MySQL 5.7

One 、 Based on the environment

linux centos 7, mysql 5.7.26

Two 、 Problem description

Use group by In group query , Prompt exception : SELECT list is not in GROUP BY clause and contains nonaggregated column ‘XXX’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode =only_full_group_by

3、 ... and 、 Question why

I've looked up the reasons , intend : When you use group by When doing group query , Columns used for grouping , So you select You have to bring the grouped Columns , Because mysql5.7 The above version has been added sql_mode=ONLY_FULL_GROUP_BY Set up , This is also indicated in the exception description .

for instance : select id,name from Users group by id,sex; If it's here select Column without sex, Then it will report the above exception .

Four 、 Solution

According to the actual business operation , The example above is this sql There is a way of writing , And there are many applications , So we want the example to be written in line with mysql A standard , You need to change mysql Of sql_mode Configured with , The specific operation is as follows :

  1. adopt sql Change the way sql_mode( Currently in force , restart mysql Post failure )

Get into mysql client , Execute the following command :

select @@GLOBAL.sql_mode;
+--------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------+
|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 
+--------------------------------------------------------------------------------------------------------------------------------------+

found ONLY_FULL_GROUP_BY, Perform the following settings :

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';
  1. adopt mysql To configure my.cnf change sql_mode( Once and for all )

Find the server mysql Corresponding my.cnf The configuration file , For example, here my profile is in /etc/my.cnf

  • vim /etc/my.cnf
  • change / Join in sql_mode To configure :
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  • preservation , sign out , And restart mysql: service mysql restart ( This is because I configured mysql Serving the system )
  • Review sql_mode, It's been released ONLY_FULL_GROUP_BY Set up

5、 ... and 、 matters needing attention

nothing

Participation of this paper Tencent cloud media sharing plan , You are welcome to join us , share .

版权声明
本文为[The blank of writing memory]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224104023959u.html

Scroll to Top