小白学习MySQL - only_full_group_by的校验规则

bisal的个人杂货铺 2021-08-02

967

周末一位Java大神探讨了一个MySQL的问题，为了形象地说明，我们创建测试表，并插入测试数据，

create table t1 (
  t1_id int,
  t1_date  varchar(100),
  t1_month  varchar(100), 
  t1_prd varchar(100)
);

INSERT INTO t1 VALUES 
(10010000,'20210725','202106','01'),
(10010000,'20210725','202107','01'),
(10020000,'20210725','202106','01'),
(10020000,'20210725','202107','01'),
(10030000,'20210725','202106','01'),
(10030000,'20210725','202107','01');

create table t2 (
  t2_id int unsigned,
  t2_prd varchar(100), 
  t2_date varchar(100), 
  t2_org_id varchar(100), 
  t2_port varchar(100)
);

INSERT INTO t2 VALUES 
(1000566201,'1','20210725','10010000','A1022'),
(1000566202,'1','20210725','10020000','A1022'),
(1000566203,'1','20210725','10030000','A1022');

两张表的数据，如下所示，

select * from t1;
t1_id   |t1_date |t1_month|t1_prd|
--------|--------|--------|------|
10010000|20210725|202106  |01    |
10010000|20210725|202107  |01    |
10020000|20210725|202106  |01    |
10020000|20210725|202107  |01    |
10030000|20210725|202106  |01    |
10030000|20210725|202107  |01    |

select * from t2;
t2_id     |t2_prd|t2_date |t2_org_id|t2_port|
----------|------|--------|---------|-------|
1000566201|1     |20210725|10010000 |A1022  |
1000566202|1     |20210725|10020000 |A1022  |
1000566203|1     |20210725|10030000 |A1022  |

原始SQL结果如下，SQL的select字段是t2.t2_date、t2.t2_org_id、t1.t1_month，其中t2.t2_date是1个值，t2.t2_org_id是3个值，t1.t1_month是2个值(对应每个t2.t2_date和t2.t2_org_id都是2个值)，因此需求的结果集应该是6条记录。但是执行结果，只得到3条记录，

select t2.t2_date, t2.t2_org_id, t1.t1_month
from t1, t2
where t1.t1_date=t2.t2_date and t1.t1_id = t2.t2_org_id and t2.t2_port='A1022'
group by t2.t2_date,t2.t2_org_id,t2.t2_port;

t2_date |t2_org_id|t1_month|
--------|---------|--------|
20210725|10010000 |202106  |
20210725|10020000 |202106  |
20210725|10030000 |202106  |

单从这条SQL，group by中的字段分别是t2.t2_date、t2.t2_org_id、t2.t2_port，根据如上的t2表的数据，能看到t2.t2_date是1个值，t2.t2_org_id是3个值，t2.t2_port是1个值，因此得到3条记录是合理的。但是请注意到，这条SQL的select字段和group by字段不是相同的，select的t1.t1_month没在group by中，就是说需求是select字段描述的，但是实际执行则是通过group by得到的，两者实际不同，因此最直接的，就是将t1.t1_month加到group by子句中，此时才是所需的，

select t2.t2_date, t2.t2_org_id, t1.t1_month
from t1, t2
where t1.t1_date=t2.t2_date and t1.t1_id = t2.t2_org_id and t2.t2_port='A1022'
group by t2.t2_date,t2.t2_org_id,t1.t1_month,t2.t2_port;

t2_date |t2_org_id|t1_month|
--------|---------|--------|
20210725|10010000 |202106  |
20210725|10010000 |202107  |
20210725|10020000 |202106  |
20210725|10020000 |202107  |
20210725|10030000 |202106  |
20210725|10030000 |202107  |

可能有朋友会提出这个问题，如果这种select和group by字段不同的SQL在Oracle中跑一定是报错，提示如下，即要求在select中的非聚合列必须出现在group by子句中，为什么在MySQL中就可以执行？

ORA-00979: not a GROUP BY expression

其实这和一个校验规则相关，即only_full_group_by，该规则的核心原则如下，没有遵循原则的SQL会被认为是不合法的，

1. order by后面的列必须是在select后面存在的。

2. select、having或order by后面存在的非聚合列必须全部出现在group by子句中。

这里要提到的一个参数就是sql_mode，他存储的是MySQL应该支持的SQL语法，对数据的校验等，一些常见的值，如下所示，

(1) ONLY_FULL_GROUP_BY

对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的，因为列不在GROUP BY从句中。

(2) STRICT_TRANS_TABLES

在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制。

(3) NO_ZERO_IN_DATE

在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。

(4) NO_ZERO_DATE

在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告。

(5) ERROR_FOR_DIVISION_BY_ZERO

在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X, 0))，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL。

(6) NO_AUTO_CREATE_USER

防止GRANT自动创建新用户，除非还指定了密码。

(7) NO_ENGINE_SUBSTITUTION

如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常。

在MySQL 5.7以上，sql_mode参数值是默认包含这个only_full_group_by校验规则的，但是在5.7以下，不包含此规则，如上的SQL，就是跑在5.5，

show variables like '%sql_mode%';

Variable_name|Value                                                                                                                                    |
-------------|-----------------------------------------------------------------------------------------------------------------------------------------|
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 5.7创建一张测试表，

create table test(c1 varchar(1), c2 int, c3 varchar(1));

我们知道sql_mode默认包含规则only_full_group_by，执行如下SQL，会提示错误，group by中未包含非聚合列是c1，而且明确提示，他和sql_mode=only_full_group_by不兼容，

select c1, c2 from test group by c2;
SQL 错误 [1055] [42000]: Expression 
    #1 of SELECT list is not in GROUP BY clause 
    and contains nonaggregated column 'bisal.test.c1' 
    which is not functionally dependent on columns 
    in GROUP BY clause; 
    this is incompatible with sql_mode=only_full_group_by

我们删除sql_mode的only_full_group_by，

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，此时就可以了，

select c1, c2 from test group by c2;
c1|c2|
--|--|

其实原始SQL如果支持规则only_full_group_by，或许可能不会出现这种错误结果集的情况，因为就根本不让执行这种SQL，如果支持这种group by子句中未包含非聚合列的SQL，就需要写SQL的同学能保证select和group by的一致性，否则执行的SQL很可能得到的就是错误的结果集，从MySQL 5.7开始就默认支持校验规则only_full_group_by能看出，MySQL提高了校验强度，毕竟更严格的校验规则，才可能避免这些非常容易出现的错误。

小白学习MySQL，

《小白学习MySQL - max_allowed_packet》

《小白学习MySQL - mysqldump保证数据一致性的参数差异》

《小白学习MySQL - 查询会锁表？》

《小白学习MySQL - 索引键长度限制的问题》

《小白学习MySQL - MySQL会不会受到“高水位”的影响？》

《小白学习MySQL - 数据库软件和初始化安装》

《小白学习MySQL - 闲聊聊》

近期更新的文章：

《PG逻辑复制的REPLICA IDENTITY设置》

《最近碰到的几个问题》

《Linux的dd指令》