mysql中如果查询语句包含in并且参数中in的数量还比较多的情况下，一般不走索引。会造成查询慢的情况。一般情况下，in语句我们可以通过转化成连接语句，利用union all来优化查询：如下：
其中ids是个List
<select id=”getXXXXX” resultType=”XXXXX”>
SELECT A.id,A.name FROM A
<if test=’ids != null and ids.size() != 0′>
inner join
<foreach collection=’ids’ item=’id’ open='(‘ close=’)’ separator=’ union all ‘>
select #{id} id
</foreach>
B on A.id=B.id
</if>
</select>
如果ids的数量还是特别多，union all 会造成sql长度超限。
上述原因下，想通过传字符串参数在mysql下通过拆分字符串，利用临时表来实现
<select id="getXXXXX" resultType="XXXXX">
create temporary table a_temp
select #{ids} as name ;
select A.id,A.name from A
inner join(
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(tmp.name,',',b.id),',',-1) as item
from a_temp tmpa left join ${tableName} tmp_id tmpb
on tmpb.id <![CDATA[<=]]> (LENGTH(tmpa.name)-LENGTH(REPLACE(tmpa.name,',',''))+1)) B on A.id=B.item;
drop table a_temp ;
</select>
此时 ids 是多个ID逗号分隔的字符串，首先将该字符串塞进临时表 a_temp 中，通过SUBSTRING_INDEX函数将临时表中的长字符串拆开。然后再用主表跟拆开的ID表连接得到查询结果。参数${tableName} 可以根据参数数量的多少事先生成不同数量级别的连续ID表在数据库中：
我是通过建立存储过程创建了几个不同数量ID的表格，
CREATE PROCEDURE generate_id(rows int)
begin
DECLARE i INT; — 申明变量
SET i = 1; — 变量赋值
WHILE i<=rows DO — 结束循环的条件: 当i大于a时跳出while循环
— 往tmp_id_1000表添加数据
INSERT INTO `tmp_id_1000` (name) VALUES (i);
SET i = i+1; — 循环一次,i加1
END WHILE; — 结束while循环
END
生成了 tmp_id_500，tmp_id_1000，tmp_id_2000，tmp_id_5000等表，用枚举管理起来
package cn.focusmedia.stormwind.tunnel.dataobject;
import java.util.Comparator;
import java.util.stream.Stream;
import lombok.Getter;
public enum TMP_ID_TABLENAME {
tmp_id_500(500),
tmp_id_1000(1000),
tmp_id_2000(2000),
tmp_id_5000(5000);
@Getter
Integer value;
TMP_ID_TABLENAME(Integer value) {
this.value = value;
}
public static TMP_ID_TABLENAME getByValue(Integer val) {
var op = Stream.of(values()).filter(t -> t.value >= val)
.sorted(Comparator.comparing(TMP_ID_TABLENAME::getValue)).findFirst();
if (op.isPresent()) {
return op.get();
}
return null;
}
}
dao层调用
public List<XXXXX> getListTest(List<Long> ids) {
var tmpTableName = TMP_ID_TABLENAME.getByValue(ids.size());
if (Objects.nonNull(tmpTableName)) {
return xxxxMapper.getListTest(String.join(",", ids.stream().map(t -> t.toString())
.collect(Collectors.toList())), tmpTableName.name());
} else {
throw new BizException("参数数量是否过多，请重新调整业务控制ID数量！");
}
}