开发中遇到SQL IN传入参数的个数超过2100的bug

在SQL Server或者MySQL中，当你在IN子句中需要处理的结果集可能超过2100个元素时，由于SQL Server对IN子句中的参数数量存在大约2100个左右的限制，直接使用IN会导致错误。为了解决这个问题，可以采取以下策略：

1、将参数拆分，分批次查询出结果然后合并

2、建立临时表批量插入IN参数 join 或者 exists 查询后删除掉

1.创建临时表

create table #temp(columnname1   type,columnname2 type)

2.为临时表插入数据

insert into #temp(columnname1,columnname1)

values(?,?)

3.注意事项:在使用临时表的时候自己写分页，如果使用mybatis自带的page对象进行分页会报错:SQL语句中创建临时表create附近有错

4.开发中遇到问题:

List<String>=dto.getGrids().split(",");

原来的SQL

<select id="queryPageInfo" resultType="UserInfo">

select A.name,A.adress,A.createtime createTime from UserInfo A

<if dto.getGrids!=null && dto.getGrids!=''>

A.grid in (

<foreach collect=dto.gridValues,seperator=',',item=date>

(#{data})

<\foreach>

</if>

)

</select>

dto.getGrids传入的参数过多报错:

在SQL Serve中， Server对IN子句中的参数数量超过2100个左右的限制，请重新传入参数

解决方法:

第一步:Mapper.xml中SQL修改

<sql id="queryInfo>

<if dto.gridValues!=null && dto.GridValues!=''>

create table #temp(grid varchar (20))

insert into #temp(grid)

values (

<foreach collect=dto.gridValues,seperator=',',item=data>

(#{data})

<\foreach>

)

</if>

</sql>

<select id="queryPageInfo" resultType="UserInfo">

<if dto.gridValues!=null && dto.gridValues!=''>

<include ref="queryInfo">

</if>

select A.name,A.adress,A.createtime createTime from UserInfo A

<if dto.gridValues!=null && dto.gridValues!=''>

A.grid=#temp.grid

</if>

</select>

第二步:手动分页

public List <UserInfo> queryPageInfo(UserInfoDto dto,Page page){

List <UserInfo> list=new ArrayList <>;

List<String> gridsList=dto.getGrids.split(",");

Integer  num=1000;

Integer size=gridsList.size();

//关键代码解决 SQL in参数个数超过2100错误

if(size>1000){

dto.setGrids(null);

//n代表数据需要分几次查询

Integer n=size/num==0?size/num:size/num+1;

for (int j=0;j<n;j++){

Integer start=j*num;

Integer end=start+n*1000>size?size:start+n*1000;

dto.setGridValues(list.subList(start,end));

list.addAll(userMapper.queryPageInfo(dto,null);

//创建时间降序

list.sort(Comparator.comparing(UserInfer::getCreateTime).reverse());

//手动分页

Integer offset=(page.getCurrentPag()-1)*page.getSize();

list=list.stream.skip(offset).list(page.getSize()).collect(Collectors.toList());

return list;

}