MySQL批量插入与更新

来源:csdn博客 分类: 文章浏览史 发布时间:2020-12-11 11:40:33 最后更新:2020-12-11 浏览:215
转载声明:
本文为摘录自“csdn博客”,版权归原作者所有。
温馨提示:
为了更好的体验,请点击原文链接进行浏览
摘录时间:
2020-12-11 11:40:33

目录

准备表和测试数据

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会忽略很多数据上的冲突与约束,平时很少使用。
php技术微信