設(shè)置主鍵id自增，name為唯一索引

一、避免重復(fù)插入

insert ignore into（有唯一索引）

關(guān)鍵字/句: insert ignore into，如果插入的數(shù)據(jù)會(huì)導(dǎo)致 UNIQUE索引 或 PRIMARY KEY 發(fā)生沖突/重復(fù)，則忽略此次操作/不插入數(shù)據(jù)，例:

INSERT IGNORE INTO `student`(`name`,`age`) VALUES(`Jack`，18);

-- row(s) affected

這里已經(jīng)存在 name='Jack”的數(shù)據(jù)，所以會(huì)忽略新插入的數(shù)據(jù)，受影響行數(shù)為 0，表數(shù)據(jù)不變

需要注意 主鍵會(huì)自增

當(dāng)使用了insert ignore into 新增數(shù)據(jù)，即使沒(méi)有插入，某些版本的mysql會(huì)自增主鍵。
比如原來(lái)有數(shù)據(jù)1（id為1），你又插入了數(shù)據(jù)1，但是重復(fù)了沒(méi)插入，之后再插入數(shù)據(jù)2，此時(shí)的數(shù)據(jù)2的主鍵為3而非2.
mysql5.7.26不會(huì)自增，8.0會(huì)自增

如何避免自增？

在MySQL5.7中做INSERT IGNORE時(shí)發(fā)現(xiàn), 即使INSERT未成功執(zhí)行, 表的自增主鍵卻自動(dòng)加1了, 在某些情況下需要避免這種行為. 需要修改的變量是 innodb_autoinc_lock_mode, 將其設(shè)為0后, 在INSERT未成功執(zhí)行時(shí)不會(huì)自增主鍵.

innodb_autoinc_lock_mode在MySQL各版本的默認(rèn)值

根據(jù)MySQL官方手冊(cè)的說(shuō)明:

There are three possible settings for the innodb_autoinc_lock_mode configuration parameter. The settings are 0, 1, or 2, for “traditional”, “consecutive”, or “interleaved” lock mode, respectively. As of MySQL 8.0, interleaved lock mode (innodb_autoinc_lock_mode=2) is the default setting. Prior to MySQL 8.0, consecutive lock mode is the default (innodb_autoinc_lock_mode=1).

在MySQL8中, 默認(rèn)值為 2 (interleaved, 交錯(cuò)), 在MySQL8以前, 準(zhǔn)確地說(shuō)在8之前, 5.1之后, 默認(rèn)值為 1 (consecutive, 連續(xù)), 在更早的版本是 0

innodb_autoinc_lock_mode的說(shuō)明

這個(gè)值主要用于平衡性能與安全(主從的數(shù)據(jù)一致性), insert主要有以下類型

simple insert 如insert into t(name) values(‘test’)
bulk insert 如load data | insert into … select … from …
mixed insert 如insert into t(id,name) values(1,‘a(chǎn)’),(null,‘b’),(5,‘c’);
innodb_autoinc_lock_mode = 0:

與更高版本的MySQL向后兼容
在這一模式下，所有的insert語(yǔ)句都要在語(yǔ)句開(kāi)始的時(shí)候得到一個(gè)表級(jí)的auto_inc鎖，在語(yǔ)句結(jié)束的時(shí)候才釋放這把鎖，一個(gè)事務(wù)可能包涵有一個(gè)或多個(gè)語(yǔ)句
它能保證值分配的可預(yù)見(jiàn)性，與連續(xù)性，可重復(fù)性，這個(gè)也就保證了insert語(yǔ)句在復(fù)制到slave的時(shí)候還能生成和master那邊一樣的值(它保證了基于語(yǔ)句復(fù)制的安全)
由于在這種模式下auto_inc鎖一直要保持到語(yǔ)句的結(jié)束，所以這個(gè)就影響到了并發(fā)的插入
innodb_autoinc_lock_mode = 1:

這一模式對(duì)simple insert做了優(yōu)化，由于simple insert一次性插入值的個(gè)數(shù)可以立即確定, 所以mysql可以一次生成幾個(gè)連續(xù)的值用于這個(gè)insert語(yǔ)句, 總的來(lái)說(shuō)這個(gè)對(duì)復(fù)制也是安全的(保證了基于語(yǔ)句復(fù)制的安全)
這一模式也是MySQL8.0之前的默認(rèn)模式, 這個(gè)模式的好處是auto_inc鎖不要一直保持到語(yǔ)句的結(jié)束, 只要語(yǔ)句得到了相應(yīng)的值后就可以提前釋放鎖
innodb_autoinc_lock_mode = 2:

由于這個(gè)模式下已經(jīng)沒(méi)有了auto_inc鎖, 所以這個(gè)模式下的性能是最好的, 但是它也有一個(gè)問(wèn)題, 就是對(duì)于同一個(gè)語(yǔ)句來(lái)說(shuō)它所得到的auto_incremant值可能不是連續(xù)的
現(xiàn)在mysql已經(jīng)推薦把二進(jìn)制的格式設(shè)置成row, 所以在binlog_format不是statement的情況下這個(gè)模式可以達(dá)到最好的性能

insert if not exists（無(wú)唯一索引）

數(shù)據(jù)字段沒(méi)有設(shè)置主鍵或唯一索引，當(dāng)插入數(shù)據(jù)時(shí)，首先判斷是否存在這條數(shù)據(jù)，不存在正常插入，存在則忽略?，F(xiàn)在我把主鍵和唯一索引都去掉了。完整sql為

# 示例一:插入多條記錄,假設(shè)有一個(gè)主鍵為 client_id 的 clients 表,可以使用下面的語(yǔ)句:

INSERT INTO clients (client_id,client_name,client_type)
SELECT supplier_id,supplier_name,’advertising’
FROM suppliers
WHERE not exists
(select * from clients
where clients.client_id = suppliers.supplier_id);

#示例二:插入單條記錄
INSERT INTO clients(client_id,client_name,client_type)
SELECT 10345,’IBM’,’advertising’
FROM dual
WHERE not exists
(select*from clients
where clients.client_id=10345
);

二、不存在則插入，存在則更新

on duplicate key update

如果插入的數(shù)據(jù)會(huì)導(dǎo)致UNIQUE 索引或PRIMARY KEY發(fā)生沖突/重復(fù)，則執(zhí)行UPDATE語(yǔ)句，例：

#name 有唯一索引
INSERT INTO `student`(`name`, `age`) VALUES('Jack', 19)
  ON DUPLICATE KEY  UPDATE `age`=19; 
  -- If will happen conflict, the update statement is executed

-- 2 row(s) affected

這里受影響的行數(shù)是2，因?yàn)閿?shù)據(jù)庫(kù)中存在name='Jack’的數(shù)據(jù)，如果不存在此條數(shù)據(jù)，則受影響的行數(shù)為1

可能遇到死鎖

bug在5.7.26以及8.0.15版本上已經(jīng)修復(fù)了，當(dāng)插入數(shù)據(jù)時(shí)，不會(huì)在形成間隙鎖

但是此方法也有坑，如果表中不止一個(gè)唯一索引的話，在特定版本的mysql中容易產(chǎn)生dead lock（死鎖）

當(dāng)mysql執(zhí)行INSERT ON DUPLICATE KEY的 INSERT時(shí)，存儲(chǔ)引擎會(huì)檢查插入的行是否會(huì)產(chǎn)生重復(fù)鍵錯(cuò)誤。如果是的話，它會(huì)將現(xiàn)有的
行返回給mysql，mysql會(huì)更新它并將其發(fā)送回存儲(chǔ)引擎。當(dāng)表具有多個(gè)唯一或主鍵時(shí)，此語(yǔ)句對(duì)存儲(chǔ)引擎檢查密鑰的順序非常敏感。根據(jù)這個(gè)順序，
存儲(chǔ)引擎可以確定不同的行數(shù)據(jù)給到mysql，因此mysql可以更新不同的行。存儲(chǔ)引擎檢查key的順序不是確定性的。例如，InnoDB按照索引添加到
表的順序檢查鍵。

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寫入。
如果有兩個(gè)事務(wù)并發(fā)的執(zhí)行同樣的語(yǔ)句，那么就會(huì)產(chǎn)生death lock

mysql官方描述很簡(jiǎn)單

An INSERT … ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)

insert on duplicate key update 如果命中主鍵或者唯一鍵索引，加行鎖，未命中加gap鎖，即會(huì)阻塞插入數(shù)據(jù)

過(guò)程分析
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寫入。
如果有兩個(gè)事務(wù)并發(fā)的執(zhí)行同樣的語(yǔ)句，那么就會(huì)產(chǎn)生death lock，如：

低版本的解決方法

1. 盡量不對(duì)存在多個(gè)唯一鍵的table使用該語(yǔ)句
2. 在有可能有并發(fā)事務(wù)執(zhí)行的insert 的內(nèi)容一樣情況下不使用該語(yǔ)句。將批量insert on duplicate key update，拆分成多個(gè)語(yǔ)句。保證一次事務(wù)中不要插入過(guò)多值，將多個(gè)數(shù)據(jù)，變成多個(gè)sql，執(zhí)行插入?？梢杂行У臏p少死鎖命中的發(fā)生。
3. 重試：死鎖不可怕，當(dāng)出現(xiàn)死鎖發(fā)生時(shí)，多執(zhí)行重試操作可以有效保證插入成功，更新不丟失。
4. 線程池多線程并發(fā)執(zhí)行改為單線程排隊(duì)處理。

replace into（先刪除再插入）

replace into 會(huì)根據(jù)唯一索引或主鍵進(jìn)行判斷，如果存在則覆蓋寫入字段，如果不存在則新增。
此方法有坑，如果主鍵是自增的，且通過(guò)唯一索引來(lái)進(jìn)行操作時(shí)，主鍵會(huì)變更，該方法底層是先進(jìn)性delete，在insert
如果有子表依賴的話不建議使用。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-786925.html

到了這里，關(guān)于MYSQL不存在插入 存在更新的解決方法和對(duì)比的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！