Java实现向MySQL数据库批量更新数据的常见方法
Java实现向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;
总结
上面一共介绍了六种办法,其中我比较推荐前三种,一是语法简单容易理解,最重要的是不容易出错,或者出错成本较低;而像第四、第五种其实不太推荐,因为真的容易出错;最后临时表这种方案,虽然也不麻烦,但我们往往并没有创建临时表的权限,所以不太现实。
案例
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 (?, ?, ?, ?, ?)
更多推荐
所有评论(0)