mybatis批量删除操作

最新推荐文章于 2025-03-11 08:55:42 发布

Double_zhi

最新推荐文章于 2025-03-11 08:55:42 发布

阅读量4k

点赞数 4

文章标签： mybatis

本文链接：https://blog.youkuaiyun.com/Double_zhi/article/details/133774439

版权

1.复杂条件批量删除

建议增加联合索引

<!--通过条件删除数据-->
<delete id="batchDeleteByCondition">
    delete from test where
    <foreach collection="list" item="item" separator=" or " index="index">
        (name = #{item.name} and code= #{item.code} and type = #{item.type})
    </foreach>
</delete>

2. 复杂条件批量删除 IN操作

<delete id="deleteBatch" parameterType="java.util.List">
    delete from test
    where (NAME, CODE) in
    <foreach item="item" index="index" collection="list" separator="," open="(" close=")">
(#{item.name},#{item.code})
	</foreach>
</delete>

3. 批量删除常规

<delete id="batchDelete">
        delete from test where id in (
        <foreach collection="list" item="id" separator=",">
            #{id}
        </foreach>
        )  
 </delete>

4.Oracle IN超过1000删除

//方案1:
	DELETE FROM test
    WHERE id IN
        <!-- 处理in的集合超过1000条时Oracle不支持的情况 -->
        <trim suffixOverrides=" OR id IN()">
            <foreach collection="list" item="item" index="index" open="(" close=")">
                <if test="index != 0">
                    <choose>
                        <when test="index % 1000 == 999">) OR id IN (</when>
                        <otherwise>,</otherwise>
                    </choose>
                </if>
                #{item}
            </foreach>
        </trim>

方案2:
 		DELETE
        FROM test
        WHERE
        org_id IN
        <foreach collection="list" item="item" index="index" open="(" close=")">
            <choose>
                <!--第一项-->
                <when test="index == 0">#{item}</when>
                <!--OR语句中的第一项-->
                <when test="index != 0 and index % 500 == 0">) OR org_id IN ( #{item}</when>
                <!--非第一项-->
                <otherwise>,#{item}</otherwise>
            </choose>
        </foreach>