開心一刻
今天我爸、我、我女兒一起吃飯，我們每人一個雞腿
女兒問道：爸爸，你吃雞腿嗎
我以為她要把她的雞腿給我吃，倍感欣慰地說道：我不吃，寶貝
女兒一把抓起我的雞腿放進了她爺爺的碗里，說道：不吃給爺爺吃
我沒想到她會來這一出，我從我爸碗里夾回我的雞腿，對女兒說道：不是，你這樣問問你爺爺
女兒向她爺爺問道：爺爺，你吃雞腿嗎
我爸一臉溺愛的說道：吃
女兒又一把抓起我的雞腿放進了她爺爺的碗里，說道：爺爺吃，給爺爺
我一臉不可思議的看著我女兒，竟然套路我，那我就陪你玩到底，我又從我爸碗里夾回雞腿，對女兒說道：不對不對，讓你問懵了，你再問我一次
女兒問道：爸爸，你吃公雞腿嗎
我信誓旦旦地說道：我吃
女兒又一把抓起我的雞腿放進了她爺爺的碗里，說道：這是母雞腿，你去找公雞腿吃
我徹底懵了
需求背景
環境
MySQL 版本： 5.7.20-log
開發規范
公司后端開發規范有這么一點：
更新資料庫表中資料的時候，不允許先刪，然后批量插入
需要將入參與表中資料比判斷，找出哪些是新插入，哪些需要更新，哪些是洗掉的，然后再做對應的資料操作
需求
我們有表如下：
當商品配送完后之后，需要記錄它的最新配送價，若商品最新配送價已經存在則進行更新，不存在則執行插入
針對這個需求，我們有哪些實作方式？
代碼處理
按開發規范中說的處理
通過代碼在記憶體中進行資料處理，找出插入串列與更新串列，然后執行資料庫操作
因為是很常規的插入與更新操作，所以這種處理方式適用于所有的關系型資料庫
REPLACE INTO
當資料庫是 MySQL ，碰到 不存在則插入，存在則更新 的需求時，第一時間往往想到的是 REPLACE INTO
作業原理
replace into 跟 insert 功能類似
不同點在于： replace into 首先嘗試插入資料到表中，如果發現表中已經有此行資料（根據主鍵或者唯一索引判斷）則先洗掉此行資料，然后插入新的資料，否則直接插入新資料
replace 陳述句會回傳一個數，表示受影響的行的數目，該數是被洗掉和被插入的行數的和
我們來看個示例
對于示例結果，相信大家都能理解
需要強調的是：根據唯一索引 uk_comany_ware 判定 (1001,10001,19.8,1,1) 已經存在，那么先洗掉此記錄，然后插入 (1001,10001,20.5,1,1)
而 (1001,10002,5.45,1,1) 判定為不存在，那么直接插入
這就導致我們看到的輸出結果是： 受影響的行：3 ，同時自增主鍵由 1 變成了 2 3 ，而不是 1 2
有坑
正是因為 replace into 的作業原理，不可避免就產生了一些需要注意的地方
1、破壞外鍵約束
如果主鍵被指定成了其他表的外鍵，那么 replace into 更新（非插入）時影響到了其他表的外鍵約束，那么會執行失敗，提示類似資訊：
可能很多小伙伴會說：我們開發程序中，會遵循阿里開發手冊中的規約，其中有一條規約如下：
我們不用外鍵了，也就不會出現前面的 [Err] 1451 錯誤了
其實阿里開發手冊中的這條規約，不是說不讓我們用外鍵，而是說不用資料庫層面的外鍵約束，在應用代碼層面解決外鍵邏輯
用資料庫層面的外鍵，問題提示的很明顯，也不會產生臟資料
而應用層解決外鍵，反而使外鍵約束的資料一致性問題更隱晦，產生臟資料，如下
從此我們踏上了修資料的不歸路
2、主鍵加速自增
很多情況下，我們的主鍵是 int 或者 bigint 型別，并且設定成了自增
不管是 int 還是 bigint ，都有一個最大值，如果一直自增下去，總有一天會達到最大值（可能到地老天荒也達不到這個值）
replace into 的更新是先洗掉再插入，會導致主鍵自增 1（照理來說，更新是不應該導致主鍵自增 1）
如果更新頻率遠遠大于插入頻率，本不用考慮的自增主鍵用完的問題，可能就需要考慮了
另外也會導致主鍵不連續，主鍵值跳躍式的出現在表中
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 的資料是一致的，但是 master 上的下一個自增主鍵是 AUTO_INCREMENT=12 ，而 slave 上卻是 AUTO_INCREMENT=11
可能會有人覺得：資料一致就行，下一個自增主鍵不一致有什么關系？
我們來想一下這個問題：如果 master 庫崩了，我們會怎么做？會將 slave 提升為 master
此時問題就來了： slave 提升成 master 之前，實際資料的 id 已經到了 11 ，但其 AUTO_INCREMENT=11 ，也就說下一個自增主鍵是 11
那么下一條不指定 id 值的新紀錄是插入時就會發生 duplicate key error ，每次沖突之后 AUTO_INCREMENT += 1，直到增長為 max(id) + 1 之后才能恢復正常
INSERT UPDATE
針對 不存在則插入，存在則更新 ， MySQL 還提供了另外一種方言實作： INSERT ... ON DUPLICATE KEY UPDATE Statement
作業原理
如果指定 ON DUPLICATE KEY UPDATE 子句，并且要插入的行將導致唯一索引或主鍵中出現重復值，則會更新舊行，否則則是插入
例如，如果 列 a 被宣告為唯一且包含值 1，則以下兩條陳述句具有類似的效果
但是這兩條 SQL 的效果并不完全相同，我們以 t_ware_last_delivery_price 為例，來看看它們的區別
我們先來看看 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、為什么受影響的行數是 2，而不是 1
2、自增主鍵為什么自增了 1（ AUTO_INCREMENT 為什么等于 13，而不是原有的 12）
為什么受影響的行數是 2，而不是 1，官方檔案有這么一段說明
意思就是：1 表示新插入一行，2 表示更新了一行，0 表示更新前后值未變
我們換個角度來理解，假設讓我們來設計，一條 SQL 既能插入，也能更新，我們如何告知用戶到底是插入成功了，還是更新成功了？
所以 1，2 僅僅只是用來區分插入和更新，2 并非真正受影響的行數
主鍵明明沒有變化，為什么 AUTO_INCREMENT=13 自增了 1 ？
這和 MySQL 的主鍵自增的引數有關 innodb_autoinc_lock_mode ，它有 3 個值 0,1,2
mysql5.1 之后其默認值是 1
因為 innodb_autoinc_lock_mode = 1
所以上述 SQL 被當作簡單插入處理，在真正修改資料之前就對 AUTO_INCREMENT 自增 1 處理了
批量操作
不僅支持單條操作，也支持批量操作
和批量插入類似
有坑
因為 innodb_autoinc_lock_mode = 1 是一個折中的選擇，一般不會去改它，所以有些需要注意的點
1、主鍵加速自增
與 replace into 類似，即使是更新，也會導致 AUTO_INCREMENT 自增，加速了主鍵的衰老
同時也會導致主鍵的跳躍
2、主從切換問題
與 replace into 類似， master 上的更新導致 AUTO_INCREMENT 自增，而 AUTO_INCREMENT 又未同步到 slave
當 slave 升級成 master 后，可能會出現 duplicate key error
與 replace into 不同的是，上述兩個問題可以通過設定 innodb_autoinc_lock_mode = 0 來避免，因為很多場景下對性能要求并不高
總結
1、如何選擇哪種方式
上述三種方式各有優略，代碼處理不依賴于具體的資料庫，可移植性高，也不會引入特定資料庫的在這方面的缺陷
replace into 的方式不推薦（坑有點多），它完全可以由 INSERT UPDATE 替代
INSERT UPDATE 可以減少我們的代碼，但它是 MySQL 的拓展實作，只有 MySQL 支持，可移植性差
2、針對 INSERT UPDATE 的 “坑”，我們可以結合具體的業務來設定 innodb_autoinc_lock_mode ，適當的避免它的 “坑”
3、道路千萬條，合適第一條
針對某個需求，實作方式往往有很多，我們要做的就是從中找到最適合的那一條
參考
REPLACE Statement
INSERT ... ON DUPLICATE KEY UPDATE Statement
mysql自增id超大問題查詢
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/302082.html
標籤：其他
上一篇：MySQL45講之幻讀
下一篇：MySQL45講之幻讀