https://yq.aliyun.com/ziliao/59813

MySql中4种批量更新的方法

最近在完成MySql项目集成的情况下，需要增加批量更新的功能，根据网上的资料整理了一下，很好用，都测试过，可以直接使用。

mysql 批量更新共有以下四种办法

1、.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批量更新

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

使用INSERT的时候有表T(id,A,B,C,D)

插入的时候希望通过A,B索引唯一记录，有重复的时候更新C,D

INSERT INTO T(A,B,C,D) VALUES (a,b,c,d) ON DUPLICATE KEY UPDATE C=C+1，D=d

这条语句相当于：

INSERT INTO ... VALUES ...

当INSERT(因为主键或唯一键冲突)失败时，执行

UPDATE ... SET ... WHERE A = a AND B = b

只是这个过程用一个原子语句来实现罢了，具体可参阅MySQL的官方文档： https://dev.mysql.com/doc/ref...

如果表中有多个唯一键，情况就更复杂了，有兴趣可以深入研究。
还有一个类似的语句叫 REPLACE 跟INSERT ... ON DUPLICATE KEY UPDATE作用类似，但原理是不一样的，有兴趣可参阅： https://dev.mysql.com/doc/ref...

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

例子：insert into book (`Id`,`Author`,`CreatedTime`,`UpdatedTime`) values (1,'张飞2','2017-12-12 12:20','2017-12-12 12:20'),(2,'关羽2','2017-12-12 12:20','2017-12-12 12:20') on duplicate key update Author=values(Author),CreatedTime=values(CreatedTime),UpdatedTime=values(UpdatedTime);

insert into tran_test (f1,f2) values ("A3",2),("B3",3) on duplicate key update f2=values(f2);

解释：f1是主键，唯一性，如果没有测插入（insert into tran_test (f1,f2) values ("A3",2),("B3",3)）。有责更新，更新的内容为update f2=values(f2); values就是insert的关键字values(对应的更新字段)

insert into tran_test (f1,f2) values ("A3",2),("B3",3) on duplicate key update f2=10; 有责更新 f2=10

   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,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)

    到这里，已经完成一条mysql语句更新多条记录了。

好了，很有用，记录下来，具体的Book的表结构不写了，大家可以建立自己的表结构，字段改成自己的字段就可以了。MySql是挺好用的。

MySQL 自4.1版以后开始支持INSERT … ON DUPLICATE KEY UPDATE语法，使得原本需要执行3条SQL语句（SELECT,INSERT,UPDATE），缩减为1条语句即可完成。

例如ipstats表结构如下：

代码如下	复制代码
CREATE TABLE ipstats ( ip VARCHAR(15) NOT NULL UNIQUE, clicks SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' );

原本需要执行3条SQL语句，如下：

代码如下	复制代码
IF (SELECT * FROM ipstats WHERE ip='192.168.0.1') { UPDATE ipstats SET clicks=clicks+1 WHERE ip='192.168.0.1'; } else { INSERT INTO ipstats (ip, clicks) VALUES ('192.168.0.1', 1); }

而现在只需下面1条SQL语句即可完成：

代码如下	复制代码
INSERT INTO ipstats VALUES('192.168.0.1', 1) ON DUPLICATE KEY UPDATE clicks=clicks+1;

注意，要使用这条语句，前提条件是这个表必须有一个唯一索引或主键。

总结如下：

1.如果表中不存在主键记录，replace和insert*update都与insert是一样的特点。
2.如果表中存在主键记录，replace相当于执行delete 和 insert两条操作，而insert*update的相当于执行if exist do update else do insert操作。因此，如果replace填充的字段不全，则会导致未被更新的字段都会修改为默认值，并且如果有自增id的话，自增id会变化为最新的值（这样如果是以自增id为标志的话可能导致记录丢失）；而insert*update只是更新部分字段，对于未被更新的字段不会变化（不会强制修改为默认值）。

多条记录操作：

代码如下	复制代码
insert into t(a,b,c) values ('a1','b1','c1'),('a2','b2','c2') on duplicate key update t.c=values(t.c)

前言

编辑

我们在实际业务场景中，经常会有一个这样的需求，插入某条记录，如果已经存在了则更新它如果更新日期或者某些列上的累加操作等，我们肯定会想到使用INSERT ... ON DUPLICATE KEY UPDATE语句，一条语句就搞定了查询是否存在和插入或者更新这几个步骤，但是使用这条语句在msyql的innodb5.0以上版本有很多的陷阱，即有可能导致death lock死锁也有可能导致主从模式下的replication产生数据不一致。

正文

正如前言说的那样，在实际业务中，曾经有过一个需求就是插入一条业务数据，如果不存在则新增，存在则累加更新某一个字段的值，于是乎就想到了使用insert... on duplicate key update这个语句，但是有一天去测试环境查看错误日志时，却发现了在多个事务并发执行同一条insert...on duplicate key update 语句时，也就是insert的内容相同时，发生了死锁。

对于insert...on duplicate key update这个语句会引发dealth lock问题，官方文档也没有相关描述，只是进行如下描述：

An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)

也就是如果一个表定义有多个唯一键或者主键时，是不安全的，这又引发了以一个问题，见https://bugs.mysql.com/bug.php?id=58637

也就是

     当mysql执行INSERT ON DUPLICATE KEY的 INSERT时，存储引擎会检查插入的行是否会产生重复键错误。如果是的话，它会将现有的
行返回给mysql，mysql会更新它并将其发送回存储引擎。当表具有多个唯一或主键时，此语句对存储引擎检查密钥的顺序非常敏感。根据这个顺序，
存储引擎可以确定不同的行数据给到mysql，因此mysql可以更新不同的行。存储引擎检查key的顺序不是确定性的。例如，InnoDB按照索引添加到
表的顺序检查键。

insert ... on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上S（共享锁）锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，然后对该记录加上X（排他锁），最后进行update写入。

如果有两个事务并发的执行同样的语句，那么就会产生death lock，如：