Mysql數據庫Char和Varchar字段類型長度的選擇比較

 　　網上有很多關於char和varchar的相關比較，但是都歷史悠久，這裡轉載一篇信息比較新的，個人認為對我的設計字段決定幫助很大。

　　現代數據庫一般都支持CHAR與VARCHAR字符型字段類型，CHAR是用來保存定長字符，存儲空間的大小為字段定義的長度，與實際字符長度無關，當輸入的字符小於定義長度時最後會補上空格。VARCHAR是用來保留變長字符，在數據庫中存儲空間的大小是實際的字符長度，不會像CHAR一樣補上空格，這樣占用的空間更少。

　　從以上特點來看，VARCHAR比CHAR有明顯的優勢，因此大部份數據庫設計時都應該采用VARCHAR類型。那為什麼還需要CHAR類型呢，個人認為有以下幾個原因：

　　1、為了跟以前版本的數據庫進行一個兼容，因為很久以前數據庫只支持CHAR類型，有些應用的業務邏輯也只是針對CHAR類型設計的，所以數據庫軟件也就一直保留CHAR類型。

　　2、CHAR類型是定長的，一些數據庫可以在每條記錄中不存儲字段長度信息，這樣可以節省部份空間，也可以方便做一些內存對齊提高性能，但個人認為這帶來的性能提升非常微小，至少ORACLE數據庫是沒有意義的。

　　3、還有說法是有些數據經常修改，長度可能變化，會引起碎片，采用CHAR就不會產生碎片，這個說法比較多，但我認為既然長度會變化，那用VARCHAR更能節省內存與存儲空間來提升性能，只要數據塊預留的空間沒有問題，采用VARCHAR性能更好。

　　對於ORACLE數據庫，我找不到充足的理由來使用CHAR類型，而且CHAR還會帶來討厭的空格，有些文章說MYSQL的MYISAM存儲引擎在和長度固定的情況下CHAR比VARCHAR好，這個沒有測試過，不太了解。

　　由於VARCHAR是變長存儲，那麼很多人會有疑問，比如STATUS字段定義VARCHAR(10)與VARCHAR(1000)有什麼區別，反正是變長的，存儲空間都一樣，省得以後要加長又要改變字段定義。 下面說一下我的理解：

　　1、字段長度是數據庫一種約束，可以保證進入數據庫的數據符合長度要求，定義合理的字段長度可以減少一部份非法數據進入，比如：我們業務中STATUS只有‘NEW’,‘DELETE’,‘CLOSE’3種狀態，使用VARCHAR(5)保存，這樣可以有效的減少非法數據進入，定義合理的長度也可以讓人容易理解字段的用途，試想一下，如果你所有的字符字段長度都是VARCHAR(4000)會是什麼樣的情況。

　　2、VARCHAR的字段長度雖然對數據存儲沒有太大影響，但對特定的數據庫還是有一些細微差別，比如MYSQL中定義的長度如果小於255，字段長度用1個字節表示，如果超過255，字段的長度將固定用2個字節表示。如果你的業務數據最大長度只有10，但定義長度為256則每條記錄會多浪費了一個字節來存儲長度。ORACLE沒有這樣的問題，它會根據每條記錄字段的實際長度動態選擇長度標識。

　　3、字段定義的長度對索引也有較大影響。ORACLE對索引長度還是有一定限制，8i官方文檔說明單條記錄索引信息的長度不能超過數據塊大小的40%，9i中是75%，實際上也差不多，具體可以見jametong的http://www.dbthink.com/?p=20這篇文檔，裡面有詳細的測試結果。如果你的數據塊大小是8K，那麼索引字段的定義長度不能超過6398，比如，你要給表上2個VARCHAR(4000)字段建組合索引，創建時會直接報錯。另外索引組織表及在線重建索引(因為中間會臨時創建一個索引組織表)允許的索引信息長度更小，只能是數據塊大小的40%，實際中8K的數據塊大小，要使用在線重建索引，那定義的長度不能超過3215。從以上可以看出，數據塊大小為8K時，設計字段時如果要定義為VARCHAR(4000)，那這個字段就不能考慮建立索引，因為即使能建上，也不能做在線重定義操作，DBA要進行索引維護時只能停止應用，這將對系統的可用性產生較大影響。關於ORACLE索引長度限制測試的腳本如下：

　　[sql] view plaincopy

　　SQL> create table test1

　　2 (

　　3 c1 varchar2(4000),

　　4 c2 varchar2(4000),

　　5 c3 varchar2(4000)

　　6 )

　　7 ;

　　Table created

　　SQL> create index test1_ind1 on TEST1 (c1);

　　Index created

　　SQL> alter index test1_ind1 rebuild online;

　　alter index test1_ind1 rebuild online

　　ORA-00604: error occurred at recursive SQL level 1

　　ORA-01450: maximum key length (3215) exceeded

　　SQL> create index test1_ind2 on TEST1 (c2, c3);

　　create index test1_ind2 on TEST1 (c2, c3)

　　ORA-01450: maximum key length (6398) exceeded

　　SQL>

　　關於ORACLE的索引長度還有一些特別的規則，比如自定義函數返回的字符定義長度固定是4000，所以要用自定義函數做函數索引需要特別注意一下，這可能會影響在線重建索引不能操作。

　　內置函數的索引長度根據函數決定，比如UPPER這種不改變長度的就是索引字段定義的長度，SUBSTR這種會改變長度要根據函數截取長度決定。

　　NUMBER類型字段的長度固定是22。

　　DATA類型字段的長度固定是7。

　　索引默認是升序，如果要降序建的索引長度是字段定義長度*1.5+1。

　　MYSQL對索引長度限制比較復雜，每種版本及存儲引擎都不一樣，如下是MYSQL5.1.58測試的結果：

　　INNODB的最大總長度是3072字節，單個字符字段是767字節，如果字段長度大於767則自動截取前767個字符。

　　MYISAM最大總長度是1000字節，單個字符字段是1000字節。

　　MEMORY的最大總長度是3072字節，單個字符字段是3072字節。

　　4、變長字段定義的長度雖然不會影響服務器數據空間大小，但是對於客戶端的內存有影響，因為客戶端在用SQL從數據庫讀取數據時，首先會取到字段定義的長度，然後分配足夠的內存，也就是說如果你定義的字段長度是1K，實際長度是10字節，要取1K記錄，那客戶端會分配1MB的內存， 但只保存了10K有效數據。這將會比較嚴重的浪費客戶端內存。特別是一些高並發或者是取大量數據的場景，容易產生內存溢出。

　　5、關於字段長度對齊的問題，有些設計人員喜歡定義字段的長度為4或者8的倍數，如16,32,64,128之類的，理由是可以做到內存對齊，對於這個問題我沒有深入分析過，個人認為必要性不大，也沒看到過這種優化能提升性能的案例。如果一個VARCHAR(1)定義為VARCHAR(4)反而浪費內存與存儲，實際上我看到在ORACLE jdbc驅動中會將所有的字符類型數據保存在一個大的char[]中，把所有NUMBER與DATE類型放在另一個char[]中，這樣整合後都不清楚如何內存對齊了。

　　綜上所述：VARCHAR類型字段長度不能隨便定義，並不是越大越好，還是需要根據實際業務數據定義一個合適的長度。我個人對於一些可以完全預估的長度就按實際長度定義，比如年月、狀態、標記之類的信息。對於不確定長度的業務數據如NAME、STYLE之類的信息定義一個合理值，如VARCHAR(20)，VARCHAR(30) 之類 。對於描述性或備注性的信息，這些字段也確定不會有索引，長度也不可預知，所以留更大的長度，避免以後經常進行長度調整，如VARCHAR(1024)，或者直接VARCHAR2(4000) 。