mysql的varchar字段最大長(zhǎng)度為什么不是65535而是65533呢？

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

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

比方說(shuō)user表里的名字，就是個(gè)字符串。mysql里有兩個(gè)類型比較適合這個(gè)場(chǎng)景。

char和varchar。

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

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

所以，varchar比起char更省空間，一般沒(méi)啥大事，大家都愛(ài)用varchar。

那問(wèn)題來(lái)了，聲明varchar字段時(shí)，它的最大長(zhǎng)度是多少呢？

相信大家應(yīng)該聽(tīng)說(shuō)過(guò)varchar字段的最大長(zhǎng)度是65535吧。

沒(méi)聽(tīng)過(guò)也沒(méi)關(guān)系，你現(xiàn)在聽(tīng)到了。

但實(shí)際上是這樣嗎？

我們來(lái)做個(gè)實(shí)驗(yàn)。

varchar最大值是多少

我們直接拿65535來(lái)試一下。

長(zhǎng)度為65535的varchar報(bào)錯(cuò)

很明顯報(bào)錯(cuò)了。

報(bào)錯(cuò)內(nèi)容也說(shuō)了,由于列長(zhǎng)度過(guò)大導(dǎo)致報(bào)錯(cuò)，最長(zhǎng)是16383。

把上面的65535改成 16383，確實(shí)是成功了。

哦？所以說(shuō)varchar最大值是16383？

當(dāng)然不是。

這其實(shí)還有好幾個(gè)因素影響這這個(gè)最大值。

不同字符集的影響

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

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

建表語(yǔ)句里有個(gè)CHARSET，這里填的是字符集。

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

查看mysql支持哪些charset

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

我們?cè)賵?zhí)行下，會(huì)發(fā)現(xiàn)，最大值又不一樣了。

utf8mb3下的報(bào)錯(cuò)

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

不講武德。

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

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

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

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

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

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

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

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

總的來(lái)說(shuō)接近于 65535 除以 字符集的maxlen。

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

是否可以為NULL的影響

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

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

是否為NULL的影響

這是因?yàn)橐粋€(gè)字段是否為NULL這件事情，是需要一個(gè)字節(jié)去記錄下來(lái)的。

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

列數(shù)的影響

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

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

兩個(gè)varchar列的情況

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

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

注意上面加粗的部分，加起來(lái)不超過(guò)65535。

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

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

為什么不是65535而是65533？

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

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

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

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

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

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

通過(guò)上面的Row_format字段可以看到這個(gè)表用的是Dynamic行格式。

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

我們來(lái)看下Dynamic行格式長(zhǎng)什么樣子。

Dynamic行記錄格式

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

行記錄的額外信息：

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

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

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

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

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

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

隱藏列有哪些

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

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

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

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

所以65535 - 65533 = 2 。這里面差的2，是用來(lái)存varchar字段長(zhǎng)度去了。

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

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

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

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

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

行溢出

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

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

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

像下面這樣就可以將數(shù)據(jù)類型定義為L(zhǎng)ONGTEXT。

CREATETABLE`test_max_length`(
`test`LONGTEXTNOTNULLCOMMENT'測(cè)試長(zhǎng)度字段'
)ENGINE=InnoDBDEFAULTCHARSET=latin1;

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

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

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

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

blob和text的區(qū)別

一般來(lái)說(shuō)，blob和text都可以用來(lái)放超長(zhǎng)字符串。但它們會(huì)有一點(diǎn)點(diǎn)區(qū)別。

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

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

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

總結(jié)

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

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

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

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

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

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

審核編輯：劉清