mysql in查询 结果乱序 引发的思考
Mysql in查询 结果集 乱序
SQL: select * from table where id IN (3,6,9,1,2,5,8,7);
这样的情况取出来后,其实,id还是按1,2,3,4,5,6,7,8,9,排序的,但如果我们真要按IN里面的顺序排序怎么办?SQL能不能完成?是否需要取回来后再foreach一下?其实mysql就有这个方法
sql: select * from table where id IN (3,6,9,1,2,5,8,7) order by field(id,3,6,9,1,2,5,8,7);
出来的顺序就是指定的顺序了。。。。
但是这么写 explain select * from table where id IN (3,6,9,1,2,5,8,7) order by field(id,3,6,9,1,2,5,8,7);
会Using filesort 导致查询效率降低
所以可以这样 把结果集查询出来 自己sort
List<Employee> employees = new ArrayList<>();
employees.add(new Employee(123, "Jack", "Johnson", LocalDate.of(1988, Month.APRIL, 12)));
employees.add(new Employee(345, "Cindy", "Bower", LocalDate.of(2011, Month.DECEMBER, 15)));
employees.add(new Employee(567, "Perry", "Node", LocalDate.of(2005, Month.JUNE, 07)));
employees.add(new Employee(467, "Pam", "Krauss", LocalDate.of(2005, Month.JUNE, 07)));
employees.add(new Employee(435, "Fred", "Shak", LocalDate.of(1988, Month.APRIL, 17)));
employees.add(new Employee(678, "Ann", "Lee", LocalDate.of(2007, Month.APRIL, 12)));
employees=employees.stream().sorted((e1, e2) -> e1.getHireDate().compareTo(e2.getHireDate())).collect(Collectors.toList());
接着:
描述下问题的由来
存在条件:
1. 有一个合辑表 A, 有一个内容表 C. C表中有一个列a_id 关联两个表
2. A表中有3000条数据 C表中有2w条数据 合辑表中有的有内容 有的没有内容
需求:
1. 分页显示合辑列表, 每个合辑有个ContentSize字段 表示合辑的内容数量.
解析步骤:
分两次查询: 第一次查分页. 第二次查合辑的内容数量.
1.分页sql 忽略.
2.统计list<合辑id> 合辑id列表中的 每合辑对应的内容数量
当时想到了第一种方式 连接查询 count in group by order by id的顺序 (次方法有两点不好1. left join 连表 效率低 2. 结果集顺序 Using filesort 效率低)
@Query(value = "select count(c.id) from abm_album a left join abm_album_content c on a.id=c.album_id and c.is_deleted =:isDeleted where a.id in (:albumIds) group by a.id order by field(a.id,:albumIds) ",nativeQuery = true)
public List<BigInteger> countByAlbumIdsAndIsDeleted(@Param("isDeleted")Integer isDeleted,@Param("albumIds")List<Integer> albumIds);---------------------------方式一:解析SQL如下-------------------------select
count(c.id)
from
abm_album a left join abm_album_content c
on a.id=c.album_id and c.is_deleted =0
where
a.id in (5138822,5160757,5000142,5160750,5159885)
group by a.id
order by field(a.id,5138822,5160757,5000142,5160750,5159885)@Query(value = "select c.album_id ,count(c.id) from abm_album_content c where c.is_deleted =:isDeleted and c.album_id in (:albumIds) group by c.album_id ",nativeQuery = true)
public List<Object[]> countMapByAlbumIdsAndIsDeleted(@Param("isDeleted")Integer isDeleted,@Param("albumIds")List<Integer> albumIds);---------------------------方式二:解析SQL如下-------------------------select
c.album_id , count(c.id)
from abm_album_content c
where
c.is_deleted =0
and c.album_id in (5138822,5160757,5000142,5160750,5159885 )
group by c.album_id---------------------------查询结果处理-------------------------List<Album> datas = albumRepository.find(album, page.getStart(), page.getPageSize(), sort);
if(null!=datas && datas.size()>0){
//设置稿件数量
try {
List<Integer> ids= datas.stream().map(Album::getId).collect(Collectors.toList());
// 方式一 list查询 in带顺序 然后顺序set sql关联查询 执行效率低
/*
List<BigInteger> bigIntegers = albumContentRepository.countByAlbumIdsAndIsDeleted(AlbumConstant.UNDELETED, ids);
if(datas.size()==ids.size()&&datas.size()==bigIntegers.size()){
for (int i=0;i<datas.size();i++){
datas.get(i).setContentsCount(bigIntegers.get(i).intValue());
}
}
*/
// 方式二 执行单表count 返回值list<Object[]> 循环设置 效率高
List<Object[]> maps = albumContentRepository.countMapByAlbumIdsAndIsDeleted(AlbumConstant.UNDELETED, ids);
Map<Integer,BigInteger> countMap=new HashMap<>();
maps.forEach(map->countMap.put((Integer)map[0],(BigInteger)map[1]));
datas.forEach(data-> {
if(null!=countMap.get(data.getId())){
data.setContentsCount(countMap.get(data.getId()).intValue());
}else {
data.setContentsCount(0);
}
});
}catch (Exception e){
log.error("查询合辑下稿件数量出错",e);
}
}
//set结果集
page.setDatas(datas);
总结:
1. in 查询 返回的结果集 如果有数据不存在的时候 返回结果集的个数 和合辑id_list的size 是不一样的.
2. 能单表查询 尽量不要链表查询
OK. 到此结束 小记...
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决:字节Trae VS Cursor,谁才是开发者新宠?
· 开源Multi-agent AI智能体框架aevatar.ai,欢迎大家贡献代码
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南