this is incompatible with sql_mode=only_full_group_by
时间: 2023-10-21 18:37:44 浏览: 115
解决MySQL 5.7.9版本sql_mode=only_full_group_by问题
This error message occurs when the SQL query includes a GROUP BY clause, but not all non-aggregated columns are listed in the GROUP BY clause or have an aggregate function applied to them.
The "only_full_group_by" mode is a MySQL mode that requires all non-aggregated columns to be listed in the GROUP BY clause. This mode is enabled by default in newer versions of MySQL to prevent queries that generate ambiguous or incorrect results.
To fix this error, you can either add all non-aggregated columns to the GROUP BY clause or apply an aggregate function to them. For example, if you have a query like this:
SELECT name, category, AVG(price)
FROM products
GROUP BY category;
You can add the "name" column to the GROUP BY clause:
SELECT name, category, AVG(price)
FROM products
GROUP BY category, name;
Or apply an aggregate function to the "name" column:
SELECT MAX(name), category, AVG(price)
FROM products
GROUP BY category;
Alternatively, you can disable the "only_full_group_by" mode by modifying the MySQL configuration or using the SQL command below:
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
阅读全文