sql_mode之only_full_group_by

发表于

起因

最近将一个项目的MySQL升级到5.7，出现类似下面的错误：

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘yourdb.yourtable.yourfield’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

说明

这个是由于MySQL在5.7版本中添加了一个sql_mode: ONLY_FULL_GROUP_BY，当配置了此sql_mode后，select语句中要查询的字段必须严格是group by语句中的字段或者是聚合函数。

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.20    |
+-----------+
# 创建数据库
mysql> create database sql_test charset utf8mb4;
# 创建user表
mysql> create table user (id int(11) unsigned auto_increment primary key, account varchar(50));
# 创建order表
mysql> create table user_order (id int(11) unsigned auto_increment primary key, user_id int(11) unsigned, product_id int(11), order_money float);
# 插入测试数据
mysql> insert into user (account) values ('zhangsan'), ('lisi'), ('wangwu'), ('zhaoliu');
mysql> insert into user_order(user_id, product_id, order_money) values (1, 1, 1), (1, 1, 2), (2, 2, 5);
# 测试查询
mysql> select b.user_id, a.account, sum(b.order_money) as total_money from user_order b left join user a on b.user_id = a.id group by a.id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sql_test.b.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决

修改sql

将需要查询的列加入到group by语句中。

1
2
3
4
5
6
7
mysql> select b.user_id, a.account, sum(b.order_money) as total_money from user_order b left join user a on b.user_id = a.id group by b.user_id, a.account;
+---------+----------+-------------+
| user_id | account  | total_money |
+---------+----------+-------------+
|       1 | zhangsan |           3 |
|       2 | lisi     |           5 |
+---------+----------+-------------+

去除sql_mode中的ONLY_FULL_GROUP_BY

首先查询当前的sql_mode，分为全局的和当前session的。

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> 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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

mysql> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

然后对sql_mode进行修改

1
2
mysql> set global sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

再执行初始的查询sql语句：

1
2
3
4
5
6
7
mysql> select b.user_id, a.account, sum(b.order_money) as total_money from user_order b left join user a on b.user_id = a.id group by a.id, a.account;
+---------+----------+-------------+
| user_id | account  | total_money |
+---------+----------+-------------+
|       1 | zhangsan |           3 |
|       2 | lisi     |           5 |
+---------+----------+-------------+

参考文章：