前言

如果不特別指出，默認(rèn)mysql版本為8.0

簡介

往數(shù)據(jù)庫中插入記錄時，如果發(fā)生唯一索引值沖突，insert on duplicate允許進行進一步的crud操作。偽代碼如下：

insert record
IF exist duplicate record THEN
  do something on duplicated rows
ELSE 
  do nothing
END IF

具體用法

先初始化將要用到的表跟數(shù)據(jù)

create table t1
(
    id bigint primary key auto_increment,
    a  integer unique,
    b  integer default 999
);
INSERT INTO test_insert_on_dup_update(id, a)
VALUES (1, 1);
INSERT INTO test_insert_on_dup_update(id, a)
VALUES (5, 5);
INSERT INTO test_insert_on_dup_update(id, a)
VALUES (10, 10);

1. 單個唯一索引插入沖突

通過如下sql進行數(shù)據(jù)插入

insert into t1(a,b) values(1,199) on duplicate update b = 1;

因為表中已經(jīng)存在a=1的記錄，這個時候會觸發(fā)on duplicate后面的update操作，將a=1的記錄的b從999修改為1.

在這種情況下，上面的sql等價于

update b=1 where a = 1;

1.2 多個唯一索引插入沖突

如果插入的記錄與a跟b上的索引值都發(fā)生了沖突，且發(fā)生沖突的記錄有多條會怎么樣呢？

insert into t1(id, a) values(1,5) 
  on duplicate update b = 1;

因為a=1跟b=5都存在，這個時候有兩行記錄與即將插入的記錄有沖突。按照前面介紹的規(guī)則來看，貌似id=1跟a=5這兩條記錄的b都會被更新成1。但事實是只有一條有沖突的記錄會應(yīng)用on duplicate后面的子句。而這條被命中記錄就是在所有滿足條件的記錄中，其id值在聚集索引葉節(jié)點的鏈表中最靠前的那條記錄。在本例中也就是id=1的那條記錄。該sql的實際效果等價于

update t1 set b=1 where id=1 or a=5 limit 1;

所以，當(dāng)發(fā)生這種情況時，我們很難去預(yù)料語句的行為。應(yīng)當(dāng)盡量避免這種情況。

1.3 子句獲取插入列即將插入的值

在8.0.19之前

insert into t1(id, a) values(1,5) 
  on duplicate update b = values(a);

等價于

insert into t1(id, a) values(1,5) 
  on duplicate update b = 5;

values(a)獲取的是原本準(zhǔn)備插入的a=5這個值.

要注意的是：這種寫法將在8.0.20版本被廢棄，對應(yīng)的功能在未來會被移除。

在8.0.19之后

insert into t1(id, a) values(1,5) as new
  on duplicate update b = new.a;

這里為新插入的記錄設(shè)置了一個別名new，通過這個別名可以獲取到準(zhǔn)備插入的數(shù)據(jù)。另外，還可以基于這個別名更進一步的為里面的每個列設(shè)置別名

insert into t1(id, a) values(1,5) as new(x,y)
  on duplicate update b = x;

1.4 根據(jù)查詢結(jié)果進行插入

insert into t1(id, a) select x,y from t2
  on duplicate update b = x

像這類語句，由于插入的順序依賴于select的結(jié)果集里行的順序，而mysql不能保證這個select的結(jié)果集在主從上的順序是完全一致的，這就會導(dǎo)致基于statement的主從復(fù)制會出現(xiàn)數(shù)據(jù)不一致的問題。而基于行的復(fù)制模式不存在這個問題。所以，如果存在這類子句中帶select的sql，注意將復(fù)制模式設(shè)置為row-based或者mixed

跟鎖相關(guān)的部分

根據(jù)不同的隔離級別，有如下特征：

1. repeatable read

   • 普通唯一索引（非主鍵）發(fā)生唯一key沖突，這種情況會鎖住該索引以及聚集索引。
   • 主鍵值發(fā)生沖突。則會為發(fā)生沖突的主鍵值設(shè)置行鎖。

2. READ COMMITTED：會為沖突的索引值設(shè)置行鎖