MySQL如何实现批量更新为不同的值？三种方法：临时表，prepared statement，on duplicate key

前言

在对MySQL进行大量数据导入的时候，对于插入，我们通常会采用批量插入的方式

insert into `tb` (`id`, `f1`, `f2`) values (1, 'a','b'), (2, 'c', 'd'), (3, 'e', 'f')...

这样一方面是能够节约网络IO的次数，另一方面也能减少MySQL硬解析的时间，对于效率的提升有很大帮助。

那么对于批量更新，如果我们想更新为不同的值，该如何去操作呢？

标准SQL

其实，我们想实现批量更新，其实就是要优化大量数据更新的时间，最简单的方式就是将其放到一个连接里去执行，即：

update `tb` set f1 = 'c', f2 = 'd' where id = 1;update `tb` set f1 = 'e', f2 = 'f' where id = 2;update `tb` set f1 = 'g', f2 = 'h' where id = 3;

这样能够减少网络IO的次数，节约时间。

更进一步的，我们可以将其作为一个事务来做，一方面保证原子性，另一方面减少事务提交次数，提升性能。因此，在执行update之前，我们先start transcation，之后再commit，即

start transaction; update `tb` set f1 = 'c', f2 = 'd' where id = 1;update `tb` set f1 = 'e', f2 = 'f' where id = 2;update `tb` set f1 = 'g', f2 = 'h' where id = 3; commit;

将其放到一个连接中执行。

但是我们注意到所有的update语句有着相同的形式，这可以用prepared statement来优化，节约硬解析的时间，当sql语句很复杂时，能有很大的效率提升。即：

start transaction; prepare stmt from "update `tb` set `f1` = ?, `f2` = ? where `id` = ?"; set @a = 1, @b = 'a', @c = 'b'; execute stmt using @a, @b, @c; set @a = 2, @b = 'c', @c = 'd'; execute stmt using @a, @b, @c; set @a = 3, @b = 'e', @c = 'f'; execute stmt using @a, @b, @c; set @a = 4, @b = 'g', @c = 'h'; execute stmt using @a, @b, @c; ...... commit;

这也是本文介绍的第一个方法，将sql语句放到一个请求中减少网络IO次数，将语句封装到单一事务中保证原子性、减少事务提交次数，利用prepared statement节约硬解析时间，这是sql的标准语法。

我们想要节约硬解析时间还有第二种方法，利用临时表：

create table `tmp_table` (`id`, `f1`, `f2`); insert into `tmp_table` (`id`, `f1`, `f2`) values (1, v11, v12), (2, v21, v22), (3, v31, v32)... update `origin_table` as ogn, `tmp_table` as tmp set ogn.f1 = tmp.f1 and ogn.f2 = tmp.f2 where ogn.id = tmp.id; drop table `tmp_table`;

我们创建一个临时表来存储所有待更新的数据，随后用临时表来更新原表，这样也减少了sql语句的重复解析。

这是本文介绍的第二种方法，同样是将待更新的数据放到了一个请求中减少网络IO次数，批量插入临时表减少硬解析时间，这也是SQL标准语法。

MySQL特有语法 on duplicate key

接下来介绍的这种方法是MySQL所自有的语法，因此若迁移到其他数据库可能会带来兼容性问题，不过按照笔者的测试，这种方法在MySQL中是效率最高的。

insert into `tb` (`id`, `f1`, `f2`) values (1, val11, val12), (2, val21, val22), (3, val31, val32) on duplicate key update f1 = f1 + values(f1), f2 = f2 + values(f2);

这种方法需要记录有唯一键或主键，当出现键重复冲突时，MySQL会执行后面的update语句，我们利用values()来动态的选取对应的值。这也是最简洁的一种实现方式，只需一条sql语句即可实现。

这是本文介绍的第三种方法，将待更新的 数据放到一个请求中减少网络IO次数，利用on duplicate key和values()实现批量更新为不同的值，这是MySQL自有语法。

问题

以上三种方法，都存在着一个问题，那就是批量更新数据可能会带来的死锁问题。这主要是因为我们要把批量操作封装为原子性的，如果不是原子性的，就可能会带来部分更新成功，部分更新失败的问题，难以处理。

对于死锁问题，我们可以为每个批量更新操作暴力的加一个表锁来解决，但是这种做法对性能的影响可能会比较大，但是考虑到死锁带来的影响，这种损失是可以接受的。