Mysql更新自增主鍵id遇到的問題

Posted on by

本是一個自己知道的問題，還是差點踩坑（差點忘瞭，還好上線前整理上線點時想起來瞭），特此記錄下來

為什麼要更新自增id

我是因為歷史業務上的坑，導致必須更新一批id，且為瞭避免沖突需要將id擴大多少倍進行更新，因為我這個表的數據數量不高，屬於高讀低寫的情況，所以就簡單的擴大瞭1000

問題

MySQL中如果我們把自增主鍵更新為更大的值（例如現在自增id最大值是1000，你更新id=49這個記錄到id=1049），MySQL並不會把表的自增值修改為更新後的值，在某些情況下，如DDL，重啟等之後，業務開始報錯，這時如果不知道當前操作可能會誤認為是當前業務操作的問題，實則是因為更新id埋下的坑（主鍵沖突）
如下圖：

圖1:更新前原始數據

原始數據

執行更新語句

update test set id = 10 where id = 2;

圖2:更新後的數據

更新後數據

執行新的插入語句

insert test (name) values ('dddd')

圖3:插入的新數據

插入新數據後

想必這時大傢也都看出問題瞭，更新後可能剛開始沒有問題，但當自增id追上你更新的最大值後，id沖突在所難免瞭。。。

如何解決

1.如果是個人測試庫，不怎麼重要，可以重啟數據庫
2.當然線上數據庫是沒法按照1這種方式搞瞭，除非你很任性（還需要dba陪著你任性），，，這時可以嘗試指定id插入一條業務上無意義的數據，例如軟刪除的數據，（我的案列表沒有軟刪除標識，大傢可以意會下）

insert test (id,name) values (20,'eeee');

操作後如圖：

指定id插入

在執行下面SQL語句，對照結果

insert test (name) values ('ffff');

對照結果

此時自增id已從最大值開始自增瞭

找資料發現，這個BUG在2005年就被提出瞭，因為性能以及場景很少的沒有被修復；這個問題在MySQL 8.0.11中表現正常。

到此這篇關於Mysql更新自增主鍵id遇到的問題的文章就介紹到這瞭,更多相關Mysql更新自增主鍵id內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：