内容提要
最近在做一个开源项目,遇到了group by查询的问题。在本地测试时没有问题,但在服务器上出现了错误。错误信息是SELECT语句中的列必须包含在GROUP BY中。解决方案有两种:修改global.sql_mode或通过修改配置文件。文章还提供了一个栗子来演示解决方法。文章最后提到,如果使用了with_entities指定查询列,查询结果将不再是对象,而是嵌套元组的列表。
延伸解读
错误根源:ONLY_FULL_GROUP_BY 模式
MySQL 5.7 默认启用 ONLY_FULL_GROUP_BY 模式,要求 SELECT、HAVING、ORDER BY 中的非聚合列必须出现在 GROUP BY 中。这源于 SQL-92 标准,旨在避免分组结果不确定。本地测试正常而服务器报错,通常是因为环境 sql_mode 配置不同。理解这一点有助于快速定位问题,而非盲目修改配置。
解决方案对比:修改 sql_mode 与使用 ANY_VALUE
文章提到两种方案:修改 global.sql_mode 或配置文件,但后者可能导致服务无法启动。更推荐使用 ANY_VALUE() 函数包裹非分组列,如 SELECT name, ANY_VALUE(address) FROM t GROUP BY name。这样既符合 ONLY_FULL_GROUP_BY 要求,又无需改动全局配置,适合生产环境。
SQLAlchemy 中的实践与版本兼容
在 Flask-SQLAlchemy 中,可用 with_entities 指定查询列,并用 func.any_value 包裹非分组列。注意:使用 with_entities 后,查询结果不再是模型对象,而是嵌套元组的列表。此外,ANY_VALUE 仅 MySQL 5.7+ 支持,低版本需回退到普通查询,如文章示例中的 try-except 兼容处理。
Q&A
MySQL 5.7 之后 GROUP BY 查询报错 'Expression #1 of SELECT list is not in GROUP BY clause' 是什么原因?
这是因为 MySQL 5.7 及以上版本默认启用了 ONLY_FULL_GROUP_BY 模式,要求 SELECT 列表、HAVING 条件或 ORDER BY 列表中的非聚合列必须出现在 GROUP BY 子句中,否则会报错。
如何临时修改 MySQL 的 sql_mode 来解决 GROUP BY 报错?
可以通过 SET 语句修改 global 或 session 级别的 sql_mode,例如:SET @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; 如果 global 不生效,可以尝试修改 session 级别。
修改 MySQL 配置文件来关闭 ONLY_FULL_GROUP_BY 有什么风险?
修改配置文件(如 my.cnf)添加 sql_mode 设置并重启 MySQL 服务,但这种方法可能不生效,甚至导致 MySQL 服务无法启动,因此可行性有待商榷。
ANY_VALUE() 函数在 GROUP BY 查询中有什么作用?
ANY_VALUE() 可以包裹不在 GROUP BY 中的非聚合列,使查询合法,同时抑制 ONLY_FULL_GROUP_BY 检查。例如:SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
在 SQLAlchemy 中使用 with_entities 指定查询列后,查询结果是什么形式?
使用 with_entities 指定查询列后,查询结果不再是对象,而是嵌套元组的列表。
如何让 GROUP BY 查询兼容 MySQL 5.7 以下版本?
由于 MySQL 5.7 以下版本没有 ANY_VALUE 函数,可以通过 try-except 捕获异常,在异常时使用普通列代替 ANY_VALUE 包裹的列,例如:try 中使用 func.any_value,except 中直接使用列名。