mysql的varchar與text對比

varchar和text是mysql字符存儲爭議比較多的領域,究竟大字段用那個比較好,我們來對比一下,然后自行選擇.

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

注意:如果使用utf-8mb4字符集時,有些需要存儲utf-8字符的時候,還是會只占3字節,所以有時會比這個計算值能存更多個字符,因為utf-8mb4是utf-8的超集.

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

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

其他text:

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

TinyText:最大長度255個字節,實際上是個沒什么意義的類型了.

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

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

示例:

#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
時間:?0.155s
#雖然每個BLOB和TEXT列?賬戶只占其中的5至9個字節。但是還不夠
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
時間:?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字段會把超過768字節的大部分數據溢出存放到硬盤其他空間,看上去是會更加增加磁盤壓力.但從處理形態上來講varchar大于768字節后，實質上存儲和text差別不是太大了.因為超長的varchar也是會用到溢出存儲,讀取該行也是要去讀硬盤然后加載到內存,基本認為是一樣的。

另外從8000byte這個點說明一下,mysql的innodb data page默認一個數據頁是16K,要存兩行數據,所以對于varcahr, text如果一行數據不超過8000byte ,overflow不會存到別的page中。
----------------------------------------

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

注意:

InnoDB引擎單一字段索引的默認長度最大為767字節,myisam為1000字節.例如字符編碼是utf8,那么varchar的索引最大長度是256個字符.超出限制會導致索引創建不成功,轉而需要創建前綴索引.設置innodb_large_prefix=1可以增大限制,允許索引使用動態壓縮,但是表的row_format必須是compressed或者dynamic.可以使索引列長度大于767bytes,但是總長度不能大于3072 bytes.
----------------------------------------

總結：
　　根據存儲的實現:可以考慮用varchar替代text,因為varchar存儲更彈性,存儲數據少的話性能更高
　　如果需要非空的默認值，就必須使用varchar
　　如果存儲的數據大于64K，就必須使用到mediumtext , longtext,因為varchar已經存不下了
　　如果varchar(255+)之后,和text在存儲機制是一樣的,性能也相差無幾
　　需要特別注意varchar(255)不只是255byte ,實質上有可能占用的更多。

感謝吳炳錫老師指導

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

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

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