在升级到 MySQL 5.7 或更高版本后,许多网站维护人员可能会遇到一个特定的 SQL 查询错误,它源于 MySQL 的默认配置变更。本文将深入解析该错误的成因,并提供一种有效的解决方法。
错误描述与原因
当您尝试执行某个 SELECT 查询时,可能会遇到如下所示的错误信息:
[42000][1055] Expression #34 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cloud.guest_os_category.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
这个错误通常出现在 MySQL 5.7 及以上版本中,其根源在于 MySQL 的一项默认配置——sql_mode。在较新版本中,sql_mode 默认包含了 ONLY_FULL_GROUP_BY 选项,这意味着 MySQL 严格遵守了 SQL92 标准中关于 GROUP BY 子句的规定。
按照 ONLY_FULL_GROUP_BY 规则,当使用 GROUP BY 子句时,SELECT 列表中的每一项要么是聚合函数(如 COUNT(), SUM(), AVG() 等)的返回值,要么必须包含在 GROUP BY 子句中。换句话说,对于非聚合列,如果它们不在 GROUP BY 子句中,那么这些列的值必须在分组后是确定的,即它们对 GROUP BY 子句中列的值具有函数依赖关系。否则,MySQL 将抛出上述错误。
在您的例子中,错误提示指出列 'cloud.guest_os_category.id' 是一个非聚合列,它既未出现在 GROUP BY 子句中,也不具备对 GROUP BY 子句中列的函数依赖性。这与启用的 ONLY_FULL_GROUP_BY 模式不兼容,导致查询失败。
解决方案:调整 sql_mode
配置
为了解决这个问题并让您的查询能够顺利执行,可以考虑调整 MySQL 的 sql_mode
配置,移除 ONLY_FULL_GROUP_BY
选项。以下是具体操作步骤:
1. 编辑 MySQL 配置文件
使用文本编辑器打开 MySQL 的主配置文件,通常位于 /etc/my.cnf
。在 [mysqld]
部分(如果没有,请自行添加)下方添加如下代码:
[mysqld] #linux sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION #window sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
这里我们保留了其他有益于数据一致性和安全性的选项,而移除了可能导致问题的 ONLY_FULL_GROUP_BY
。
2. 重启 MySQL 服务
完成配置文件修改后,需要重启 MySQL 服务以使更改生效。在 Linux 系统中,可以使用以下命令:
sudo systemctl restart mysqld
3. 验证配置变更
重启服务后,您可以再次执行之前出错的查询,确认问题是否已得到解决。此外,通过运行 SHOW VARIABLES LIKE 'sql_mode';
命令,检查当前的 sql_mode
设置,确认 ONLY_FULL_GROUP_BY
已被移除。
结语
调整 MySQL 的 sql_mode
配置,移除 ONLY_FULL_GROUP_BY
选项,可以有效地解决因严格 GROUP BY 规则引发的查询错误。然而,请注意,放宽 sql_mode
设置可能会影响到其他查询的行为和数据一致性。因此,在生产环境中进行此类更改时,务必谨慎评估其对整体系统的影响,并确保其他查询仍能满足业务需求和数据完整性要求。在可能的情况下,优化查询语句以符合 ONLY_FULL_GROUP_BY
规则是更为推荐的做法。