条件更新与删除

条件更新与删除是日常开发中最常见的操作。

动态条件更新

<update id="updateSelective" parameterType="com.example.entity.User">
    UPDATE user
    <set>
        <if test="username != null">username = #{username},</if>
        <if test="email != null">email = #{email},</if>
        <if test="status != null">status = #{status},</if>
        <if test="updateTime != null">update_time = #{updateTime},</if>
    </set>
    WHERE id = #{id}
</update>

User user = new User();
user.setId(1);
user.setUsername("newname");  // 只更新 username
mapper.updateSelective(user);

注意：set 标签自动移除最后一个逗号，只更新非 null 字段。

批量更新

<update id="batchUpdateStatus" parameterType="java.util.List">
    UPDATE user SET status = #{status}
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</update>

List<Integer> ids = Arrays.asList(1, 2, 3, 4, 5);
int rows = mapper.batchUpdateStatus(ids, 1);
System.out.println("更新 " + rows + " 条记录");

条件删除

<delete id="deleteByStatus">
    DELETE FROM user WHERE status = #{status}
</delete>

<delete id="deleteBatch">
    DELETE FROM user WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

软删除更新

<update id="softDeleteBatch">
    UPDATE user SET is_deleted = 1, update_time = NOW()
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</update>

注意：推荐软删除而非物理删除，便于数据恢复与审计。

要点总结

• 动态更新使用 set + if 标签，只更新非 null 字段
• 批量更新/删除使用 foreach 构造 IN 条件
• 软删除通过更新状态字段实现，推荐用于生产环境
• 所有批量操作需在事务中执行