内容目录
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)