mybatis批量插入，更新，删除

MySQL中，insert后面带上 on duplicate key update 子句，数据库中存在记录时，执行这个语句会更新，而不存在这条记录时，就会插入。 
insert into tablename(field1,field2,field3,......) values(value1,value2,value3,....) on duplicate key update field1=values(value1),field2=values(value2),field3=values(value3),......;
 insert into `person_device`(`person_id`,`device_id`,`type`)
        values
        <foreach collection="list" separator="," item="item">
            (#{item.personId}, #{item.deviceId}, #{item.type})
        </foreach>
        on duplicate key update `type`=values(`type`)

//重复主键不插入
INSERT IGNORE INTO 表示，检查主键（PrimaryKey）可能是联合主键，如果数据库中已经存在相同的记录，则忽略当前新数据插入；
    <insert id="batchInsertPersonDevice">
        insert ignore into `person_device`(`person_id`,`device_id`,`type`)
        values
        <foreach collection="list" separator="," item="item">
            (#{item.personId}, #{item.deviceId},0)
        </foreach>
    </insert>

输出
==>  Preparing: insert ignore into person_office(person_id,office_id) values (?,?)
==> Parameters: 2(Integer), 10000(String)
<==    Updates: 0

<insert id="batchInserOrUpdate">
  insert into fnd_dept(
                       dept_code,
                       dept_name,
                       parent_dept_code,
                       type,
                       dept_type,
                       status,
                       order_num,
                       memo,
                       dept_path,
                       dept_name_path,
                       creation_date,
                       last_update_date,
                       created_by,
                       last_updated_by)
    values
    <foreach collection="list" separator="," item="item">
        (
         #{item.deptCode},
         #{item.deptName},
         #{item.parentDeptCode},
         #{item.type},
         #{item.deptType},
         #{item.status},
         #{item.orderNum},
         #{item.memo},
         #{item.deptPath},
         #{item.deptNamePath},
         SYSDATE(),
         SYSDATE(),
        'admin',
        'admin'
        )
    </foreach>
    on duplicate key
        update  dept_name=values(dept_name),
                parent_dept_code=values(parent_dept_code),
                type=values(type),
                dept_type=values(dept_type),
                status=values(status),
                order_num=values(order_num),
                memo=values(memo),
                dept_path=values(dept_path),
                dept_name_path=values(dept_name_path),
                last_updated_by="admin",
                last_update_date=SYSDATE()

</insert>

//批量删除 （or条件）
    <delete id="issueFeginDeleteUser">
            DELETE FROM person_device
            WHERE
            <foreach collection="list" separator="or" item="item">
                (person_id=#{item.personId} AND device_id=#{item.deviceId})
            </foreach>
    </delete>

输出
==>  Preparing: DELETE FROM person_device WHERE (person_id=? AND device_id=?) or (person_id=? AND device_id=?) or (person_id=? AND device_id=?)
==> Parameters: 3(Integer), CH3U212760001(String), 100(Integer), CH3U212760001(String), 12(Integer), CH3U212760001(String)
<==    Updates: 0

//批量updte (ignore可以去掉)
<update id="batchUpdate">
        <foreach collection="list" item="item" separator=";">
            update ignore person_device
            <set>
                device_id=#{item.deviceId}
            </set>
            <where>
                person_id=#{item.personId}
            </where>
        </foreach>
   </update>

输出
==>  Preparing: update ignore person_device SET device_id=? WHERE person_id=?
==> Parameters: CH3U212760002(String), 2(Integer)
<==    Updates: 0