引入

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

暴露問題

我們先創(chuàng)建一個測試表，創(chuàng)建語句如下：

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

然后我們插入兩條數(shù)據(jù)：

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

查詢表顯示正常：

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

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

結(jié)果顯示正常：

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

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

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

解決問題

（1）使用更大的數(shù)據(jù)類型bigint

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

我們修改數(shù)據(jù)類型為bigint，如圖

再執(zhí)行插入語句：

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

又能夠正常插入了：

（2）使用UUID作為主鍵

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

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

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

然后咱們再創(chuàng)建一個測試表：

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

插入一條數(shù)據(jù)：

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

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

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

插入一條數(shù)據(jù)：

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

結(jié)果能正常添加

總結(jié)

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

(2) 用UUID更方便。

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

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

大家也能說出一些優(yōu)點：對上層業(yè)務(wù)透明，插入數(shù)據(jù)時無需顯示指定；數(shù)據(jù)類型簡單，更便于存儲維護表結(jié)構(gòu)

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

優(yōu)點：

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

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

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

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

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

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

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論