mysql如何防止插入相同数据_Mysql

一、建唯一索引的方式

mysql在存在主键冲突或者唯一键冲突的情况下，根据插入策略不同，一般有以下三种避免方法

insert ignore
replace into
insert on duplicate key update

注意：除非表有一个primary key或unique索引，否则，使用以上三个语句没有意义，与使用单纯的insert into相同

表结构

create table `ups_lower_electricity_data` (
  `id` int(11) not null auto_increment,
  `componentinstanceid` int(11) default null,
  `starttime` bigint(20) default null,
  `endtime` bigint(20) default null,
  `type` int(4) default null,
  `electricity` decimal(8,2) default null,
  `year` int(20) default null,
  `month` int(20) default null,
  `day` int(20) default null,
  primary key (`id`) using btree
) engine=innodb auto_increment=164 default charset=utf8mb4 row_format=dynamic;

insert ignore

insert ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断)，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据

没有建索引插入

insert into ups_lower_electricity_data (componentinstanceid,starttime,endtime,type,electricity,year,month,day) values (499,1664121600000,1664146800000,3,18.00,2022,9,26)

建立索引进行插入

insert ignore into ups_lower_electricity_data (componentinstanceid,starttime,endtime,type,electricity,year,month,day) values (499,1664121600000,1664146800000,3,18.00,2022,9,26)

replace into

replace into 首先尝试插入数据到表中。如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据，然后插入新的数据，否则，直接插入新数据

使用replace into，你必须具有delete和insert权限

replace into  ups_lower_electricity_data (componentinstanceid,starttime,endtime,type,electricity,year,month,day) values (499,1664294400000,1664319600000,3,22.00,2022,9,28)

insert on duplicate key update

如果在insert into 语句末尾指定了on duplicate key update，并且插入行后会导致在一个unique索引或primary key中出现重复值，则在出现重复值的行执行update；如果不会导致重复的问题，则插入新行，跟普通的insert into一样

使用insert into，你必须具有insert和update权限

如果有新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2；如果记录被更新前后值是一样的，则受影响行数的值显示0

insert into ups_lower_electricity_data (componentinstanceid,starttime,endtime,type,electricity,year,month,day) values (499,1664294400000,1664319600000,3,22.00,2022,9,28) on duplicate key update day = day+1

insert…on duplicate key update产生死锁

insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上s(共享锁)锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，然后对该记录加上x(排他锁)，最后进行update写入。

如果有两个事务并发的执行同样的语句，那么就会产生death lock，如：

解决办法：

1、尽量对存在多个唯一键的table使用该语句
2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

结论：

这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。
insert ignore能忽略重复数据，只插入不重复的数据。
replace into和insert … on duplicate key update，都是替换原有的重复数据，区别在于replace into是删除原有的行后，在插入新行，如有自增id，这个会造成自增id的改变；insert … on duplicate key update在遇到重复行时，会直接更新原有的行，具体更新哪些字段怎么更新，取决于update后的语句

二、insert into if exists

语法

insert into table (field1, field2, fieldn) select
	'field1',
	'field2',
	'fieldn'
from
	dual
where
	not exists (
		select
			field
		from
			table
		where
			field = ?
	)

第一次执行：affected rows: 1，之后执行都是affected rows: 0，说明实现了单条记录的防重插入