sql_mode中的ONLY_FULL_GROUP_BY的作用

MySQL 专栏收录该内容
61 篇文章 2 订阅

MySQL 8中的sql_mode中的默认值如下:

mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@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的配置中ONLY_FULL_GROUP_BY是开发人员最容易遇到坑,这个设置对SQL语句的要求是:出现在select字段后面的只能是group by后面的分组字段,或使用聚合函数包裹着的字段。
例如下面的SQL语句在select的字段中增加了一个不是group by后的字段后将出错:

mysql> select staff_id , max(amount) from payment group by staff_id;
+----------+-------------+
| staff_id | max(amount) |
+----------+-------------+
|        1 |       11.99 |
|        2 |       11.99 |
+----------+-------------+
2 rows in set (0.04 sec)

mysql> select rental_id, staff_id , max(amount) from payment group by staff_id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sakila.payment.rental_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> 

遇到这种错误可以修改SQL语句解决,也可以修改sql_mode,去掉其中的ONLY_FULL_GROUP_BY,例如:

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql>  select rental_id, staff_id , max(amount) from payment group by staff_id;
+-----------+----------+-------------+
| rental_id | staff_id | max(amount) |
+-----------+----------+-------------+
|        76 |        1 |       11.99 |
|      1422 |        2 |       11.99 |
+-----------+----------+-------------+
2 rows in set (0.19 sec)
  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值