前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >sql_mode之only_full_group_by

sql_mode之only_full_group_by

作者头像
AsiaYe
发布2019-11-06 17:22:28
3.7K0
发布2019-11-06 17:22:28
举报
文章被收录于专栏:DBA随笔

sql_mode之only_full_group_by

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

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

代码语言:javascript
复制
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中是不被允许的。

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

代码语言:javascript
复制
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来查看是否可以通过:

代码语言:javascript
复制
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呢?

代码语言:javascript
复制
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的情况:

代码语言:javascript
复制
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的组合呢?

代码语言:javascript
复制
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试试:

代码语言:javascript
复制
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的情况,先切换到这种非严格模式:

代码语言:javascript
复制
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语句的健壮性。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-06-27,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA随笔 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档