需求背景

環(huán)境

MySQL 版本：?5.7.20-log

開發(fā)規(guī)范

公司后端開發(fā)規(guī)范有這么一點：

更新數(shù)據(jù)庫表中數(shù)據(jù)的時候，不允許先刪，然后批量插入

需要將入?yún)⑴c表中數(shù)據(jù)比判斷，找出哪些是新插入，哪些需要更新，哪些是刪除的，然后再做對應的數(shù)據(jù)操作

需求

我們有表如下：

當商品配送完后之后，需要記錄它的最新配送價，若商品最新配送價已經(jīng)存在則進行更新，不存在則執(zhí)行插入

針對這個需求，我們有哪些實現(xiàn)方式？

代碼處理

按開發(fā)規(guī)范中說的處理

通過代碼在內(nèi)存中進行數(shù)據(jù)處理，找出插入列表與更新列表，然后執(zhí)行數(shù)據(jù)庫操作

因為是很常規(guī)的插入與更新操作，所以這種處理方式適用于所有的關系型數(shù)據(jù)庫

REPLACE INTO

當數(shù)據(jù)庫是?MySQL?，碰到?不存在則插入，存在則更新?的需求時，第一時間往往想到的是?REPLACE INTO

工作原理

replace into?跟?insert?功能類似

不同點在于：?replace into?首先嘗試插入數(shù)據(jù)到表中，如果發(fā)現(xiàn)表中已經(jīng)有此行數(shù)據(jù)（根據(jù)主鍵或者唯一索引判斷）則先刪除此行數(shù)據(jù)，然后插入新的數(shù)據(jù)，否則直接插入新數(shù)據(jù)

replace?語句會返回一個數(shù)，表示受影響的行的數(shù)目，該數(shù)是被刪除和被插入的行數(shù)的和

我們來看個示例

對于示例結(jié)果，相信大家都能理解

需要強調(diào)的是：根據(jù)唯一索引?uk_comany_ware?判定?(1001,10001,19.8,1,1)?已經(jīng)存在，那么先刪除此記錄，然后插入?(1001,10001,20.5,1,1)

而?(1001,10002,5.45,1,1)?判定為不存在，那么直接插入

這就導致我們看到的輸出結(jié)果是：?受影響的行：3?，同時自增主鍵由 1 變成了?2 3?，而不是?1 2

有坑

正是因為?replace into?的工作原理，不可避免就產(chǎn)生了一些需要注意的地方

1、破壞外鍵約束

如果主鍵被指定成了其他表的外鍵，那么?replace into?更新（非插入）時影響到了其他表的外鍵約束，那么會執(zhí)行失敗，提示類似信息：

可能很多小伙伴會說：我們開發(fā)過程中，會遵循阿里開發(fā)手冊中的規(guī)約，其中有一條規(guī)約如下：

我們不用外鍵了，也就不會出現(xiàn)前面的?[Err] 1451?錯誤了

其實阿里開發(fā)手冊中的這條規(guī)約，不是說不讓我們用外鍵，而是說不用數(shù)據(jù)庫層面的外鍵約束，在應用代碼層面解決外鍵邏輯

用數(shù)據(jù)庫層面的外鍵，問題提示的很明顯，也不會產(chǎn)生臟數(shù)據(jù)

而應用層解決外鍵，反而使外鍵約束的數(shù)據(jù)一致性問題更隱晦，產(chǎn)生臟數(shù)據(jù)，如下

從此我們踏上了修數(shù)據(jù)的不歸路

2、主鍵加速自增

很多情況下，我們的主鍵是?int?或者?bigint?類型，并且設置成了自增

不管是?int?還是?bigint?，都有一個最大值，如果一直自增下去，總有一天會達到最大值（可能到地老天荒也達不到這個值）

Replace into?的更新是先刪除再插入，會導致主鍵自增 1（照理來說，更新是不應該導致主鍵自增 1）

如果更新頻率遠遠大于插入頻率，本不用考慮的自增主鍵用完的問題，可能就需要考慮了

另外也會導致主鍵不連續(xù)，主鍵值跳躍式的出現(xiàn)在表中

3、主從切換問題

master：master-local?，slave：slave-192.168.0.112?，同步庫：my_project

從上圖可以看出，主從復制是正常的

接下來我們看看?replace into?對主從復制有什么影響

此時?master?與?slave?上的?t_ware_last_delivery_price?的下一個非手工指定的主鍵都是 11（?AUTO_INCREMENT=11?），兩者是一致的

我們在?master?上使用?replace into?更新一條記錄

master?與?slave?的數(shù)據(jù)是一致的，但是?master?上的下一個自增主鍵是?AUTO_INCREMENT=12?，而?slave?上卻是?AUTO_INCREMENT=11

可能會有人覺得：數(shù)據(jù)一致就行，下一個自增主鍵不一致有什么關系？

我們來想一下這個問題：如果?master?庫崩了，我們會怎么做？會將?slave?提升為?master

此時問題就來了：?slave?提升成?master?之前，實際數(shù)據(jù)的?id?已經(jīng)到了?11?，但其?AUTO_INCREMENT=11?，也就說下一個自增主鍵是?11

那么下一條不指定?id?值的新紀錄是插入時就會發(fā)生?duplicate key error?，每次沖突之后 AUTO_INCREMENT += 1，直到增長為 max(id) + 1 之后才能恢復正常

INSERT UPDATE

針對?不存在則插入，存在則更新?，?MySQL?還提供了另外一種方言實現(xiàn)：?INSERT … ON DUPLICATE KEY UPDATE Statement

工作原理

如果指定?ON DUPLICATE KEY UPDATE?子句，并且要插入的行將導致唯一索引或主鍵中出現(xiàn)重復值，則會更新舊行，否則則是插入

例如，如果?列 a?被聲明為唯一且包含值 1，則以下兩條語句具有類似的效果

但是這兩條 SQL 的效果并不完全相同，我們以?t_ware_last_delivery_price?為例，來看看它們的區(qū)別

我們先來看看?UPDATE

只是對?id = 11?的?last_delivery_price?就行了修改，受影響的行只有 1，不會影響?AUTO_INCREMENT?的值

我們再來看看?INSERT INTO … ON DUPLICATE KEY UPDATE

對?id = 11?的?last_delivery_price?進行了修改，受影響的行是 2，并且?AUTO_INCREMENT=13

此刻，我相信我們有共同的兩個疑問

1、為什么受影響的行數(shù)是 2，而不是 1

2、自增主鍵為什么自增了 1（?AUTO_INCREMENT?為什么等于 13，而不是原有的 12）

為什么受影響的行數(shù)是 2，而不是 1，官方文檔有這么一段說明

意思就是：1 表示新插入一行，2 表示更新了一行，0 表示更新前后值未變

我們換個角度來理解，假設讓我們來設計，一條 SQL 既能插入，也能更新，我們?nèi)绾胃嬷脩舻降资遣迦氤晒α耍€是更新成功了？

所以 1，2 僅僅只是用來區(qū)分插入和更新，2 并非真正受影響的行數(shù)

主鍵明明沒有變化，為什么?AUTO_INCREMENT=13?自增了 1 ？

這和?MySQL?的主鍵自增的參數(shù)有關?innodb_autoinc_lock_mode?，它有 3 個值?0,1,2

mysql5.1?之后其默認值是 1

因為?innodb_autoinc_lock_mode = 1

所以上述 SQL 被當作簡單插入處理，在真正修改數(shù)據(jù)之前就對?AUTO_INCREMENT?自增 1 處理了

批量操作

不僅支持單條操作，也支持批量操作

和批量插入類似

有坑

因為?innodb_autoinc_lock_mode = 1?是一個折中的選擇，一般不會去改它，所以有些需要注意的點

1、主鍵加速自增

與?replace into?類似，即使是更新，也會導致?AUTO_INCREMENT?自增，加速了主鍵的衰老

同時也會導致主鍵的跳躍

2、主從切換問題

與?replace into?類似，?master?上的更新導致?AUTO_INCREMENT?自增，而?AUTO_INCREMENT?又未同步到?slave

當?slave?升級成?master?后，可能會出現(xiàn)?duplicate key error

與?replace into?不同的是，上述兩個問題可以通過設置?innodb_autoinc_lock_mode = 0?來避免，因為很多場景下對性能要求并不高

總結(jié)

1、如何選擇哪種方式

上述三種方式各有優(yōu)略，代碼處理不依賴于具體的數(shù)據(jù)庫，可移植性高，也不會引入特定數(shù)據(jù)庫的在這方面的缺陷

replace into?的方式不推薦（坑有點多），它完全可以由?INSERT UPDATE?替代

INSERT UPDATE?可以減少我們的代碼，但它是?MySQL?的拓展實現(xiàn)，只有?MySQL?支持，可移植性差

2、針對?INSERT UPDATE?的 “坑”，我們可以結(jié)合具體的業(yè)務來設置?innodb_autoinc_lock_mode?，適當?shù)谋苊馑?“坑”

3、道路千萬條，合適第一條

針對某個需求，實現(xiàn)方式往往有很多，我們要做的就是從中找到最適合的那一條