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