MySQL varchar詳解

LvLaoTou發表於2024-03-20
MySql

varcahr(255)是什麼含義？

varchar(255) 表示可以儲存最大255個字元，至於佔多少個位元組由字符集決定。

varchar的最大值是多少？

如果你去搜尋過這個答案，我相信你或多或少都看到過65535這個答案。比如我們嘗試詢問一下當下最火的人工智慧，你可能會得到和我類似答案。

Untitled

那麼varchar的最大值真的是65535嗎？我們不妨實驗一下。

create table test(
 test_varchar_max varchar(65535) not null default '' comment '測試varchar最大值'
) engine=innodb default charset=utf8mb4;
Untitled

可以看到，mysql已經給我們提示錯誤了，而且已經提示了最大值是16383，怎麼和我們想的不一樣？那麼varchar的最大值就是16383嗎？接著看

create table test(
 test_varchar_max varchar(65535) not null default '' comment '測試varchar最大值'
) engine=innodb default charset=utf8;
Untitled

可以看到這次的提示又不一樣了，那麼varchar的最大值到底是多少呢？

再回答這個問題之前，我們還需要先了解幾個概念。

字符集

細心的小夥伴已經發現了，上面我們兩個建表語句只有一處不一樣，那就是charset的值不一樣。其實charset就是設定表的字符集。

什麼是字符集？看看百科給出的解釋

❝

字元（Character）是各種文字和符號的總稱，包括各國家文字、標點符號、圖形符號、數字等。字符集（Character set）是多個字元的集合，字符集種類較多，每個字符集包含的字元個數不同，常見字符集名稱：ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等。計算機要準確的處理各種字符集文字，就需要進行字元編碼，以便計算機能夠識別和儲存各種文字。中文文字數目大，而且還分為簡體中文和繁體中文兩種不同書寫規則的文字，而計算機最初是按英語單位元組字元設計的，因此，對中文字元進行編碼，是中文資訊交流的技術基礎。

❞

根據上面的實驗，我們可以證明不同的字符集，因為對字元的編碼規則不一樣，所以佔用儲存大小也不一樣。

那麼mysql支援哪些字符集，而每種字符集佔用多大空間呢？我們可以透過 show charset;檢視，結果如下

Untitled

那我們怎麼才能知道這個最大長度是多少呢？還記得我們在上一個環節測試字符集錯誤提示varchar的最大長度嗎？我們用那個長度 * 字符集的Maxlen 是不是就是varchar的最大值嗎？我們用上面的測試結果算算看

❝

utf8mb4 的 Maxlen = 4 對應 varchar的最大值為 4 * 16383 = 65532 utf8 的 Maxlen = 3 對應 varchar的最大值為 3 * 21845 = 65535

❞

咦！怎麼utf8mb4和utf8算出來的結果對應不上呢？我們再找一個gbk字符集測試一下呢。

Untitled
❝

gbk 的 Maxlen = 2 對應 varchar的最大值為 2 * 32767 = 65534

❞

這下徹底對不上了，那怎麼辦呢？其實去測試過的小夥伴現在應該已經發現問題了，雖然utf8和gbk錯誤提示了一個Max值，但是你嘗試設定為這個值的時候，你會發現會報錯，只能設定為比提示小1的值。看測試

Untitled
Untitled
Untitled

那我們根據最新能成功的實際最大值再計算一下varchar對應的最大值呢。

❝

utf8mb4 的 Maxlen = 4 對應 varchar的最大值為 4 * 16383 = 65532 utf8 的 Maxlen = 3 對應 varchar的最大值為 3 * 21844 = 65532 gbk 的 Maxlen = 2 對應 varchar的最大值為 2 * 32766 = 65532

❞

那你是不是就以為varchar的最大值就是65532「位元組」了？先說答案，肯定是錯！！！

null or not null?

上面的測試基本上證明了varchar可以儲存65532位元組的資料。不知道大家有沒有發現，上面測試的字符集的Maxlen都是大於1的，有沒有可能65532是因為剛好是上面幾種字符集Maxlen的整數倍呢？

要驗證這個問題，其實很簡單。我們找一個字符集的Maxlen是1的測試一下不就知道了嗎？

Untitled

可以看到，latin字符集的varchar可以設定為65533，也就是varchar的最大位元組是65533 * 1 = 65533。那麼varchar的最大值真的就是65533位元組了嗎？

大家仔細看我們的建表SQL，你就會發現兩點規律。

  1. 欄位指定為了非空，也就是not null
  2. 整張表只有一個欄位

那not null對varchar的最大值有影響嗎？既然這麼問了肯定是有影響的，實踐是檢驗真理的唯一標準。上測試

Untitled

測試證明，當欄位設定為not null的時候，varchar可以最大儲存65533位元組的內容。而欄位設定為允許為null的時候，最大可以儲存65532位元組的內容。

這是因為Innodb需要單獨使用一個位元組來儲存允許為Null的欄位。

多欄位的影響

上面講了為Null會對varchar的最大值有影響，其實表的欄位數量也對varchar的最大值有影響。帶大家回顧一下，mysql的錯誤提示

❝

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

❞

這裡有兩個重要資訊，一個是所用表型別（不包括BLOB）的最大行大小為65535，還有一個就是這裡面還包括儲存開銷。

其實從這裡面我們不難看出，65535是一行資料的（不包括BLOB）最大位元組數量，那如果我們一行有多個欄位呢。

Untitled

上面測試我們加如了一個int型別的欄位，然後就發現原本能儲存16383個utf8mb4字元，現在只能儲存16382個utf8mb4字元了。那是因為一個int在Innodb中佔4個位元組，所以varchar就只能少一個字元了。字元數對應為 （行最大位元組數 - int欄位位元組數）/ Maxlen = （65532 - 4）/ 4 = 16382。

Untitled

再次證明，65532是行的最大位元組數，而非varchar的最大位元組數。

而提示的65535位元組是包含其他開銷的，所以其他開銷就佔65535 - 65533 = 2個位元組。這裡為什麼是65533，因為Maxlen為1的字符集最大是65533，65532是字符集Maxlen的整數倍最接近65533的值。

那麼回到最初的問題，varchar到底最大能儲存多少字元？其實varchar能儲存多大字元取決於兩點，表欄位有多少，是否可以為null。在不允許為null 且只有一個varchar欄位的話，那最大能儲存的字元數就等於65533 / Maxlen;

❝

根據Innodb的規定，如果表欄位包含變長欄位varchar，需要額外用兩個位元組來儲存varcahr的長度。為什麼是兩個位元組？因為極限情況下就是表只有一個不允許為null的varchar欄位，把麼這個欄位的長度就最大為65533個位元組，那麼就至少需要兩個位元組才能存下這個長度。2byte=16bit=2^16=65536，所以需要兩個位元組儲存長度。

❞

本文使用 markdown.com.cn 排版

相關文章