近期由于公司数据库升级,遇到了一个问题,以前写的SQL语句查询结果出现了异常。
sql实现的功能:将数据以PID进行分组,在组内获取创建时间最新的数据。
项目数据比较敏感写了一些测试数据:
执行的sql语句:
select * from (select * from test order by create_time desc) as a group by a.PID order by a.create_time desc
MySQL版本5.5(未升级)
可以发现当前查询的结果是正确的。
MySQL版本5.7
查询结果出现了异常。
使用下面新的方法来执行查询:
select t.* from test t join (select subString_index(group_concat(id order by create_time desc),',',1)as id from test group by pID) tmp on t.id = tmp.id
语句拆解:
##将相同PID分组
select group_concat(id order by create_time desc) as id from test group by pID
##获取相同分组中时间最新的ID值
select subString_index(group_concat(id order by create_time desc),',',1)as id from test group by pID
思考:为啥同一条语句在不同版本执行的结果不一样?
经过比对MySQL5.7和MySQL5.5的区别发现,官网在5.7版本做了功能优化,使用了新的优化器,使的查询语句的顺序做了调整。
执行explain获取查询计划的信息:
explain(select * from (select * from test order by create_time desc) as a group by a.PID order by a.create_time desc)
5.5版本
5.7版本
经过比对发现两者在执行顺序上确实有所区别,5.5版本中首先执行的是派生表,也就是先执行内部子查询并进行排序,而5.7版本直接没进行子查询。
#5.7版本中可以发现以下两条语句执行结果一样
select * from (select * from test order by create_time desc) as a group by a.PID order by a.create_time desc
select * from test as a group by a.PID order by a.create_time desc
此时这两条语句执行的效果一样的