时间戳

首页 > 解决方案 > 如何在mybatis的IN子句中使用超过1000个args？

java - 如何在mybatis的IN子句中使用超过1000个args？

问题描述

我使用 Oracle 数据库和 myBatis 作为 ORM 框架，所有查询都在 XML 映射器文件中。我需要在INcaluse 中加入数千个参数，并且我知道有一个限制，即只能放置 1000 个参数。我已经决定解决方案：

WHERE something IN (a1, a2, ..., a1000)
OR something IN (a1001, a1002, ..., a2000)
OR ...

我怎么能在 XML 映射器文件中做到这一点？

有<foreach>，<if>标签等，但我找不到任何合适的可以将上面的 SQL 代码插入到模板中。

像这样的东西可以使它工作会很棒：

<some_tag collection="huge_collection" item="huge_collection[1:1000]>

</some_tag>

标签： javaoracleormibatisspring-mybatis

解决方案

Although you can work around number of IN parameters:

  1. Thousands of bound values will results in potentially megabytes of SQL. It will take long time to send this SQL to the database.

  2. It will be inefficient due to SQL parsing. Not only it takes long time to parse this long SQL but each invocation with different number of bound parameters will be parsed and planned separately (see this article which explains it).

  3. There is a hard limit of bound parameters in a SQL statement. You can repeat the OR few times to work around IN limit but you are going to hit the SQL statement limit at some point.

For that type of queries it's usually better to create a temporary tables. Create it before the query, insert all the identifiers into it and join it with this temporary table in the actual query to simulate the WHERE statement.

Ideally you can replace the query with a stored procedure. Pulling out tens of thousands identifiers from the database just to pass it back to the database in the next query is just inefficient.

推荐阅读