需求背景

環(huán)境

MySQL 版本：?5.7.20-log

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

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

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

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

需求

我們有表如下：

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

針對(duì)這個(gè)需求，我們有哪些實(shí)現(xiàn)方式？

代碼處理

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

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

因?yàn)槭呛艹Ｒ?guī)的插入與更新操作，所以這種處理方式適用于所有的關(guān)系型數(shù)據(jù)庫(kù)

REPLACE INTO

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

工作原理

replace into?跟?insert?功能類似

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

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

我們來(lái)看個(gè)示例

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

需要強(qiáng)調(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)?判定為不存在，那么直接插入

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

有坑

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

1、破壞外鍵約束

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

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

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

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

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

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

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

2、主鍵加速自增

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

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

Replace into?的更新是先刪除再插入，會(huì)導(dǎo)致主鍵自增 1（照理來(lái)說，更新是不應(yīng)該導(dǎo)致主鍵自增 1）

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

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

3、主從切換問題

master：master-local?，slave：slave-192.168.0.112?，同步庫(kù)：my_project

從上圖可以看出，主從復(fù)制是正常的

接下來(lái)我們看看?replace into?對(duì)主從復(fù)制有什么影響

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

我們?cè)?master?上使用?replace into?更新一條記錄

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

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

我們來(lái)想一下這個(gè)問題：如果?master?庫(kù)崩了，我們會(huì)怎么做？會(huì)將?slave?提升為?master

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

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

INSERT UPDATE

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

工作原理

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

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

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

我們先來(lái)看看?UPDATE

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

我們?cè)賮?lái)看看?INSERT INTO … ON DUPLICATE KEY UPDATE

對(duì)?id = 11?的?last_delivery_price?進(jìn)行了修改，受影響的行是 2，并且?AUTO_INCREMENT=13

此刻，我相信我們有共同的兩個(gè)疑問

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

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

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

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

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

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

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

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

mysql5.1?之后其默認(rèn)值是 1

因?yàn)?innodb_autoinc_lock_mode = 1

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

批量操作

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

和批量插入類似

有坑

因?yàn)?innodb_autoinc_lock_mode = 1?是一個(gè)折中的選擇，一般不會(huì)去改它，所以有些需要注意的點(diǎn)

1、主鍵加速自增

與?replace into?類似，即使是更新，也會(huì)導(dǎo)致?AUTO_INCREMENT?自增，加速了主鍵的衰老

同時(shí)也會(huì)導(dǎo)致主鍵的跳躍

2、主從切換問題

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

當(dāng)?slave?升級(jí)成?master?后，可能會(huì)出現(xiàn)?duplicate key error

與?replace into?不同的是，上述兩個(gè)問題可以通過設(shè)置?innodb_autoinc_lock_mode = 0?來(lái)避免，因?yàn)楹芏鄨?chǎng)景下對(duì)性能要求并不高

總結(jié)

1、如何選擇哪種方式

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

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

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

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

3、道路千萬(wàn)條，合適第一條

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