在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)中。
審核編輯:劉清
-
編碼器
+關(guān)注
關(guān)注
44文章
3556瀏覽量
133836 -
MySQL
+關(guān)注
關(guān)注
1文章
794瀏覽量
26359 -
MYSQL數(shù)據(jù)庫
+關(guān)注
關(guān)注
0文章
95瀏覽量
9372
原文標(biāo)題:mysql的varchar字段最大長度真的是65535嗎?
文章出處:【微信號:良許Linux,微信公眾號:良許Linux】歡迎添加關(guān)注!文章轉(zhuǎn)載請注明出處。
發(fā)布評論請先 登錄
相關(guān)推薦
評論