mysql in 排序无数据混乱_mysql in查询结果乱序引发的思考

迈不过友情╰ 2023-01-12 04:25 112阅读 0赞

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);

出来的顺序就是指定的顺序了。。。。

写法二: select \* from table where id in (3,6,9,1,2,5,8,7) order by find\_in\_set(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  导致查询效率降低

所以可以这样  把结果集查询出来 自己sortList employees = newArrayList<>();employees.add(newEmployee(123,"Jack","Johnson",LocalDate.of(1988,Month.APRIL,12)));employees.add(newEmployee(345,"Cindy","Bower",LocalDate.of(2011,Month.DECEMBER,15)));employees.add(newEmployee(567,"Perry","Node",LocalDate.of(2005,Month.JUNE,07)));employees.add(newEmployee(467,"Pam","Krauss",LocalDate.of(2005,Month.JUNE,07)));employees.add(newEmployee(435,"Fred","Shak",LocalDate.of(1988,Month.APRIL,17)));employees.add(newEmployee(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列表中的 每合辑对应的内容数量

当时想到了第一种方式  连接查询 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)

publicList countByAlbumIdsAndIsDeleted(@Param("isDeleted")Integer isDeleted,@Param("albumIds")List 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 =0where

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)

publicList countMapByAlbumIdsAndIsDeleted(@Param("isDeleted")Integer isDeleted,@Param("albumIds")List albumIds);---------------------------方式二:解析SQL如下-------------------------select

c.album\_id ,count(c.id)

from abm\_album\_content cwhere

c.is\_deleted =0

and c.album\_id in(5138822,5160757,5000142,5160750,5159885)

group by c.album\_id---------------------------查询结果处理-------------------------List datas = albumRepository.find(album,page.getStart(),page.getPageSize(),sort);if(null!=datas && datas.size()>0)\{

//设置稿件数量 try\{

List ids= datas.stream().map(Album::getId).collect(Collectors.toList()); // 方式一 list查询 in带顺序 然后顺序set sql关联查询 执行效率低/\*List bigIntegers = albumContentRepository.countByAlbumIdsAndIsDeleted(AlbumConstant.UNDELETED, ids);if(datas.size()==ids.size()&&datas.size()==bigIntegers.size())\{for (int i=0;i 循环设置 效率高 List maps = albumContentRepository.countMapByAlbumIdsAndIsDeleted(AlbumConstant.UNDELETED,ids); Map countMap=newHashMap<>(); 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. 到此结束 小记...