mysql中char,varchar,text

標籤：

1、char

char最大長度是255字元，注意是字元數和字元集沒關係。

1）可以有預設值，

2）尾部有空格會被截斷

3）不管漢字、英文，還是其他編碼，都可以存255字元

2、varchar

1）varchar最多能儲存65535個位元組的資料,varchar 的最大長度受限於最大行長度（max row size，65535bytes），65535並不是一個很精確的上限，可以繼續縮小這個上限

65535個位元組包括所有欄位的長度，變長欄位的長度標識（每個變長欄位額外使用1或者2個位元組記錄實際資料長度）、NULL標識位的累計

NULL標識位，如果varchar欄位定義中帶有default null允許列空,則需要需要1bit來標識，每8個bits的標識組成一個欄位

一張表中存在N個varchar欄位，那麼需要（N+7）/8 （取整）bytes儲存所有的NULL標識位

如果資料表只有一個varchar欄位且該欄位DEFAULT NULL，那麼該varchar欄位的最大長度為65532個位元組，即65535-2-1=65532 bytes

在實體儲存體上，varchar使用1到2個額外的位元組表示實際儲存的字串長度（bytes）。如果列的最大長度小於256個位元組，用一個位元組表示（標識）。如果最大長度大於等於256，使用兩個位元組。當選擇的字元集為latin1，一個字元佔用一個bytevarchar(255)儲存一個字元，一共使用2個bytes物理空間儲存資料實際資料長度和資料值。varchar(256)儲存一個字元，使用2 bytes表示實際資料長度，一共需要3 bytes實體儲存體空間。

2）可以有預設值

3）尾部有空格不會截斷

create table test(name varchar(65533) not null)engine=innodb DEFAULT CHARSET=latin1

使用latin1編碼的時候

mysql>  drop table if exists  test;create table test(name varchar(65533) not null)engine=innodb DEFAULT CHARSET=latin1;Query OK, 0 rows affected (0.04 sec)Query OK, 0 rows affected (0.18 sec)mysql>  drop table if exists  test;create table test(name varchar(65534) not null)engine=innodb DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.05 sec)ERROR 1118 (42000): 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 BLOBsmysql>  drop table if exists  test;create table test(name varchar(65533)  null)engine=innodb DEFAULT CHARSET=latin1;    Query OK, 0 rows affected, 1 warning (0.00 sec)ERROR 1118 (42000): 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 BLOBsmysql>  drop table if exists  test;create table test(name varchar(65532)  null)engine=innodb DEFAULT CHARSET=latin1; Query OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected (0.04 sec)

 可以看出最大可儲存的為65533位元組，not null 的時候，其中兩個位元組記錄長度

使用utf8編碼的時候

mysql>  drop table if exists  test;create table test(name varchar(65533) not null)engine=innodb DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.06 sec)ERROR 1074 (42000): Column length too big for column ‘name‘ (max = 21845); use BLOB or TEXT insteadmysql>  drop table if exists  test;create table test(name varchar(21845) not null)engine=innodb DEFAULT CHARSET=utf8;     Query OK, 0 rows affected, 1 warning (0.00 sec)ERROR 1118 (42000): 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 BLOBsmysql>  drop table if exists  test;create table test(name varchar(21844) not null)engine=innodb DEFAULT CHARSET=utf8; Query OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected (0.24 sec)

 可以看出最大值為(65535-2)/3=21844,(65535-2-1)/3=21844

 

InnoDB中的varchar
InnoDB中varchar的實體儲存體方式與InnoDB使用的innodb_file_format有關。早期的innodb_file_forma使用的Antelope檔案格式，支援redundant和compact兩種row_format。從5.5開始或者InnoDB1.1，可以使用一種新的file format，Barracuda。Barracuda相容Redundant，另外還支援dynamic和compressed兩種row_format.
當innodb_file_format=Antelope，ROW_FORMAT=REDUNDANT 或者COMPACT。
innodb的叢集索引（cluster index）僅僅儲存varchar、text、blob欄位的前768個位元組，多餘的位元組儲存在一個獨立的overflow page中，這個列也被稱作off-page。768個位元組首碼後面緊跟著20位元組指標，指向overflow pages的位置。
另外，在innodb_file_format=Antelope情況下，InnoDB中最多能儲存10個大欄位（需要使用off-page儲存）。innodbd的預設page size為16KB，InnoDB單行的長度不能超過16k/2=8k個位元組，（768+20）*10 < 8k。
當innodb_file_format=Barracuda, ROW_FORMAT=DYNAMIC 或者 COMPRESSED
innodb中所有的varchar、text、blob欄位資料是否完全off-page儲存，根據該欄位的長度和整行的總長度而定。對off-page儲存的列，cluster index中僅僅儲存20位元組的指標，指向實際的overflow page儲存位置。如果單行的長度太大而不能完全適配cluster index page，innodb將會選擇最長的列作為off-page儲存，直到行的長度能夠適配cluster index page。

 

MyISAM中的varchar
對於MyISAM引擎，varchar欄位所有資料存放區在資料行內（in-line）。myisam表的row_format也影響到varchar的實體儲存體行為。
MyISAM的row_format可以通過create或者alter sql語句設為fixed和dynamic。另外可以通過myisampack產生row_format=compresse的儲存格式。
當myisam表中不存在text或者blob類型的欄位，那麼可以把row_format設定為fixed(也可以為dynamic)，否則只能為dynamic。
當表中存在varchar欄位的時候，row_format可以設定為fixed或者dynamic。使用row_format=fixed儲存varchar欄位資料，浪費儲存空間，varchar此時會定長儲存。row_format為fixed和dynamic，varchar的物理實現方式也不同（可以查看原始碼檔案field.h和field.cc），因而myisam的row_format在fixed和dynamic之間發生轉換的時候，varchar欄位的實體儲存體方式也將會發生變化

 

text

1）text和varchar基本相同

2）text會忽略指定的大小這和varchar有所不同，text不能有預設值

3）尾部有空格不會被截斷

4）text使用額 外的2個位元組來儲存資料的大小，varchar根據儲存資料的大小選擇用幾個位元組來儲存

5）text的65535位元組全部用來儲存資料，varchar則會 佔用1－3個位元組去儲存資料大小

mysql中char,varchar,text