数据库报错：which is not functionally dependent on columns in GROUP BY clause

在 SQL 查询中，SELECT 列表中的某些字段没有出现在 GROUP BY 子句中，同时也没有被聚合函数包围。这在 SQL 模式设置为 ONLY_FULL_GROUP_BY 时是非法的。ONLY_FULL_GROUP_BY 模式要求所有非聚合字段都必须出现在 GROUP BY 子句中。

环境说明

• 操作系统: CentOS 7.6
• Java 版本: 17
• 数据库: MySQL

问题描述

在执行 SQL 查询时，遇到以下错误：

java.sql.SQLSyntaxErrorException: Expression #31 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'username' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决方法

1. 修改 SQL 查询

   确保所有在 SELECT 列表中的字段，要么出现在 GROUP BY 子句中，要么被聚合函数（如 COUNT, SUM, AVG 等）包围。

   例如，假设你的原始 SQL 查询如下：

   SELECT id, name, username, COUNT(*)
   FROM users
   GROUP BY id;
   

   由于 name 和 username 没有出现在 GROUP BY 子句中，这将导致上述错误。你可以将其修改为：

   SELECT id, name, username, COUNT(*)
   FROM users
   GROUP BY id, name, username;
   

   或者，如果 name 和 username 是不需要的聚合字段，可以使用聚合函数，如：

   SELECT id, MAX(name) as name, MAX(username) as username, COUNT(*)
   FROM users
   GROUP BY id;
   

2. 修改 MySQL 配置

   如果修改 SQL 查询不是一个选项，你可以更改 MySQL 的 SQL 模式，禁用 ONLY_FULL_GROUP_BY。这可以在 MySQL 配置文件中完成，或者通过命令行临时更改。

   • 永久禁用 ONLY_FULL_GROUP_BY

     编辑 MySQL 配置文件（通常是 /etc/my.cnf 或 /etc/mysql/my.cnf），在 [mysqld] 部分添加以下行：

     [mysqld]
     sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
     

     然后重启 MySQL 服务：

     sudo systemctl restart mysqld
     

   • 临时禁用 ONLY_FULL_GROUP_BY

     你可以通过命令行登录到 MySQL，然后执行以下命令：

     SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
     

完整步骤

1. 登录到 MySQL

   mysql -u your_username -p
   

2. 检查当前 SQL 模式

   SELECT @@sql_mode;
   

3. 禁用 ONLY_FULL_GROUP_BY

   临时禁用：

   SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
   

   或者，永久禁用，通过编辑配置文件并重启 MySQL 服务：

   sudo nano /etc/my.cnf
   

   添加或修改以下内容：

   [mysqld]
   sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
   

   保存并关闭文件，然后重启 MySQL：

   sudo systemctl restart mysqld
   

检查修改后的配置

再次登录 MySQL 并确认 ONLY_FULL_GROUP_BY 已被禁用：

SELECT @@sql_mode;

以上是解决 SQLSyntaxErrorException 以及 ONLY_FULL_GROUP_BY 模式导致的问题的方法。