MySQL中的两种特殊插入方式

﹏ヽ暗。殇╰゛Y 2024-03-17 15:08 92阅读 0赞

## 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">
    
        <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">
    
    
        <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耗尽）