数据库避免插入重复数据需求解决：MySQL之insert、insert ignore、replace和insert into on duplicate key update区别

背景

最近在做项目的时候，简单的后台增删改查，但是每次在做新增数据操作的时候，都需要校验一次数据库中是否存在相同的数据（唯一索引字段），起初同时的做法是每次按照唯一主键作为条件去数据库中进行查询，如果存在，则不进行insert操作，如果不存在，则进行insert操作，这样做有两个弊端：

1. 每次进行insert操作之前都会有一步select操作，我们知道，数据库的io是比较耗性能的，尤其是在数据量比较大的情况下；
2. 本人想偷懒，每次去查询太繁琐；
3. 在并发场景下同意出问题，除非加锁（目前还没有这个场景，博主未验证~）

那么到底有没有什么比较爽的方式可以解决这样的问题呢?

答案是当然的！

下面就来看看博主总结的几种方式，大家可以根据情况自行选取：

1.insert

2.insert ignore

3.replace

4.insert into on duplicate key update

测试代码

创建表

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_username_password` (`username`,`password`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
});

插入数据

insert into test (username, password, age) values ('xzb','xzb', 11);
insert into test (username, password, age) values ('xzb1','xzb1', 12);
insert into test (username, password, age) values ('xzb2','xzb2', 13);

insert

1. 插入已存在, id会自增，但是插入不成功，会报错

insert into test (username, password, age) values ('xzb','xzb', 11);

[Err] 1062 - Duplicate entry 'xzb-xzb' for key 'test.index_username_password'

1. 插入不存在, id会自增，插入成功，不报错

insert into test (username, password, age) values ('xzb3','xzb3', 14);

insert ignore

1. 插入已存在，忽略新插入的记录，id会自增，不会报错

insert ignore into test (username, password, age) values ('xzb3','xzb3', 14);

1. 插入不存在，添加新的记录

insert into test (username, password, age) values ('xzb4','xzb4', 15);

replace

1. 已存在替换，删除原来的记录，添加新的记录

replace into test (username, password, age) values ('xzb', 'xzb', 11);

1. 不存在替换，添加新的记录

replace into test (username, password, age) values ('xzb5', 'xzb5', 15);

insert into on duplicate key update

1. 已存在替换，根据UPDATE进行相应操作，如没有字段变更，则不会有变动，且ID不会自增

INSERT INTO test (username, password, age) VALUES ('xzb', 'xzb', 11) ON DUPLICATE KEY UPDATE id = id;

INSERT INTO test (username, password, age) VALUES ('xzb', 'xzb', 11) ON DUPLICATE KEY UPDATE username = "xxb"

1. 不存在替换，添加新的记录，且UPDATE不生效

INSERT INTO test (username, password, age) VALUES ('xzb6', 'xzb6', 16) ON DUPLICATE KEY UPDATE username = "xxb";

总结

指令	已存在	不存在	举例
insert	报错	插入	insert into test (username,password) values ('xzb','xzb');
insert ignore	忽略	插入	insert ignore into test (username,password) values ('xzb','xzb');
replace	忽略	插入	REPLACE INTO test (username,password) VALUES ('xzb', 'xzb');
insert into on duplicate key update	更新	插入	INSERT INTO test (username,password) VALUES ('xzb', 'xzb') ON DUPLICATE KEY UPDATE id = id;