MySQL 错误：this is incompatible with sql_mode=ONLY_FULL_GROUP_BY 完美解决方案
在 MySQL 中，当你遇到以下错误时：
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table_name.column_name' which is not functionally dependent on columns in GROUP BY clause.
该错误提示 this is incompatible with sql_mode=ONLY_FULL_GROUP_BY，意味着你在执行 GROUP BY 查询时，选择的列不符合 ONLY_FULL_GROUP_BY SQL 模式要求。
原因解析：
在 MySQL 的 ONLY_FULL_GROUP_BY 模式下，要求查询中的所有 非聚合列（即不在 GROUP BY 子句中的列）必须是 函数依赖 的，或者必须应用聚合函数（如 COUNT()、SUM()、MAX() 等）进行处理。这是为了避免产生不确定的结果，因为在 GROUP BY 后，可能有多行数据被合并，而这些数据在选择列时可能是不一致的。
例如，以下 SQL 会引发错误：
SELECT column1, column2
FROM table_name
GROUP BY column1;
在这个查询中，column2 不在 GROUP BY 中，也没有使用聚合函数，因此会违反 ONLY_FULL_GROUP_BY 的限制。
解决方案
有几种方法可以解决这个问题，以下是常见的解决方案：
1. 关闭 ONLY_FULL_GROUP_BY 模式
如果你不需要 ONLY_FULL_GROUP_BY 模式，可以临时或永久禁用它。
1.1 临时禁用
你可以通过以下 SQL 命令临时禁用 ONLY_FULL_GROUP_BY 模式：
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
这个命令会禁用当前会话中的 ONLY_FULL_GROUP_BY，仅在当前会话有效。
1.2 永久禁用
要永久禁用 ONLY_FULL_GROUP_BY 模式，你需要修改 MySQL 配置文件 my.cnf 或 my.ini，并重新启动 MySQL 服务。
- 打开 MySQL 配置文件：
- 在 Linux 系统中：
/etc/my.cnf或/etc/mysql/my.cnf - 在 Windows 系统中：
my.ini
- 在 Linux 系统中：
- 在
[mysqld]部分添加或修改以下配置：
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
- 重新启动 MySQL 服务：
sudo service mysql restart # 对于 Linux 系统
# 或者
# 在 Windows 上重启 MySQL 服务
这个设置会禁用 ONLY_FULL_GROUP_BY 模式，并且允许你执行不符合标准的 GROUP BY 查询。
2. 修改查询，使用聚合函数
另一种解决方案是修改你的查询，使其符合 ONLY_FULL_GROUP_BY 的要求。你可以通过使用聚合函数（如 MAX()、MIN()、AVG()、GROUP_CONCAT() 等）来解决。
示例：
如果你在 SELECT 中选择了多个列，但是没有在 GROUP BY 中列出这些列，你可以使用聚合函数来确保查询合法。
原始查询：
SELECT column1, column2
FROM table_name
GROUP BY column1;
修改后的查询：
SELECT column1, MAX(column2) AS column2
FROM table_name
GROUP BY column1;
在这个修改后的查询中，MAX(column2) 作为聚合函数使用，这样就满足了 ONLY_FULL_GROUP_BY 模式的要求。
3. 确保所有非聚合列出现在 GROUP BY 中
如果你希望查询返回多个列并且不使用聚合函数，另一种方法是将所有非聚合列添加到 GROUP BY 子句中。
示例：
SELECT column1, column2
FROM table_name
GROUP BY column1, column2;
在这个查询中，column2 也被添加到了 GROUP BY 中，这样就避免了错误的发生。
4. 通过 ANY_VALUE() 函数绕过
如果你希望选择的列不在 GROUP BY 中，但不想使用聚合函数，你可以使用 MySQL 提供的 ANY_VALUE() 函数来绕过 ONLY_FULL_GROUP_BY 限制。
示例：
SELECT column1, ANY_VALUE(column2)
FROM table_name
GROUP BY column1;
在这个查询中，ANY_VALUE(column2) 允许你选择一个列，而不违反 GROUP BY 的要求。ANY_VALUE() 会返回 column2 的一个任意值，而不关心其具体值。这对于一些特定的用例非常有用，但要注意，它可能不适用于所有场景。
5. 检查现有的 SQL 模式
在某些情况下，你可能只需要检查或修改现有的 SQL 模式，而不是完全禁用 ONLY_FULL_GROUP_BY。你可以查看当前的 SQL 模式，了解是否有其他模式导致问题。
SELECT @@sql_mode;
如果返回的结果中包含 ONLY_FULL_GROUP_BY，则说明该模式已经启用。如果只想禁用该模式，你可以使用以下命令：
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
总结
ONLY_FULL_GROUP_BY是 MySQL 中的一种 SQL 模式，用于确保GROUP BY查询的列符合严格的要求。- 你可以通过 关闭模式、修改查询 或 使用聚合函数 来解决与该模式相关的错误。
- 如果关闭该模式不符合你的需求，确保查询符合
GROUP BY的要求，或者使用 MySQL 提供的ANY_VALUE()函数来解决问题。
根据你的实际需求选择合适的方案。
发表回复