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');
具体使用那种,可以看情况