mysql的varchar字段最大長度為什么不是65535而是65533呢？

在mysql建表sql里，我們經(jīng)常會有定義字符串類型的需求。

CREATETABLE`user`(
`name`varchar(100)NOTNULLDEFAULT''COMMENT'名字'
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

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

char和varchar。

聲明它們都需要在字段邊上加個數(shù)組，比如char(100)和varchar(100)，這個100是指當(dāng)前字段能放的最大字符數(shù)。

char和varchar的區(qū)別在于，varchar雖然聲明了最大能放100個字符，但一開始不需要分配100個字符的空間，可以根據(jù)需要慢慢增加空間。而char一開始聲明是多少，就固定預(yù)留多少空間。

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

那問題來了，聲明varchar字段時，它的最大長度是多少呢？

相信大家應(yīng)該聽說過varchar字段的最大長度是65535吧。

沒聽過也沒關(guān)系，你現(xiàn)在聽到了。

但實際上是這樣嗎？

我們來做個實驗。

varchar最大值是多少

我們直接拿65535來試一下。

長度為65535的varchar報錯

很明顯報錯了。

報錯內(nèi)容也說了,由于列長度過大導(dǎo)致報錯，最長是16383。

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

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

當(dāng)然不是。

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

不同字符集的影響

varchar里放的是字符串，而字符串看起來可以是英文字母，也可以是數(shù)字或中文。但不管怎么樣，都可以把這樣的中英文數(shù)字轉(zhuǎn)成二進(jìn)制的01串。

按照一定規(guī)則把符號和二進(jìn)制碼對應(yīng)起來，這就是編碼。而把n多這種已經(jīng)編碼的字符聚在一起，就是我們常說的字符集。

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

不同的字符集要求使用的字節(jié)個數(shù)也不同，我們可以通過show charset;看到mysql支持哪些字符集，以及這些字符集里存儲一個字符所需的最大字節(jié)數(shù)（Maxlen）。

查看mysql支持哪些charset

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

我們再執(zhí)行下，會發(fā)現(xiàn)，最大值又不一樣了。

utf8mb3下的報錯

并且，上面雖然提示max=21845，但要是真執(zhí)行起來會發(fā)現(xiàn)還是報錯。在改為21844之后才成功。

不講武德。

再把字符集改為 latin1。會發(fā)現(xiàn)，最大值會是 65533。

varchar為65533時創(chuàng)建成功

這里漸漸可以發(fā)現(xiàn)規(guī)律。

utf8mb4的maxlen=4，對應(yīng)varchar最大長度=16383。4*16383 = 65532。

utf8mb3的maxlen=3，對應(yīng)varchar最大長度=21844。3*21844 = 65532。

latin1的maxlen=1，對應(yīng)varchar最大長度=65533。 1 * 65533 = 65533。

也就是說varchar邊上的長度代表的是這一列能放的最大字符數(shù)，而maxlen代表單個字符占用的最大字節(jié)數(shù)。相乘的結(jié)果很接近65535。說明65535是指的字節(jié)數(shù)，而不是字符數(shù)。

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

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

但其實這樣還不夠嚴(yán)謹(jǐn)。還有其他影響因素。

是否可以為NULL的影響

上面的建表語句里聲明了test字段都是NOT NULL，也就是非空，如果我們將這個改成可以為NULL，再用CHARSET=latin1去試試。這時候就會發(fā)現(xiàn)，前面NOT NULL的時候最大能使用65533去建表，現(xiàn)在報錯了。

改成65532，就能成功了，也就是最長長度少了1個字節(jié)。

是否為NULL的影響

這是因為一個字段是否為NULL這件事情，是需要一個字節(jié)去記錄下來的。

而當(dāng)字段為NOT NULL的時候，則可以省下這個字節(jié)。

列數(shù)的影響

上面提到的情況都是在表里只有一列時的結(jié)果，當(dāng)我們表里有更多的列時，我們會發(fā)現(xiàn)varchar的最大值還會有變化。比如同樣還是latin1字符集，我們再增加一列varchar類型，并且用的還是前面允許的最大值65533。

結(jié)果發(fā)現(xiàn)這次會失敗。

兩個varchar列的情況

查了一下資料發(fā)現(xiàn)，原來65535是mysql單行的最大長度（不包含blob和text等類型的情況下）

mysql表里單行中的所有列加起來（不考慮其他隱藏列和記錄頭信息） ，占用的最大長度是65535個字節(jié)。

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

比如如果還有int的列，那它占用4個字節(jié)，bigint占用8個字節(jié)，字段越多，留給單個varchar列的空間就越少。

因此，前面提到的 varchar 的最大長度，接近于 65535 除以 字符集的maxlen，但前提是只有一列not null 的varchar類型的字段。

為什么不是65535而是65533？

不過問題又來了，上面建表sql里，不管是那種字符集，最后得到的字符數(shù)都約等于65533。

但數(shù)據(jù)庫單行最大值應(yīng)該是65535。65535 - 65533 = 2 。這里面還差了個2，為什么呢？

這就要聊一下mysql單行里數(shù)據(jù)到底是怎么存儲的。

數(shù)據(jù)表行存儲的格式

我們可以通過show table status命令，查看到當(dāng)前表格使用的行格式。

查看到當(dāng)前表格使用的行格式

通過上面的Row_format字段可以看到這個表用的是Dynamic行格式。

事實上，現(xiàn)在的mysql數(shù)據(jù)表一般都是采用Dynamic行記錄格式。

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

Dynamic行記錄格式

Dynamic格式將行記錄分為兩部分，分為是行記錄的額外信息和行記錄的真實數(shù)據(jù)。

行記錄的額外信息：

變長字段長度列表：指的是varchar，text，blob這種類型，它們屬于變長字段，這里表示的就是這些字段的長度。

NULL值列表：用來記錄當(dāng)前行里哪些列是為null的。如果全部列都是not null的話，那就不需要有這個字段。

記錄頭信息：這是固定5個字節(jié)，用來記錄一些特殊的信息，比如這一行是否被刪了，這一行在這個16k的數(shù)據(jù)頁內(nèi)是不是最小的，以及指向下一條記錄的指針之類的一些信息，不需要太關(guān)注。

行記錄的真實數(shù)據(jù)：

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

比如Row_ID，這個是在建表是沒有聲明主鍵時，數(shù)據(jù)表自動會生成的隱藏主鍵。另外還有trx_id字段，用于記錄當(dāng)前這一行數(shù)據(jù)行是被哪個事務(wù)修改的，和一個roll_pointer字段，這個字段是用來指向當(dāng)前這個數(shù)據(jù)行的上一個版本，通過這個字段，可以為這行數(shù)據(jù)形成一條版本鏈，從而實現(xiàn)多版本并發(fā)控制（MVCC）。有沒有很眼熟，這個在之前寫的文章里出現(xiàn)過。

隱藏列有哪些

所以我們回過頭來看我們建的表，當(dāng)只有一列not null的 varchar字段時，行記錄長下面這樣。

單條varchar數(shù)據(jù)的Dynamic行記錄格式.drawio

前面提到，行最大值65535字節(jié)是不包含隱藏列和記錄頭信息的，所以其實是指上圖中紅色的部分。

而最左邊的變長字段長度列表中，為了表示varchar列的長度，占用了兩個字節(jié)，也就是16位，2的16次方，最大可以表示65535的長度，正好足夠用來表示varchar列當(dāng)前的長度是65533。

所以65535 - 65533 = 2 。這里面差的2，是用來存varchar字段長度去了。

一個頁才16k，怎么保存65533（64k）數(shù)據(jù)？

之前的文章里其實多次提到了mysql底層是以頁的形式去存儲數(shù)據(jù)的，而一個頁固定16k，而一個varchar字段最大能放65533字節(jié)數(shù)據(jù)，換算一下大概是64k，整整4個16k的頁。

頁結(jié)構(gòu)

這里面是怎么實現(xiàn)的？

對于這種情況，其實行數(shù)據(jù)里針對這個超大的varchar字段只保存?zhèn)€20字節(jié)的指針（實際上是個偏移量），這個指針會指向新的頁（off page），這些頁里保存的是實際的varchar字段里的65533字節(jié)數(shù)據(jù)。這種由于字段過長導(dǎo)致需要額外的頁來保存數(shù)據(jù)的現(xiàn)象叫行溢出。

行溢出

大于64k的字符串該怎么處理？

如果離譜點，數(shù)據(jù)量更大，比64k還大，這時候就不能繼續(xù)用varchar了，需要改用text和blob類型字段。

而text和blob類型本身也是分TINY、MEDIUM，LONG三個檔位的，對應(yīng)著不同的數(shù)據(jù)長度，最大到4G左右。

像下面這樣就可以將數(shù)據(jù)類型定義為LONGTEXT。

CREATETABLE`test_max_length`(
`test`LONGTEXTNOTNULLCOMMENT'測試長度字段'
)ENGINE=InnoDBDEFAULTCHARSET=latin1;

而他們的存儲方式也跟varchar的情況類似，只保存20個字節(jié)的指針，實際數(shù)據(jù)保存在其他溢出頁里。

以前我們查某一行數(shù)據(jù)，他們都在一個16k的數(shù)據(jù)頁里，查詢時只要一次磁盤IO就能將這個數(shù)據(jù)頁讀取出來。

當(dāng)一個數(shù)據(jù)庫里某行數(shù)據(jù)里有個特別大的字符串時，我們?nèi)绻€想把整行數(shù)據(jù)給讀出來，那我們還得把off page的數(shù)據(jù)給全部讀出來，這意味著更多的磁盤IO，性能就更差了。

為了規(guī)避這個問題，我們寫select sql的時候，如果發(fā)現(xiàn)某列字段，是個特別長的字符串時，能不讀它就盡量不加到select里，這也是為什么大家不建議使用select * from table的原因。

blob和text的區(qū)別

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

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

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

還有一個區(qū)別，blob還能保存二進(jìn)制數(shù)據(jù)，比如壓縮過的文本數(shù)據(jù)，圖片或者視頻，別笑，雖然不合適，但我確實見過有人拿它來保存視頻。。。

總結(jié)

現(xiàn)在的mysql數(shù)據(jù)表一般采用Dynamic行記錄格式。它由行記錄的額外信息和行記錄的真實數(shù)據(jù)組成。

mysql表里單行中的所有列加起來（不考慮其他隱藏列和記錄頭信息） ，占用的最大長度是65535個字節(jié)。

如果數(shù)據(jù)表里只有一列 not null的varchar字段，它的最大長度，接近于65535 除以 字符集的maxlen。

如果要存放大于64k的字段數(shù)據(jù)，可以考慮使用longtext和longblob等類型。

mysql的數(shù)據(jù)頁大小是16k，為了保存varchar或者text，blob這種長度可能大于16k的字段，在Dynamic行格式中，會只保留20個字節(jié)的指針，實際數(shù)據(jù)則放在其他溢出頁中。為了將它們讀取出來，會需要更多的磁盤IO。

blob和text很像，但blob沒有字符集的概念，并且還能存放二進(jìn)制的數(shù)據(jù)，比如圖片或視頻，但實際上圖片和視頻更推薦放在對象存儲（ObjectStorageService，簡稱oss）中。

審核編輯：劉清