MySQL之選擇欄位資料類型

標籤：

MySQL支援的資料類型很多，選擇正確的資料類型對於 獲得高效能至關重要。在選擇時有個簡單的原則有助於做出更好的選擇。

 

簡單的原則：

A、通常最小的是最好的

     因為這樣可以用更少的磁碟、內容、CPU緩衝，大大減少IO開銷。

B、簡單就好

     簡單的資料類型操作通常需要更少的CPU周期。例如，整型比字元操作代價更小，因為字元集和校對規則（定序）使它比整型更複雜。比如應該使用MySQL內建的類型而不是使用字元型來儲存日期和時間。

C、盡量避免使用NULL

     NULL是列預設的屬性，通常我們要指定為NOT NULL。有NULL的列值會使得索引、索引統計和值比較更加複雜。可為NULL的列會使用更多的儲存空間，在MySQL中也需要對它進行特殊處理，當可為NULL列做索引時，每個索引需要一個額外的位元組，在MyISAM更有可能導致固定大小的索引變成可變大小索引，在InnoDB中使用單獨的位(bit)儲存NULL值。

 

1、整數類型

     幾種整數類型：TINYINT(8位),SMALLINT(16位),MEDIUMINT(24位),INT(32位),BIGINT(64位)，它們的範圍是-2的(n-1)次方到2的(n-1)次方-1。如果選擇了UNSIGNED表示非負，它可以使整數最大值提高一倍，有符號和無符號使用相同的儲存空間，具有相同的效能。

     為整型指定寬度，如INT(11)，對於儲存來說INT(1)和INT(20)是相同的，它不會限制值的合法範圍，只是規定了MySQL與用戶端的互動應該顯示多少位而已，比如你向INT(1)中插入了123456值，資料庫中其實已經存入了123456，只是對於用戶端查出來是1而已。

 

2、實數類型

     實數是帶小數部分的數字，然而它不只是為了儲存小數部分，也可以使用DECIMAL儲存比BIGINT還大的整數，FLOAT和DOUBLE類型支援使用標準的浮點運算進行四捨五入。DECIMAL用於儲存精確的小數。FLOAT佔用4個位元組，DOBULE佔用8個位元組，它比DECIMAL佔用空間更小、計算浮更更快，因此在非儲存財務資料時，可以考慮其它的類型，如放大N倍後存為BIGINT。

 

3、字串類型

     MySQL支援多種字串類型，每個字串列可以定義自己的字元集和定序，或者說校對規則，這些東西很大程度上影響效能。

     A、VARCHAR和CHAR類型

          VARCHAR類型用於儲存變長字串，它會刪除末尾的所有空格，它比定長字串更省空間，因為它僅使用必要的空間（越短的字串佔用越少），VARCHAR會用1或2個額外位元組記錄字串長度，VARCHAR節省了空間，因此對效能是有協助的，但是因為行是變長的，所以每次UPDATE時會重新計算字串長度，會比定長多做額外工作。因此不是說VARCHAR就一定能提升效能，同樣要分使用情境，如果字串的最大長度比平均長度大很多，且列的更新很少時則適合用VARCHAR。注意InnoDB會把過長的VARCHAR儲存為BLOB，通常人們喜歡將IP地址存儲為VARCHAR型，其實IP地址是32位二進位表示的,你可以轉為十進位表示，因而不是字串，用小數是將它分為四段便於閱讀。

          CHAR是定長的，每個列無論字串長度大小都會分配固定儲存空間，CHAR適合儲存字元很短，或者所有值接近一個長度時，如儲存MD5碼、SESSION_ID等，對於經常變更的資料CHAR也比VARCHAR好，再一個是對於非常短的列，CHAR比VARCHAR更節省空間的，例如使用CHAR(1)和VARCHAR(1)來儲存Y或N時，CHAR需要一個字元，而VARCHAR需要兩個，因為還有一個長度位元組

    B、BLOB和TEXT類型

         它們都是為儲存大資料而設計的，BLOB是採用二進位/TEXT採用字元儲存。MySQL不能將BLOB和TEXT列全部進行索引（只能做前面某長度的索引，因此在查詢SQL時不能用LIKE前模糊比對，那樣就走不到索引了），也不能使用這些索引進行排序。

   技巧：使用枚舉代替字串類型，枚舉可以把一些重複的字串儲存成一個預定義的集合，MySQL在儲存枚舉時非常緊湊，MySQL在列中儲存值為枚舉中的位置整數。枚舉最不好的是字串是固定的，添加或刪除必須使用ALTER TABLE。因此對於未來會改變的字串，使用枚舉不是一個好主意，除非能接受在枚舉末尾添加元素，由於枚舉有一個映射轉換過程，所以枚舉雖然能減少儲存空間，但是也會增加一些額外開銷。

 

4、時間和時期類型

          DATETIME：這個類型能儲存1001到9999年，精度為秒，與時區不轉換，使用8個位元組儲存，儲存格式封裝為YYYYMMDDHHMMSS的整數，因此它是一種可排序的類型，顯示時以ANSI標準定義的日期和時間表示方法顯示。

          TIMESTAMP：它儲存了從1970年1月1日午夜以來的秒數，也就是常說的時間戳記，它使用4個位元組來儲存，依賴於時區。除了特殊情況外，通常我們應該盡量使用TIMESTAMP，因為它比DATETIME更省空間，現實中有些人喜歡使用int來儲存時間戳記（包括剛出來工作時的我），這並不會帶來任何收益，用整數儲存時間戳記不方便，因為取出來之後還需要程式轉換下，所以不推薦這樣做。

 

5、位元據類型

     MySQL有少數幾種儲存類型使用緊湊的位儲存資料。不管底層儲存格式如何處理，從技術上說都是字元型。

     BIT：BIT(1)表示定義一個包含一位的長度，BIT(2)表示2個位，以此類推，BIT列最大長度是64位，MySQL把BIT當字串處理，而不是數字，當檢索BIT(1)的值時，結果是一個包含二進位0或1的字串，而不是ASCII碼的0或1，例如00111001，它的二進位等於57，檢索它時得到是一個字元碼為57的字元，也就是ASCII碼57對應字元為9。一般在應用中盡量避免使用BIT，對於大部分應用來說最好是不用。

MySQL之選擇欄位資料類型