mybatis exists 中使用代替in关键字

 

 

使用场景，in适合数据量小的时候，exists适合数据量大的时候。

       <if test="torqueRecordPageDTO.vinList != null and torqueRecordPageDTO.vinList.size >0">
            and exists (
                select 1 from (
                <foreach collection="torqueRecordPageDTO.vinList" item="item" index="index" separator="union all">
                    select #{item} as vin
                    from dual
                </foreach>
                 ) a where a.vin = f.record_vin and a.product_id = t1.product_id
            )
        </if>
<!--        <if test="torqueRecordPageDTO.vinList != null and torqueRecordPageDTO.vinList.size >0">-->
<!--            AND F.record_vin in-->
<!--            <foreach collection="torqueRecordPageDTO.vinList" separator="," item="v" open="(" close=")">-->
<!--                #{v}-->
<!--            </foreach>-->
<!--        </if>-->

　　

 

 <select id="getProductCapitalBookListByIds" resultMap="ProductCapitalBookResultMap">
        SELECT
        <include refid="ProductCapitalBook_Column_List"/>
        FROM
        product_capital_book t1
        WHERE 1 = 1
        <if test="booksRequestList!= null and booksRequestList.size() > 0">
            and exists (
                select 1 from (
                    <foreach collection="booksRequestList" item="item" index="index" separator="union all">
                        select #{item.bookId} as book_id,#{item.productId} as product_id
                        from dual
                    </foreach>
                ) a where a.book_id=t1.book_id and a.product_id = t1.product_id
            )
        </if>
    </select>

　　

使用场景，in适合数据量小的时候，exists适合数据量大的时候。

原文地址：https://blog.csdn.net/weixin_43763459/article/details/121422693