.jpg)
最近有幾個同學(xué)問我varchar和text有啥別嗎，這個問題，以前說真的也沒太多的整理，以前遇到text在設(shè)計(jì)中就是盡可能的拆到另一個表中，保持主表盡量的瘦小，可以讓innodb bp緩存更多的數(shù)據(jù)。
今天借次機(jī)會系統(tǒng)整理一下，主要從存儲上，最大值，默認(rèn)值幾個方面進(jìn)行比較。
BTW： 從ISO SQL:2003上講VARCHAR是一個標(biāo)準(zhǔn)型，但TEXT不是（包括tinytext）.varchar在mysql 5.0.3之前只支持0-255byte, 在5.0.3之后才支持到0-65535byte.
從存儲上講：
– text 是要要進(jìn)overflow存儲。 也是對于text字段，不會和行數(shù)據(jù)存在一起。但原則上不會全部overflow ,
會有768字節(jié)和原始的行存儲在一塊，多于768的行會存在和行相同的Page或是其它Page上。
?
– varchar 在MySQL內(nèi)部屬于從blob發(fā)展出來的一個結(jié)構(gòu)，在早期版本中innobase中，也是768字節(jié)以后進(jìn)行overfolw存儲。
?
– 對于Innodb-plugin后： 對于變長字段處理都是20Byte后進(jìn)行overflow存儲
（在新的row_format下：dynimic compress）
說完存儲后，說一下使用這些大的變長字段的缺點(diǎn)：
– 在Innobase中,變長字段，是盡可能的存儲到一個Page里，這樣，如果使用到這些大的變長字段，會造成一個Page里能容納的行
數(shù)很少，在查詢時，雖然沒查詢這些大的字段，但也會加載到innodb buffer pool中，等于浪費(fèi)的內(nèi)存。
（buffer pool 的緩存是按page為單位）（不在一個page了會增加隨機(jī)的IO）
?
– 在innodb-plugin中為了減少這種大的變長字段對內(nèi)存的浪費(fèi)，引入了大于20個字節(jié)的，都進(jìn)行overflow存儲，
而且希望不要存到相同的page中，為了增加一個page里能存儲更多的行，提高buffer pool的利用率。 這也要求我們，
如果不是特別需要就不要讀取那些變長的字段。?
那問題來了？ 為什么varchar(255+)存儲上和text很相似了，但為什么還要有varchar, mediumtext, text這些類型？
（從存儲上來講大于255的varchar可以說是轉(zhuǎn)換成了text.這也是為什么varchar大于65535了會轉(zhuǎn)成mediumtext)
我理解：這塊是一方面的兼容，另一方面在非空的默認(rèn)值上varchar和text有區(qū)別。從整體上看功能上還是差別的。
這里還涉及到字段額外開銷的：
– 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?
備注 overhead是指需要幾個字節(jié)用于記錄該字段的實(shí)際長度。
從處理形態(tài)上來講varchar 大于768字節(jié)后，實(shí)質(zhì)上存儲和text差別不是太大了。 基本認(rèn)為是一樣的。
另外從8000byte這個點(diǎn)說明一下： 對于varcahr, text如果行不超過8000byte（大約的數(shù)，innodb data page的一半） ,overflow不會存到別的page中。基于上面的特性可以總結(jié)為text只是一個MySQL擴(kuò)展出來的特殊語法有兼容的感覺。
默認(rèn)值問題：
– 對于text字段，MySQL不允許有默認(rèn)值。
– varchar允許有默認(rèn)值
?
總結(jié)：
根據(jù)存儲的實(shí)現(xiàn)： 可以考慮用varchar替代tinytext
如果需要非空的默認(rèn)值，就必須使用varchar
如果存儲的數(shù)據(jù)大于64K，就必須使用到mediumtext , longtext
varchar(255+)和text在存儲機(jī)制是一樣的
需要特別注意varchar(255)不只是255byte ,實(shí)質(zhì)上有可能占用的更多。
特別注意，varchar大字段一樣的會降低性能，所以在設(shè)計(jì)中還是一個原則大字段要拆出去，主表還是要盡量的瘦小
源碼中類型：
+--Field_str?(abstract) ?|??+--Field_longstr ?|??|??+--Field_string ?|??|??+--Field_varstring ?|??|??+--Field_blob ?|??|?????+--Field_geom ?|??| ?|??+--Field_null ?|??+--Field_enum ?|?????+--Field_set
（末完待續(xù)，也希望大家一塊討論一下）
參考：
http://yoshinorimatsunobu.blogspot.com/2010/11/handling-long-textsblobs-in-innodb-1-to.html
http://nicj.net/mysql-text-vs-varchar-performance/
http://www.pythian.com/blog/text-vs-varchar/
測試SQL及方法
create?table?tb_01( c1?varchar(255), c2?varchar(255), c3?varchar(255), c4?varchar(255), c5?varchar(255), c6?varchar(255), c7?varchar(255), c8?varchar(255), c9?varchar(255), c10?varchar(255), c11?varchar(255) )engine=Innodb; ?? insert?into?tb_01(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11)?values(repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255)); ERROR?1118?(42000):?Row?size?too?large?(>?8126).?Changing?some?columns?to?TEXT?or?BLOB?or?using?ROW_FORMAT=DYNAMIC?or?ROW_FORMAT=COMPRESSED?may?help.?In?current?row?format,?BLOB?prefix?of?768?bytes?is?stored?inline. ?? (testing)root@localhost?[wubx]>?set?global?innodb_file_format=BARRACUDA; Query?OK,?0?rows?affected?(0.00?sec) ?? (testing)root@localhost?[wubx]>?alter?table?tb_01?row_format=dynamic; Query?OK,?0?rows?affected?(0.19?sec) Records:?0??Duplicates:?0??Warnings:?0 ?? (testing)root@localhost?[wubx]>?insert?into?tb_01(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11)?values(repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255)); Query?OK,?1?row?affected?(0.00?sec) ?? ?? set?global?innodb_file_format=Antelope; create?table?tb_02( c1?varchar(2000), c2?varchar(2000), c3?varchar(2000), c4?varchar(2000), c5?varchar(2000), c6?varchar(2000), c7?varchar(2000), c8?varchar(2000) )engine=Innodb; ?? insert?into?tb_02(c1,?c2,?c3,c4,c5,c6,c7,c8)?values(repeat('吳',2000),repeat('吳',2000),repeat('吳',2000),repeat('吳',2000),repeat('吳',2000),repeat('吳',2000),repeat('吳',2000),repeat('吳',2000)?); ?? ?? create?table?tb_03( c1?text, c2?text, c3?text, c4?text, c5?text, c6?text, c7?text, c8?text, c9?text, c10?text, c11?text )engine=Innodb; insert?into?tb_03(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11)?values(repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255)); ?? (testing)root@localhost?[wubx]>?insert?into?tb_03(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11)?values(repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255)); ERROR?1118?(42000):?Row?size?too?large?(>?8126).?Changing?some?columns?to?TEXT?or?BLOB?or?using?ROW_FORMAT=DYNAMIC?or?ROW_FORMAT=COMPRESSED?may?help.?In?current?row?format,?BLOB?prefix?of?768?bytes?is?stored?inline. ?? set?global?innodb_file_format=BARRACUDA; alter?table?tb_03?row_format=dynamic;
.png)
