mysql批量更新数据操作

前置操作

-- 创建一个表， id设置主键，名字设置唯一索引
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int,
  `gender` varchar(255),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
);
​
-- 新增基础数据
insert into user(name, age, gender) values ('小红', 18, '女'),('小明', 18, '男');

1、使用 ON DUPLICATE KEY UPDATE 批量更新

适合做新增并更新的操作，当需要新增的数据和已存在的数据索引重复，就会对已存在的数据进行后面的on duplicate key update操作，如果数据不存在数据库则直接新增数据

insert into user(name, age, gender) values ('小黄', 13, '女'),('小明', 11, '男') 
on duplicate key update age = values(age);

执行了上面的操作后，数据库的数据更新为 ('小红', 18, '女'),('小黄', 13, '女'),('小明', 11, '男')

注意: 要更新的字段在on duplicate key update关键字后面设置，上面语句中我只对age进行了更新

2、使用replace into批量更新

和ON DUPLICATE KEY UPDATE 的区别是，replace into对重复的数据会先进行delete操作再insert

replace into user(name, age) values('小黄', 0);

执行了上面的操作后，数据库的数据更新为 ('小红', 18, '女'),('小明', 11, '男'),('小黄', 0, null)

注意：此小黄非彼小黄，这个小黄是新增的记录，原来的记录被delete了

3、使用临时表

仅对已存在的数据进行更新操作，可以处理相对复杂的逻辑

-- 临时表条件更新
CREATE temporary table tmp(
  `name` varchar(255) NOT NULL UNIQUE KEY,
  `age`,
  `gender` varchar(255)
);
​
insert into tmp(name, age, gender) values ('小黄', 11, '女'),('小明', 19, '男');
-- 关联更新
update user, tmp set user.age = tmp.age, user.gender = tmp.gender 
where user.name = tmp.name;
​
drop temporary table tmp;