一、建唯一索引的方式

mysql在存在主鍵沖突或者唯一鍵沖突的情況下，根據(jù)插入策略不同，一般有以下三種避免方法

• insert ignore
• replace into
• insert on duplicate key update

注意：除非表有一個(gè)PRIMARY KEY或UNIQUE索引，否則，使用以上三個(gè)語(yǔ)句沒(méi)有意義，與使用單純的INSERT INTO相同

表結(jié)構(gòu)

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會(huì)忽略數(shù)據(jù)庫(kù)中已經(jīng)存在的數(shù)據(jù)(根據(jù)主鍵或者唯一索引判斷)，如果數(shù)據(jù)庫(kù)沒(méi)有數(shù)據(jù)，就插入新的數(shù)據(jù)，如果有數(shù)據(jù)的話(huà)就跳過(guò)這條數(shù)據(jù)

沒(méi)有建索引插入

insert into ups_lower_electricity_data (componentInstanceId,startTime,endTime,type,electricity,year,month,day) values (499,1664121600000,1664146800000,3,18.00,2022,9,26)

建立索引進(jìn)行插入

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 首先嘗試插入數(shù)據(jù)到表中。 如果發(fā)現(xiàn)表中已經(jīng)有此行數(shù)據(jù)(根據(jù)主鍵或者唯一索引判斷)則先刪除此行數(shù)據(jù)，然后插入新的數(shù)據(jù)，否則，直接插入新數(shù)據(jù)

使用replace into，你必須具有delete和insert權(quán)限

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 語(yǔ)句末尾指定了on duplicate key update，并且插入行后會(huì)導(dǎo)致在一個(gè)UNIQUE索引或PRIMARY KEY中出現(xiàn)重復(fù)值，則在出現(xiàn)重復(fù)值的行執(zhí)行UPDATE；如果不會(huì)導(dǎo)致重復(fù)的問(wèn)題，則插入新行，跟普通的insert into一樣

使用insert into，你必須具有insert和update權(quán)限

如果有新記錄被插入，則受影響行的值顯示1；如果原有的記錄被更新，則受影響行的值顯示2；如果記錄被更新前后值是一樣的，則受影響行數(shù)的值顯示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產(chǎn)生死鎖

insert … on duplicate key 在執(zhí)行時(shí)，innodb引擎會(huì)先判斷插入的行是否產(chǎn)生重復(fù)key錯(cuò)誤， 如果存在，在對(duì)該現(xiàn)有的行加上S(共享鎖)鎖，如果返回該行數(shù)據(jù)給mysql,然后mysql執(zhí)行完duplicate后的update操作， 然后對(duì)該記錄加上X(排他鎖)，最后進(jìn)行update寫(xiě)入。

如果有兩個(gè)事務(wù)并發(fā)的執(zhí)行同樣的語(yǔ)句， 那么就會(huì)產(chǎn)生death lock，如：

解決辦法：

• 1、盡量對(duì)存在多個(gè)唯一鍵的table使用該語(yǔ)句
• 2、在有可能有并發(fā)事務(wù)執(zhí)行的insert 的內(nèi)容一樣情況下不使用該語(yǔ)句

結(jié)論：

• 這三種方法都能避免主鍵或者唯一索引重復(fù)導(dǎo)致的插入失敗問(wèn)題。
• insert ignore能忽略重復(fù)數(shù)據(jù)，只插入不重復(fù)的數(shù)據(jù)。
• replace into和insert … on duplicate key update，都是替換原有的重復(fù)數(shù)據(jù)，區(qū)別在于replace into是刪除原有的行后，在插入新行，如有自增id，這個(gè)會(huì)造成自增id的改變；insert … on duplicate key update在遇到重復(fù)行時(shí)，會(huì)直接更新原有的行，具體更新哪些字段怎么更新，取決于update后的語(yǔ)句

二、INSERT INTO IF EXISTS

語(yǔ)法

INSERT INTO TABLE (field1, field2, fieldn) SELECT
	'field1',
	'field2',
	'fieldn'
FROM
	DUAL
WHERE
	NOT EXISTS (
		SELECT
			field
		FROM
			TABLE
		WHERE
			field = ?
	)

第一次執(zhí)行：Affected rows: 1，之后執(zhí)行都是Affected rows: 0，說(shuō)明實(shí)現(xiàn)了單條記錄的防重插入

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論