mysql中VARCHAR長度詳細介紹

 

在MySQL5.0以上的版本中，varchar資料類型的長度支援到了65535，也就是說可以存放65532個位元組的資料，起始位和結束位佔去了3個位元組，也就是說，在4.1或以下版本中需要使用固定的TEXT或BLOB格式存放的資料可以使用可變長的varchar來存放，這樣就能有效減少資料庫檔案的大小。

一.VARCHAR儲存和行長度限制

1.VARCHAR(N)中，N指的是字元的長度,VARCHAR類型最大支援65535,指的是65535個位元組,但並不支援65535長度的varchar，65535中應該包含了所有欄位的長度、變長欄位長度標示位、NULL標示位的累計。其中內容開頭用1到2個位元組表示實際長度（長度超過255時需要2個位元組）。所以還有別的開銷，實際能存放的長度為65532.

(MySQL資料庫的varchar類型在5.0.3以下的版本中的最大長度限制為255，其資料範圍可以是0~255)

2.因為null標示位佔用了一個位元組，所以可以去掉not null限制。

 

3.MySQL要求一個行的定義長度不能超過65535 是指所有列的長度總和不能超過65535.如果列的長度總和超過這個長度，依然無法建立.

 

二．VARCHAR長度的編碼限制

1.概述

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

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

若定義的時候超過上述限制，則varchar欄位會被強行轉為text類型，併產生warning。

2.舉例

(1)若一個表只有一個varchar類型，如定義為

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

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

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

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

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

(2) 若一個表定義為

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”了。

 

三．關於SQL模式

在MySQL中，SQL模式常用來解決下面幾類問題：

1.通過設定SQL Mode，可以完成不同嚴格程度的資料校正，有效地保障資料準確性。

2.通過設定SQL Mode為ANSI模式，來保證大多數SQL符合標準的SQL文法，這樣應用在不同資料庫之間進行遷移時，則不需要對業務SQL進行較大的修改。

3.在不同資料庫之間進行資料移轉之前，通過設定SQL Mode可以使MySQL上的資料更方便地遷移到目標資料庫中。

查看當SQL模式

mysql>select @@sql_mode;

STRICT_TRANS_TABLES(strict 模式) 實現資料的嚴格校正，使錯誤資料不能插入表中.

如果將sql_mode設為''，則可能會出現可以建立表，但是會有一條警告資訊，

mysql>set session sql_mode='';

mysql>create table test4(a varchar(25000)) charset=utf8;

mysql>show warnings;

警告資訊提示了，之所以可以建立，是因為MySQL自動將VARCHAR轉換成了Text類型。

mysql>show create table test4;

1、限制規矩

 

欄位的限制在欄位定義的時辰有以下規矩：

 

a）    儲存限制

 

       varchar 欄位是將實際內容零丁儲存在聚簇索引之外，內容開首用1到2個位元組默示實際長度（長度跨越255時須要2個位元組），是以最大長度不克不及跨越65535。

 

b）    編碼長度限制

 

     字元類型若為gbk，每個字元最多佔2個位元組，最大長度不克不及跨越32766;

 

　　字元類型若為utf8，每個字元最多佔3個位元組，最大長度不克不及跨越21845。

 

　　對於英文鬥勁多的論壇 ，應用GBK則每個字元佔用2個位元組，而應用UTF－8英文卻只佔一個位元組。

 

　　若定義的時辰跨越上述限制，則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。

 

如果某一項中設定的是varchar(50)
那麼對英文當然是50
那麼對中文呢
utf-8的中文佔3個位元組
那麼,這個varchar(50)是不是只能存16個漢字了?
mysql varchar(50) 不管中文 還是英文 都是存50個的

MySQL5的文檔，其中對varchar欄位類型這樣描述：varchar(m) 變長字串。M 表示最大列長度。M的範圍是0到65,535。(VARCHAR的最大實際長度由最長的行的大小和使用的字元集確定，最大有效長度是65,532位元組）。
為何會這般變換？真是感覺MySQL的手冊做的太不友好了，因為你要仔細的繼續往下讀才會發現這段描述：MySQL 5.1遵從標準SQL規範，並且不刪除VARCHAR值的尾部空格。VARCHAR儲存時用一個位元組或兩個位元組長的首碼+資料。如果VARCHAR列聲明的長度大於255，長度首碼是兩個位元組。
好了，貌似懂了一點。但具體他說的長度大於255時使用2個位元組長度首碼，小學減法題：65535 - 2 = 65533啊。不知道這些大牛如何計算的，暫且保留疑問吧？
註：我測試了一下使用UTF8編碼，varchar的最大長度為21854位元組。
在mysql 5.0.45版本，資料庫編碼utf8下進行測試：varchar最長定義為21785。也就是說不論字母、數字、漢字，只能放21785個。
推想：varchar位元組最大65535，utf8編碼一個字元3個位元組65535/3=21785。