mysql与oracle在groupby语句上的细节差异

前言

之所以去纠那么细节的问题,是因为之前有过一个这样的场景:

有个同学,给了一条数据库的语句给我,问,为啥这样子的语句在oracle语句下执行不了。

select * from xx where xxx is not null group by yy;

拿到这条语句的第一时间,其实,我也并没有看出是什么错。仔细一想之后才想起,在基础的SQL中包含该group by的select语句,select语句部分是有限制的,一般是聚合函数和group by 的字段。随后,我就告诉他,这条语句本身是有错的。

然而,我同学却反驳说,这条语句他在其他数据库中是能运行的。然后,就有了这篇博客

细说

为了询证,我询问了同学他说能运行的数据库是什么数据库。结果同学说是mysql。

oracle 下的执行情况
mysql 下的执行情况

如图所示,相同类型的语句,在oracle和mysql执行,一个是可以正常运行的,而在oracle上,却是没能通过语句的校验。为啥呢?

其实,这是标准与非标准的区别。 SQL 标准中不允许 SELECT 列表,HAVING 条件语句,或 ORDER BY 语句中出现 GROUP BY 中未列表的可聚合列。 因此,严格按照标准的Oracle无法执行上述语句。可是MySQL可以通过配置,选择GROUP BY的标准。

mysql> 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_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

通过sql_mode可以查询,如果MySQL的Group by 模式为ONLY_FULL_GROUP_BY,则是严格按照标准模式进行规范检测的,当将ONLY_FULL_GROUP_BY关闭之后,MySQL就允许并不算规范的语句的执行了。

关闭方式

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,’ONLY_FULL_GROUP_BY,’,”));

Leave a Comment

邮箱地址不会被公开。 必填项已用*标注