MySQL中varchar(n) 中 n 最大取值為多少？

大家好，我是小林。

上周發(fā)了一篇字節(jié)一面:MySQL 的 NULL 值是怎么存放的?，文章里面有提及這個問題：「varchar(n) 中 n 最大取值為多少？」

當(dāng)時這部分內(nèi)容寫的不夠嚴(yán)謹(jǐn)，所以我重寫了這部分內(nèi)容。

所以，這次就聊聊這個問題。

前置知識

要回答這個問題，首先我們得先知道 MySQL 存儲一條記錄的格式長什么樣子。

以 Compact 行格式作為例子，它長這樣：

可以看到，一條完整的記錄分為「記錄的額外信息」和「記錄的真實(shí)數(shù)據(jù)」兩個部分。

這里重點(diǎn)講講記錄的額外信息，它包含 3 個部分：變長字段長度列表、NULL 值列表、記錄頭信息。

• 變長字段長度列表

用于存儲一行記錄中每個變長字段的長度。

「變長字段長度列表」所占用的字節(jié)數(shù) = 所有「變長字段長度」占用的字節(jié)數(shù)之和。

舉個例子，假設(shè)數(shù)據(jù)庫表中有 2 個 varchar(10) 類型的字段，分別為 a 和 b，且數(shù)據(jù)庫表的字符集為 ascii 字符集（1 個字符占用 1 字節(jié)）。

那么a和b字段的數(shù)據(jù)值的長度分別只需要用1字節(jié)表示就行了，因?yàn)?字節(jié)能表示最大的字節(jié)數(shù)是 255，而 varchar(10) 類型的字段最大允許存儲的字節(jié)數(shù)是 10 字節(jié)，所以只需要用 1 字節(jié)表示變長字段的長度就行。

那么這種情況下的 「變長字段長度列表」所占用的字節(jié)數(shù) = 1 字節(jié) + 1字節(jié) = 2 字節(jié)。

「變長字段長度列表」不是必須的，如果數(shù)據(jù)庫表沒有變長字段，比如字段類型都是int，那么行格式中就不需要「變長字段長度列表」。

• NULL 值列表

用于標(biāo)記一行記錄中字段值為 NULL 的字段，二進(jìn)制位的值為 1 時，代表該字段的值為NULL，二進(jìn)制位的值為 0 時，代表該字段的值不為 NULL。

另外，NULL 值列表必須用整數(shù)個字節(jié)的位表示（1字節(jié)8位），如果使用的二進(jìn)制位個數(shù)不足整數(shù)個字節(jié)，則在字節(jié)的高位補(bǔ) 0。

如果表中允許為 NULL 值的記錄的個數(shù)小于等于 8 個，那么 NULL 值列表就會用 1 字節(jié)表示。

如果如果表中允許為 NULL 值的記錄的個數(shù)大于8 并且小于等于 16，那么 NULL 值列表就會用 2 字節(jié)表示，以此類推。

因此，如果表中有字段允許為NULL，那么「NULL 值列表」至少占用 1 字節(jié)空間。

「NULL 值列表」不是必須的，如果數(shù)據(jù)庫表中的字段都定義成NOT NULL，那么行格式中就不需要「NULL 值列表」。

• 記錄頭信息

記錄頭信息中包含的內(nèi)容很多，比如記錄的刪除標(biāo)記位，指向下一條記錄的指針等等，不是本文問題的重點(diǎn)，所以我就不細(xì)講了。

varchar(n) 中 n 最大取值為多少？

我們要清楚一點(diǎn)，MySQL 規(guī)定除了 TEXT、BLOBs 這種大對象類型之外，其他所有的列（不包括隱藏列和記錄頭信息）占用的字節(jié)長度加起來不能超過 65535 個字節(jié)。

也就是說，一行記錄除了 TEXT、BLOBs 類型的列，限制最大為 65535 字節(jié)，注意是一行的總長度，不是一列。

知道了這個前提之后，我們再來看看這個問題：「varchar(n) 中 n 最大取值為多少？」

varchar(n) 字段類型的 n 代表的是最多存儲的字符數(shù)量，并不是字節(jié)大小哦。

要算 varchar(n) 最大能允許存儲的字節(jié)數(shù)，還要看數(shù)據(jù)庫表的字符集，因?yàn)樽址碇?個字符要占用多少字節(jié)。

比如 ascii 字符集， 1 個字符占用 1 字節(jié)，那么 varchar(100) 意味著最大能允許存儲 100 字節(jié)的數(shù)據(jù)。

單字段的情況

前面我們知道了，一行記錄最大只能存儲 65535 字節(jié)的數(shù)據(jù)。

那假設(shè)數(shù)據(jù)庫表只有一個 varchar(n) 類型的列且字符集是 ascii，在這種情況下， varchar(n) 中 n 最大取值是 65535 嗎？

不著急說結(jié)論，我們先來做個實(shí)驗(yàn)驗(yàn)證一下。

我們定義一個 varchar(65535) 類型的字段，字符集為 ascii 的數(shù)據(jù)庫表。

CREATETABLEtest(
`name`VARCHAR(65535)NULL
)ENGINE=InnoDBDEFAULTCHARACTERSET=asciiROW_FORMAT=COMPACT;

看能不能成功創(chuàng)建一張表：

結(jié)果顯示，創(chuàng)建失敗了。

從報錯信息就可以知道一行數(shù)據(jù)的最大字節(jié)數(shù)是 65535（不包含 TEXT、BLOBs 這種大對象類型），其中包含了 storage overhead。

問題來了，這個 storage overhead 是什么呢？其實(shí)就是「變長字段長度列表」和 「NULL 值列表」。

也就是說一行數(shù)據(jù)的最大字節(jié)數(shù) 65535，其實(shí)是包含「變長字段長度列表」和 「NULL 值列表」所占用的字節(jié)數(shù)的。

所以， 我們在算 varchar(n) 中 n 最大值時，需要減去 storage overhead 占用的字節(jié)數(shù)。

這是因?yàn)槲覀兇鎯ψ侄晤愋蜑?varchar(n) 的數(shù)據(jù)時，其實(shí)分成了三個部分來存儲：

• 真實(shí)數(shù)據(jù)
• 真實(shí)數(shù)據(jù)占用的字節(jié)數(shù)
• NULL 標(biāo)識，如果不允許為NULL，這部分不需要

本次案例中，「NULL 值列表」所占用的字節(jié)數(shù)是多少？

前面我創(chuàng)建表的時候，字段是允許為 NULL 的，所以會用 1 字節(jié)來表示「NULL 值列表」。

本次案例中，「變長字段長度列表」所占用的字節(jié)數(shù)是多少？

「變長字段長度列表」所占用的字節(jié)數(shù) = 所有「變長字段長度」占用的字節(jié)數(shù)之和。

所以，我們要先知道每個變長字段的「變長字段長度」需要用多少字節(jié)表示？具體情況分為：

• 條件一：如果變長字段允許存儲的最大字節(jié)數(shù)小于等于 255 字節(jié)，就會用 1 字節(jié)表示「變長字段長度」；
• 條件二：如果變長字段允許存儲的最大字節(jié)數(shù)大于 255 字節(jié)，就會用 2 字節(jié)表示「變長字段長度」；

我們這里字段類型是 varchar(65535) ，字符集是 ascii，所以代表著變長字段允許存儲的最大字節(jié)數(shù)是 65535，符合條件二，所以會用 2 字節(jié)來表示「變長字段長度」。

因?yàn)槲覀冞@個案例是只有 1 個變長字段，所以「變長字段長度列表」= 1 個「變長字段長度」占用的字節(jié)數(shù)，也就是 2 字節(jié)。

因?yàn)槲覀冊谒?varchar(n) 中 n 最大值時，需要減去 「變長字段長度列表」和 「NULL 值列表」所占用的字節(jié)數(shù)的。

所以，在數(shù)據(jù)庫表只有一個 varchar(n) 字段且字符集是 ascii 的情況下，varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。

我們先來測試看看 varchar(65533) 是否可行？

可以看到，還是不行，接下來看看 varchar(65532) 是否可行？

可以看到，創(chuàng)建成功了。說明我們的推論是正確的，在算 varchar(n) 中 n 最大值時，需要減去 「變長字段長度列表」和 「NULL 值列表」所占用的字節(jié)數(shù)的。

當(dāng)然，我上面這個例子是針對字符集為 ascii 情況，如果采用的是 UTF-8，varchar(n) 最多能存儲的數(shù)據(jù)計(jì)算方式就不一樣了：

• 在 UTF-8 字符集下，一個字符串最多需要三個字節(jié)，varchar(n) 的 n 最大取值就是 65532/3 = 21844。

上面所說的只是針對于一個字段的計(jì)算方式。

多字段的情況

如果有多個字段的話，要保證所有字段的長度 + 變長字段字節(jié)數(shù)列表所占用的字節(jié)數(shù) + NULL值列表所占用的字節(jié)數(shù) <= 65535。

這里舉個多字段的情況的例子。

實(shí)驗(yàn)結(jié)果：

總結(jié)

varchar(n) 中 n 最大取值為多少？

一行記錄最大能存儲 65535 字節(jié)的數(shù)據(jù)，但是這個是包含「變長字段字節(jié)數(shù)列表所占用的字節(jié)數(shù)」和「NULL值列表所占用的字節(jié)數(shù)」。所以， 我們在算 varchar(n) 中 n 最大值時，需要減去這兩個列表所占用的字節(jié)數(shù)。

如果一張表只有一個 varchar(n) 字段，且允許為 NULL，字符集為 ascii。varchar(n) 中 n 最大取值為 65532。

計(jì)算公式：65535 - 變長字段字節(jié)數(shù)列表所占用的字節(jié)數(shù)- NULL值列表所占用的字節(jié)數(shù) = 65535 - 2 - 1 = 65532。

如果有多個字段的話，要保證所有字段的長度 + 變長字段字節(jié)數(shù)列表所占用的字節(jié)數(shù) + NULL值列表所占用的字節(jié)數(shù) <= 65535。

歷史好文：

字節(jié)一面：HTTP 長連接和 TCP 長連接有區(qū)別？

美團(tuán)三面：一直追問我， MySQL 幻讀被徹底解決了嗎？

保姆級教程！2 萬字 + 30 張圖搞懂 MySQL 是怎么加行級鎖的？

MySQL 全局鎖、表級鎖、行級鎖，你搞清楚了嗎？