mysql的varchar欄位最大長度真的是65535嗎？

來源：小白debug

在mysql建表sql裡，我們經常會有定義字串型別的需求。

CREATE TABLE `user` (
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

比方說user表裡的名字，就是個字串。mysql裡有兩個型別比較適合這個場景。

char和varchar。

宣告它們都需要在欄位邊上加個陣列，比如char(100)和varchar(100)，這個100是指當前欄位能放的最大字元數。

char和varchar的區別在於，varchar雖然宣告瞭最大能放100個字元，但一開始不需要分配100個字元的空間，可以根據需要慢慢增加空間。而char一開始宣告是多少，就固定預留多少空間。

所以，varchar比起char更省空間，一般沒啥大事，大家都愛用varchar。

那問題來了，宣告varchar欄位時，它的最大長度是多少呢？

相信大家應該聽說過varchar欄位的最大長度是65535吧。

沒聽過也沒關係，你現在聽到了。

但實際上是這樣嗎？

我們來做個實驗。

varchar最大值是多少

我們直接拿65535來試一下。

很明顯報錯了。

報錯內容也說了, 由於列長度過大導致報錯，最長是16383。

把上面的65535改成 16383，確實是成功了。

哦？所以說varchar最大值是16383？

當然不是。

這其實還有好幾個因素影響這這個最大值。

不同字符集的影響

varchar裡放的是字串，而字串看起來可以是英文字母，也可以是數字或中文。但不管怎麼樣，都可以把這樣的中英文數字轉成二進位制的01串。

按照一定規則把符號和二進位制碼對應起來，這就是編碼。而把n多這種已經編碼的字元聚在一起，就是我們常說的字符集。

建表語句裡有個CHARSET，這裡填的是字符集。

不同的字符集要求使用的位元組個數也不同，我們可以透過 show charset; 看到mysql支援哪些字符集，以及這些字符集裡儲存一個字元所需的最大位元組數（Maxlen）。

我們嘗試下把建表sql語句裡的CHARSET改一改，比如改成utf8mb3。

我們再執行下，會發現，最大值又不一樣了。

並且，上面雖然提示max=21845，但要是真執行起來會發現還是報錯。在改為21844之後才成功。

不講武德。

再把字符集改為 latin1。會發現，最大值會是 65533。

這裡漸漸可以發現規律。

• utf8mb4的maxlen=4，對應varchar最大長度=16383。4*16383 = 65532。

• utf8mb3的maxlen=3，對應varchar最大長度=21844。3*21844 = 65532。

• latin1的maxlen=1，對應varchar最大長度=65533。   1 * 65533 = 65533。

也就是說varchar邊上的長度代表的是這一列能放的最大字元數，而maxlen代表單個字元佔用的最大位元組數。相乘的結果很接近65535。說明65535是指的位元組數，而不是字元數。

也就是說varchar的最大長度，根據選擇的字符集的不同，會有區別。

總的來說接近於 65535 除以 字符集的maxlen。

但其實這樣還不夠嚴謹。還有其他影響因素。

是否可以為NULL的影響

上面的建表語句裡宣告瞭test欄位都是NOT NULL，也就是非空，如果我們將這個改成可以為NULL，再用 CHARSET=latin1去試試。這時候就會發現，前面NOT NULL的時候最大能使用65533去建表，現在報錯了。

改成65532，就能成功了，也就是最長長度少了1個位元組。

這是因為一個欄位是否為NULL這件事情，是需要一個位元組去記錄下來的。

而當欄位為NOT NULL的時候，則可以省下這個位元組。

列數的影響

上面提到的情況都是在表裡只有一列時的結果，當我們表裡有更多的列時，我們會發現varchar的最大值還會有變化。比如同樣還是latin1字符集，我們再增加一列varchar型別，並且用的還是前面允許的最大值65533。

結果發現這次會失敗。

查了一下資料發現，原來65535是mysql單行的最大長度（不包含blob和text等型別的情況下）

mysql表裡單行中的所有列加起來（不考慮其他隱藏列和記錄頭資訊） ，佔用的最大長度是65535個位元組。

注意上面加粗的部分，加起來不超過65535。

比如如果還有int的列，那它佔用4個位元組，bigint佔用8個位元組，欄位越多，留給單個varchar列的空間就越少。

因此，前面提到的 varchar 的最大長度，接近於 65535 除以 字符集的maxlen，但前提是隻有一列not null 的varchar型別的欄位。

為什麼不是65535而是65533？

不過問題又來了，上面建表sql裡，不管是那種字符集，最後得到的字元數都約等於65533。

但資料庫單行最大值應該是65535。65535 - 65533 = 2 。這裡面還差了個2，為什麼呢？

這就要聊一下mysql單行裡資料到底是怎麼儲存的。

資料錶行儲存的格式

我們可以透過 show table status 命令，檢視到當前表格使用的行格式。

透過上面的 Row_format 欄位可以看到這個表用的是 Dynamic 行格式。

事實上，現在的mysql資料表一般都是採用Dynamic行記錄格式。

我們來看下Dynamic行格式長什麼樣子。

Dynamic格式將行記錄分為兩部分，分為是行記錄的額外資訊和行記錄的真實資料。

行記錄的額外資訊：

• 變長欄位長度列表：指的是varchar，text，blob這種型別，它們屬於變長欄位，這裡表示的就是這些欄位的長度。

• NULL值列表：用來記錄當前行裡哪些列是為null的。如果全部列都是not null的話，那就不需要有這個欄位。

• 記錄頭資訊：這是固定5個位元組，用來記錄一些特殊的資訊，比如這一行是否被刪了，這一行在這個16k的資料頁內是不是最小的，以及指向下一條記錄的指標之類的一些資訊，不需要太關注。

行記錄的真實資料：

裡面放的就是一行裡，每一列的真正內容。除了我們建表時裡涉及到的列以外，還有一些隱藏列。

比如Row_ID，這個是在建表是沒有宣告主鍵時，資料表自動會生成的隱藏主鍵。另外還有trx_id欄位，用於記錄當前這一行資料行是被哪個事務修改的，和一個roll_pointer欄位，這個欄位是用來指向當前這個資料行的上一個版本，透過這個欄位，可以為這行資料形成一條版本鏈，從而實現多版本併發控制（MVCC）。有沒有很眼熟，這個在之前寫的文章裡出現過。

所以我們回過頭來看我們建的表，當只有一列not null的 varchar欄位時，行記錄長下面這樣。

前面提到，行最大值65535位元組是不包含隱藏列和記錄頭資訊的，所以其實是指上圖中紅色的部分。

而最左邊的變長欄位長度列表中，為了表示varchar列的長度，佔用了兩個位元組，也就是16位，2的16次方，最大可以表示65535的長度，正好足夠用來表示varchar列當前的長度是65533。

所以65535 - 65533 = 2 。這裡面差的2，是用來存varchar欄位長度去了。

一個頁才16k，怎麼儲存65533（64k）資料？

之前的文章裡其實多次提到了mysql底層是以頁的形式去儲存資料的，而一個頁固定16k，而一個varchar欄位最大能放65533位元組資料，換算一下大概是64k，整整4個16k的頁。

這裡面是怎麼實現的？

對於這種情況，其實行資料裡針對這個超大的varchar欄位只儲存個20位元組的指標（實際上是個偏移量），這個指標會指向新的頁（off page），這些頁裡儲存的是實際的varchar欄位裡的65533位元組資料。這種由於欄位過長導致需要額外的頁來儲存資料的現象叫行溢位。

大於64k的字串該怎麼處理？

如果離譜點，資料量更大，比64k還大，這時候就不能繼續用varchar了，需要改用text和blob型別欄位。

而text和blob型別本身也是分TINY、MEDIUM，LONG三個檔位的，對應著不同的資料長度，最大到4G左右。

像下面這樣就可以將資料型別定義為LONGTEXT。

CREATE TABLE `test_max_length` (
  `test` LONGTEXT NOT NULL COMMENT '測試長度欄位'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

而他們的儲存方式也跟varchar的情況類似，只儲存20個位元組的指標，實際資料儲存在其他溢位頁裡。

以前我們查某一行資料，他們都在一個16k的資料頁裡，查詢時只要一次磁碟IO就能將這個資料頁讀取出來。

當一個資料庫裡某行資料裡有個特別大的字串時，我們如果還想把整行資料給讀出來，那我們還得把off page的資料給全部讀出來，這意味著更多的磁碟IO，效能就更差了。

為了規避這個問題，我們寫select sql的時候，如果發現某列欄位，是個特別長的字串時，能不讀它就儘量不加到select裡，這也是為什麼大家不建議使用select * from table的原因。

blob和text的區別

一般來說，blob和text都可以用來放超長字串。但它們會有一點點區別。

我們知道字符集（charset）下還有個校對規則（collation）的概念，比如同樣是a，大寫A和小寫a能不能算作是一個字元，這會影響比較和排序，collation就是定義這個規則用的。

blob沒有字符集的概念，而text有。這意味如果用blob來存文字的話，就沒法用字符集的校對規則來排序和做比較。

還有一個區別，blob還能儲存二進位制資料，比如壓縮過的文字資料，圖片或者影片，別笑，雖然不合適，但我確實見過有人拿它來儲存影片。。。

總結

• 現在的mysql資料表一般採用Dynamic行記錄格式。它由行記錄的額外資訊和行記錄的真實資料組成。

• mysql表裡單行中的所有列加起來（不考慮其他隱藏列和記錄頭資訊） ，佔用的最大長度是65535個位元組。

• 如果資料表裡只有一列 not null 的varchar欄位，它的最大長度，接近於 65535 除以 字符集的maxlen。

• 如果要存放大於64k的欄位資料，可以考慮使用longtext和longblob等型別。

• mysql的資料頁大小是16k，為了儲存varchar或者text，blob這種長度可能大於16k的欄位，在Dynamic行格式中，會只保留20個位元組的指標，實際資料則放在其他溢位頁中。為了將它們讀取出來，會需要更多的磁碟IO。

• blob和text很像，但blob沒有字符集的概念，並且還能存放二進位制的資料，比如圖片或影片，但實際上圖片和影片更推薦放在物件儲存（Object Storage Service，簡稱oss）中。

參考資料

《mysql技術內幕》

《從根兒理解mysql》