mysql的varchar與text對(duì)比

varchar和text是mysql字符存儲(chǔ)爭(zhēng)議比較多的領(lǐng)域,究竟大字段用那個(gè)比較好,我們來對(duì)比一下,然后自行選擇.

大小對(duì)比
VARCHAR:varchar在mysql中必須滿足最大行寬度限制，也就是 65535(64k)字節(jié)，而varchar本身是按字符串個(gè)數(shù)來定義的,在mysql中使用uft-8字符集一個(gè)字符占用三個(gè)字節(jié)，所以單表varchar實(shí)際占用最大長度如下.
?? ?1.使用utf-8字符編碼集varchar最大長度是(65535-2)/3=21844個(gè)字符（超過255個(gè)字節(jié)會(huì)有2字節(jié)的額外占用空間開銷，所以減2,如果是255以下,則減1）。
?? ?2.使用utf-8mb4字符集，mysql中使用 utf-8mb4 字符集一個(gè)字符占用4個(gè)字節(jié)，所以 varchar 最大長度是(65535-2)/4=16383 個(gè)字符（超過255個(gè)字節(jié)會(huì)有2字節(jié)的額外占用空間開銷，所以減2,如果是255以下,則減1）。

注意:如果使用utf-8mb4字符集時(shí),有些需要存儲(chǔ)utf-8字符的時(shí)候,還是會(huì)只占3字節(jié),所以有時(shí)會(huì)比這個(gè)計(jì)算值能存更多個(gè)字符,因?yàn)閡tf-8mb4是utf-8的超集.

TEXT:最大限制也是64k個(gè)字節(jié),但是本質(zhì)是溢出存儲(chǔ),innodb默認(rèn)只會(huì)存放前768字節(jié)在數(shù)據(jù)頁中,而剩余的數(shù)據(jù)則會(huì)存儲(chǔ)在溢出段中,雖然也受單表65535最大行寬度限制,但mysql表中每個(gè)BLOB和TEXT列實(shí)際只占其中的5至9個(gè)字節(jié)，其他部分將進(jìn)行溢出存儲(chǔ).所以實(shí)際占用表最大行寬度為9+2字節(jié),外加的是額外開銷,跟表的實(shí)際寬度沒有關(guān)系.
?? ?1.如果使用utf-8字符集,那么單字段占用最大長度也是21844個(gè)字符.
?? ?2.不過單表可以設(shè)置多個(gè)text字段,這就突破了單表最大行寬度65535的限制

注意:如果采用了新的行格式類型Barracuda (梭子魚)，該文件格式擁有新的兩種行格式:compressed和dynamic,兩種格式對(duì)blob/text字段采用完全溢出的方式,數(shù)據(jù)頁中只存放20字節(jié),其余的都存放在溢出段中.

其他text:

text字段是分長中短類型,不像varchar只有一種,除了上面的text,還有下面三個(gè).

TinyText:最大長度255個(gè)字節(jié),實(shí)際上是個(gè)沒什么意義的類型了.

MEDIUMTEXT:最大長度限制16M個(gè)字節(jié)。和普通text一樣也支持溢出存儲(chǔ)，所以實(shí)際占用表最大行寬度為9+3字節(jié),外加的是額外開銷
LONGTEXT:最大長度限制4G個(gè)字節(jié)。和普通text一樣也支持溢出存儲(chǔ)，所以實(shí)際占用表最大行寬度為9+4字節(jié),外加的是額外開銷

----------------------------------------

示例:

#VARCHAR單表單字段最長不能超過21844
CREATE?TABLE?test(
????va?VARCHAR(21845)
)DEFAULT?CHARSET=utf8;
[Err]?1118?-?Row?size?too?large.?The?maximum?row?size?for?the?used?table?type,?not?counting?BLOBs,?is?65535.?This?includes?storage?overhead,?check?the?manual.?You?have?to?change?some?columns?to?TEXT?or?BLOBs
#這樣就可以了
CREATE?TABLE?test(
????va?VARCHAR(21844)
)DEFAULT?CHARSET=utf8;
受影響的行:?0
時(shí)間:?0.155s
#雖然每個(gè)BLOB和TEXT列?賬戶只占其中的5至9個(gè)字節(jié)。但是還不夠
CREATE?TABLE?test(
????va?VARCHAR(21841),
????tx?text
)DEFAULT?CHARSET=utf8;
[Err]?1118?-?Row?size?too?large.?The?maximum?row?size?for?the?used?table?type,?not?counting?BLOBs,?is?65535.?This?includes?storage?overhead,?check?the?manual.?You?have?to?change?some?columns?to?TEXT?or?BLOBs
#然后9+2就可以了
CREATE?TABLE?test(
????va?VARCHAR(21840),
????tx?text
)DEFAULT?CHARSET=utf8;
受影響的行:?0
時(shí)間:?0.170s

--------------------------------------------------------

額外占用空間開銷說明:
varchar 小于255byte? 1byte overhead
varchar 大于255byte? 2byte overhead
tinytext 0-255 1 byte overhead
text 0-65535 byte 2 byte overhead
mediumtext 0-16M? 3 byte overhead
longtext 0-4Gb 4byte overhead

注意:

雖然text字段會(huì)把超過768字節(jié)的大部分?jǐn)?shù)據(jù)溢出存放到硬盤其他空間,看上去是會(huì)更加增加磁盤壓力.但從處理形態(tài)上來講varchar大于768字節(jié)后，實(shí)質(zhì)上存儲(chǔ)和text差別不是太大了.因?yàn)槌L的varchar也是會(huì)用到溢出存儲(chǔ),讀取該行也是要去讀硬盤然后加載到內(nèi)存,基本認(rèn)為是一樣的。

另外從8000byte這個(gè)點(diǎn)說明一下,mysql的innodb data page默認(rèn)一個(gè)數(shù)據(jù)頁是16K,要存兩行數(shù)據(jù),所以對(duì)于varcahr, text如果一行數(shù)據(jù)不超過8000byte ,overflow不會(huì)存到別的page中。
----------------------------------------

差異點(diǎn):
text字段，MySQL不允許有默認(rèn)值。建立索引必須給出前綴索引長度.
varchar允許有默認(rèn)值,對(duì)索引長度沒限制,

注意:

InnoDB引擎單一字段索引的默認(rèn)長度最大為767字節(jié),myisam為1000字節(jié).例如字符編碼是utf8,那么varchar的索引最大長度是256個(gè)字符.超出限制會(huì)導(dǎo)致索引創(chuàng)建不成功,轉(zhuǎn)而需要?jiǎng)?chuàng)建前綴索引.設(shè)置innodb_large_prefix=1可以增大限制,允許索引使用動(dòng)態(tài)壓縮,但是表的row_format必須是compressed或者dynamic.可以使索引列長度大于767bytes,但是總長度不能大于3072 bytes.
----------------------------------------

總結(jié)：
　　根據(jù)存儲(chǔ)的實(shí)現(xiàn):可以考慮用varchar替代text,因?yàn)関archar存儲(chǔ)更彈性,存儲(chǔ)數(shù)據(jù)少的話性能更高
　　如果需要非空的默認(rèn)值，就必須使用varchar
　　如果存儲(chǔ)的數(shù)據(jù)大于64K，就必須使用到mediumtext , longtext,因?yàn)関archar已經(jīng)存不下了
　　如果varchar(255+)之后,和text在存儲(chǔ)機(jī)制是一樣的,性能也相差無幾
　　需要特別注意varchar(255)不只是255byte ,實(shí)質(zhì)上有可能占用的更多。

感謝吳炳錫老師指導(dǎo)

參考文章地址:http://wubx.net/varchar-vs-text/

https://blog.csdn.net/free_ant/article/details/52936756

https://blog.csdn.net/q3dxdx/article/details/51014357