Solve the grouping error Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated in MySQL versions greater than 5.7

reason:

MySQL 5.7.5 and up implements detection of functional dependencies. If the only_full_group_by SQL mode is enabled (which it is by default), MySQL rejects queries whose select lists, conditions, or order lists refer to nonaggregate columns that are not named in the group by without being functionally dependent on them. (Prior to 5.7.5, MySQL did not detect feature dependencies and only_full_group_by was not enabled by default. See the MySQL 5.6 Reference Manual for a description of the pre-5.7.5 behavior.)

Execute the following command to view the content of sql_mode:

mysql> SHOW SESSION VARIABLES;
mysql> SHOW GLOBAL VARIABLES;
mysql> select @@sql_mode;

It can be seen that the values ​​of sql_mode for session and global 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

only_full_group_by description:

only_full_group_by: This option uses the same group rules as Oracle. The selected columns must be in the group or be aggregate columns (SUM, AVG, MAX, MIN). In fact, I personally feel that this configuration is similar to distinct, so just remove it.

solve:

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';<br data-filtered="filtered">set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';<br data-filtered="filtered">

Summarize

The above is the solution to the grouping error Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated in MySQL version greater than 5.7. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!