MySQL中的两种特殊插入方式

MySQL中的两种特殊插入方式

更新插入(on duplicate key update)

代码案例

PointMapper.java

@Mapper
public interface PointMapper {
        /**
     * on duplicate key update ,是基于主键 或唯一索引 ,已存在数据则执行更新，不存在则执行插入
     */
    int updateBatchByOdku(List<Point> points);

}    

PointMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.orm.dao.PointMapper">
<!--
mysql中的 on duplicate key update ,是基于主键 PK 或唯一索引 UK 使用的
-->
    <update id="updateBatchByOdku" parameterType="java.util.List">
       insert into t_point (id,point_name,point_type)
       values
       <foreach collection="list" item="item" index="index" separator=",">
           (
           #{item.id},
           #{item.pointName},
           #{item.pointType}

           )
       </foreach>
       on duplicate key update
       point_name = values(point_name) ,
       point_type = values(point_type)

    </update>
</mapper>    

---

替换插入(replace into)

代码案例

PointMapper.java

@Mapper
public interface PointMapper {
     /**
     * replace into ,是基于主键 或唯一索引， 先删除 ，再插入
     */
    int replaceBatch(List<Point> points);

}

PointMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.orm.dao.PointMapper">

<!--
  replace into 语句
  数据已存在，则先删除此行数据，然后插入新数据；
  数据不存在，则直接插入新数据；
  注意：若当前的数据库用户账号没有删除权限，是不能使用replace into 的
-->
    <insert id="replaceBatch" parameterType="java.util.List">
        replace into t_point(id,point_name,point_type)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.id},
            #{item.pointName},
            #{item.pointType}
            )
        </foreach>
    </insert>
</mapper>       

---

这两种插入方式的区别

on duplicate key update 和 replace into 是两种处理重复键冲突的方法，但它们具有一些区别

功能不同

on duplicate key update 在插入数据时，如果遇到重复键冲突，会更新已存在的行的值

replace into在插入数据时，如果遇到重复键冲突，会删除已存在的行，并插入新的行

影响的数据行不同

on duplicate key update 只会更新与重复键 冲突的数据行

replace into 删除与重复键 冲突的数据行，并插入一条新的行

对于自增主键的处理

on duplicate key update 当存在重复键冲突时，不会修改自增主键的值

replace into 在插入新的行时，会分配一个新的自增主键值，（最终可能导致自增id耗尽）

执行效率和性能影响

on duplicate key update 适用于只需要更新部分列的场景，并且对于批量更新操作较为高效

replace into 适用于需要替换整行数据的场景，但对于删除和插入操作开销较大，不适用于大量数据的处理

---

总结

on duplicate key update 更适合在插入重复数据时进行部分列的更新。

replace into更适合替换整行数据，并且需要注意其对自增主键的处理。

在选择使用哪种方法时，根据具体的需求和场景进行选择。