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

大家好，我是小林。

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

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

所以，這次就聊聊這個(gè)問題。

前置知識(shí)

要回答這個(gè)問題，首先我們得先知道 MySQL 存儲(chǔ)一條記錄的格式長(zhǎng)什么樣子。

以 Compact 行格式作為例子，它長(zhǎng)這樣：

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

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

• 變長(zhǎng)字段長(zhǎng)度列表

用于存儲(chǔ)一行記錄中每個(gè)變長(zhǎng)字段的長(zhǎng)度。

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

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

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

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

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

• NULL 值列表

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

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

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

如果如果表中允許為 NULL 值的記錄的個(gè)數(shù)大于8 并且小于等于 16，那么 NULL 值列表就會(huì)用 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 這種大對(duì)象類型之外，其他所有的列（不包括隱藏列和記錄頭信息）占用的字節(jié)長(zhǎng)度加起來不能超過 65535 個(gè)字節(jié)。

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

知道了這個(gè)前提之后，我們?cè)賮砜纯催@個(gè)問題：「varchar(n) 中 n 最大取值為多少？」

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

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

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

單字段的情況

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

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

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

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

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

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

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

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

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

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

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

這是因?yàn)槲覀兇鎯?chǔ)字段類型為 varchar(n) 的數(shù)據(jù)時(shí)，其實(shí)分成了三個(gè)部分來存儲(chǔ)：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

多字段的情況

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

這里舉個(gè)多字段的情況的例子。

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

總結(jié)

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

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

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

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

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

歷史好文：

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

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

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

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