专栏首页DBA随笔sql_mode之only_full_group_by

sql_mode之only_full_group_by

sql_mode之only_full_group_by

今天上班的时候,业务方问了我这样一个问题:能不能把线上的sql_mode值改为和测试环境一致?因为我们在测试环境上写的sql在线上可能会出错,原因是线上的环境设置了sql_mode=only_full_group_by。

听到这个需求的时候,我还是很震惊的,这个线上的环境竟然还有人想修改,而且是往更加宽松的程度上去修改,这我肯定不能同意啊。不过也可以理解他们,他们可能没有意识到这个参数的作用,只是从自己的角度出发,想着这样能够解决他们的问题。后面我了解了一下情况,大概的情况就是他们写了一个sql,使用了group by,然后group by里面的字段只有一个,但是select语句中查询了多个字段,导致MySQL报了类似下面的错误:

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

大概就是说这个select语句中的字段了group by中的字段不一致,这在MySQL中是不被允许的。

了解了需求之后,我大概举了一个例子,对这种情况进行了一个测试,且看如下例子:

mysql--dba_admin@127.0.0.1:yeyztest 22:15:04>>select * from test;
+----+------+-------+
| id | age  | score |
+----+------+-------+
|  1 |    1 |     5 |
|  2 |    2 |    10 |
|  5 |    5 |    25 |
|  8 |    8 |    40 |
|  9 |    2 |    45 |
| 10 |    5 |    50 |
| 11 |    8 |    55 |
+----+------+-------+
7 rows in set (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyztest 22:15:13>>select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

这个例子中创建了一个test的表,其中id是主键,其他两个字段是普通的字段,然后sql_mode包含了only_full_group_by,然后我们开始写SQL来查看是否可以通过:

mysql--dba_admin@127.0.0.1:yeyztest 22:15:21>>select * from test group by age;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'yeyztest.test.id' which is not functionally dependent on columns in
 GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

可以看到,这个问题复现了,我们使用了select *的语法,然后最后group by一个age字段,这样的话,id和score字段没有用到,提示我们的是不可以执行,因为前后字段数量不一致。那么如果我们使用三个字段做group by呢?

mysql--dba_admin@127.0.0.1:yeyztest 22:16:10>>select * from test group by age,score,id;
+----+------+-------+
| id | age  | score |
+----+------+-------+
|  1 |    1 |     5 |
|  2 |    2 |    10 |
|  9 |    2 |    45 |
|  5 |    5 |    25 |
| 10 |    5 |    50 |
|  8 |    8 |    40 |
| 11 |    8 |    55 |
+----+------+-------+
7 rows in set (0.00 sec)

可以看到,如果使用了group by三个字段,那么这个结果是可靠的,没有报错误,而且是按照group by最近的一个字段age来进行的分组,再来看单独使用id的情况以及使用id和score的情况:

mysql--dba_admin@127.0.0.1:yeyztest 22:16:25>>select * from test group by id;
+----+------+-------+
| id | age  | score |
+----+------+-------+
|  1 |    1 |     5 |
|  2 |    2 |    10 |
|  5 |    5 |    25 |
|  8 |    8 |    40 |
|  9 |    2 |    45 |
| 10 |    5 |    50 |
| 11 |    8 |    55 |
+----+------+-------+
7 rows in set (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyztest 22:17:05>>select * from test group by score,id;
+----+------+-------+
| id | age  | score |
+----+------+-------+
|  1 |    1 |     5 |
|  2 |    2 |    10 |
|  5 |    5 |    25 |
|  8 |    8 |    40 |
|  9 |    2 |    45 |
| 10 |    5 |    50 |
| 11 |    8 |    55 |
+----+------+-------+
7 rows in set (0.00 sec)

我们发现,都可以执行成功。虽然前面使用了select *后面的group by只使用了部分字段,但是依旧可以成功,没有报错,这里我就有一些疑问了,为什么这个也是部分字段但是却没有报错,那么如果我不使用id,使用age和score的组合呢?

mysql--dba_admin@127.0.0.1:yeyztest 22:17:08>>select * from test group by score,age;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
 'yeyztest.test.id' which is not functionally dependent on columns in
 GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我们可以发现,单独使用这两个字段age和score进行group by,那么结果一定是不可靠的,因为会报错。问题到底出在那里呢?把select * 改成age和score试试:

mysql--dba_admin@127.0.0.1:yeyztest 22:19:02>>select age,score from test group by age,score ;
+------+-------+
| age  | score |
+------+-------+
|    1 |     5 |
|    2 |    10 |
|    2 |    45 |
|    5 |    25 |
|    5 |    50 |
|    8 |    40 |
|    8 |    55 |
+------+-------+
7 rows in set (0.00 sec)

发现这样是可以的,当然,这样符合要求,就是group by后面的字段和select 的字段一致。

以上都是有这个参数的情况,再来看看没有only_full_group_by的情况,先切换到这种非严格模式:

mysql--dba_admin@127.0.0.1:yeyztest 22:17:35>>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';
Query OK, 0 rows affected (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyztest 22:17:54>>select * from test group by score,age;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yeyztest.test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql--dba_admin@127.0.0.1:yeyztest 22:17:58>>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';
Query OK, 0 rows affected (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyztest 22:18:20>>select * from test group by score,age;
+----+------+-------+
| id | age  | score |
+----+------+-------+
|  1 |    1 |     5 |
|  2 |    2 |    10 |
|  5 |    5 |    25 |
|  8 |    8 |    40 |
|  9 |    2 |    45 |
| 10 |    5 |    50 |
| 11 |    8 |    55 |
+----+------+-------+
7 rows in set (0.00 sec)

上面两个测试,仅仅是set global和set session的区别,可以看到,当我们set global的时候,这个sql_mode相当于还是以前的,要使他生效,需要重新连接进来,也就是使用新会话,否则不会生效,而我们使用了set session之后,当前回话的sql_mode就直接被改掉了。也就支持select和group by的前后字段不一致了。

经过我们上面这么多实验,可以得到下面的结论:

1、当我们的sql_mode使用了only_full_group_by的时候,如果要想实现select的字段和group by的字段数量不相同而查询语句不出错,那么我们必须在group by后面添加主键字段,否则一定会出错。

2、当我们去掉sql_mode的only_full_group_by的时候,之所以前后字段数量不一致还能查询成功,实际上是mysql为我们进行了补齐。这种情况下我们不必保持前后字段数量一致,但是需要知道,这是一种不好的习惯,在我们日常写sql的时候,还是尽量要在严格模式下面写,这样mysql会为我们进行校验,如果一旦不合适,就会抛出错误,有助于提升你sql语句的健壮性。

本文分享自微信公众号 - DBA随笔(gh_acc2bbc0d447),作者:AsiaYe

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-06-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • innodb之事务隔离级别示例

    下面我们用真实的例子来说明各个级别的情况,首先我们创建一个数据库test,然后再数据库中创建一个表city,在这个city表中来进行测试:

    AsiaYe
  • Python中的参数解析argparse用法

    在使用python写脚本的时候,我们经常会用到命令行解析包argparse,我们引入这个包之后,就可以对传入python的参数进行解析,得到我们想要的结果...

    AsiaYe
  • group by如何优化?

    今天分享的内容是MySQL里面的group by语句,部分案例节选自极客时间的《MySQL45讲》,大家有兴趣可以购买相应课程进行学习,废话就不多说了,直接从例...

    AsiaYe
  • Mysql常用sql语句(12)- group by 分组查询

    https://www.cnblogs.com/poloyy/category/1683347.html

    小菠萝测试笔记
  • mysql查找出现出现两次的项半路遇到迪威贵宾会I8Io883oo

    mysql> select a, count(*) from x group by a;

    用户5782834
  • 图片预加载

    达达前端
  • 如何实现高性能的在线 PDF 预览

    最近接到产品需求,用户需要在我们的站点上在线查看 PDF 文件,并且查看时,用户可以对 PDF 文件的进行旋转、缩放、跳转到指定页码等操作。

    政采云前端团队
  • 二手车交易市场火爆空前,业绩下滑!鸿海决心进军大数据领域 | 大数据24小时

    数据猿导读 二手车电商优信获5亿美元融资,利用互联网和大数据重点发力B端车商;收购夏普反倒使业绩下滑,鸿海决心发力大数据领域;大数据移动互联网服务商点春科技新三...

    数据猿
  • Weex学习笔记

    我是按照http://alibaba.github.io/weex/doc/demo/modal.html 这个网址的内容进行weex学习的,这只是笔记。

    提莫队长
  • iOS 复制知识点总结

    Dwyane

扫码关注云+社区

领取腾讯云代金券