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 那种方式,都需要挨个接口去改,简直想死
其次,如果在业务代码里修改,会让业务代码逻辑变得复杂,同时代码也不再优雅,作为强迫症不能容忍
package com.hwq.admin.back.config.mybatis; 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.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 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) {} /** * 拦截执行器,打印完成可执行的 SQL 语句 */ @Override public Object intercept(Invocation invocation) throws Throwable { Object[] args = invocation.getArgs(); MappedStatement ms = (MappedStatement) args[0]; Object parameter = args[1]; BoundSql boundSql = args.length >= 6 ? (BoundSql) args[5] : ms.getBoundSql(parameter); String inSql = buildInSql(boundSql.getSql()); Field field = boundSql.getClass().getDeclaredField("sql"); field.setAccessible(true); field.set(boundSql, inSql); return invocation.proceed(); } /** * 替换超过 1000 的 IN 语句为 ( IN OR IN ) 结构 * @param sql 原 sql 语句 */ private String buildInSql(String sql) { 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; } }
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 (?,?,?,?,?,?,?,?,?))