Java學習篇之---Mysql中varchar類型總結

標籤：java

Mysql中varchar類型總結

今天新做一個項目，需要自己進行資料庫設計，發現自己對varchar的用法還不是很熟悉，所以查閱資料總結若下：

1.varchar類型的變化

MySQL 資料庫的varchar類型在4.1以下的版本中的最大長度限制為255，其資料範圍可以是0~255或1~255（根據不同版本資料庫來定）。在 MySQL5.0以上的版本中，varchar資料類型的長度支援到了65535，也就是說可以存放65532個位元組的資料，起始位和結束位佔去了3個字 節，也就是說，在4.1或以下版本中需要使用固定的TEXT或BLOB格式存放的資料可以使用可變長的varchar來存放，這樣就能有效減少資料庫文 件的大小。

MySQL 資料庫的varchar類型在4.1以下的版本中,nvarchar（儲存的是Unicode資料類型的字元）不管是一個字元還是一個漢字,都存為2個位元組 ，一般用作中文或者其他語言輸入，這樣不容易亂碼 ;varchar: 漢字是2個位元組,其他字元存為1個位元組 ，varchar適合輸入英文和數字。

4.0版本以下，varchar(20)，指的是20位元組，如果存放UTF8漢字時，只能存6個（每個漢字3位元組） ；5.0版本以上，varchar(20)，指的是20字元，無論存放的是數字、字母還是UTF8漢字（每個漢字3位元組），都可以存放20個，最大大小是65532位元組 ；varchar(20)在Mysql4中最大也不過是20個位元組,但是Mysql5根據編碼不同,儲存大小也不同，具體有以下規則：

a) 儲存限制

varchar 欄位是將實際內容單獨儲存在聚簇索引之外，內容開頭用1到2個位元組表示實際長度（長度超過255時需要2個位元組），因此最大長度不能超過65535。

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.CHAR(M), VARCHAR(M)不同之處

CHAR(M)定義的列的長度為固定的，M取值可以為0～255之間，當儲存CHAR值時，在它們的右邊填充空格以達到指定的長度。當檢索到CHAR值時，尾部的空格被刪除掉。在儲存或檢索過程中不進行大小寫轉換。CHAR儲存定長資料很方便，CHAR欄位上的索引效率級高，比如定義char(10)，那麼不論你儲存的資料是否達到了10個位元組，都要佔去10個位元組的空間,不足的自動用空格填充。

VARCHAR(M)定義的列的長度為可變長字串，M取值可以為0~65535之間，(VARCHAR的最大有效長度由最大行大小和使用的字元集確定。整體最大長度是65,532位元組）。VARCHAR值儲存時只儲存需要的字元數，另加一個位元組來記錄長度(如果列聲明的長度超過255，則使用兩個位元組)。VARCHAR值儲存時不進行填充。當值儲存和檢索時尾部的空格仍保留，符合標準SQL。varchar儲存變長資料，但儲存效率沒有CHAR高。如果一個欄位可能的值是不固定長度的，我們只知道它不可能超過10個字元，把它定義為 VARCHAR(10)是最合算的。VARCHAR類型的實際長度是它的值的實際長度+1。為什麼"+1"呢？這一個位元組用於儲存實際使用了多大的長度。從空間上考慮，用varchar合適；從效率上考慮，用char合適，關鍵是根據實際情況找到權衡點。

CHAR和VARCHAR最大的不同就是一個是固定長度，一個是可變長度。由於是可變長度，因此實際儲存的時候是實際字串再加上一個記錄字串長度的位元組(如果超過255則需要兩個位元組)。如果分配給CHAR或VARCHAR列的值超過列的最大長度，則對值進行裁剪以使其適合。如果被裁掉的字元不是空格，則會產生一條警告。如果裁剪非空格字元，則會造成錯誤(而不是警告)並通過使用嚴格SQL模式禁用值的插入。

3. VARCHAR和TEXT、BlOB類型的區別

VARCHAR，BLOB和TEXT類型是變長類型，對於其儲存需求取決於列值的實際長度(在前面的表格中用L表示)，而不是取決於類型的最大可能尺寸。例如，一個VARCHAR(10)列能儲存最大長度為10個字元的一個字串，實際的儲存需要是字串的長度 ，加上1個位元組以記錄字串的長度。對於字串‘abcd‘，L是4而儲存要求是5個位元組。

BLOB和TEXT類型需要1，2，3或4個位元組來記錄列值的長度，這取決於類型的最大可能長度。VARCHAR需要定義大小，有65535位元組的最大限制；TEXT則不需要。如果你把一個超過列類型最大長度的值賦給一個BLOB或TEXT列，值被截斷以適合它。

一個BLOB是一個能儲存可變數量的資料的二進位的大對象。4個BLOB類型TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB僅僅在他們能儲存值的最大長度方面有所不同。

BLOB 可以儲存圖片,TEXT不行，TEXT只能儲存純文字檔案。4個TEXT類型TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT對應於4個BLOB類型，並且有同樣的最大長度和儲存需求。在BLOB和TEXT類型之間的唯一差別是對BLOB值的排序和比較以大小寫敏感方式執行，而對TEXT值是大小寫不敏感的。換句話說，一個TEXT是一個大小寫不敏感的BLOB。

4.char，varchar，text區別

長度的區別，char範圍是0～255，varchar最長是64k，但是注意這裡的64k是整個row的長度，要考慮到其它的column，還有如果存在not null的時候也會佔用一位，對不同的字元集，有效長度還不一樣，比如utf8的，最多21845，還要除去別的column，但是varchar在一般情況下儲存都夠用了。如果遇到了大文本，考慮使用text，最大能到4G。

效率來說基本是char>varchar>text，但是如果使用的是Innodb引擎的話，推薦使用varchar代替char

char和varchar可以有預設值，text不能指定預設值

資料庫選擇合適的資料類型儲存還是很有必要的，對效能有一定影響。這裡在零碎記錄兩筆，對於int類型的，如果不需要存取負值，最好加上unsigned；對於經常出現在where語句中的欄位，考慮加索引，整形的尤其適合加索引。

5.varchar(10)和varchar(100)的區別

一般初學會認為，二者佔用的空間是一樣的。比如說我儲存5個char，二者都是實際佔用了5個char了【勘誤：varchar在實際儲存的時候會多一個byte用來存放長度】。
但是深入一下，設計資料庫的時候，二者一樣嗎？
答案是否定的【至少varchar類型需要在資料之前利用一個或者兩個位元組來儲存資料的長度】【二者在記憶體中的操作方式也是不同的，下面的例子中有體現】。
如現在使用者需要儲存一個地址資訊。根據評估，只要使用100個字元就可以了。但是有些資料庫管理員會認為，反正Varchar資料類型是根據實際的需要來分配長度的。還不如給其大一點的呢。為此他們可能會為這個欄位一次性分配200個字元的儲存空間。這VARCHAR(100)與VARCHAR(200)真的相同嗎?結果是否定的。雖然他們用來儲存90個字元的資料，其儲存空間相同。但是對於記憶體的消耗是不同的。對於VARCHAR資料類型來說，硬碟上的儲存空間雖然都是根據實際字元長度來分配儲存空間的，但是對於記憶體來說，則不是。其時使用固定大小的記憶體塊來儲存值。簡單的說，就是使用字元類型中定義的長度，即200個字元空間。顯然，這對於排序或者暫存資料表(這些內容都需要通過記憶體來實現)作業會產生比較大的不利影響。解釋可以參見這裡。所以如果某些欄位會涉及到檔案排序或者基於磁碟的暫存資料表時，分配VARCHAR資料類型時仍然不能夠太過於慷慨。還是要評估實際需要的長度，然後選擇一個最長的欄位來設定字元長度。如果為了考慮冗餘，可以留10%左右的字元長度。千萬不能認為其為根據實際長度來分配儲存空間，而隨意的分配長度，或者說乾脆使用最大的字元長度。

6.char的利弊

1）、從片段角度進行考慮，使用CHAR字元型時，由於儲存空間都是一次性分配的。為此某個欄位的內容，其都是儲存在一起的。單從這個角度來講，其不存在片段的困擾。而可變長度的字元資料類型，其儲存的長度是可變的。當其更改前後資料長度不一致時，就不可避免的會出現片段的問題。故使用可變長度的字元型資料時，資料庫管理員要時不時的對片段進行整理。如執行資料庫匯出匯入作業，來消除片段。
2）、考慮其長度的是否相近，如果某個欄位其長度雖然比較長，但是其長度總是近似的，如一般在90個到100個字元之間，甚至是相同的長度。此時比較適合採用CHAR字元類型。比較典型的應用就是MD5雜湊值。當利用MD5雜湊值來儲存使用者密碼時，就非常使用採用CHAR字元類型。因為其長度是相同的。另外，像用來儲存使用者的社會安全號碼碼等等，一般也建議使用CHAR類型的資料。
另外請大家考慮一個問題，CHAR(1)與VARCHAR(1)兩這個定義，會有什麼區別呢?雖然這兩個都只能夠用來儲存單個的字元，但是VARCHAR要比CHAR多佔用一個儲存位置。這主要是因為使用VARCHAR資料類型時，會多用1個位元組用來儲存長度資訊。這個管理上的開銷char字元類型是沒有的。

3）、二者在磁碟上儲存占的空間是一樣的。區別有二。第一、一個變長一個固定長度。第二、在記憶體中的操作方式，varchar也是按照最長的方式在記憶體中進行操作的。比如說要進行排序的時候，varcahr(100)是按照100這個長度來進行的。

4）、mysql的vachar欄位的類型雖然最大長度是65535，但是並不是能存這麼多資料，最大可以到65533（不允許非空欄位的時候），當允許非空欄位的時候只能到65532【在允許空的時候，varchar(65532) will be 2 bytes (length) + up to 65532 chars (latin1) + 1 null byte】

5）、請注意所有MySQL校對規則屬於PADSPACE類。這說明在MySQL中的所有CHAR和VARCHAR值比較時不需要考慮任何尾部空格。請注意所有MySQL版本均如此，並且它不受SQL伺服器模式的影響。如果在一個char或者varchar列上建立唯一索引之後，那麼‘a‘和‘a ‘，會引起duplicate-key error。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

Java學習篇之---Mysql中varchar類型總結