大家好,我是小林。
上周發了一篇字節一面:MySQL 的 NULL 值是怎么存放的?,文章里面有提及這個問題:「varchar(n) 中 n 最大取值為多少?」
當時這部分內容寫的不夠嚴謹,所以我重寫了這部分內容。
所以,這次就聊聊這個問題。
前置知識
要回答這個問題,首先我們得先知道 MySQL 存儲一條記錄的格式長什么樣子。
以 Compact 行格式作為例子,它長這樣:
可以看到,一條完整的記錄分為「記錄的額外信息」和「記錄的真實數據」兩個部分。
這里重點講講記錄的額外信息,它包含 3 個部分:變長字段長度列表、NULL 值列表、記錄頭信息。
- 變長字段長度列表
用于存儲一行記錄中每個變長字段的長度。
「變長字段長度列表」所占用的字節數 = 所有「變長字段長度」占用的字節數之和。
舉個例子,假設數據庫表中有 2 個 varchar(10) 類型的字段,分別為 a 和 b,且數據庫表的字符集為 ascii 字符集(1 個字符占用 1 字節)。
那么a和b字段的數據值的長度分別只需要用1字節表示就行了,因為1字節能表示最大的字節數是 255,而 varchar(10) 類型的字段最大允許存儲的字節數是 10 字節,所以只需要用 1 字節表示變長字段的長度就行。
那么這種情況下的 「變長字段長度列表」所占用的字節數 = 1 字節 + 1字節 = 2 字節。
「變長字段長度列表」不是必須的,如果數據庫表沒有變長字段,比如字段類型都是int,那么行格式中就不需要「變長字段長度列表」。
- NULL 值列表
用于標記一行記錄中字段值為 NULL 的字段,二進制位的值為 1 時,代表該字段的值為NULL,二進制位的值為 0 時,代表該字段的值不為 NULL。
另外,NULL 值列表必須用整數個字節的位表示(1字節8位),如果使用的二進制位個數不足整數個字節,則在字節的高位補 0
。
如果表中允許為 NULL 值的記錄的個數小于等于 8 個,那么 NULL 值列表就會用 1 字節表示。
如果如果表中允許為 NULL 值的記錄的個數大于8 并且小于等于 16,那么 NULL 值列表就會用 2 字節表示,以此類推。
因此,如果表中有字段允許為NULL,那么「NULL 值列表」至少占用 1 字節空間。
「NULL 值列表」不是必須的,如果數據庫表中的字段都定義成NOT NULL,那么行格式中就不需要「NULL 值列表」。
- 記錄頭信息
記錄頭信息中包含的內容很多,比如記錄的刪除標記位,指向下一條記錄的指針等等,不是本文問題的重點,所以我就不細講了。
varchar(n) 中 n 最大取值為多少?
我們要清楚一點,MySQL 規定除了 TEXT、BLOBs 這種大對象類型之外,其他所有的列(不包括隱藏列和記錄頭信息)占用的字節長度加起來不能超過 65535 個字節。
也就是說,一行記錄除了 TEXT、BLOBs 類型的列,限制最大為 65535 字節,注意是一行的總長度,不是一列。
知道了這個前提之后,我們再來看看這個問題:「varchar(n) 中 n 最大取值為多少?」
varchar(n) 字段類型的 n 代表的是最多存儲的字符數量,并不是字節大小哦。
要算 varchar(n) 最大能允許存儲的字節數,還要看數據庫表的字符集,因為字符集代表著,1個字符要占用多少字節。
比如 ascii 字符集, 1 個字符占用 1 字節,那么 varchar(100) 意味著最大能允許存儲 100 字節的數據。
單字段的情況
前面我們知道了,一行記錄最大只能存儲 65535 字節的數據。
那假設數據庫表只有一個 varchar(n) 類型的列且字符集是 ascii,在這種情況下, varchar(n) 中 n 最大取值是 65535 嗎?
不著急說結論,我們先來做個實驗驗證一下。
我們定義一個 varchar(65535) 類型的字段,字符集為 ascii 的數據庫表。
CREATETABLEtest(
`name`VARCHAR(65535)NULL
)ENGINE=InnoDBDEFAULTCHARACTERSET=asciiROW_FORMAT=COMPACT;
看能不能成功創建一張表:
結果顯示,創建失敗了。
從報錯信息就可以知道一行數據的最大字節數是 65535(不包含 TEXT、BLOBs 這種大對象類型),其中包含了 storage overhead。
問題來了,這個 storage overhead 是什么呢?其實就是「變長字段長度列表」和 「NULL 值列表」。
也就是說一行數據的最大字節數 65535,其實是包含「變長字段長度列表」和 「NULL 值列表」所占用的字節數的。
所以, 我們在算 varchar(n) 中 n 最大值時,需要減去 storage overhead 占用的字節數。
這是因為我們存儲字段類型為 varchar(n) 的數據時,其實分成了三個部分來存儲:
- 真實數據
- 真實數據占用的字節數
- NULL 標識,如果不允許為NULL,這部分不需要
本次案例中,「NULL 值列表」所占用的字節數是多少?
前面我創建表的時候,字段是允許為 NULL 的,所以會用 1 字節來表示「NULL 值列表」。
本次案例中,「變長字段長度列表」所占用的字節數是多少?
「變長字段長度列表」所占用的字節數 = 所有「變長字段長度」占用的字節數之和。
所以,我們要先知道每個變長字段的「變長字段長度」需要用多少字節表示?具體情況分為:
- 條件一:如果變長字段允許存儲的最大字節數小于等于 255 字節,就會用 1 字節表示「變長字段長度」;
- 條件二:如果變長字段允許存儲的最大字節數大于 255 字節,就會用 2 字節表示「變長字段長度」;
我們這里字段類型是 varchar(65535) ,字符集是 ascii,所以代表著變長字段允許存儲的最大字節數是 65535,符合條件二,所以會用 2 字節來表示「變長字段長度」。
因為我們這個案例是只有 1 個變長字段,所以「變長字段長度列表」= 1 個「變長字段長度」占用的字節數,也就是 2 字節。
因為我們在算 varchar(n) 中 n 最大值時,需要減去 「變長字段長度列表」和 「NULL 值列表」所占用的字節數的。
所以,在數據庫表只有一個 varchar(n) 字段且字符集是 ascii 的情況下,varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。
我們先來測試看看 varchar(65533) 是否可行?
可以看到,還是不行,接下來看看 varchar(65532) 是否可行?
可以看到,創建成功了。說明我們的推論是正確的,在算 varchar(n) 中 n 最大值時,需要減去 「變長字段長度列表」和 「NULL 值列表」所占用的字節數的。
當然,我上面這個例子是針對字符集為 ascii 情況,如果采用的是 UTF-8,varchar(n) 最多能存儲的數據計算方式就不一樣了:
- 在 UTF-8 字符集下,一個字符串最多需要三個字節,varchar(n) 的 n 最大取值就是 65532/3 = 21844。
上面所說的只是針對于一個字段的計算方式。
多字段的情況
如果有多個字段的話,要保證所有字段的長度 + 變長字段字節數列表所占用的字節數 + NULL值列表所占用的字節數 <= 65535。
這里舉個多字段的情況的例子。
實驗結果:
總結
varchar(n) 中 n 最大取值為多少?
一行記錄最大能存儲 65535 字節的數據,但是這個是包含「變長字段字節數列表所占用的字節數」和「NULL值列表所占用的字節數」。所以, 我們在算 varchar(n) 中 n 最大值時,需要減去這兩個列表所占用的字節數。
如果一張表只有一個 varchar(n) 字段,且允許為 NULL,字符集為 ascii。varchar(n) 中 n 最大取值為 65532。
計算公式:65535 - 變長字段字節數列表所占用的字節數- NULL值列表所占用的字節數 = 65535 - 2 - 1 = 65532。
如果有多個字段的話,要保證所有字段的長度 + 變長字段字節數列表所占用的字節數 + NULL值列表所占用的字節數 <= 65535。
歷史好文:
字節一面:HTTP 長連接和 TCP 長連接有區別?
美團三面:一直追問我, MySQL 幻讀被徹底解決了嗎?
保姆級教程!2 萬字 + 30 張圖搞懂 MySQL 是怎么加行級鎖的?
MySQL 全局鎖、表級鎖、行級鎖,你搞清楚了嗎?
審核編輯 :李倩
-
數據
+關注
關注
8文章
7004瀏覽量
88944 -
MySQL
+關注
關注
1文章
804瀏覽量
26531
原文標題:面試官:MySQL 中 varchar(n) 中 n 最大取值為多少?
文章出處:【微信號:小林coding,微信公眾號:小林coding】歡迎添加關注!文章轉載請注明出處。
發布評論請先 登錄
相關推薦
評論