mbatis中mysql数据库使用in条件，个数超过1000报错解决方法

淡淡的烟草味﹌ 2024-03-22 12:05 50阅读 0赞

##### springboot-mbatis中mysql数据库使用in条件，个数超过1000报错解决方法 #####

> 在项目中使用mysql数据库，用到in的查询条件，个数过多的解决方案。例如sql如下：
> 
> select \* from table where id in (1,2,……10000)，in里面个数超过1000就会报错

###### 解决方案一：把in里面的个数分组，多次查询。最好先排序，能减少查询的时间（有索引的情况下） ######

List<Long> newAllReceiveIdList = allReceiveIdList.stream().sorted().distinct().collect(Collectors.toList());
    List<List<Long>> partitionReceiveIdList = Lists.partition(newAllReceiveIdList, 800);
    List<ReceiveBill> receiveBillList = new ArrayList<>();
    for (List<Long> singlePartitionReceiveIdList : partitionReceiveIdList) {
        
        if(CollUtil.isNotEmpty(singlePartitionReceiveIdList)){
        
            LambdaQueryWrapper<ReceiveBill> receiveBillLambdaQueryWrapper = new LambdaQueryWrapper<ReceiveBill>()
                .in(ReceiveBill::getId, singlePartitionReceiveIdList)
                .eq(ReceiveBill::getDeleted, BoolEnum.NO.getKey());
            List<ReceiveBill> billList = receiveBillService.list(receiveBillLambdaQueryWrapper);
            receiveBillList.addAll(billList);
        }
    }

###### 解决方案二：直接修改sql，用or隔开 ######

> 改成类似以下sql：select \* from table where id in (1,2,……999）or (1000,1001,…1888)
> 
> 因为项目中使用了mybatis框架，每个值都是用’,'分隔符隔开，导致最后会多一个逗号,所以需要加上null
> 
> select \* from table where id in (1,2,……998,null）or (998,1000,1001,…1888)

<if test="bo.receiveIds != null and bo.receiveIds.length != 0">
        and (
        f.id in
        <foreach item='receiveId' index='index' collection='bo.receiveIds' open='(' separator=',' close=')'>
            <if test = 'index%999== 998'>
                null ) or f.id in (
            </if>
            #{receiveId}
        </foreach>
        )
    </if>