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

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

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

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

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

但實際上是這樣嗎？

我們來做個實驗。

varchar最大值是多少

我們直接拿65535來試一下。

長度為65535的varchar報錯

很明顯報錯了。

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

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

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

當然不是。

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

不同字符集的影響

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

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

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

不同的字符集要求使用的字節(jié)個數也不同，我們可以通過 ??show charset;?? 看到mysql支持哪些字符集，以及這些字符集里存儲一個字符所需的最大字節(jié)數（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，對應varchar最大長度=16383。4*16383 = 65532。
• utf8mb3的maxlen=3，對應varchar最大長度=21844。3*21844 = 65532。
• latin1的maxlen=1，對應varchar最大長度=65533。   1 * 65533 = 65533。

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

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

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

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

是否可以為NULL的影響

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

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

是否為NULL的影響

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

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

列數的影響

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

結果發(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里，不管是那種字符集，最后得到的字符數都約等于65533。

但數據庫單行最大值應該是65535。65535 - 65533 = 2 。這里面還差了個2，為什么呢？

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

數據表行存儲的格式

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

查看到當前表格使用的行格式

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

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

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

Dynamic行記錄格式

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

行記錄的額外信息：

• 變長字段長度列表：指的是varchar，text，blob這種類型，它們屬于變長字段，這里表示的就是這些字段的長度。
• NULL值列表：用來記錄當前行里哪些列是為null的。如果全部列都是not null的話，那就不需要有這個字段。
• 記錄頭信息：這是固定5個字節(jié)，用來記錄一些特殊的信息，比如這一行是否被刪了，這一行在這個16k的數據頁內是不是最小的，以及指向下一條記錄的指針之類的一些信息，不需要太關注。

行記錄的真實數據：

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

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

隱藏列有哪些

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

單條varchar數據的Dynamic行記錄格式.drawio

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

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

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

一個頁才16k，怎么保存65533（64k）數據？

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

頁結構

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

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

行溢出

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

如果離譜點，數據量更大，比64k還大，這時候就不能繼續(xù)用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個字節(jié)的指針，實際數據保存在其他溢出頁里。

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

當一個數據庫里某行數據里有個特別大的字符串時，我們如果還想把整行數據給讀出來，那我們還得把off page的數據給全部讀出來，這意味著更多的磁盤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還能保存二進制數據，比如壓縮過的文本數據，圖片或者視頻，別笑，雖然不合適，但我確實見過有人拿它來保存視頻。

總結

• 現(xiàn)在的mysql數據表一般采用Dynamic行記錄格式。它由行記錄的額外信息和行記錄的真實數據組成。
• mysql表里單行中的所有列加起來（不考慮其他隱藏列和記錄頭信息） ，占用的最大長度是65535個字節(jié)。
• 如果數據表里只有一列 not null 的varchar字段，它的最大長度，接近于 65535 除以 字符集的maxlen。
• 如果要存放大于64k的字段數據，可以考慮使用longtext和longblob等類型。
• mysql的數據頁大小是16k，為了保存varchar或者text，blob這種長度可能大于16k的字段，在Dynamic行格式中，會只保留20個字節(jié)的指針，實際數據則放在其他溢出頁中。為了將它們讀取出來，會需要更多的磁盤IO。
• blob和text很像，但blob沒有字符集的概念，并且還能存放二進制的數據，比如圖片或視頻，但實際上圖片和視頻更推薦放在對象存儲（Object Storage Service，簡稱oss）中。