mysql：防止插入相同数据

柔情只为你懂 2024-04-03 07:23 58阅读 0赞

## 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;

![在这里插入图片描述][3d3a6024e730467ba5bcef18fe12de57.png_pic_center]

#### 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)

![在这里插入图片描述][64b196e19a244d35be3aa2288900758f.png_pic_center]

##### 建立索引进行插入 #####

![在这里插入图片描述][8ff925a23e0a42969a30e0575589dc63.png_pic_center]

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)

![在这里插入图片描述][01e06b36268f487891789c34427b8039.png_pic_center]

#### 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)

![在这里插入图片描述][45096a194e9f4cc7b868ab1a3f0d8ed0.png_pic_center]

#### 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

![在这里插入图片描述][e2dcddb0e087496b9ed3d091ad2ecaf5.png_pic_center]

**INSERT…ON DUPLICATE KEY UPDATE产生死锁**

insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误， 如果存在，在对该现有的行加上S(共享锁)锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作， 然后对该记录加上X(排他锁)，最后进行update写入。 如果有两个事务并发的执行同样的语句， 那么就会产生death lock，如：

![在这里插入图片描述][74b546bc823d443dac945fde0f6dd94b.png_pic_center]

> 解决办法
> 
> 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，说明实现了单条记录的防重插入

![在这里插入图片描述][c3acb64a85b24c07a7561a7db5cac44b.png_pic_center]

[3d3a6024e730467ba5bcef18fe12de57.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/9b3c0a770c224bbdbe9f95b85439a749.png
[64b196e19a244d35be3aa2288900758f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/098f6e5ed1aa4d1880820e22a1a3d6c4.png
[8ff925a23e0a42969a30e0575589dc63.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/2015e60aa6274579a54a13243c7cebd5.png
[01e06b36268f487891789c34427b8039.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/6c4f882764d14bc988a5411c5e9185a2.png
[45096a194e9f4cc7b868ab1a3f0d8ed0.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/692d125967704484b9708d83e8c3bb81.png
[e2dcddb0e087496b9ed3d091ad2ecaf5.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/3373aeabf2ed43eb885bb17cc22c8758.png
[74b546bc823d443dac945fde0f6dd94b.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/2112e85ad3c94fa6ad0320e78c9dd812.png
[c3acb64a85b24c07a7561a7db5cac44b.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/d37de63e30bb40cfafa09aef67e74efc.png