An article explaining in detail the error reporting problem of MySQL setting only_full_group_by

The development environment is connected tomysql5.6, while the test environment ismysql5.7. During development, a friend wrote asqlstatement aboutgroup by. It runs normally in the development environment, but exceptions are found in the test environment.

Cause analysis:MySQL5.7version has themysql sql_mode = only_full_group_byattribute set by default, causing an error.

ONLY_FULL_GROUP_BYis the culprit causing this error. In this strict mode, for thegroup byaggregation operation, if inselect## If the columns in # do not appear ingroup by, then thisSQLis illegal. Because in thesqlwritten by the developer, theselectcolumn is not in thegroup byclause, and an error will be reported when usinggroup by.

The test environment downloaded and installed the latest version of

mysql5.7.x. Theonly_full_group_bymode is enabled by default.

1. Check sql_mode

SELECT` `@@sql_mode;

and the value queried 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 the newly created database.

For existing databases, 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 configuration file of

MySQL

, in the/etc/my.cnffile on thelinuxsystem, querysql_modefield, 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, like this After adding, restartmysqlfor it to take effect. Exit the database: exit, restart command:

service mysqld restart #lnmp重启mysql lnmp restart mysql

Refresh the page and the error message disappears. It is successfully resolved. Connect to the database again to view

sql_mode

Configurationselect @@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 ends this article about

Mysql5 .7

and above versionONLY_FULL_GROUP_BYThis article introduces the error solution, more relatedMysql5.7 ONLY_FULL_GROUP_BY## is as follows For the meaning of common values of sql_mode, refer to:

ONLY_FULL_GROUP_BY: For GROUP BY aggregation operation, if the column in SELECT does not appear in GROUP BY, then this SQL is illegal because the column Not in the GROUP BY clause

NO_AUTO_VALUE_ON_ZERO: This value affects the insertion of auto-growing columns. Under the default settings, inserting 0 or NULL represents generating the next auto-increasing value. This option is useful if the user wants to insert a value of 0 and the column is auto-increasing.

STRICT_TRANS_TABLES: In this mode, if a value cannot be inserted into a transaction table, the current operation will be interrupted, and there will be no restriction on non-transaction tables.

NO_ZERO_IN_DATE: In strict mode, Zero dates and months are not allowed

NO_ZERO_DATE: Set this value, the mysql database does not allow the insertion of zero dates, and inserting zero dates will throw an error instead of a warning.

ERROR_FOR_DIVISION_BY_ZERO: During the INSERT or UPDATE process, if the data is divided by zero, an error is generated instead of a warning. If the mode is not given, MySQL returns NULL when the data is divided by zero

NO_AUTO_CREATE_USER: Disables GRANT from creating users with empty passwords

NO_ENGINE_SUBSTITUTION: If the required storage engine is disabled or not compiled , then an error is thrown. When this value is not set, the default storage engine is used instead and an exception is thrown.

PIPES_AS_CONCAT: Treat "||" as a string connection operator instead of an OR operator, which is the same as the Oracle database Similarly, it is similar to the string concatenation function Concat

ANSI_QUOTES: After enabling ANSI_QUOTES, double quotes cannot be used to quote the string because it is interpreted as an identifier.

Recommended learning: "

MySQL Video Tutorial

"

The above is the detailed content of An article explaining in detail the error reporting problem of MySQL setting only_full_group_by. For more information, please follow other related articles on the PHP Chinese website!