如何解決mysql的int型主鍵自增問題

這篇文章主要介紹“如何解決mysql的int型主鍵自增問題”，在日常操作中，相信很多人在如何解決mysql的int型主鍵自增問題問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”如何解決mysql的int型主鍵自增問題”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

引入

我們在使用mysql數據庫時，習慣使用int型作為主鍵，并設置為自增，這既能夠保證唯一，使用起來又很方便，但int型的長度是有限的，如果超過長度怎么辦呢？

暴露問題

我們先創建一個測試表，創建語句如下：

CREATE TABLE test1 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
)

然后我們插入兩條數據：

INSERT INTO test1 VALUES(NULL,'小牛');
INSERT INTO test1 VALUES(NULL,'大牛');

查詢表顯示正常：

int型的有符號的范圍為231 -1 = 2147483647，我們直接插入一條數據id為2147483647，如下：

INSERT INTO test1 VALUES(2147483647 ,'小華')

結果顯示正常：

此時自增ID已達到了int型的上限，如果我再插入數據，就會報錯：

INSERT INTO test1 VALUES(NULL,'母牛');

此時主鍵已無法自增，插入的id仍然是2147483647，就違反了主鍵唯一的條件，所以報錯。

解決問題

（1）使用更大的數據類型bigint

bigint的范圍是263-1，所謂指數爆炸，此時的大小達到了9,223,372,036,854,775,807的可怕量級，簡單來說就是用bigint 一天100w條數據也得存200億年才能自增爆炸，所以在當前場景，幾乎不用擔心bigint會自增滿

我們修改數據類型為bigint，如圖

再執行插入語句：

INSERT INTO test1 VALUES(NULL,'母牛');

又能夠正常插入了：

（2）使用UUID作為主鍵

我們都知道，UUID會根據當前系統性能，時間戳等一系列參數經過運算得到一個全世界唯一的字符串，并且mysql提供了生成UUID的方法，用它作為主鍵能夠保證數據的唯一性。

利用如下代碼可以生成32位的UUID：

-- 生成32位UUID
SELECT REPLACE(UUID(),'-','') AS UUID;

然后咱們再創建一個測試表：

CREATE TABLE test2(
  id VARCHAR(50) PRIMARY KEY,
  NAME VARCHAR(20) NOT NULL
)

插入一條數據：

-- 插入UUID
INSERT INTO test2 VALUES(REPLACE(UUID(),'-',''),'老王');

但這樣寫插入語句每次都要手寫UUID函數，貌似有點太麻煩了，咱們可以寫一個觸發器，讓觸發器自動為我們設置ID：

-- 創建觸發器
DELIMITER $$
CREATE
TRIGGER auto_id        -- 名稱
BEFORE INSERT          -- 觸發時機
ON test2 FOR EACH ROW   -- 作用于test2表，對每行數據生效
BEGIN
IF new.id = '' THEN     -- 當id為空字符串時設置UUID
  SET new.id = REPLACE(UUID(),'-','');
END IF;
END$$

插入一條數據：

-- 插入一條數據
INSERT INTO test2 VALUES('','小王');

結果能正常添加

總結

(1) 用int型和bigInt型增刪改查速度較UUID更快，并且更節省空間。

(2) 用UUID更方便。

為何要使用自增int作為主鍵

相信大家都知道要使用無符號自增int作為主鍵的數據類型，可你知道為何要使自用增int而不是使用varchar、text、varchar等類型嗎？

大家也能說出一些優點：對上層業務透明，插入數據時無需顯示指定；數據類型簡單，更便于存儲維護表結構

其實，使用自增int作為主鍵好處多多，今天我們就來一起學習一下，并強烈建議大家在實際開發中使用自增int作為主鍵。

優點：

1、int 相比varchar、char、text使用更少的存儲空間，而且數據類型簡單，可以節約CPU的開銷，更便于表結構的維護

2、默認都會在主鍵上建立主鍵索引，使用整形作為主鍵可以將更多的索引載入內存，提高查詢性能

3、對于InnoDB存儲引擎而言，每個二級索引都會使用主鍵作為索引值的后綴，使用自增主鍵可以減少索引的長度（大小），方便更多的索引數據載入內存

4、可以使索引數據更加緊湊，在數據插入、刪除、更新時可以做到索引數據盡可能少的移動、分裂頁，減少碎片的產生（可以通過optimize table 來重建表），減少維護開銷

5、在數據插入時，可以保證邏輯相鄰的元素物理也相鄰，便于范圍查找

當然，使用自增int作為主鍵也不是百利無一害，在高并發的情況下也可能會造成鎖的爭用問題。

到此，關于“如何解決mysql的int型主鍵自增問題”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！