MySQL資料類型 詳細講解

MySQL資料類型 詳細講解
MySQL資料類型

MySQL支援多種資料類型，主要有數實值型別、日期/時間類型和字串類型。

1. 數值資料類型：包括整數類型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮點小數資料類型FLOAT和DOUBLE、定點小樹類型DECIMAL。
2. 日期/時間類型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。

3. 字串類型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。

   1.1 整數類型

   數值型資料類型主要用來儲存數字，MYSQL提供了多種數值資料類型，不同的資料庫有不同的取值範圍，可以儲存的值範圍越大，其所儲存的控制項也會越大。MySQL主要提供整數類型有：TINYTINT、SMALLINT、MEDIUMINT、INT、BIGINT。整數類型屬性的欄位可以添加AUTO_INCREMENT自增約束條件。下表列出了MySQL中的數實值型別。

類型名稱	說明	儲存需求
TINYINT	很小的整數	1個位元組
SMALLINT	小的整數	2個位元組
MEDIUMINT	中等大小的整數	3個位元組
INT	普通大小的整數	4個位元組
BIGINT	大整數	8個位元組

從表可知，不同類型整數儲存所需的位元組數是不同的，佔用位元組數最小的是TINYINT類型，佔用位元組最大的是BIGINT類型，相應的佔用位元組越多的類型所能表示的數值範圍越大。根據佔用位元組數可以求出每一種資料的取值範圍，例如TINYINT需要1個位元組(8bits)來儲存，那麼TINYINT無符號數的最大值為2^8-1，即255；TINYINT有符號數的最大值為2^7-1，即127。其它類型如下表所示：

資料類型	有符號	無符號
TINYINT	-128~127	0~255
SMALLINT	-32768~32767	0~65535
MEDIUMINT	-8388608~8388607	0~16777215
INT	-2147483648~2147483647	0~4294967295
BIGINT	-9223372036854775808~9223372036854775807	0~18446744073709551615

1.2 浮點數類型和定點數類型

MySQL中使用浮點數和定點數來表示小數。浮點類型有兩種：單精確度（FLOAT）和雙精確度（DOUBLE）。定點類型只有一種：DECIMAL。浮點類型和定點類型都可以用（M，N）來表示，其中M稱為精度，表示總共的位元；N稱為標度，是表示小數的位元。下表列出了小數類型儲存的需求。

類型名稱	說明	儲存需求
FLOAT	單精確度浮點數	4個位元組
DOUBLE	雙精確度浮點數	8個位元組
DECIMAL(M,D),DEC	壓縮的”嚴格”定點數	M+2個位元組

DECIMAL類型不同於FLOAT和DOUBLE，DECIMAL實際是以串存放的，DECIMAL可能的最大取值範圍與DOUBLE一樣，但是其有效取值範圍由M和D的值決定。如果改變M而固定D，則取值範圍將隨M的變大而變大。

1.3 日期/時間類型

MySQL中有多種表示日期的資料類型，主要有：DATETIME、DATE、TIMESTAMP、TIME和YEAR。下表列出了MySQL日期/時間類型：

類型名稱	日期格式	日期範圍	儲存需求
YEAR	YYYY	1901~2155	1位元組
TIME	HH:MM:SS	-838:59:59~838:59:59	3位元組
DATE	YYYY-MM-DD	1000-01-01~9999-12-3	3位元組
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8位元組
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 UTC ~ 2038-01-19 03:14:07 UTC	4位元組

1.4 字串類型

字串類型用來儲存字串資料，除了可以儲存字串之外，還可以儲存其它資料，比片和聲音位元據。字串可以進行區分或者不區分大小寫串比較，另外，還可以進行模式比對尋找。MySQL中字串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。下表列出了字串資料型別。

類型名稱	說明	儲存需求
CHAR(M)	固定長度非二進位字串	M位元組,1<=M<=255
VARCHAR(M)	變長非二進位字串	L+1位元組,在此L<=M和1<=M<=255
TINYTEXT	非常小的非二進位字串	L+1位元組，在此L<2^8
TEXT	小的非二進位字串	L+2位元組，在此L<2^16
MEDIUMTEXT	中等大小的非二進位字串	L+3位元組,在此L<2^32
LONGTEXT	大的非二進位字串	L+4位元組,在此L<2^24
ENUM	枚舉類型,只能有一個枚舉字串值	1或2個位元組,取決於枚舉值數目(最大值65535)
SET	一個設定,字串對象可以有0個或多個SET成員	1,2,3,4或8個位元組，取決於集合成員的數量（最多64個成員）

1.5 二進位類型

MySQL支援兩類字元型資料：文本字串和二進位字串。MySQL中儲存二進位字串資料型別有：BIT、BINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。下表列出了位元據類型：

類型名稱	說明	儲存需求
BIT(M)	位欄位類型	大約(M+7)/8個位元組
BINARY(M)	固定長度二進位字串	M個位元組
VARBINARY(M)	可變長度二進位字串	M+1個位元組
TINYBLOB(M)	非常小的BLOB	L+1個位元組,在此L<2^8
BLOB(M)	小BLOB	L+2位元組,在此L<2^16
MEDIUMBLOB(M)	中等大小的BLOB	L+3位元組,在此L<2^24
LONGBLOB(M)	非常大的BLOB	L+4位元組,在此L<2^32

2. 如何選擇資料類型

MySQL提供了大量的資料類型，為了最佳化儲存，提高資料庫效能，在任何情況下均應該使用精確的類型。即在所有可以表示該列值的類型中，該類型使用的儲存最少。

2.1 整數和浮點數

    如果不需要小數部分，則使用整數來儲存資料；如果需要小數部分，則使用浮點數來表示。對於浮點數據列，存入的數值會對該列定義的小數位進行四捨五入。例如，如果列的值範圍為1-99999，若使用整數，則MEDIUMINT UNSIGNED是最好的類型；若需儲存小數，則使用FLOAT類型。
    浮點類型包括FLOAT和DOUBLE類型。DOUBLE類型精度比FLOAT類型高，因此，如要求儲存精度較高時，應選擇DOUBLE。

2.2 浮點數和定點數

浮點數FLOAT、DOUBLE相對於定點數DECIMAL的優勢是：在長度一定的情況下，浮點數能表示更大的資料範圍。但是由於浮點數容易產生誤差，因此對精確度要求比較高時，建議使用DECIMAL來儲存。DECIMAL在MySQL中是以字串儲存的，用於定義貨幣等對精確度要求較高的資料。DECIMAL在MySQL中是以字串儲存的。在資料移轉中，float(M,D)是非標準SQL定義，資料庫遷移可能會出現問題，最好不要這樣使用。另外兩個浮點數進行減法和比較運算時也容易出現問題，因此在進行計算的時候，一定要小心。如果進行數值比較，建議使用DECIMAL類型。

2.3 日期/時間類型

    MySQL對於不同種類的日期和時間有多種資料類型，比如YEAR和TIME。如果只需記錄年份，則使用YEAR即可，如果只記錄時間，則使用TIME類型。
    如果同時需要記錄日期和時間則可以使用TIMESTAMP或者DATETIME類型。由於TIMESTAMP這個列取值時範圍小於DATETIME的取值範圍，因此儲存範圍較大的日期最好使用DATETIME。
    TIMESTAMP也有一個DATETIME不具備的屬性。預設情況下，當插入一條記錄但並沒有指定TIMESTAMP這個��值時，MySQL會把TIMESTAMP列設為當前的時間。因此需要插入記錄同時插入目前時間時，使用TIMESTAMP是方便的，另外TIMESTAMP在空間上比DATETIME更有效。

2.4 CHAR與VARCHAR之間的特點與選擇

CHAR與VARCHAR之間的區別：
CHAR是固定長度字元，VARCHAR是可變長度字元；CHAR會自動刪除插入資料的尾部空格，VARCHAR不刪除尾部空格。
CHAR是固定長度，所以它的處理速度比VARCHAR速度要快，但是它的缺點就是浪費儲存空間。所以對儲存不大，但在速度上有要求的可以使用CHAR類型，反之可以使用VARCHAR類型來實現。

儲存引擎對CHAR和VARCHAR的影響：

    對於MyISAM儲存引擎，最好使用固定長度的資料列代替可變長度的資料列。這樣可以使整個表靜態化，從而使資料檢索更快，用儲存空間換查詢時間。
    對於InnoDB儲存引擎：使用可變長的資料列，因為InnoDB資料表的儲存格式不分固定長度和可變長度，因此使用CHAR不一定比使用VARCHAR更好，但由於VARCHAR是按照實際存的長度儲存，比較節省空間的，所以對磁碟I/O和資料存放區總量比較好。

2.5 ENUM和SET

    ENUM只能取單值，它的資料列表示一個枚舉集合。它的合法值列表最多有65535個成員。因此，在需要從多個值中選取一個時，可以使用ENUM。比如：性別欄位適合定義成ENUM類型，每次只能從’男’ 或 ‘女’中取一個值。
    SET可取多值。它的合法取值列表最多允許有64個成員。Null 字元串也是一個合法的SET值。在需要取多個值的時候，適合使用SET類型，比如：要儲存一個興趣愛好，最好使用SET類型。
    ENUM和SET的值是以字串形式出現的，但在內部，MySQL以數值的形式儲存它們。

2.6 BLOB和TEXT

BLOB是二進位字串，TEXT是非二進位字串，二者均可存放大容量資訊。BLOB主要儲存圖片、音頻資訊等，而TEXT只能儲存純文字檔案，應分清二者儲存的關係。