SQL语句如何避免在mysql插入重复数据

目录

准备

insert ignore into

on duplicate key update

replace into

insert if not exists

---

如何避免在mysql插入重复数据？

最常见的做法，就是唯一索引或主键，不过，有时候不能只用唯一索引或者主键，毕竟真的重复了，会报错，如果处理不当，会有问题，因此，有一些特殊的语句就可以拿来用

准备

先进行表的准备工作，

CREATE TABLE `usermessage` (
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `index_username` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert ignore into

在插入数据时，如果数据存在，就忽略

前提条件要求插入的字段设置了主键或者唯一索引，插入数据时，DB会首先查找已有数据，如果存在，本次就不插入，不存在，就正常插入

我们先插入一条

INSERT INTO usermessage(`name`, `word`) VALUES ('ad', 'ad123');

然后我们试试再次插入此记录

看，影响行数0，我们再试试别的记录

on duplicate key update

插入数据时，如果存在，那就更新

前提也是要有主键或者唯一索引，插入数据时，DB会首先查找已有数据，如果存在，本次就更新，不存在，就正常插入

INSERT INTO usermessage(`name`, `word`) VALUES ('sysadmin', 'update_password') on duplicate key update word ='update_password';

这是表里的数据，我们执行以下SQL，再看结果

replace into

插入数据时，如果数据存在，那就删除再插入

前提也是要有主键或者唯一索引，如果存在，那就先删除，不存在，就直接插入

这是替换前的数据，我们执行以下SQL

replace into usermessage(name, word) values ('root', 'root_replace');

注意，id也变了，id我们设置的是自增的，所以是删除再插入的，不是修改

insert if not exists

如果不存在就插入

前面三种都有主键或者唯一索引的限制，这一种没有

insert into usermessage(`name`, word) select 'user1', 'reset' from DUAL where not exists (select `name` from usermessage where `name` = 'user1');

 具体使用那种，可以看情况