Oracle：ORA-01795: maximum number of expressions in a list is 1000

最近测试老师反映一个页面加载异常，查看日志发现返回Cause: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000，意思是说Oracle的in查询中数据不能超过1000，解决方案也不复杂，就是拆分in查询语句

SQL

• 原句

  select * from table_test where name in(a, b, c, ...)
  

• 拆分一

  select * from table_test where name=a or name=b or name=c or ...
  

• 拆分二

  select * from table_test where name in(a, b, c, ...) and name in(x, y, z, ...)
  

MyBatis

• 原句

  <select id="getList" resultType="cn.khue.test.do.TableTest">
  	select *
  	from table_test
  	<where>
  		<if test="nameList != null and nameList.size &gt; 0">
  			<foreach collection="nameList" item="name" index="index" open="name in(" close=")" separator=",">
  				#{name, jdbcType=VARCHAR}
  			</foreach>
  		</if>
  	</where>
  </select>
  

• 拆分一

  <select id="getList" resultType="cn.khue.test.do.TableTest">
  	select *
  	from table_test
  	<where>
  		<if test="nameList != null and nameList.size &gt; 0">
  			<foreach collection="nameList" item="name" index="index" open="" close="" separator="or">
  				name = #{name, jdbcType=VARCHAR}
  			</foreach>
  		</if>
  	</where>
  </select>
  

• 拆分二

  <select id="getList" resultType="cn.khue.test.do.TableTest">
  	select *
  	from table_test
  	<where>
  		<if test="nameList != null and nameList.size &gt; 0">
  			<foreach collection="nameList" item="name" index="index" open="name in(" close=")" separator=",">
  				<if test="index != 0 and (index % 999) == 0">
  					#{name, jdbcType=VARCHAR} ) and name in( #{name, jdbcType=VARCHAR}
  				</if>
  				<if test="index == 0 or (index % 999) != 0">
  					#{name, jdbcType=VARCHAR}
  				</if>
  			</foreach>
  		</if>
  	</where>
  </select>
  

优化思考

由于前999次不需要判断其大小，所以可以使用when标签拆分，以此减少需要判断的次数

<select id="getList" resultType="cn.khue.test.do.TableTest">
	select *
	from table_test
	<where>
		<choose>
			<when test="nameList != null and nameList.size &gt; 0 and nameList.size &lt; 1000">
				<foreach collection="nameList" item="name" index="index" open="name in(" close=")" separator=",">
					#{name, jdbcType=VARCHAR}
				</foreach>
			</when>
			<when test="nameList != null and nameList.size &gt;= 1000">
				<foreach collection="nameList" item="name" index="index" open="" close="" separator="or">
					name = #{name, jdbcType=VARCHAR}
				</foreach>
			</when>
		</choose>
	</where>
</select>

如果in中的数据来自于同库查询，那么可以使用join替换

# in查询
select A.* from A where A.a in(b1, b2, b3, ...)

# join替换
select A.* from A left join (select b from B) as B on A.a = B.b