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

The development environment is connected to mysql5.6, while the test environment is mysql5.7. During development, a friend wrote a sql statement about group by. It runs normally in the development environment, but exceptions are found in the test environment.

Cause analysis: MySQL5.7 version has the mysql sql_mode = only_full_group_by attribute set by default, causing an error.

ONLY_FULL_GROUP_BY is the culprit causing this error. In this strict mode, for the group by aggregation operation, if in select## If the columns in # do not appear in group by, then this SQL is illegal. Because in the sql written by the developer, the select column is not in the group by clause, and an error will be reported when using group by.

The test environment downloaded and installed the latest version of

mysql5.7.x. The only_full_group_by mode 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.cnf file on the linux system, query sql_mode field, I did not find this keyword in the configuration file, so I added it manually: <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</pre><div class="contentsignin">Copy after login</div></div>One thing to note is that it must be added in the

[mysqld]

configuration, like this After adding, restart mysql for it to take effect. Exit the database: exit, restart command: <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">service mysqld restart #lnmp重启mysql lnmp restart mysql</pre><div class="contentsignin">Copy after login</div></div> 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_BY This article introduces the error solution, more related Mysql5.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!