How to Fix MySQL Error 1055: Expression Not in GROUP BY Clause?

#1055 - Expression Not in GROUP BY Clause Incompatible with Server Configuration

In MySQL versions 5.7.12 and later, using the sql_mode=only_full_group_by setting ensures that aggregate functions only operate on columns included in the GROUP BY clause or are themselves aggregate functions.

To resolve the error "Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by," consider the following steps:

1. Enable Compatibility Mode:

   • Edit the MySQL configuration file (my.cnf or mysql.conf.d/mysql.cnf).

   • Add the following line to the bottom of the file:

     [mysqld]
     sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2. Restart MySQL:

   • Run the command sudo service mysql restart to restart the MySQL server.

This modified sql_mode setting will allow aggregate functions to be used on columns not included in the GROUP BY clause without raising the error. However, it is recommended to only use this mode for compatibility purposes and to consider restructuring the query to adhere to the only_full_group_by mode when possible.

The above is the detailed content of How to Fix MySQL Error 1055: Expression Not in GROUP BY Clause?. For more information, please follow other related articles on the PHP Chinese website!