MySQL ONLY_FULL_GROUP_BY

MySQL ONLY_FULL_GROUP_BY

场景

在开发环境中遇到一个报错：

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

报错信息基本说明了是因为你的SQL与SQL Mode不兼容，其中关键是sql_mode=only_full_group_by。

为什么会报错？

MySQL 版本默认启用了only_full_group_by并且SQL并没有遵循SQL-92标准的话，MySQL 5.7.5及以后版本会检测这个场景，如果only_full_group_by SQL模式被启用，那么MySQL会直接报错。

• only_full_group_by这个SQL Mode在MySQL中默认是启用的，可以使用 select @@global.sql_mode;来查询MySQL的SQL Mode，只有返回的结果中有only_full_group_by就表示被启用。

什么是only_full_group_by?

在SQL-92或者更早的标准中，SELECT语句有group by从句时，如果having条件或者order by的字段列表包含有group by 从句中没有的条件，那么SQL执行就会报错。
在SQL 1999标准则允许group by从中没有的条件在select列表中出现。
例如：

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;
# 根据SQL-92标准，SELECT列表中的username必须在group by 从句中
# 根据SQL 1999标准，上面这个SQL语句是合法的。

复现

MySQL 版本： 8.0

表结构

表DDL语句如下：

CREATE TABLE `user_balance` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `username` varchar(32) NOT NULL COMMENT '用户名',
  `balance` bigint NOT NULL COMMENT '余额, 单位：分',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = '用户余额';

-- 插入6条记录
INSERT INTO `user_balance` (`user_id`, `username`, `balance`) VALUES (1, '张三', 1);
INSERT INTO `user_balance` (`user_id`, `username`, `balance`) VALUES (2, '李四', 1);
INSERT INTO `user_balance` (`user_id`, `username`, `balance`) VALUES (3, '王五', 1);
INSERT INTO `user_balance` (`user_id`, `username`, `balance`) VALUES (4, '张大麻子', 1);
INSERT INTO `user_balance` (`user_id`, `username`, `balance`) VALUES (5, '李二麻子', 1);
INSERT INTO `user_balance` (`user_id`, `username`, `balance`) VALUES (6, '王三麻子', 1);

错误复现

select user_id, username, sum(balance) from user_balance group by user_id;

错误输出：

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fullgroupby.user_balance.username' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

怎么解决？

方式1-修改全局变量

这种方式在数据库重启后会失效，所以使用这种方式在重启数据库后需要再次执行更改sql_mode。
查询当前sql_mode，然后删除返回sql_mode字符串中的only_full_group_by,再设置sql_mode全局变量。

select @@global.sql_mode;

+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

将查询出来的sql_mode复制出来，删除掉，然后执行

# 
set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ';
Query OK, 0 rows affected (0.00 sec)

方式2-修改my.cnf

在my.cnf文件的[mysqld]模块下新增sql_mode配置

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION '

方式3-使用any_value()函数

如果你不想修改sql_mode，但是又想使用到SQL 1999标准， 那么就使用any_value()函数。MySQL提供了any_value函数用来告诉MySQL不要拒绝执行这类SQL。

select user_id, any_value(username), sum(balance) from user_balance group by user_id;
+---------+---------------------+--------------+
| user_id | any_value(username) | sum(balance) |
+---------+---------------------+--------------+
|       1 | ??                  |            1 |
|       2 | ??                  |            1 |
|       3 | ??                  |            1 |
|       4 | ????                |            1 |
|       5 | ????                |            1 |
|       6 | ????                |            1 |
+---------+---------------------+--------------+
6 rows in set (0.00 sec)