How to solve the problem of mysql5.7 error reporting by this is incompatible with sql_mode=only_full_group_by

This article mainly explains "how to solve the problem of mysql5.7 error reporting this is incompatible with sql_mode=only_full_group_by". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "mysql5.7 error report this is incompatible with sql_mode=only_full_group_by how to solve it"!

Download and install the latest version of mysql5.7.x. Only_full_group_by mode is turned on by default, but when this mode is turned on, the original group by statement reports an error and then removes it.

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

It is better to turn on only_full_group_by mode.

Because there is a function in mysql: any_value (field) allows the presence of non-grouped fields (which has the same effect as turning off only_full_group_by mode).

Specific error tips:

[Err] 1055-Expression # 1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

1. View sql_mode

Select @ @ global.sql_mode

The values queried are:

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 the 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 built database. At this point, I believe you have a deeper understanding of "how to solve mysql5.7 error this is incompatible with sql_mode=only_full_group_by". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!