群里一網(wǎng)友這兩天剛?cè)肼毿鹿?，遇到一個(gè)重啟 MySQL 服務(wù)后，自動(dòng)增長值丟失問題，差點(diǎn)背鍋?zhàn)呷?。下面我們一起來回顧一下這個(gè)問題。在 mysql 中用自增列作為主鍵時(shí)，先往表里插入 5 條數(shù)據(jù)，此時(shí)表里數(shù)據(jù) id 為 1、2、3、4、5，如果此時(shí)刪除 id=4、5 的數(shù)據(jù)后，再重啟數(shù)據(jù)庫，重啟成功后向表里 insert 數(shù)據(jù)的時(shí)候，INNODB、MyISAM 引擎下 ID 分別是從幾開始增加？如果你沒經(jīng)歷過，或者當(dāng)面試時(shí)被問到這個(gè)問題時(shí)，相信多數(shù)人都是一臉懵逼。MD 誰有事沒事去重啟線上數(shù)據(jù)庫嘛。最主要的是很多沒有測試過這個(gè)場景，沒有這方面的經(jīng)驗(yàn)，我在這里做個(gè)筆記，大家輕點(diǎn)噴！MySQL 通常使用的引擎都是 InnoDB，在建表時(shí)，一般使用自增列作為表的主鍵，這樣的表對提高性能有一定的幫助。但是自增列有一個(gè)坑，并且這個(gè)坑存在了很久，一直到 MySQL 8.0 版本，才修復(fù)了這個(gè)坑，這個(gè)坑就是表的自增列變量 auto_increment 在 MySQL 重啟后，有可能丟失。
「innodb 引擎(低版本)：Innodb 表中把自增列作為主鍵 ID 時(shí)，自增列是通過 auto-increment 計(jì)數(shù)器實(shí)現(xiàn)的，計(jì)數(shù)器的最大值是記錄到內(nèi)存中的，重啟數(shù)據(jù)庫后，會(huì)導(dǎo)致 auto-increment 計(jì)數(shù)器重置，從而會(huì)導(dǎo)致主鍵 ID 重置?！?/strong>
「MyISam 引擎：MyISAM 表會(huì)把自增列(auto-increment 計(jì)數(shù)器)最大值是記錄到數(shù)據(jù)文件里，重啟 MySQL 自增列(計(jì)數(shù)器)最大值不會(huì)丟失，從而使用自增列作為主鍵 ID 時(shí)也不會(huì)丟失?！?/strong>
「mysql 數(shù)據(jù)庫不重啟時(shí)，innodb 自增主鍵 ID 會(huì)根據(jù) auto-increment 計(jì)數(shù)器一直遞增?！?/strong>向 user 表里插入 5 條數(shù)據(jù)，主鍵 ID 按自增列通過 auto-increment 計(jì)數(shù)器實(shí)現(xiàn)自增。在 user 表里刪除 id 為 4、5 的數(shù)據(jù)，再向 user 表中插入一條數(shù)據(jù)，主鍵 ID 是 auto-increment 的值 6。
場景二
mysql 數(shù)據(jù)庫重啟后，innodb 自增主鍵 ID 會(huì)根據(jù) auto-increment 計(jì)數(shù)器的重置而重置。在場景一的基礎(chǔ)上，在刪除 id 為 6、3 的數(shù)據(jù)后，此時(shí) auto-increment 計(jì)數(shù)器的值為 7，user 表里的 id 最大是 2。然后重啟數(shù)據(jù)庫后，auto-increment 計(jì)數(shù)器的值變?yōu)?3，也就是 user 表里的自增列 ID 的最大值 2 加 1。此時(shí)在插入數(shù)據(jù)時(shí)，自增 ID 會(huì)從 3 開始自增。Innodb 表中把自增列作為主鍵 ID 時(shí)，在 mysql 重啟后就會(huì)存在 ID 重置問題。**刪除數(shù)據(jù)后，再重啟，AUTO_INCREMENT 會(huì)查詢表里最大 ID 并進(jìn)行重置，重置后和重啟前AUTO_INCREMENT 計(jì)數(shù)器的值不同。**在 MyISAM 引擎表中的自增列不會(huì)存在這個(gè)問題。
1）如果 mysql 重啟了，那么 innodb 表在啟動(dòng)后，AUTO_INCREMENT 值會(huì)自動(dòng)檢測出、并重置為當(dāng)前表中自增列的最大值 +1。2）假如一個(gè)表里 AUTO_INCREMENT 計(jì)數(shù)器的值是 10，此時(shí)執(zhí)行update table set id = 15 where id = 9后，如果這時(shí)再繼續(xù)插入數(shù)據(jù)，到了自增 ID=15 的時(shí)候是會(huì)報(bào)錯(cuò)。但是這個(gè)時(shí)候繼續(xù)插入，就不會(huì)報(bào)錯(cuò)。因?yàn)閯偛偶词箞?bào)錯(cuò)了，AUTO_INCREMENT 的值依舊會(huì)增加。3）現(xiàn)在使用的一般都是 innodb 引擎，如果將 myisam 引擎轉(zhuǎn)換過來的時(shí)候，一定要小心這個(gè)引擎在自增 id 上的不同表現(xiàn)。在主從使用不同引擎的時(shí)候,也會(huì)出現(xiàn)問題，最好將引擎改完一致性的。
