MySQL批量插入与更新

目录

准备表和测试数据

insert into values、insert into select

replace into

insert into on duplicate key update

insert ignore into

总结

---

准备表和测试数据

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_copy` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- t_copy 创建两条条数据
INSERT INTO t_copy VALUES (3, 30, 'c'),(4, 22, 'd');

---

insert into values、insert into select

INSERT INTO t 
VALUES
  (1, 20, 'a'),
  (2, 26, 'b');

-- 两张表的字段要一一对应
INSERT INTO t 
  SELECT id,age,name from t_copy where id < 10;

结果：

1  20  a
2  26  b
3  30  c
4  22  d

注：insert into values 或 insert into select批量插入时，都满足事务的原子性与一致性，一条出错整体都会回滚。在使用insert into select时，MySQL会对select的数据加S（读）锁，在事务较为复杂的场景下可能有死锁的风险，下篇博客会总结。 

---

replace into

replace into表示插入替换数据，当记录中有PrimaryKey，或者unique索引的话，如果数据库已经存在数据，则用新数据替换（先delete再insert），如果没有数据效果则和insert into一样。

REPLACE INTO t (id,age) VALUES (2, 15);

结果：

1  20  a
2  15  NULL
3  30  c
4  22  d

---

insert into on duplicate key update

 insert into on duplicate key update表示插入更新数据，当记录中有PrimaryKey，或者unique索引的话，如果数据库已经存在数据，则用新数据更新（update），如果没有数据效果则和insert into一样。

INSERT INTO t 
    (id, age) 
VALUES 
    (3, 28),
    (4, 29) 
ON DUPLICATE KEY UPDATE
    id = VALUES(id),
    age = VALUES(age);

结果：

1  20  a
2  15  NULL
3  28  c
4  29  d

---

insert ignore into

insert ignore into表示尽可能的忽略冲突，暴力插入。

INSERT IGNORE INTO t VALUES(1,30,'f'),(6,33,'o');

结果：

1  20  a
2  15  NULL
3  28  c
4  29  d
6  33  o

注：可以看到，虽然记录为1的数据会产生主键冲突，但insert ignore into会进行忽略，继续执行记录6的插入。另外除了唯一约束的冲突，类似于字段超出长度、类型不匹配等错误，insert into on duplicate key update与replace into都会整体失败，而insert ignore into不会失败，它对于类型不匹配且无法转化的提供了默认值，超出长度的按照最大长度进行了截取。

---

总结

1. insert into values 或 insert into select批量插入时，都满足事务的原子性与一致性，但要注意insert into select的加锁问题。
2. replace into与insert into on duplicate key update都可以实现批量的插入更新，具体是更新还是插入取决与记录中的pk或uk数据在表中是否存在。如果存在，前者是先delete后insert，后者是update。
3. insert ignore into会忽略很多数据上的冲突与约束，平时很少使用。