1055 error occurs when using group by statement on MySQL 5.7

The main content of this article is about the analysis and solutions of the 1055 error when using the group by statement on MySQL 5.7. Friends in need can take a look.

1. When using the group by statement for grouping in mysql version 5.7 or above, if the selected field does not exactly correspond to the field after group by, but has other fields, then this error will be reported

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

2. This is because the higher version of mysql adds a sql_mode

View sql_mode

select @@sql_mode;

mysql> select @@sql_mode;
---------------------- -------------------------------------------------- -------------------------------------------------- ------------------
| @@sql_mode                                                                                                                                              -------------------------------------------------- -------------------------------------------------- ------------------
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_pISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
---------- -------------------------------------------------- -------------------------------------------------- --------------------------

You can see that there is an ONLY_FULL_GROUP_BY, which strictly controls the group by and select fields. , just remove this without changing the sql statement

3. I edited this file /etc/mysql/conf.d/mysql.cnf under my ubuntu system, and other systems found the corresponding Just configure the configuration file. Under the [mysqld] block, configure sql_mode and remove ONLY_FULL_GROUP_BY

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_pISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU TION'

4 .It’s normal now

Related tutorials:

mysql video tutorial

The above is the detailed content of 1055 error occurs when using group by statement on MySQL 5.7. For more information, please follow other related articles on the PHP Chinese website!