一、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;

总结

上面一共介绍了六种办法,其中我比较推荐前三种,一是语法简单容易理解,最重要的是不容易出错,或者出错成本较低;而像第四、第五种其实不太推荐,因为真的容易出错;最后临时表这种方案,虽然也不麻烦,但我们往往并没有创建临时表的权限,所以不太现实。

案例

public Integer batchUpdate(List<ManualVo> mVo) {
    StringBuffer sql = new StringBuffer();
    StringBuffer where = new StringBuffer(" where manual_id in (");
    List<Object> params = new ArrayList<>();
    List<Object> whereParams = new ArrayList<>();
    sql.append("update manual set execution_seq = case  manual_id");
    mVo.forEach(mvo -> {
        sql.append(" when ? then ? ");
        where.append(" ? , ");
        params.add(mvo.getManualId());
        params.add(mvo.getExecutionSeq());
        whereParams.add(mvo.getManualId());
    });
    sql.append(" end ");
    String whereSql =  StringUtils.substringBeforeLast(where.toString()," ,") + ") ";
    sql.append(whereSql);
    params.addAll(whereParams);
    Object[] args = params.toArray();
    return jdbcTemplate.update(sql.toString(),args);
}
SET execution_seq = CASE manual_id
WHEN ? THEN
	?
WHEN ? THEN
	?
WHEN ? THEN
	?
WHEN ? THEN
	?
WHEN ? THEN
	?
END
WHERE
	manual_id IN (?, ?, ?, ?, ?)
Logo

腾讯云面向开发者汇聚海量精品云计算使用和开发经验,营造开放的云计算技术生态圈。

更多推荐