From the development of a project to the test and online operation, the team’s immature management of the project will affect the development efficiency of the project. Since I just took over the project and set up the PHP environment in Centos alone, I considered using the higher version, choosing Mysql5.7, the local development environment or the Windows inheritance environment, the PHP environment and Centos are unified 7.0, but the Mysql version is 5.3 , The local development went well, and some problems appeared under Centos, which was caused by the inconsistency between the development environment and the test|online environment.

mysql command groop by error this is incompatible with sql_mode=only_full_group_by

Take a look at the meaning of ONLY_FULL_GROUP_BY: For the GROUP BY aggregation operation, if the column in the SELECT does not appear in the GROUP BY, then this SQL is illegal, because the column is not in the GROUP BY clause, that is to say, the column found It must appear after the group by, otherwise an error will be reported, or this field appears in the aggregate function.

Next, let’s just talk about the process of solving the problem.

First enter the Mysql client to execute the 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 
+------ -------------------------------------------------- -------------------------------------------------- ----------------------------+

See the first paragraph of the above repentant command line —> ONLY_FULL_GROUP_BY;

There are about three solutions given on the Internet. One is to add aggregate functions to the query condition column in the statement of the program code. If you do this, it will be very troublesome, and you can use group by in the development process. You have to work hard on the sentence, and this method is fine.

It is recommended to turn off this configuration item directly.

So how to turn off this item, can it be turned off directly by executing the command on the client side, set @@global.sql_mode = ……., the execution is successful, and then execute the command to check it out. One item is gone, and then we restart the mysql service again, query again, still… haha. .

So, let’s play a trick next, let’s start from the Mysql configuration file.

Find my.cnf, edit it~Put the following commands in the right place, restart the mysql service, OK~the error is correct~

Note: There is not extra set in front.

[mysqld] 
#sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION