mysql的ONLY_FULL_GROUP_BY语义 --转自http://www.wtoutiao.com/p/19dh3ec.html

执行SET GLOBAL sql_mode = ''; 把sql_mode 改成非only_full_group_by模式。验证是否生效 SELECT @@GLOBAL.sql_mode 或 SELECT @@sql_mode

STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

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常用值如下:
ONLY_FULL_GROUP_BY:

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

NO_AUTO_VALUE_ON_ZERO:

该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。

STRICT_TRANS_TABLES:

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

NO_ZERO_IN_DATE:

在严格模式下,不允许日期和月份为零

NO_ZERO_DATE:

设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO:

在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER:

禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION:

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

PIPES_AS_CONCAT:

将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似

ANSI_QUOTES:

启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符

ORACLE的sql_mode设置等同:PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

如果使用mysql,为了继续保留大家使用oracle的习惯,可以对mysql的sql_mode设置如下

在my.cnf添加如下配置
[mysqld]
sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,

ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'

ONLY_FULL_GROUP_BY是mysql提供的一个sql_mode,通过这个sql_mode来提供SQL语句合法性的检查,在mysql的sql_mode=default的情况下是非ONLY_FULL_GROUP_BY语义,也就是说一条select语句,mysql允许target list中输出的表达式是除聚集函数,group by column以外的表达式,这个表达式的值可能在经过group by操作后变成undefined,无法确定(实际上mysql的表现是分组内第一行对应列的值),例如:

mysql的ONLY_FULL_GROUP_BY语义 --转自http://www.wtoutiao.com/p/19dh3ec.html而对于语义限制都比较严谨的多家数据库,如SQLServer、Oracle、PostgreSql都不支持select target list中出现语义不明确的列,这样的语句在这些数据库中是会被报错的,所以mysql在后续的版本中出了一个修正语义,就是我们所说的ONLY_FULL_GROUP_BY语义,例如:

mysql的ONLY_FULL_GROUP_BY语义 --转自http://www.wtoutiao.com/p/19dh3ec.html这种sql_mode下,刚才通过的查询语句被server拒绝掉了!

所以ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在ONLY_FULL_GROUP_BY模式下,target list中的值要么是来自于聚集函数的结果,要么是来自于group by list中的表达式的值。但是由于表达式的表现形式非常丰富,对于程序来说,很难精确的确定一些表达式的输出结果是明确的,比如:

mysql的ONLY_FULL_GROUP_BY语义 --转自http://www.wtoutiao.com/p/19dh3ec.html在上面的查询语句中,其实c2的值也是能被唯一确定的,但是由于程序无法分析出这种复杂的关系,所以这条查询也被拒绝掉了。

我们来看下哪些语句是在mysql的ONLY_FULL_GROUP_BY模式下是被支持的。

mysql的ONLY_FULL_GROUP_BY语义 --转自http://www.wtoutiao.com/p/19dh3ec.html这条query满足ONLY_FULL_GROUP_BY的限制:

target list中的max(c1+1), count(c1), min(c1)引用到的basic column都是出现在聚集函数中,c2出现在group by list中。

mysql> select c1, max(c2) from t1 group by c2;

ERROR 1055 (42000): 'yuming.t1.c1' isn't in GROUP BY

这条query被server拒绝掉了,因为target list中的c1没有出现在聚集函数中,并且也没有出现在group by list中

这条语句中c1虽然出现在了group
by list中,但是在mysql看来,group
by中的list是c1+2这个表达式整体,mysql无法分析出c1在这个表达式中的单调性,所以这条语句也被拒绝掉了。同理还有select c1
from t1 group by
c1+1;而这样的查询分析在SQLServer中被做的更好,在SQLServer中,这样的查询,SQLServer认为是合理的。

mysql的ONLY_FULL_GROUP_BY语义 --转自http://www.wtoutiao.com/p/19dh3ec.html

这条语句target list中的c1+1和group by中的c1+1是严格匹配的,所以mysql认为target list中的c1+1是语义明确的,因此该语句可以通过。

mysql的ONLY_FULL_GROUP_BY语义 --转自http://www.wtoutiao.com/p/19dh3ec.html

因此,如果查询语句中的target
list, having condition 或者order by list里引用了的表达式不是聚集函数,但是和group by
list中的表达式严格匹配,该语句也是合法的(c1+1和c1+1是严格匹配的,c1+1和c1+2在mysql认为是不严格匹配的,
c1+1和1+c1也是不严格匹配的)

mysql的ONLY_FULL_GROUP_BY语义 --转自http://www.wtoutiao.com/p/19dh3ec.htmlmysql的ONLY_FULL_GROUP_BY语义 --转自http://www.wtoutiao.com/p/19dh3ec.html

mysql允许target
list中对于非聚集函数的alias column被group by、having condition以及order
by语句引用(version 5.7中允许having condition引用alias column,version 5.6不支持having
condition引用alias column),从上面两条语句可以看出,group by和order by中引用了alias
column,并且其等价于基础列语义

mysql的ONLY_FULL_GROUP_BY语义 --转自http://www.wtoutiao.com/p/19dh3ec.html

从上面的语句可以看出,mysql的ONLY_FULL_GROUP_BY模式支持对basic column进行组合但是不支持对于复杂表达式进行组合,这个受限于表达式分析程度。

总结一下:

mysql对于ONLY_FULL_GROUP_BY语义的判断规则是,如果group
by list中的表达式是basic column,那么target list中允许出现表达式是group by list中basic
column或者alias column的组合结果,如果group by list中的表达式是复杂表达式(非basic
column或者alias column),那么要求target list中的表达式必须能够严格和group by
list中的表达式进行匹配,否者这条查询会被认为不合法。

由于阿里集团使用到mysql集群的业务都是默认打开了ONLY_FULL_GROUP_BY模式,所以OceanBase支持了ONLY_FULL_GROUP_BY模式,大部分的规则都是兼容mysql的行为,由于OB使用到的plan_cache策略,会对查询语句中的常量进行参数化,所以在语义检查的时候,所有常量表达式都是无法匹配的,因此在OB的ONLY_FULL_GROUP_BY模式下,含常量表达式的group
by list是非法的,例如:select c1+1 from t1 group by
c1+1,这条语句在OB的ONLY_FULL_GROUP_BY模式下会被拒绝掉,如果用于有group by常量的需求,可以使用alias
column这个特性,例如上面的语句可以改写为:select c1+1 as a from t1 group by
a这条语句在OB的ONLY_FULL_GROUP_BY模式下是被支持的。

在下一篇文章中,我将介绍一种更精确的分析算法,可以将ONLY_FULL_GROUP_BY语义下group by list的合法性检查做得更加灵活。

上一篇:Activator.CreateInstance 反射实例化对象


下一篇:使用APUE(UNIX高级编程)源代码