技术分享 | MySQL 分组需求探秘

作者:刘晨

网名 bisal ,具有十年以上的应用运维工作经验,目前主要从事数据库应用研发能力提升方面的工作,Oracle ACE ,拥有 Oracle OCM & OCP 、EXIN DevOps Master 、SCJP 等国际认证,国内首批 Oracle YEP 成员,OCMU 成员,《DevOps 最佳实践》中文译者之一,CSDN & ITPub 专家博主,公众号"bisal的个人杂货铺",长期坚持分享技术文章,多次在线上和线下分享技术主题。

本文来源:原创投稿

*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


前两天同事有个 MySQL 数据分组的需求,如下测试数据,需要找出每个 name 分组中 create_date 最近的记录:

需要注意的是,此处用的 MySQL 是5.6,最初是使用这条语句:

select name, value, create_date, update_date from t1 group by name order by create_date desc;

查询结果如下,看着好像是对的,但是仔细看下,就会发现其中的问题,例如 name=a 最近的 create_date 应该是 value=3 的记录,name=d 最近的create_date应该是 value=10 的记录:

用这条 SQL 得到的其实只是每个 name 分组中最先插入的记录,然后按照 create_date 进行了降序排列,和原始需求,完全不同。

此时可采用分而治之的策略,先做排序,再做分组:

select * from (select name, value, create_date, update_date from t1 order by create_date desc) t group by t.name;

即可得到原始需求的数据:

当然,针对此需求,可能有其他方法,有兴趣的朋友,可以尝试写写,共享一下。

可能有细心的朋友会发现个问题,就是上述 SQL 中的 group by ,好像有些奇怪,如果按照常规,select 中的字段需要出现在 group by 中,上述语句竟然没报错?

如果我们在 MySQL 5.7 执行相同的语句:

select name, value, create_date, update_date from t1 group by name order by create_date desc; 

就会提示这个错:

如果是在 Oracle ,则会提示这个:

难道这是 MySQL 5.6 的特性?搜了一下,其实因为这个,在这套5.6的环境中,sql_mode 参数如下:

而在5.7中,sql_mode 参数如下,多了一个 ONLY_FULL_GROUP_BY ,他的意思是“出现在 SELECT 语句、HAVING 条件和 ORDER BY 语句中的列,必须是 GROUP BY 的列或者依赖于 GROUP BY 列的函数列”,这就不难解释,为什么5.6和5.7中现象是不同的,还是因为5.7默认对 SQL 格式校验的要求高了:

因此从5.6升级到5.7,很可能出现这种相同的 SQL 执行结果不同的现象,这对兼容性测试的要求就会很高,究其原因,一方面是特性决定的,另一方面就是各种配置参数不同导致的。

可以在5.7的 sql_mode 中删除这个 ONLY_FULL_GROUP_BY ,即可达到5.6相同效果了,或者改写 SQL ,例如:

select * from t1 a where create_date = (select max(create_date) from t1 b where a.name = b.name);

或者,

select * from t1 a where not exists (select * from t1 b where a.name = b.name and b.create_date > a.create_date);

MySQL 8.0支持 row_number()函数,操作应该和如下 Oracle 相近的。

Oracle 中可以使用 row_number()实现此需求:

select * from (select name, create_date, row_number() over (partition by name order by create_date desc) as r from t1) where r=1;

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提...

355 声望
178 粉丝
0 条评论
推荐阅读
OB运维 | tenant--删除租户的流程设计
作者:姚嵩不知道是地球人还是外星人,知道的可以留言告诉小编...本文来源:原创投稿*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。

爱可生云数据库阅读 174

万字长文~vue+express+mysql带你彻底搞懂项目中的权限控制(附所有源码)
所谓的权限,其实指的就是:用户是否能看到,以及是否允许其对数据进行增删改查的操作,因为现在开发项目的主流方式是前后端分离,所以整个项目的权限是后端权限控制搭配前端权限控制共同实现的

水冗水孚11阅读 1.5k

花了几个月时间把 MySQL 重新巩固了一遍,梳理了一篇几万字 “超硬核” 的保姆式学习教程!(持续更新中~)
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。

民工哥11阅读 1.1k

封面图
一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山8阅读 2.3k评论 2

初学后端,如何做好表结构设计?
这篇文章介绍了设计数据库表结构应该考虑的4个方面,还有优雅设计的6个原则,举了一个例子分享了我的设计思路,为了提高性能我们也要从多方面考虑缓存问题。

王中阳Go3阅读 789评论 2

封面图
2023最新MySQL高频面试题汇总
本文已经收录到Github仓库,该仓库包含计算机基础、Java基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等核心知识点,欢迎star~

程序员大彬3阅读 1k

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 969

封面图

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提...

355 声望
178 粉丝
宣传栏