Mysql exception ERROR 1055 (42000) description
The specific exception information is as follows:
ERROR 1055 (42000): Expression # 1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'aaa.test.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Execute such a SQL statement to reproduce the exception select age, name from test group by name
Mysql5.1, no exception reported
Mysql5.5, no exception reported
Mysql5.6, no exception reported
Mysql5.7.22, report an exception (which version has not been determined yet since 5.7, some people on the Internet say 5.7.5)
Mysql has adjusted the default parameter sql_ mode since version 5.7 to:
Mysql > show variables like 'sql_mode' +- -- + | Variable_name | Value | +- -- +- -+ | sql_mode | 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 | +-+- -- + 1 row in set (0.00 sec) mysql >
Among them, ONLY_FULL_GROUP_BY affects whether the above SQL statement reports an exception.
Solution:
1. Rewrite the SQL statement. After all, the above is not a standard SQL statement.
2. Log in to MySQL server, execute the following command, and modify it at both global and session level
Mysql > 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';mysql > 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'
3. Combined with the second method, specify the value of sql_mode in the my.cnf 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
Note: if you combine 2 with 3, you don't have to restart the server, and even the restart configuration is still valid.
