MySQL解决错误this is incompatible with sql_mode=only_full_group_by…

在SQL查询语句中加入group by时报了以下错误：

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'user.varieties.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

错误原因：

这个错误信息表明在使用 MySQL 数据库时，ONLY_FULL_GROUP_BY SQL 模式被启用了，而SQL 查询违反了该模式的要求。具体来说，SELECT 查询中包含了一个或多个非聚合列，这些列没有出现在 GROUP BY 子句中，也没有被用作聚合函数的一部分。

在 ONLY_FULL_GROUP_BY 模式下，MySQL 要求 SELECT 列表中的每个列都必须是 GROUP BY 子句的一部分，或者是通过聚合函数（如 COUNT(), MAX(), MIN(), SUM(), AVG() 等）处理的。这是因为，当使用 GROUP BY 时，MySQL 需要知道如何为每个组选择这些非聚合列的值，而在没有指定的情况下，选择哪个值是不确定的。

解决方案：

1、修改查询以包含 GROUP BY 子句：

确保 SELECT 列表中的所有非聚合列都包含在 GROUP BY 子句中。例如，如果查询是这样的：

SELECT crop_varieties.id, COUNT(*)  
FROM crop_varieties  
GROUP BY some_other_column;

需要修改为：

SELECT crop_varieties.id, COUNT(*)  
FROM crop_varieties  
GROUP BY crop_varieties.id;

2、使用聚合函数：

如果 id 列的值在每个组内不是唯一的，并且不需要知道具体的 id 值，可以使用聚合函数（如 MAX() 或 MIN()）来选择每个组中的一个 id 值：

SELECT MAX(crop_varieties.id) AS max_id, COUNT(*)  
FROM crop_varieties  
GROUP BY some_other_column;

3、禁用 ONLY_FULL_GROUP_BY：

如果不想修改查询，并且确信查询逻辑是正确的，可以考虑禁用 ONLY_FULL_GROUP_BY SQL 模式。但是，这通常不推荐，因为它可能会隐藏潜在的查询问题。

禁用 ONLY_FULL_GROUP_BY，可以从 sql_mode 中移除它：

-- 全局级别  
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));  
  
-- 会话级别  
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

启用 ONLY_FULL_GROUP_BY：

-- 全局级别  
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','') || 'ONLY_FULL_GROUP_BY');  
  
-- 会话级别  
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','') || 'ONLY_FULL_GROUP_BY');

检查 ONLY_FULL_GROUP_BY 是否启用：

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

注意事项

• 修改 sql_mode 需要足够的权限。
• 在生产环境中修改 sql_mode 需要谨慎，因为它可能会影响到现有的查询和应用逻辑。
• 在开发或测试环境中启用 ONLY_FULL_GROUP_BY 是一个好习惯，这有助于发现潜在的 SQL 问题并提高代码质量。