在mysql建表sql里，我們經(jīng)常會有定義字符串類型的需求。
CREATE?TABLE?`user`?(
??`name`?varchar(100)?NOT?NULL?DEFAULT?''?COMMENT?'名字'
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?;
比方說user表里的名字，就是個字符串。mysql里有兩個類型比較適合這個場景。
char和varchar。
聲明它們都需要在字段邊上加個數(shù)組，比如char(100)和varchar(100)，這個100是指當前字段能放的最大字符數(shù)。
char和varchar的區(qū)別在于，varchar雖然聲明了最大能放100個字符，但一開始不需要分配100個字符的空間，可以根據(jù)需要慢慢增加空間。而char一開始聲明是多少，就固定預(yù)留多少空間。
所以，varchar比起char更省空間，一般沒啥大事，大家都愛用varchar。
那問題來了，聲明varchar字段時，它的最大長度是多少呢？
相信大家應(yīng)該聽說過varchar字段的最大長度是65535吧。
沒聽過也沒關(guān)系，你現(xiàn)在聽到了。
但實際上是這樣嗎？
我們來做個實驗。
我們直接拿65535來試一下。
很明顯報錯了。
報錯內(nèi)容也說了, 由于列長度過大導(dǎo)致報錯，最長是16383。
把上面的65535改成 16383，確實是成功了。
哦？所以說varchar最大值是16383？
當然不是。
這其實還有好幾個因素影響這這個最大值。
varchar里放的是字符串，而字符串看起來可以是英文字母，也可以是數(shù)字或中文。但不管怎么樣，都可以把這樣的中英文數(shù)字轉(zhuǎn)成二進制的01串。
按照一定規(guī)則把符號和二進制碼對應(yīng)起來，這就是編碼。而把n多這種已經(jīng)編碼的字符聚在一起，就是我們常說的字符集。
建表語句里有個CHARSET，這里填的是字符集。
不同的字符集要求使用的字節(jié)個數(shù)也不同，我們可以通過 show charset; 看到mysql支持哪些字符集，以及這些字符集里存儲一個字符所需的最大字節(jié)數(shù)（Maxlen）。
我們嘗試下把建表sql語句里的CHARSET改一改，比如改成utf8mb3。
我們再執(zhí)行下，會發(fā)現(xiàn)，最大值又不一樣了。
并且，上面雖然提示max=21845，但要是真執(zhí)行起來會發(fā)現(xiàn)還是報錯。在改為21844之后才成功。
不講武德。
再把字符集改為 latin1。會發(fā)現(xiàn)，最大值會是 65533。
這里漸漸可以發(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。
但其實這樣還不夠嚴謹。還有其他影響因素。
上面的建表語句里聲明了test字段都是NOT NULL，也就是非空，如果我們將這個改成可以為NULL，再用 CHARSET=latin1去試試。這時候就會發(fā)現(xiàn)，前面NOT NULL的時候最大能使用65533去建表，現(xiàn)在報錯了。
改成65532，就能成功了，也就是最長長度少了1個字節(jié)。
這是因為一個字段是否為NULL這件事情，是需要一個字節(jié)去記錄下來的。
而當字段為NOT NULL的時候，則可以省下這個字節(jié)。
上面提到的情況都是在表里只有一列時的結(jié)果，當我們表里有更多的列時，我們會發(fā)現(xiàn)varchar的最大值還會有變化。比如同樣還是latin1字符集，我們再增加一列varchar類型，并且用的還是前面允許的最大值65533。
結(jié)果發(fā)現(xiàn)這次會失敗。
查了一下資料發(fā)現(xiàn)，原來65535是mysql單行的最大長度（不包含blob和text等類型的情況下）
mysql表里單行中的所有列加起來（不考慮其他隱藏列和記錄頭信息） ，占用的最大長度是65535個字節(jié)。
注意上面加粗的部分，加起來不超過65535。
比如如果還有int的列，那它占用4個字節(jié)，bigint占用8個字節(jié)，字段越多，留給單個varchar列的空間就越少。
因此，前面提到的 varchar 的最大長度，接近于 65535 除以 字符集的maxlen，但前提是只有一列not null 的varchar類型的字段。
不過問題又來了，上面建表sql里，不管是那種字符集，最后得到的字符數(shù)都約等于65533。
但數(shù)據(jù)庫單行最大值應(yīng)該是65535。65535 - 65533 = 2 。這里面還差了個2，為什么呢？
這就要聊一下mysql單行里數(shù)據(jù)到底是怎么存儲的。
我們可以通過 show table status 命令，查看到當前表格使用的行格式。
通過上面的 Row_format 字段可以看到這個表用的是 Dynamic 行格式。
事實上，現(xiàn)在的mysql數(shù)據(jù)表一般都是采用Dynamic行記錄格式。
我們來看下Dynamic行格式長什么樣子。
Dynamic格式將行記錄分為兩部分，分為是行記錄的額外信息和行記錄的真實數(shù)據(jù)。
行記錄的額外信息：
變長字段長度列表：指的是varchar，text，blob這種類型，它們屬于變長字段，這里表示的就是這些字段的長度。
NULL值列表：用來記錄當前行里哪些列是為null的。如果全部列都是not null的話，那就不需要有這個字段。
記錄頭信息：這是固定5個字節(jié)，用來記錄一些特殊的信息，比如這一行是否被刪了，這一行在這個16k的數(shù)據(jù)頁內(nèi)是不是最小的，以及指向下一條記錄的指針之類的一些信息，不需要太關(guān)注。
行記錄的真實數(shù)據(jù)：
里面放的就是一行里，每一列的真正內(nèi)容。除了我們建表時里涉及到的列以外，還有一些隱藏列。
比如Row_ID，這個是在建表是沒有聲明主鍵時，數(shù)據(jù)表自動會生成的隱藏主鍵。另外還有trx_id字段，用于記錄當前這一行數(shù)據(jù)行是被哪個事務(wù)修改的，和一個roll_pointer字段，這個字段是用來指向當前這個數(shù)據(jù)行的上一個版本，通過這個字段，可以為這行數(shù)據(jù)形成一條版本鏈，從而實現(xiàn)多版本并發(fā)控制（MVCC）。有沒有很眼熟，這個在之前寫的文章里出現(xiàn)過。
所以我們回過頭來看我們建的表，當只有一列not null的 varchar字段時，行記錄長下面這樣。
前面提到，行最大值65535字節(jié)是不包含隱藏列和記錄頭信息的，所以其實是指上圖中紅色的部分。
而最左邊的變長字段長度列表中，為了表示varchar列的長度，占用了兩個字節(jié)，也就是16位，2的16次方，最大可以表示65535的長度，正好足夠用來表示varchar列當前的長度是65533。
所以65535 - 65533 = 2 。這里面差的2，是用來存varchar字段長度去了。
之前的文章里其實多次提到了mysql底層是以頁的形式去存儲數(shù)據(jù)的，而一個頁固定16k，而一個varchar字段最大能放65533字節(jié)數(shù)據(jù)，換算一下大概是64k，整整4個16k的頁。
這里面是怎么實現(xiàn)的？
對于這種情況，其實行數(shù)據(jù)里針對這個超大的varchar字段只保存?zhèn)€20字節(jié)的指針（實際上是個偏移量），這個指針會指向新的頁（off page），這些頁里保存的是實際的varchar字段里的65533字節(jié)數(shù)據(jù)。這種由于字段過長導(dǎo)致需要額外的頁來保存數(shù)據(jù)的現(xiàn)象叫行溢出。
如果離譜點，數(shù)據(jù)量更大，比64k還大，這時候就不能繼續(xù)用varchar了，需要改用text和blob類型字段。
而text和blob類型本身也是分TINY、MEDIUM，LONG三個檔位的，對應(yīng)著不同的數(shù)據(jù)長度，最大到4G左右。
像下面這樣就可以將數(shù)據(jù)類型定義為LONGTEXT。
CREATE?TABLE?`test_max_length`?(
??`test`?LONGTEXT?NOT?NULL?COMMENT?'測試長度字段'
)?ENGINE=InnoDB?DEFAULT?CHARSET=latin1?;
而他們的存儲方式也跟varchar的情況類似，只保存20個字節(jié)的指針，實際數(shù)據(jù)保存在其他溢出頁里。
以前我們查某一行數(shù)據(jù)，他們都在一個16k的數(shù)據(jù)頁里，查詢時只要一次磁盤IO就能將這個數(shù)據(jù)頁讀取出來。
當一個數(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ū)別。
我們知道字符集（charset）下還有個校對規(guī)則（collation）的概念，比如同樣是a，大寫A和小寫a能不能算作是一個字符，這會影響比較和排序，collation就是定義這個規(guī)則用的。
blob沒有字符集的概念，而text有。這意味如果用blob來存文本的話，就沒法用字符集的校對規(guī)則來排序和做比較。
還有一個區(qū)別，blob還能保存二進制數(shù)據(jù)，比如壓縮過的文本數(shù)據(jù)，圖片或者視頻，別笑，雖然不合適，但我確實見過有人拿它來保存視頻。。。
現(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沒有字符集的概念，并且還能存放二進制的數(shù)據(jù)，比如圖片或視頻，但實際上圖片和視頻更推薦放在對象存儲（Object Storage Service，簡稱oss）中。
《mysql技術(shù)內(nèi)幕》
《從根兒理解mysql》
END
→點關(guān)注，不迷路←
