mysql的in能超过1000吗,Oracle数据库中IN参数个数超过1000
的问题
在oracle中，我们使⽤in⽅法查询记录的时候，如果in后⾯的参数个数超过1000个，那么会发⽣错误，JDBC会抛
出“java.sql.SQLException: ORA-01795: 列表中的最⼤表达式数为 1000”这个异常。初步解决这个问题的思想是把参数列表分段，将SQL语句拼成“or XX in(.....) or XX in(.....)”形式。我写的QueryUtil类分别给出了SQL、HQL和Hibernate中解决这个问题的⽅法：
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import iterion.Criterion;
import iterion.Restrictions;
public class QueryUtil {
/**
* 解决Oracle中in的参数列表长度超过1000的问题
*
* @param length
* 每个分段的长度，
* @param paramsList
* 待拆分的参数列表
* @return 拆分后的分段列表
*/
public static List> splitInParams(int length, List paramsList) {
if (length<1||paramsList == null || paramsList.size() == 0)
return null;
int size = paramsList.size();
List> list = new ArrayList>();
int d = (int) il(size / (length+0.0));
for (int i = 0; i < d; i++) {
int fromIndex = length * i;
int toIndex = Math.min(fromIndex + length, size);
list.add(paramsList.subList(fromIndex, toIndex));
}
return list;
}
/**
* 解决Oracle中in的参数列表长度超过1000的问题
* 返回拼接的SQL语句
* eg:
* 返回的SQL语句⽚段：XX IN (.......) OR XX IN (......)这样的形式
* @param paramName 需要进⾏in查询的查询参数的名称
* @param length 每个分段的长度，对于Oracle，⼀般设置为800-1000
* @param paramsList 待拆分的参数列表
* @return 拼接的SQL语句⽚段
*/
public static String getSQLInParamsSplit(String paramName,int length,List paramsList){
if(length<1||paramName==null||paramsList==null||paramsList.size()==0)
return null;
List> list = splitInParams(length, paramsList);
StringBuilder sb = new StringBuilder();
String temp = (0).toString();
//由于List的toString⽅法返回的是[....]形式，需要去掉开头和结尾的中括号
sb.append(paramName).append(" IN ("+temp.subSequence(1,temp.length()-1)+") ");
int size = list.size();
for(int i=1;i
temp = (i).toString();
sb.append(" OR "+paramName+" IN ("+temp.subSequence(1,temp.length()-1)+") ");
}
String();
}
/**
* 解决Oracle中in的参数列表长度超过1000的问题
* 返回命名参数查询的HQL语句⽚段和对应的参数名和值的Map
mysql 要钱吗* eg:
* 返回的HQL语句⽚段：id IN (:id0) OR id IN (:id1) OR id IN (:id2) OR id IN (:id3)，命名参数的名称是以paramName加上序号* @param paramName 需要进⾏in查询的查询参数的名称
* @param length 每个分段的长度，对于Oracle，⼀般设置为800-1000
* @param paramsList 待拆分的参数列表
* @return 返回⼀个长度为2的Object数组，
* 第⼀个元素是还有in的HQL语句⽚段，
* 第⼆个元素是Map，其中String是参数名，Object是参数值的列表
*/
public static Object[] getHQLInParamsSplit(String paramName,int length,List paramsList){
if(length<1||paramName==null||paramsList==null||paramsList.size()==0)
return null;
List> list = splitInParams(length, paramsList);
Map tempParamsValues = new HashMap();
StringBuilder sb = new StringBuilder();
String tempParamName = paramName+0;
sb.append(paramName).append(" IN (:").append(tempParamName).append(")"); tempParamsValues.put(tempParamName, (0));
int size = list.size();
for(int i=1;i
tempParamName = paramName+i;
sb.append(" OR ").append(paramName).append(" IN (:").append(tempParamName).append(")"); tempParamsValues.put(tempParamName, (i));
}
Object[] hqlAndParamsMap = new Object[2];
hqlAndParamsMap[0]=sb.toString();
hqlAndParamsMap[1]=tempParamsValues;
return hqlAndParamsMap;
}
/**
* 解决Oracle中in的参数列表长度超过1000的问题
* 获取拆分in参数列表后的Criterion
* @param paramName 需要进⾏in查询的查询参数的名称
* @param length 每个分段的长度，对于Oracle，⼀般设置为800-1000
* @param paramsList 待拆分的参数列表
* @return 含有嵌套or的Criterion
*/
public static Criterion getCriterionInParamsSplit(String paramName,int length,List paramsList){ if(length<1||paramName==null||paramsList==null||paramsList.size()==0)
return null;
List> list = splitInParams(length, paramsList);
Criterion criterion = Restrictions.in(paramName, (0));
int size = list.size();
for(int i=1;i
(criterion, Restrictions.in(paramName, (i)));
return criterion;
}
}
上⾯可以初步解决问题，为什么只是初步呢？因为in后⾯的参数个数很多时会导致数据库执⾏时间过长，⽽导致连接超时，Hibernate中会抛出“java.sql.SQLException: Io 异常: Connection reset by peer: socket write error"异常，⽽直接⽤JDBC写会抛
出“java.sql.SQLRecoverableException: ⽆法从套接字读取更多的数据”。下⾯列出⼀条简单的SQL语句“SELECT count(*)from JBPM4_EXT_TASK_ENGINE_ where 1=1”后⾯拼接“ or 1=1 ”时JDBC执⾏时间：
SQL语句长度(字符数)
JDBC执⾏时间(毫秒)
最后取到的记录数(条)
第⼀组数据
1653
660
51192
第⼆组数据
24053
12391
51192
第三组数据
64053
161382
51192
由此可见SQL语句过长导致JDBC执⾏时间的增长率是很快的，当SQL语句中的字符数达到某⼀个数量时肯定会导致连接超时，当然我没有去测这个阈值。不过，产⽣这个现象的原因是什么，我还不是很清楚(可能是字符数，也可能是查询条件过多⽽导致表达式树过于庞⼤⽽超出数据库的处理能⼒)。
那么如何解决这个问题呢？⽅法⼀般有两种：1.⽤表关联代替IN；2.在where条件中使⽤⼦查询，如“select * from b where c in (select d from e ......)”
这样的形式。
总⽽⾔之，在SQL语句中要慎⽤IN，IN⼀般只⽤于参数个数较少的情况。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论