解决 Oracle IN 语句超过 1000 个报错问题，基于 Mybatis

最新推荐文章于 2025-06-13 20:32:48 发布
最新推荐文章于 2025-06-13 20:32:48 发布
阅读量2.9k 收藏 9
点赞数 2
CC 4.0 BY-SA版权
分类专栏： java oracle mybatis 文章标签： oracle mybatis java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_34304427/article/details/122254433
博客介绍了在遇到Oracle数据库IN子句元素数量超过限制时，如何通过MyBatis拦截器动态转换SQL，将单个IN转换为多个INORIN结构，从而避免修改大量业务代码，保持代码优雅。文中提供了具体的拦截器实现代码，并展示了转换前后的SQL示例。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

问题描述

  • 日常开发中，我们难免后用到下面这样的逻辑。尤其是在做数据查询的时候，后端根据前端多选控件 传递的 的条件进行查询的时候，这样的查询方式几乎没有替代方案
select * from tbl_name where id in (?, ?, ?)

  • 之前做项目的时候使用了 oracle 数据库，就出现了多选控件数据太多，选择的内容超过 1000，传递给后端查询语句的时候，超出 oracle in 语句的上限，导致 sql 异常错误，同时用户不同意前端控件控件选择 1000 的上限

  • 其实这也很好解决，基本有以下几种解决方式：

    1，将 in 语句的内容拆分，修改为 （in or in） 的结构

    2，创建临时表，把临时表作为 in 的子查询，查询完成后在删除临时表

    ps：不管采用哪种方式，对于性能都影响很大，本身 in 语句内容太多就不应该，有条件还是建议采用搜索引擎解决，本文只是提供一个简略解决方式

  • 当这个问题报出来之后，公司居然要求评估所有采用 in 语句的风险，要求排除风险，这就麻烦了，因为所有条件查询基本都离不开上面的语句，不管是 1，2 那种方式，都需要挨个接口去改，简直想死

  • 其次，如果在业务代码里修改，会让业务代码逻辑变得复杂，同时代码也不再优雅，作为强迫症不能容忍

解决方案

  • 现在大部分项目应该都是使用的 mybatis 框架查询数据库，那我们就可以考虑通过拦截器的方式，对 in 语句实行自动转化 为 (in or in) 语句的结构，这样业务代码就不用修改了，以后就可以放心大胆的使用 in 语句了
  • 相关代码如下:
  • 以下代码的重点 是 IN 这个正则表达式 能准确的匹配到 想要的那一段内容，笔者目前测试下来基本都能匹配上，如果有其它情况可以通过修改该正则，完成自己的需求
package com.xctech.xhe.provider.config;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Intercepts({
    @Signature(  // 正常查询
        type = Executor.class,
        method = "query",
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
    ),
    @Signature(  // 从缓存中查询
        type = Executor.class,
        method = "query",
        args = {
                MappedStatement.class, Object.class, RowBounds.class,
                ResultHandler.class, CacheKey.class, BoundSql.class
        }
    )
})
@Component
@Slf4j
public class MbInInterceptor implements Interceptor {

    private final Pattern EXEGESIS = Pattern.compile("-- .*\n");
    private final Pattern PATTERN = Pattern.compile("\\s+|\t|\r|\n");
    private final Pattern IN = Pattern.compile("[A-z0-9._]+ [IN|in]+ \\([, ?]+\\)");

    /**
     * 返回插件
     */
    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    /**
     * 设置参数
     */
    @Override
    public void setProperties(Properties properties) {}

    /**
     * 拦截执行器，解决 oracle 数据库 sql 语句 in 10000 上限问题
     */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        if (SqlCommandType.SELECT.equals(ms.getSqlCommandType())) {
            Object parameterObject = args[1];
            BoundSql boundSql = args.length == 6 ? (BoundSql) args[5] : ms.getBoundSql(parameterObject);
            MappedStatement statement = (MappedStatement) args[0];
            MappedStatement newStatement = newMappedStatement(statement, new BoundSqlSqlSource(boundSql));
            Field field = boundSql.getClass().getDeclaredField("sql");
            field.setAccessible(true);
            field.set(boundSql, buildInSql(boundSql.getSql()));
            args[0] = newStatement;
        }
        return invocation.proceed();
    }

    /**
     * 初始化新的 MappedStatement，替换原本 args 中的第一个元素
     */
    private MappedStatement newMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
        MappedStatement.Builder builder = new MappedStatement.Builder(
                ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.keyGenerator(ms.getKeyGenerator());
        if (ms.getKeyProperties() != null && ms.getKeyProperties().length != 0) {
            StringBuilder keyProperties = new StringBuilder();
            for (String keyProperty : ms.getKeyProperties()) {
                keyProperties.append(keyProperty).append(",");
            }
            keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
            builder.keyProperty(keyProperties.toString());
        }
        builder.timeout(ms.getTimeout());
        builder.parameterMap(ms.getParameterMap());
        builder.resultMaps(ms.getResultMaps());
        builder.resultSetType(ms.getResultSetType());
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());
        return builder.build();
    }

    /**
     * 替换超过 1000 的 IN 语句为 ( IN OR IN ） 结构
     * @param sql 原 sql 语句
     */
    private String buildInSql(String sql) {
        sql = EXEGESIS.matcher(sql).replaceAll("");  // 删掉注释
        sql = PATTERN.matcher(sql).replaceAll(" ");  // 格式化为单行
        Matcher matcher = IN.matcher(sql);
        while (matcher.find()) {
            String inSql = matcher.group();
            String[] strs = inSql.split("\\(");
            String str0 = strs[0];
            String str1 = strs[1].replaceAll("\\)", "").replaceAll(" ", "");
            int count = str1.split(",").length;
            if (count <= 1000) continue;
            List<String> inStr = new ArrayList<>();
            int start = 0, end = 0, offset = 1000;
            while (end < count) {
                end = start + offset;
                end = Math.min(end, count);
                String str = str1.substring(2 * start, 2 * end - 1);
                inStr.add(str0 + "(" + str + ")");
                start = end;
            }
            String newSql = String.join(" OR ", inStr);
            newSql = "(" + newSql + ")";
            sql = sql.replace(inSql, newSql);
        }
        return sql;
    }

    /**
     * 声明自己的 SqlSource
     */
    private static class BoundSqlSqlSource implements SqlSource {

        private final BoundSql boundSql;

        public BoundSqlSqlSource(BoundSql boundSql) {
            this.boundSql = boundSql;
        }

        @Override
        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }
        
    }

}

测试结果

ps：这里为了方便，临时把上限 调成了 10 个

  • 原语句
    SELECT id,broker_id,indicator_id,indicator_name,industry,source,fir_dir,sec_dir,thr_dir,other_dir,unit,frequency,description,dict_create_time,dict_update_time,deleted,uid_modify,gmt_modify FROM t_indicator_dict WHERE id IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) AND deleted=0 AND t.Indicator_Id in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )

  • 替换后的语句
    SELECT id,broker_id,indicator_id,indicator_name,industry,source,fir_dir,sec_dir,thr_dir,other_dir,unit,frequency,description,dict_create_time,dict_update_time,deleted,uid_modify,gmt_modify FROM t_indicator_dict WHERE (id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?)) AND deleted=0 AND (t.Indicator_Id in (?,?,?,?,?,?,?,?,?,?) OR t.Indicator_Id in (?,?,?,?,?,?,?,?,?,?) OR t.Indicator_Id in (?,?,?,?,?,?,?,?,?,?) OR t.Indicator_Id in (?,?,?,?,?,?,?,?,?,?) OR t.Indicator_Id in (?,?,?,?,?,?,?,?,?,?) OR t.Indicator_Id in (?,?,?,?,?,?,?,?,?,?) OR t.Indicator_Id in (?,?,?,?,?,?,?,?,?))

Mybatis系列解决in操作参数超过1000条出现bug的方案
雨不会一直下，但是一直会下雨
11-12 2852
需求： 执行in操作查询，且in()中的参数超过1000条，如果不做处理，当in中的参数超过1000条会发生错误。 解决方案： 可用OR操作解决 实操： mapper.java , mapper.xml List<Map<String,Object>> selectData(@Param("mydata") List<String> mydata); <select id="selectData" resultType="java.util.Map">
场景解决之mybatis批量插数报错如何揪出罪魁祸首
Neoest的博客
03-29 1635
当批量插入失败时，我们可以将数据集分成两半，对每一半进行再次的批量尝试。如果子批次仍然失败，继续对出错的子批次进行二分，递归这个过程，直到定位到具体的出错数据。
Oracle查询处理in条件超过1000条
11-06
Oracle查询处理in条件超过1000条的2种后台处理方式：A，后台拼接查询条件；B，每1000条查询一次，最后返回查询结果；
oracle在使用in时数据超过1000条时报错解决方案
qq_50839477的博客
06-13 834
oracel在使用in时数据超过1000条时报错解决方案
解决Java处理Oracle用in查询条件超过1000个的办法
u010741112的博客
06-13 161
Oracle sql语句，in函数有助于减少or条件的复合使用，但是如果in的条件中多于1000个表达式数会报ORA-01795的提示错误。这是Oracle考虑性能问题做的限制。可以把条件拆分，用多个or和in拼接查询。原文链接：https://blog.csdn.net/weixin_45665626/article/details/101074505。版权声明：本文为CSDN博主「null_able」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
Oracle查询中IN参数超过1000的解决方法
小吉子的开发记录小站
06-29 1万+
在查询一个in中超过1000个参数的时候报错了，查找了下，这边总结的解决方法有两种，一种是通过union all，一种是循环将参数写到in中再用or连接语句。方法还有几种但是暂未尝试，先不进行记录。开发中主要用了第二种方法。 第一种，语句类似
【mybatis解决oracle查询in超过1000条数据】
weixin_44503925的博客
05-27 1128
【代码】【mybatis解决oracle查询in超过1000条数据】
【Mybatis】解决Oracle查询处理in条件超过1000条的问题
光阴迷客的知识库
11-20 7184
直接上SQL吧，思路很简单： select * from test_table where 1 = 1 &lt;!-- IdList --&gt; &lt;if test="IdList != null and IdList.size &gt; 0"&gt; AND PK_ID IN &lt;!-- 处理in的集合超过1000条时Oracle不支持的情况 --&gt; &lt;tri...
Oracle+Mybatis的foreach insert批量插入报错的快速解决办法
09-01
这个问题通常由于Oracle数据库对批量插入语句的语法要求与MySQL等其他数据库系统不同所导致。以下是对这个问题的详细分析和解决方案： 首先，了解MyBatis的`<foreach>`标签。它是MyBatis提供的一个强大的动态SQL...
Oracle+Mybatis批量插入foreach报错快速解决
07-06
MyBatis的标签是一个强大的动态SQL工具，用于遍历集合并构建SQL语句。它的主要属性包括：item（集合中每个元素的别名）、index（可选，元素在集合中的索引位置）、collection（必须指定，表示要遍历的集合名称）、...
mybatis中使用oracle关键字出错的解决方法
08-29
为了解决这个问题，我们可以使用MyBatis的resultMap来处理oracle关键字。ResultMap是MyBatis的一种结果映射机制，通过它可以将数据库中的表名和列名映射到Java对象中。 在下面的示例代码中，我们可以看到如何使用...
Oracle SQL in 超过1000的解决方案list
07-07
oracle中如果非要用in作为条件的话，超过1000条可以用col in() or col in()的方式来处理
oracle sql in 连接大于1000报错解决办法
04-02
oracle sq 使用l in 连接大于1000情况下报错解决办法。
[数据库]处理 Oracle SQL in 超过1000 的解决方案.txt
02-27
[数据库]处理 Oracle SQL in 超过1000 的解决方案.txt
oracle in 超过1000解决方案
Yoke
08-24 6488
oracle in 超过1000解决方案 1.with as table建立临时表子查询 with t as ( select '1' as id from dual union all select '2' as id from dual ····· ) select i.* from table1 i where i.id in ( select t.id from t ) 2.使用or 方式1： select * from table where id in
ORA-01795 Oracle in中超过1000个值处理方法汇总
Hehuyi_In的博客
06-03 1万+
最近又又又遇到了开发同事问Oracle 中in超过1000个值遇到ORA-01795: maximum number of expressions in a list is 1000怎么处理，之前也陆陆续续查过一些方法，汇总整理一把。当然，其中的一些方法只是保证它不报错，性能可能堪忧，尽量少用。
Oracle查询-in条件超过1000
wdquan19851029的专栏
01-14 1214
oracle数据库中，in的查询条件超过1000的话，就会报错，应该怎样处理这样的情况呢？
解决Oracle使用in语句不能超过1000问题
热门推荐
谢哥哥的博客
02-21 1万+
在oracle中，使用in方法查询记录的时候，如果in后面的参数个数超过1000个，那么会发生错误，JDBC会抛出“java.sql.SQLException: ORA-01795: 列表中的最大表达式数为 1000”这个异常。 我的解决方案是：建立临时表 ORACLE临时表有两种类型：会话级的临时表和事务级的临时表。 1）ON COMMIT DELETE ROWS 它是临时表的默认参数，表示临时表中的数据仅在事务过程（Transaction）中有效，当事务提交（COMMIT）后，临时表的暂时段将被自动截断
解决MyBatis在 Oracle 中使用 IN 语句不能超过 1000 问题
发现问题，面对问题，分析问题，解决问题，总结问题
12-15 1322
在 Oracle 数据库中，IN 语句常用于查询某个字段是否属于一组特定的值。对于大多数开发者而言，IN 是一种简单直观的查询方式，能够提升开发效率，避免过多的 OR 语句。然而，许多人在使用 IN 语句时可能遇到一个问题，即查询中的 IN 子句无法处理超过 1000 个元素。这一限制是 Oracle 数据库的内在设计问题，虽然不常见，但一旦遇到，可能会造成应用程序崩溃或性能问题。
mybatis如何避免sql语句传太多的问题
最新发布
08-08
<think>嗯，用户想了解MyBatis如何避免SQL参数过多导致的问题。这确实是个很实际的问题，我在处理大量IN查询时也经常遇到。 用户可能正在开发一个需要处理大量ID查询的功能，比如导出报表或者批量操作。当IN列表里的参数超过数据库限制（通常是1000个）时，SQL就会直接报错。用户应该希望找到既保持代码简洁又能解决问题的方案。 从引用内容来看，有三种典型解法：第一种是用foreach标签配合取模运算动态拆分IN子句，这是比较优雅的方案；第二种是把大列表拆分成多个小列表分别查询；第三种是用临时表。不过用户明确问的是"避免参数过多"，所以重点应该放在前两种方案上。 让我想想怎么组织回答比较清晰。首先需要解释问题本质：数据库对SQL长度和IN列表参数数量都有限制。然后给出三种解决方案，重点展示第一种的代码实现，因为引用里提供了很完整的示例。最后补充性能考量，毕竟分多次查询会影响速度。 代码示例要特别注意两点：一是取模运算的边界条件（%999和998的配合），二是要处理Oracle的特殊语法（需要union all连接）。用户可能用的是MySQL，但提一下Oracle的差异会更专业。 性能部分要提醒用户权衡：拆分成多个SQL会增加数据库压力，临时表适合极端大数据量但需要额外清理。这个建议对用户做技术选型应该很有帮助。 最后生成的问题要覆盖用户可能延伸的疑问：性能对比、分页优化、其他框架的解决方案。这样用户后续探索时就有方向了。</think>在 MyBatis 中处理大量参数（特别是 `IN` 子句）时，避免因参数过多导致 SQL 报错或性能下降，核心思路是 **拆分参数列表**。以下是经过验证的优化方法： --- ### 📌 方法一：动态拆分 IN 子句（推荐） 利用 MyBatis `<foreach>` 标签的 `index` 属性和条件判断，在 SQL 执行时自动将长列表拆分为多个 `IN` 子句并用 `OR` 连接。 ```xml <select id="selectByItems" resultType="YourEntity"> SELECT * FROM your_table WHERE <if test="itemList != null and itemList.size() > 0"> your_column IN <foreach collection="itemList" item="item" index="index" separator="," open="(" close=")"> <if test="(index % 999) == 998"> <!-- 每1000个参数拆分一次 --> NULL ) OR your_column IN ( <!-- 关闭当前IN，开启新IN --> </if> #{item} </foreach> </if> </select> ``` **✨ 优势**： - 代码侵入性低，逻辑集中在 XML 中 - 自动适应不同参数规模 - 兼容多数数据库（需注意语法差异，如 Oracle 需用 `UNION ALL` 替代 `OR`） **⚠️ 注意**： - 确保拆分后的每个 `IN` 子句参数不超过数据库限制（如 Oracle 的 1000 项限制） - 末尾可能残留未闭合的 `)`，需测试边界情况 --- ### 📌 方法二：分批次执行查询 将参数列表拆分为多个子列表，分别执行查询后合并结果（适合结果集较小的场景）。 ```java // Service 层逻辑 public List<YourEntity> batchSelect(List<Long> idList) { List<YourEntity> result = new ArrayList<>(); // 每 1000 个 ID 分一组 Lists.partition(idList, 1000).forEach(subList -> { result.addAll(yourMapper.selectBySubList(subList)); }); return result; } ``` ```xml <!-- Mapper 映射 --> <select id="selectBySubList" resultType="YourEntity"> SELECT * FROM your_table WHERE your_column IN <foreach collection="subList" item="id" open="(" separator="," close=")"> #{id} </foreach> </select> ``` --- ### 📌 方法三：使用临时表（大数据量首选） 对超大数据集（如 10 万+），最优方案是将参数写入临时表，通过 `JOIN` 查询： 1. 创建临时表存储参数 2. 执行 `INSERT INTO temp_table VALUES (...), (...)` 批量插入 3. 执行关联查询： ```sql SELECT t.* FROM your_table t JOIN temp_table tmp ON t.your_column = tmp.param_column ``` 4. 删除临时表 **✅ 优势**： - 彻底规避参数数量限制 - 性能最佳（尤其对索引友好的 `JOIN` 操作） - 事务内自动清理临时表 --- ### ⚡ 性能与适用场景对比 | **方法** | **适用场景** | **性能影响** | **复杂度** | |------------------|----------------------------------|-----------------------|------------| | 动态拆分 IN 子句 | 中等数据量（< 10万） | 中等（需解析复杂 SQL）| ⭐⭐ | | 分批次查询 | 结果集小的场景 | 高（多次网络+查询） | ⭐ | | 临时表 | 超大数据量、高频复杂查询 | 低（单次高效查询） | ⭐⭐⭐ | > **关键建议**： > - 优先选择 **动态拆分 IN 子句** 平衡开发效率与性能 > - 超过 10 万参数时 **必须用临时表**，避免拖垮数据库 > - 分页查询时结合 `WHERE id > #{lastId} LIMIT N` 替代 `IN`，效率更高[^2] --- ### 🔍 相关问题 1. **如何优化 MyBatis 处理百万级数据的批量插入？** 2. **动态 SQL 中的 `<choose>` 和 `<if>` 标签对性能有何影响？** 3. **MyBatis 有哪些避免全表扫描的编码实践？** 4. **除了参数拆分，还有哪些解决 `IN` 子句性能问题的方案？** > 引用[^1]: 性能优化需结合业务场景选择策略，临时表方案虽高效但增加复杂度。 > 引用[^2]: 动态拆分 IN 子句时需严格验证边界值，防止 SQL 语法错误。 > 引用[^3]: 分批次查询适用于低并发场景，高并发下慎用以免连接池耗尽。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值