MySQL 之批量更新

╰半橙微兮° 2022-11-04 11:14 226阅读 0赞

### SQL ###

> 批量更新的写法一般有三种.  
> 在更新数量较少的情况下，前两种性能不相上下。但是在更新字段增加，更新条数较多(500以上)，建议使用第三种写法

*  常规写法，拼接多个单条更新语句。

#分批逐条更新
    UPDATE t_back_up set name='holy' WHERE id =1;
    UPDATE t_back_up set name='shit' WHERE id =2;

*  CASE…WHEN… 写法

#CASE WHEN 更新
    update t_back_up
    SET name =(
    CASE 
    	WHEN id=1 THEN
    		'1111'
    	WHEN id=2 THEN
    		'22222'
    END 
    ) where id IN(1,2);

注意：CASE WHEN一定要和WHERE语句一起使用，否则UPDATE会遍历和更新数据库中所有的行。会把未出现在WHEN中的数据都更新成null，如果code列设置为NOT NULL则会报错，否则会置为NULL或者默认值。

*  JOIN 写法

#使用join更新
    UPDATE t_back_up a JOIN 
    (
    SELECT 1 AS id, 'holy' AS name
    UNION 
    SELECT 2 AS id,  'shit' AS name
    ) b USING(id)
    SET a.name=b.name;

上述SQL要表达的更新语义是：将id=1的name更新为’holy’，将id=2的name更新为’shit’。  
注意，条件字段必须放在USING中

### 性能对比 ###

#### RC隔离级别 ####

更新条数小(一般小于`500`条)，`CASE WHEN`和`JOIN`优于`UDPATE`。  
更新条数较大(千级别)，`CASE WHEN`效率迅速下降，`UPDATE`居中，推荐使用`JOIN`写法

#### RR隔离级别 ####

`JOIN`性能优于`CASE WHEN`，`CASE WHEN`优于`UPDATE`。

### Mybatis数据库操作 ###

数据库配置：  
`jdbc.url=jdbc:mysql://localhost:3306/db_name？useUnicode=true&characterEncoding=utf8&allowMultiQueries=true`

#### 批量插入 ####

#int batchInsert(List<Student> list);
    #insert into t_student (id, name, age)values (?,?,?),(?,?,?)
    <insert id="batchInsert" parameterType="java.util.List">
            insert into t_student
            (
            <include refid="Base_Column_List"/>
            )
            VALUES
            <foreach collection="list" item="item" index="index" separator=",">
                (
                #{item.id},
                #{item.name},
                #{item.age}
                )
            </foreach>
        </insert>

#### 批量删除 ####

#int batchDelete(String[] array);
    <delete id="batchDelete" parameterType="java.lang.String">
            delete from t_student
            where id in
            <foreach collection="array" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </delete>

#### 批量更新 ####

#int batchUpdate(List<Student> list);
    <update id="batchUpdate" parameterType="java.util.List">
        update t_student
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="name =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.name!=null and item.name != ''">
                        when id=#{item.id} then #{item.name}
                    </if>
                </foreach>
            </trim>
            <trim prefix=" age =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.age != null and item.age > 0">
                        when id=#{item.id} then #{item.age}
                    </if>
                </foreach>
            </trim>
        </trim>
        where id in 
        <foreach collection="list" separator="," item="item" index="index" open="(" close=")">
            #{item.id}
        </foreach>
    </update>

#多条update连接，需要在连接mysql的url上加 &allowMultiQueries=true 这个才可以执行
    <update id="updateBatch" parameterType="java.util.List">  
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update tableName
            <set>
                name=${item.name},
                name2=${item.name2}
            </set>
            where id = ${item.id}
        </foreach>      
    </update>

**本文来自**：  
https://www.cnblogs.com/AaronCui/p/10968893.html  
https://www.jianshu.com/p/59bebf3d83e6