Oracle where not in条件下集合超过1000报ORA-01795错误怎么办?
🏬业务场景
公司要同步接口数据,流水有几千个,但通过计划任务进行操作时判断该比明细是否同步过,第一次同步接口表来了几千笔数据。在开发库可能数据少,检测不到,但在生产环境却报ORA-01795错了😒,原因就是这个。
📋代码
通过后台组装的id,判断流水是否存在:
<select id="queryAccounts" parameterClass="java.util.Map" resultClass="java.util.HashMap">
select *
from NC_ACCOUNT na
where 1=1
<isNotEmpty prepend="and" property="accountList">
na.ID not in
<iterate open="(" close=")" conjunction="," property="accountList">
#accountList[]#
</iterate>
</isNotEmpty>
</select>
sql形式大致为:
select *
from NC_ACCOUNT na
where 1=1
and na.ID not in ont in (A,B,C,D……)
🌱解决方案
- 📌1.从后台传值入手解决
既然超过1000会超出错误,我们可以把数据拼接分段执行,然后将查询的结果集又重新addAll数组list里。例如1010条数据,先执行999条,返回结果集1,继续执行后面的11条数据,返回结果集2,最终结果集为1.2的合并。
示例:(仅逻辑思想,不验证正确性)
//拼接数据
List<List<String>> arrAllList = new ArrayList<List<String>>();
List<String> arrList = new ArrayList<String>();
for (int i = 0; i < queryAccounts.size(); i++) {
if (i != 0 && i % 900 == 0) {
arrAllList.add(arrList);
arrList = new ArrayList<String>();
arrList.add(value);
} else {
arrList.add(value);
}
}
if (arrList.size() != 0) {
arrAllList.add(arrList);
}
//结果集处理
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
for (List<String> stringList : arrAllList) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("accountList", stringList);
logger.info("========查询接口账户信息");
List<Map<String, Object>> childList = getLocator().getAccountService().queryAccounts(map);
if (!CollectionUtils.isEmpty(childList)) {
resultList.addAll(childList);
}
}
- 📌2.从sql语句本身解决
在网上冲浪⛵️搜索到这样一个解决方案,正确性自行验证
例如:select * from employee where id in (1,2,3,...,1001)
可以修改为:
select * from employee where (0,id) in ( (0,1),(0,2),(0,3),...,(0,1001) )
传送门:https://blog.csdn.net/new__person/article/details/98474051
- 📌3.从传值方式解决
取值方式可以不通过后台,通过sql子查询查询not in吗?
答案是可以的,通过sql子查询查出的语句,结果个数超过1000也不会报错误,并且这种方式还是一种最优方式,通过后台如果数据量过大,截取次数过多,访问数据的次数频繁,并且这里的数据经过后台转化,然后MyBatis切割,效率可能会慢,子查询不乏是一种好的选择,当然这里子查询的是要相对简单就能查出,如果需要传值种类很多的话,使用多个not in 加子查询效率上面要慎重,这取决于你的查询复杂度。
这里的案例就可以完全用子查询,不需要通过sql查找值然后经过后台又传值给sql,看实际情况选择。
select *
from NC_ACCOUNT na
where 1=1
and na.ID not in ont in (select nb.ID from NB_ACCOUNT nb)