Shulou(Shulou.com)06/02 Report--

This article mainly introduces "the causes and solutions of Mysql8.0 and above error reporting only_full_group_by". In daily operation, it is believed that many people have doubts about the causes and solutions of Mysql8.0 and above reporting error only_full_group_by. The editor consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful for you to answer the questions of "Mysql8.0 and the reasons and solutions for reporting errors in only_full_group_by"! Next, please follow the editor to study!

Today, we found that mysql8 has a problem with only_full_group_by. An error message will appear when the old code queries the group by function.

ERROR 1055 (42000): Expression # 7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'postscan.verifyDelayLog.auditor' which is not functionally dependent on columns in GROUP BY clause; this is incompatible withsql_mode=only_full_group_by

After checking the data, for this kind of aggregation operation of group by, if the column in select does not appear in group by, then the SQL is illegal. Because the column is not in the clause of group by, for the database with this mode, when using group by, you need to use the aggregate functions of MAX (), SUM () and ANT_VALUE () to complete the GROUP BY aggregation operation compatibility method:

Sql modification

Enter: SELECT @ @ GLOBAL.sql_mode; result: 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 and then let's modify sql_mode set GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

This change will expire after it is restarted.

Modify my.cnf

The common values of sql_mode are as follows:

ONLY_FULL_GROUP_BY: for GROUP BY aggregation operations, if the column in SELECT does not appear in GROUP BY, then the SQL is illegal because the column is not in the GROUP BY clause. NO_AUTO_VALUE_ON_ZERO: this value affects the insertion of self-growing columns. By default, inserting 0 or NULL means the next self-growth value is generated. This option is useful if the user wants to insert a value of 0 and the column is self-growing. STRICT_TRANS_TABLES: in this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted and no restriction is imposed on the non-transactional table NO_ZERO_IN_DATE: in strict mode, zero dates and months are not allowed to NO_ZERO_DATE: set this value, the mysql database does not allow the insertion of zero dates, and inserting zero dates throws an error rather than a warning. ERROR_FOR_DIVISION_BY_ZERO: in 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 NO_AUTO_CREATE_USER when the data is divided by zero: forbids GRANT to create a user NO_ENGINE_SUBSTITUTION with an empty password: throws an error if the required storage engine is disabled or uncompiled. When this value is not set, replace it with the default storage engine and throw an exception PIPES_AS_CONCAT: treat "| |" as the concatenation operator of the string rather than the OR operator, which is the same as the Oracle database and similar to the string concatenation function Concat: when ANSI_QUOTES is enabled, the string cannot be referenced in double quotes because it is interpreted as an identifier here The study on "Mysql8.0 and the reasons and solutions for reporting errors in only_full_group_by" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.