MySql中4种批量更新的方法

我就是我 2023-08-17 17:17 118阅读 0赞

### 1、replace into 批量更新 ###

replace into test_tbl (id,name) values (1,'a'),(2,'b'),(x,'y');

**示例：**

replace into book ( 'Id', 'Author', 'CreatedTime', 'UpdatedTime' ) values (1,'张飞','2016-12-12 12:20','2016-12-12 12:20'), (2,'关羽','2016-12-12 12:20','2016-12-12 12:20');

### 2、insert into on duplicate key update批量更新 ###

> 表中需要存在唯一索引

insert into test_tbl (id,name) values (1,'a'),(2,'b'),(x,'y') on duplicate key update name=values(name);

**示例：**

insert into book (Id,Author,CreatedTime,UpdatedTime) values (1,'张飞2','2017-12-12 12:20','2017-12-12 12:21'), (2,'关羽2','2017-12-12 12:20','2017-12-12 12:21'), (3,'刘备','2017-12-12 12:21','2017-12-12 12:21') on duplicate key update Author=values(Author), CreatedTime=values(CreatedTime), UpdatedTime=values(UpdatedTime);

**replace into  和 insert into on duplicate key update的不同在于：**

*  replace into 操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值，用这个要悠着点否则不小心清空大量数据可不是闹着玩的。
 *  insert into 则是只update重复记录，不会改变其它字段。

### 3、创建临时表，先更新临时表，然后从临时表中update ###

create temporary table tmp(id int(4) primary key, name varchar(50)); insert into tmp values (0,'gone'), (1,'xx'),(m,'yy'); update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

注意：这种方法需要用户有temporary 表的create 权限。

### 4、使用mysql 自带的语句构建批量更新 ###

> 注：mysql 实现批量 可以用点小技巧来实现

UPDATE t_user SET age = CASE id WHEN 1 THEN 23 WHEN 2 THEN 24 WHEN 3 THEN 25 END, name = CASE id WHEN 1 THEN '张飞2' WHEN 2 THEN '关羽2' WHEN 3 THEN '刘备2' END WHERE id IN (1,2,3)

**mybatis xml示例：**

<update id="update" parameterType="java.util.List">
        UPDATE dic_col_display
        SET column_code = CASE id
        <foreach collection="list" item="item" index="index" open="" separator="" close="">
            WHEN #{item.id} THEN #{item.column_code} 
        </foreach>
        END,
        modify_user_name = CASE id
        <foreach collection="list" item="item" index="index" open="" separator="" close="">
            WHEN #{item.id} THEN #{item.modify_user_name} 
        </foreach>
        END,
        modify_date = NOW()
        WHERE
          id in
          <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
              #{item.id} 
          </foreach>
    </update>

转载于:https://www.cnblogs.com/kancy/p/11432767.html