ORA-01795 列表中的最大表达式数为1000

特别声明

以下代码纯楼主手打，有错误地方还请谅解，看思路就行。

问题

在使用MybatisPlus过程中，报错ORA-01795 列表中的最大表达式数为1000。

业务分析

我们由一个业务场景来慢慢引出最终的问题，根据某一种条件在数据库中的user表中查询到了3000条数据，然后对这些数据统一将状态state设置成1。此时我们会想到的sql语句如下，

update user_info set state=1 where id in (?,?,?,?.....)
1

由此我们使用MP的代码应该这么写,

//假设queryIdList方法是查询出符合条件的所有Id的集合,假设3000条数据
List<Long> ids = userService.queryIdList();
//更新这些id的状态
UserInfoEntity userUpdate = new UserInfoEntity();
userUpdate.setState(1);
this.update(userUpdate,new UpdateWrapper<UserInfoEntity>().in("id",ids))；
1
2
3
4
5
6

这时候一执行，咔直接报了开头的错误,ORA-01795 列表中的最大表达式数为1000。特别注意，MySQL数据库暂时还不会，阈值在哪个值我还目前没测。原因是这个报错信息也非常明确了，就是in的后面带的参数太多了，超过了1000条，因为我们是3000条。

解决方案

因为超过了1000条，无可厚非，不管什么方案，我们先做的肯定都有一步，那就是把拿到的数据进行分组。这里楼主会用到一个很好用的工具类，分组代码也不用自己写了。只要加一个依赖com.google.guava

<dependency>
    <groupId>com.google.guavagroupId>
    <artifactId>guavaartifactId>
    <version>19.0version>
dependency>
1
2
3
4
5

分组方法

Lists类的partition方法

public static <T> List<List<T>> partition(List<T> list, int size) {
        Preconditions.checkNotNull(list);
        Preconditions.checkArgument(size > 0);
        return (List)(list instanceof RandomAccess ? new Lists.RandomAccessPartition(list, size) : new Lists.Partition(list, size));
    }
1
2
3
4
5

方案1 循环更新

既然是不许表达式数不能超过1000，那我就分组循环更新，代码如下。

//假设queryIdList方法是查询出符合条件的所有Id的集合,假设3000条数据
List<Long> ids = userService.queryIdList();
//对ids进行分组
List<List<Long>> partitionList=Lists.partition(ids,900);
partitionList.forEach(partition->{
    //更新这些id的状态
    UserInfoEntity userUpdate = new UserInfoEntity();
    userUpdate.setState(1);
    this.update(userUpdate,new UpdateWrapper<UserInfoEntity>().in("id",partition))；
})
1
2
3
4
5
6
7
8
9
10

方案2 拆分in表达式

从sql语句表达式出发，我们可以尝试把in拆成多个in，用or来连接，因为or两边如果都是索引的话，索引是不会失效的。

update user_info set state=1 where id in (?,?,?,?.....)
--改造后
update user_info set state=1 where (id in (?,?,?)) or (id in (?,?,?)))
1
2
3

• mapper.xml写法

  这个考验的是我们写Mapper.xml文件的时候的功底，是否灵活运用那几个常用标签。

  UserMapper接口

  void updateUserInfo(@Param("idss") List<List<Long>> idss);
  1

  UserMapper.xml

  
  <update id="updateUserInfo">
          UPDATE user_info 
          SET state = 1 
          WHERE
              <foreach collection="idss" open="(" close=")" separator="or" item="ids">
                  id IN
                  <foreach collection="ids" open="(" close=")" separator="," item="x">
                      #{x}
                  foreach>
              foreach>
      update>
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

  从sql我们来分析一波,要想构造update user_info set state=1 where (id in (?,?,?) or (id in (?,?,?)))，其实难点在(id in (?,?,?)) or (id in (?,?,?))),

  我们传入的参数是一个List类型的，所以第一次循环的时候,起点是(,终点是),操作符应该是那个or,然后遍历的其实是每组里的List,这里我们取个名字叫做ids,

  然后内层循环解决的就是id in (?,?,?),这就是咋们平时熟悉的foreach了，我这就不多说了，这样说应该就能看懂上面为啥这么做了吧。至此，也就能实现分开in，避免表达式数超过1000。

• 代码写法

  代码层面要使用lambda表达式写法，考验的大家的api使用熟练度，说实话楼主也是用到学，谁去天天记那么多，大不了多测几个demo嘛，大家别害怕。这里呢要用到的是QueryWrapper和UpdateWrapper的and、or方法。

  default <T> boolean newUpdate(UserEntity userEntity, UpdateWrapper<UserEntity> wrapper, String column, List<T> ids) {
          if (CollectionUtils.isEmpty(idss)) {
              //判断传递进来的ids为空，为空则不进行ids的操作
              return this.update(userEntity, wrapper);
          } else if (ids.size() <= 1000) {
              //判断传递进来的ids长度是否小于等于1000，是则用正常的in方法
              wrapper.in(column, ids);
              return this.update(userEntity, wrapper);
          }else{
              //todo 关键解决问题的代码
              //代码走到这里，说明长度超过了1000，先进行分组
              List<List<T>> partitionList = Lists.partition(ids, 900);
              //进行 (id in (?,?,?) or (id in (?,?,?)))的构造
              queryWrapper.and(x -> {
                  for (List<Long> idPartition : partitionList) {
                      x.or(y -> y.in("id", idPartition));
                  }
          	});
              return this.update(userEntity,wrapper);
          }
      }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21

  特别注意,这段代码尤其lambda表达式那里要多写and、or方法的demo，你才会熟练。这里给出几个sql例子，自己尝试用QueryWrapper去构造，如果成功了，那么你就看得懂上面的代码了.

  --eg1
  select * from 表名 where name='张三' or name='李四'
  --eg2
  select * from 表名 where name='张三' and (age=11 or age=12)
  --eg3
  select * from 表名 where (name='张三' and address='福建') or (name='李四' and address='北京')
  --eg4
  select * from 表名 where (id in (1,2,3)) or (id in (1,3,4))
  1
  2
  3
  4
  5
  6
  7
  8