MySql中4种批量更新的方法

原文地址  https://www.cnblogs.com/PatrickLiu/p/6385167.html

MySql中4种批量更新的方法

   最近在完成MySql项目集成的情况下，需要增加批量更新的功能，根据网上的资料整理了一下，四种方式各有优缺点。希望有一款能满足你

1、replace into 

replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中， 

如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

  replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(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批量更新

使用要点：

1、如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，
反之，则执行insert语句而不是更新语句，可以是联合唯一索引

2、on duplicate key update  后面如果写成  dr=values(dr) 代表使用values()中对应字段dr的值，如果写成dr = dr +1   代表dr在原值基础上+1

3、没有where 条件

insert into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);

例子： 其中  block_id, type_id, edge_id 三个字段构成 unique联合索引 ，

INSERT INTO fa_front_alarm_setting ( block_id, type_id, edge_id, alarm_val, content )
VALUES
    ( 10041, 'floor_wet1', 1, '30', '温度过高，请开启通风口，通风口先小后大，逐步进行dup' ),
    ( 10041, 'floor_wet2', 1, '30', '温度过高，请开启通风口，通风口先小后大，逐步进行dup' ),
    ( 10041, 'floor_wet3', 1, '30', '温度过高，请开启通风口，通风口先小后大，逐步进行' ),
    ( 10041, 'floor_wet4', 1, '30', '温度过高，请开启通风口，通风口先小后大，逐步进行' ),
    ( 10041, 'floor_wet1', 2, '-1', '温度过低，注意保暖dup' ),
    ( 10041, 'floor_wet2', 2, '-1', '温度过低，注意保暖dup' ),
    ( 10041, 'floor_wet3', 2, '-11', '温度过低，注意保暖' ),
    ( 10041, 'floor_wet4', 2, '-15', '温度过低，注意保暖' ) 
ON DUPLICATE KEY //这个on语句其实是一个条件语句，意思是，只有唯一索引或者主键冲突时，才执行下面的update语句
    UPDATE 
        alarm_val = VALUES (alarm_val),
        content = VALUES (content)   //主键或唯一索引字段不需要更新
    ;

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

    replace into 操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值，用这个要谨慎否则不小心会清空大量数据；
如果表需要自增ID，且这个ID关联其他表的字段，那最好不要用replace into，

insert into ... on duplicate key update  则是只update重复记录，不会改变其它字段。

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

    create temporary table tmp(id int(4) primary key,dr 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 yoiurtable
        SET dingdan = CASE id 
            WHEN 1 THEN 3 
            WHEN 2 THEN 4 
            WHEN 3 THEN 5 
        END
    WHERE id IN (1,2,3)

    这句sql 的意思是，更新dingdan 字段，如果id=1 则dingdan 的值为3，如果id=2 则dingdan 的值为4……
    where部分不影响代码的执行，但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数，这里只有3条数据进行更新，而where子句确保只有3行数据执行。   

    例子：UPDATE book
        SET Author = CASE id 
            WHEN 1 THEN '黄飞鸿' 
            WHEN 2 THEN '方世玉'
            WHEN 3 THEN '洪熙官'
        END
    WHERE id IN (1,2,3)

    如果更新多个值的话，只需要稍加修改：

    UPDATE categories      
        SET dingdan = CASE id 
            WHEN 1 THEN 3 
            WHEN 2 THEN 4 
            WHEN 3 THEN 5 
        END, 
        title = CASE id 
            WHEN 1 THEN 'New Title 1'
            WHEN 2 THEN 'New Title 2'
            WHEN 3 THEN 'New Title 3'
        END
    WHERE id IN (1,2,3)

   例子：UPDATE book
        SET Author = CASE id 
            WHEN 1 THEN '黄飞鸿2' 
            WHEN 2 THEN '方世玉2'
            WHEN 3 THEN '洪熙官2'
        END,
        Code = CASE id 
            WHEN 1 THEN 'HFH2' 
            WHEN 2 THEN 'FSY2'
            WHEN 3 THEN 'HXG2'
        END
    WHERE id IN (1,2,3)