Solution to ONLY_FULL_GROUP_BY error in Mysql5.7 and above

Recently, during the development process, the MySQL database connected to the project development environment is Alibaba Cloud's database, and the Alibaba Cloud database version is 5.6. The mysql in the test environment is 5.7 installed by myself. Therefore, during the development process, some friends accidentally wrote the SQL statement related to group by. It runs normally in the development environment, but anomalies are found in the test environment.

Cause analysis: The MySQL 5.7 version sets the mysql sql_mode = only_full_group_by attribute by default, which causes an error.

Among them, ONLY_FULL_GROUP_BY is the culprit of this error. For the group by aggregation operation, if the column in the select does not appear in the group by, then the SQL is illegal because the column is not in the group by clause. Therefore, the database with sql_mode=only_full_group_by set will report an error when using group by.

The test environment downloaded and installed the latest version of MySQL 5.7.x. The only_full_group_by mode was enabled by default. However, after enabling this mode, the original group by statement reported an error, and then it was removed.

Once only_full_group_by is turned on, it feels like group by will become the same as distinct, which can only obtain the field information affected by it, and cannot coexist with other fields not affected by it. In this way, the function of group by will become very narrow.

It is better to enable only_full_group_by mode. Because there is a function in MySQL: any_value(field) allows the appearance of non-grouping fields (which has the same effect as turning off only_full_group_by mode).

1. Check sql_mode

SELECT @@sql_mode;

The queried value is:

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

2. Remove ONLY_FULL_GROUP_BY and reset the value.

SET @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

3. The above changes the global sql_mode, which is valid for newly created databases. For an existing database, you need to execute it under the corresponding data

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

The above method is still invalid after restarting the MySQL database, and the following method is still effective after restarting

Find the MySQL configuration file, /etc/my.cnf file on the Linux system, query the sql_mode field, I did not find this keyword in the configuration file, so I added it manually:

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

One thing to note is that it must be added in the [mysqld] configuration, so that it will take effect after restarting mysql. Exit the database: exit, restart command:

service mysqld restart

Refresh the page and the error message disappears. The problem is solved successfully. Connect to the database again to view the sql_mode configuration. Select @@sql_mode:

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

This is the end of this article about the solution to the ONLY_FULL_GROUP_BY error in Mysql5.7 and above. For more related Mysql5.7 ONLY_FULL_GROUP_BY content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!