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 = ?
    )