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语句中的字段或者是聚合函数。
测试
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语句中。
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的。
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进行修改
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语句：
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 |
+---------+----------+-------------+参考文章：
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
腾讯云开发者
