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

Posted on by

引入

我們在使用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作為主鍵也不是百利無一害，在高並發的情況下也可能會造成鎖的爭用問題。

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。

推薦閱讀：