mysql中varchar和text字段類型理解

在mysql中varchar,text字段類型是可以存儲字符串了，一個是長度不能超過1024而另一個長度小編發現是非常長的，那麼varchar,text有什麼區別呢？下面一起來看看吧。

最近有幾個同學問我varchar和text有啥別嗎，這個問題，以前說真的也沒太多的整理，以前遇到text在設計中就是盡可能的拆到另一個表中，保持主表盡量的瘦小，可以讓innodb bp緩存更多的數據。

今天借次機會系統整理一下，主要從存儲上，最大值，默認值幾個方面進行比較。

BTW： 從ISO SQL:2003上講VARCHAR是一個標准型，但TEXT不是（包括tinytext）.varchar在MySQL 5.0.3之前只支持0-255byte, 在5.0.3之後才支持到0-65535byte.

從存儲上講：

- text 是要要進overflow存儲。 也是對於text字段，不會和行數據存在一起。但原則上不會全部overflow ,
會有768字節和原始的行存儲在一塊，多於768的行會存在和行相同的Page或是其它Page上。
 
- varchar 在MySQL內部屬於從blob發展出來的一個結構，在早期版本中innobase中，也是768字節以後進行overfolw存儲。
 
- 對於Innodb-plugin後： 對於變長字段處理都是20Byte後進行overflow存儲
（在新的row_format下：dynimic compress）
說完存儲後，說一下使用這些大的變長字段的缺點：

- 在Innobase中,變長字段，是盡可能的存儲到一個Page裡，這樣，如果使用到這些大的變長字段，會造成一個Page裡能容納的行
數很少，在查詢時，雖然沒查詢這些大的字段，但也會加載到innodb buffer pool中，等於浪費的內存。
（buffer pool 的緩存是按page為單位）（不在一個page了會增加隨機的IO）
 
- 在innodb-plugin中為了減少這種大的變長字段對內存的浪費，引入了大於20個字節的，都進行overflow存儲，
而且希望不要存到相同的page中，為了增加一個page裡能存儲更多的行，提高buffer pool的利用率。 這也要求我們，
如果不是特別需要就不要讀取那些變長的字段。

那問題來了？ 為什麼varchar(255+)存儲上和text很相似了，但為什麼還要有varchar, mediumtext, text這些類型？
（從存儲上來講大於255的varchar可以說是轉換成了text.這也是為什麼varchar大於65535了會轉成mediumtext)

我理解：這塊是一方面的兼容，另一方面在非空的默認值上varchar和text有區別。從整體上看功能上還是差別的。

這裡還涉及到字段額外開銷的：

 代碼如下 復制代碼 - 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是指需要幾個字節用於記錄該字段的實際長度。

從處理形態上來講varchar 大於768字節後，實質上存儲和text差別不是太大了。 基本認為是一樣的。
另外從8000byte這個點說明一下： 對於varcahr, text如果行不超過8000byte（大約的數，innodb data page的一半） ,overflow不會存到別的page中。基於上面的特性可以總結為text只是一個MySQL擴展出來的特殊語法有兼容的感覺。

默認值問題：

- 對於text字段，MySQL不允許有默認值。
- varchar允許有默認值

總結：

根據存儲的實現： 可以考慮用varchar替代tinytext
如果需要非空的默認值，就必須使用varchar
如果存儲的數據大於64K，就必須使用到mediumtext , longtext
varchar(255+)和text在存儲機制是一樣的
 
需要特別注意varchar(255)不只是255byte ,實質上有可能占用的更多。
 
特別注意，varchar大字段一樣的會降低性能，所以在設計中還是一個原則大字段要拆出去，主表還是要盡量的瘦小
源碼中類型：

 代碼如下 復制代碼 +--Field_str (abstract)
 |  +--Field_longstr
 |  |  +--Field_string
 |  |  +--Field_varstring
 |  |  +--Field_blob
 |  |     +--Field_geom
 |  |
 |  +--Field_null
 |  +--Field_enum
 |     +--Field_set

 

測試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;