MySQL 批量更新数据的六种方法

本文主要讲述在MySQL中批量更新数据的六种方法，供参考。

一、IN

第一种方式是借助IN语句，这种方式局限性较大，更新结果必须一致，比如下面就是将满足条件的行的状态（status）都置为1。
如果是一部分置为1，一部分置为2等，则无法实现，要么就是写多条SQL语句。

Update users Set status=1 Where account IN ('xx1', 'xx2');

二、For + Update

第二种方式是借助For循环+Update语句，即一条一条地更新，优点是清晰直观，能适用大部分情况，而且不容易出错，缺点是性能较差，且容易造成阻塞。
如果是在MySQL客户端执行，这种方式其实不太方便，一般要生成多条Update语句，亦或者用存储过程实现；如果是在第三方库中执行，直接用 For循环 + 封装的Update语句即可实现。

三、insert into…on duplicate key update

第三种方式是借助主键（或者唯一键）的唯一性进行更新，优点是支持批量更新，且更新结果不需要一致，缺点就是一般第三方库并不支持这种语法，需要写原生SQL，还有就是所有字段都必须有默认值（包括NULL）。
测试过程如下：

#创建表
create table users
(
id int(11) PRIMARY KEY AUTO_INCREMENT,
name varchar(255) NOT NUll DEFAULT '',
age smallint,
job varchar(255)
);

#插入测试数据
INSERT INTO go_business.users (id, name, age, job) VALUES (1, 'name1', 1, 'job1');
INSERT INTO go_business.users (id, name, age, job) VALUES (2, 'namw2', 2, 'job2');
INSERT INTO go_business.users (id, name, age, job) VALUES (3, 'name3', 3, 'job3');
INSERT INTO go_business.users (id, name, age, job) VALUES (4, 'name4', 4, 'job4');
INSERT INTO go_business.users (id, name, age, job) VALUES (5, 'name5', 5, 'job5');

#批量更新
mysql> insert into users (id, job, age) values (1, 'job11', 11),(2, 'job22', 22)
on duplicate key update job=values(job), age=values(age);

#最终结果
mysql> select * from users where id in (1, 2);
+----+-------+------+-------+
| id | name | age | job |
+----+-------+------+-------+
| 1 | name1 | 11 | job11 |
| 2 | namw2 | 22 | job22 |
+----+-------+------+-------+

四、replace into

第四种方式是replace into ，从字面看就知道是替换的作用，相当于更新。它的语法与第三种方式比较相近，但是却比第三种方式危险，原因是如果更新时字段不全，没被覆盖到的字段会被置为默认值。

#还是沿用上面的数据
replace into users(id, job, age) VALUES (1, 'job111', 111),(2, 'job222', 222);

#可以看到没被覆盖到的字段（name），会被置成默认值，这显然与我们的初衷不符
mysql> select * from users where id in (1, 2);
+----+------+------+--------+
| id | name | age | job |
+----+------+------+--------+
| 1 | | 111 | job111 |
| 2 | | 222 | job222 |
+----+------+------+--------+
2 rows in set (0.00 sec)

究其原因，replace into 操作的本质是对重复的记录先 delete 后 insert，所以如果更新的字段不全会将缺失的字段置为默认值；而 insert into 只是update重复记录，不会改变其它字段。

五、set…case…when…where

第五种方式是Set…Case…When…Where语句，优点是可以批量更新，也支持更新多个字段和更新多种结果，缺点就是语句较长，实现较为麻烦，且较容易出错。
一般是借助主键，或者唯一键进行更新，测试如下：

# 情况一：利用主键ID更新
update users 
	set job = case id
		when 1 then 'job11'
		when 2 then 'job12'
	end,
			age = case id
		when 1 then 11
		when 2 then 12
	end
where id IN (1, 2);

mysql> select * from users where id in (1, 2);
+----+-------+------+-------+
| id | name  | age  | job   |
+----+-------+------+-------+
|  1 | name1 |   11 | job11 |
|  2 | name2 |   12 | job12 |
+----+-------+------+-------+

使用不当主要有两种情况：

# 一、条件不存在
update users
	set job = case id
		when 1 then 'job11'
		when 3 then 'job13'
	end,
		age = case id
		when 1 then 11
		when 2 then 12
	end
where id IN (1, 2);

# 可以看到，如果条件不存在（id=3），对应的字段会被置为默认值，这显然也不是我们想要的
select * from users where id in (1, 2);
+----+-------+------+-------+
| id | name  | age  | job   |
+----+-------+------+-------+
|  1 | name1 |   11 | job11 |
|  2 | name2 |   12 | NULL  |
+----+-------+------+-------+

# 二、不带where子句
update users
	set job = case id
		when 1 then 'job11'
		when 2 then 'job12'
	end,
		age = case id
		when 1 then 11
		when 2 then 12
	end;

# 这种方式极其危险，因为会进行全表更新，同时条件不存在的被置为默认值
select * from users;
+----+-------+------+-------+
| id | name  | age  | job   |
+----+-------+------+-------+
|  1 | name1 |   11 | job11 |
|  2 | name2 |   12 | job12 |
|  3 | name3 | NULL | NULL  |
|  4 | name4 | NULL | NULL  |
|  5 | name5 | NULL | NULL  |
+----+-------+------+-------+

通过上面的测试我们可以看到，这种操作方式其实还蛮危险的，稍微不慎字段就会被更新为默认值，所以使用时要非常慎重，万万不可漏了Where子句。

六、创建临时表

第六种方式是创建临时表，临时表的思路是用另一张表的数据来替换，但一般情况下，我们并没有创建表的权限，所以这种思路不太现实。

# 临时表的字段只需要更新的字段即可
create temporary table users_tmp
(
    id      int(11) PRIMARY KEY AUTO_INCREMENT,
    age     smallint,
    job     varchar(255)
);

# 插入要更新的数据
insert into users_tmp(id, job, age) values (1, 'job11', 11), (2, 'job22', 22);

# 有点类似连表更新
update users, users_tmp set users.job=users_tmp.job, users.age=users_tmp.age where users.id=users_tmp.id;

总结
上面一共介绍了六种办法，其中我比较推荐前三种，一是语法简单容易理解，最重要的是不容易出错，或者出错成本较低；如果需要更新多个字段和更新多种结果，那么第五种方法比合适；最后临时表这种方案，虽然也不麻烦，但我们往往并没有创建临时表的权限，所以不太现实。