MySQL中varchar最大長度是多少？

一. varchar存儲規則：

4.0版本以下，varchar(20)，指的是20位元組，如果存放UTF8漢字時，隻能存6個（每個漢字3位元組） 

5.0版本以上，varchar(20)，指的是20字元，無論存放的是數字、字母還是UTF8漢字（每個漢字3位元組），都可以存放20個，最大大小是65532位元組 

二. varchar和char 的差別：

char是一種固定長度的類型，varchar則是一種可變長度的類型，它們的差別是： char(M)類型的資料列裡，每個值都占用M個位元組，如果某個長度小于M，MySQL就會在它的右邊用空格字元補足．（在檢索操作中那些填補出來的空格字元将被去掉）在varchar(M)類型的資料列裡，每個值隻占用剛好夠用的位元組再加上一個用來記錄其長度的位元組（即總長度為L+1位元組）． 

在MySQL中用來判斷是否需要進行對據列類型轉換的規則

1、在一個資料表裡，如果每一個資料列的長度都是固定的，那麼每一個資料行的長度也将是固定的．

2、隻要資料表裡有一個資料列的長度的可變的，那麼各資料行的長度都是可變的．

3、如果某個資料表裡的資料行的長度是可變的，那麼，為了節約存儲空間，MySQL會把這個資料表裡的固定長度類型的資料列轉換為相應的可變長度類型．例外：長度小于4個字元的char資料列不會被轉換為varchar類型

 ps :被問到一個問題：MySQL中varchar最大長度是多少？這不是一個固定的數字。本文簡要說明一下限制規則。

1、限制規則

字段的限制在字段定義的時候有以下規則：

a) 存儲限制

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 byte。

mysql> create table t1 ( name varchar(65532) default null)charset=latin1;
Query OK, 0 rows affected (0.09 sec)

mysql>       

mysql> create table t2 ( name varchar(65533) default null)charset=latin1;  
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
mysql>       

可以看見當設定長度為65533時，已經超過行最大長度，我們可以計算一下，行最大長度是65535位元組。上面t2表name字段使用varchar（65533），字元集是latin1，占用1個位元組。還有預設為空，那麼還有null辨別位，( 1 + 7 ) / 8 =1,是以null辨別位占用1個位元組。現在我們來看看，65533 + 1 + 2=65536位元組，已經大于行最大長度。這裡2位元組怎麼來的？？？因為varchar類型存儲變長字段的字元類型，與char類型不同的是，其存儲時需要在字首長度清單加上實際存儲的字元，當存儲的字元串長度小于255位元組時，其需要1位元組的空間，當大于255位元組時，需要2位元組的空間。

如果資料表隻有一個varchar字段且該字段NOT NULL，那麼該varchar字段的最大長度為65533個位元組，即65535-2=65533byte

mysql> create table t2 ( name varchar(65533) not null) charset=latin1;   
Query OK, 0 rows affected (0.03 sec)

mysql>       

mysql> create table t3 ( name varchar(65534) not null) charset=latin1;  
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
mysql>       

b) 編碼長度限制

字元類型若為gbk，每個字元最多占2個位元組，最大長度不能超過32766;

字元類型若為utf8，每個字元最多占3個位元組，最大長度不能超過21845。

若定義的時候超過上述限制，則varchar字段會被強行轉為text類型，并産生warning。

c) 行長度限制

導緻實際應用中varchar長度限制的是一個行定義的長度。 MySQL要求一個行的定義長度不能超過65535。若定義的表長度超過這個值，則提示

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs。

2、計算例子

舉兩個例說明一下實際長度的計算。

a)  若一個表隻有一個varchar類型，如定義為

create table t4(c varchar(N)) charset=gbk;

則此處N的最大值為(65535-1-2)/2= 32766。

減1的原因是實際行存儲從第二個位元組開始;

減2的原因是varchar頭部的2個位元組表示長度;

除2的原因是字元編碼是gbk。

b) 若一個表定義為

create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;

則此處N的最大值為 (65535-1-2-4-30*3)/3=21812

減1和減2與上例相同;

減4的原因是int類型的c占4個位元組;

減30*3的原因是char(30)占用90個位元組，編碼是utf8。

如果被varchar超過上述的b規則，被強轉成text類型，則每個字段占用定義長度為11位元組，當然這已經不是varchar了。

則此處N的最大值為 (65535-1-2-4-30*3)/3=21812,例子如下:

mysql> create table t4(c int, c2 char(30), c3 varchar(21812)) charset=utf8; 
Query OK, 0 rows affected (0.05 sec)

mysql>       

mysql> create table t5(c int, c2 char(30), c3 varchar(21813)) charset=utf8;  
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
mysql>       

最後讓我們來看一個例子

CREATE TABLE t6 (
id int,
a VARCHAR(100) DEFAULT NULL,
b VARCHAR(100) DEFAULT NULL,
c VARCHAR(100) DEFAULT NULL,
d VARCHAR(100) DEFAULT NULL,
e VARCHAR(100) DEFAULT NULL,
f VARCHAR(100) DEFAULT NULL,
g VARCHAR(100) DEFAULT NULL,
h VARCHAR(100) DEFAULT NULL,
i VARCHAR(N) DEFAULT NULL
) CHARSET=utf8;                                                                                                                                                                                                                         

那麼上面這條語句中的varchar(N)的最大值是多少呢？

讓我們來計算一下

每個NULL字段用1bit辨別，10個字段都是default null，那麼需要用(10+7)/8bit = 2 bytes存儲NULL辨別位。int占用4個 byte。

(65535 - 1 - 2*8  -4 - 100*3*8 -2)/3=21037

mysql> CREATE TABLE t6 ( id int, a VARCHAR(100) DEFAULT NULL, b VARCHAR(100) DEFAULT NULL, c VARCHAR(100) DEFAULT NULL, d VARCHAR(100) DEFAULT NULL, e VARCHAR(100) DEFAULT NULL, f VARCHAR(100) DEFAULT NULL, g VARCHAR(100) DEFAULT NULL, h VARCHAR(100) DEFAULT NULL, i VARCHAR(21037) DEFAULT NULL ) CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>       

mysql> CREATE TABLE t7 ( id int, a VARCHAR(100) DEFAULT NULL, b VARCHAR(100) DEFAULT NULL, c VARCHAR(100) DEFAULT NULL, d VARCHAR(100) DEFAULT NULL, e VARCHAR(100) DEFAULT NULL, f VARCHAR(100) DEFAULT NULL, g VARCHAR(100) DEFAULT NULL, h VARCHAR(100) DEFAULT NULL, i VARCHAR(21038) DEFAULT NULL ) CHARSET=utf8;  
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
mysql>       

 可以看見多一個字元都報錯了。

varchar到底能存多少個字元？這與使用的字元集相關，latin1、gbk、utf8編碼存放一個字元分别需要占1、2、3個位元組。

3、varchar實體存儲

在實體存儲上，varchar使用1到2個額外的位元組表示實際存儲的字元串長度（bytes）。如果列的最大長度小于256個位元組，用一個位元組表示（辨別）。如果最大長度大于等于256，使用兩個位元組。

當選擇的字元集為latin1，一個字元占用一個byte

varchar(255)存儲一個字元，一共使用2個bytes實體空間存儲資料實際資料長度和資料值。

varchar(256)存儲一個字元，使用2 bytes表示實際資料長度，一共需要3 bytes實體存儲空間。

varchar對于不同的RDBMS引擎，有不通的實體存儲方式，雖然有統一的邏輯意義。對于mysql的不同存儲引擎，其實作方法與資料的實體存放方式也不同。

4、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。

5、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字段的實體存儲方式也将會發生變化。

參考資料：

http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html

<<MySQL技術内幕--InnoDB引擎第二版>>

作者：Atlas

出處：Atlas的部落格 http://www.cnblogs.com/gomysql

您的支援是對部落客最大的鼓勵，感謝您的認真閱讀。本文版權歸作者所有，歡迎轉載，但請保留該聲明。如果您需要技術支援，本人亦提供有償服務。