MySQL批量更新数据的六种方法,你能想出第七种吗?

数据库 MySQL
我们都知道MYSQL中批量插入非常简单,那么批量更新呢?

我们都知道MYSQL中批量插入非常简单,那么批量更新呢?

1.IN

IN 语句有比较大的局限性,更新后的结果必须一致。比如下面是将所有满足条件的行的状态(status)设置为1。

如果想部分设置为1,部分设置为2等,则无法实现,或者通过写多条SQL语句实现。

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

2. For + Update

借助 For 循环 + Update 语句,即逐一更新,优点是清晰直观,适用于大部分情况,不易出错。缺点是性能较差,容易造成堵塞。

如果是在MYSQL客户端执行,这种方法很不方便。一般需要生成多条Update语句,或者可以用存储过程实现。

3. 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 |
+----+-------+------+-------+

4. Replace into

众所周知,它是一个替换,相当于一个 update。语法类似于第三种方法,但比第三种方法更危险,因为更新时如果字段不完整,未覆盖的字段将被设置为默认值。

replace into users(id, job, age) VALUES (1, 'job111', 111),(2, 'job222', 222);

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 操作的本质是先删除重复记录再插入,所以如果更新的字段不完整,缺失的字段会被设置为默认值,而 insert into 只是更新重复记录,不会改变其他字段。

5. Set…case…when…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
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);

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

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子句

6.创建临时表

临时表的方式是替换另一个表的数据,但是一般情况下我们是没有创建表的权限的,所以这个想法可能不太现实。

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;


责任编辑:华轩 来源: 今日头条
相关推荐

2010-10-08 11:13:22

MySQL修改密码

2022-05-30 16:42:20

数据中心

2011-02-24 10:56:34

人才

2023-09-06 08:00:00

ChatGPT数据分析

2011-12-16 14:45:36

JavaJSP

2011-05-30 13:37:46

JSP

2016-09-28 20:05:22

2021-11-19 10:10:14

手机移动设备网络攻击

2015-06-09 15:23:50

2022-10-27 08:09:33

2023-04-18 15:57:30

2024-07-29 08:00:00

2022-09-30 10:48:12

AR制造业

2023-12-08 08:53:37

数据中心人工智能自动化

2022-01-21 10:42:55

IT领导者IT事业职业顾问

2021-12-06 06:58:50

List重复数据

2021-07-02 10:43:52

IT人才首席信息官人才招聘

2009-10-29 16:32:24

查看Oracle用户的

2023-04-26 08:41:16

Git撤消更改

2023-04-03 20:29:00

Linux环境变量
点赞
收藏

51CTO技术栈公众号