對MySQL資料類型的認識

對MySql資料類型的認知：

由於Mysql獨有的特性和實作細節對效能的影響是很明顯的，因為做好Mysql資料庫的設計很關鍵。對於資料庫設計，我們不得不提錶欄位的類型選擇，由於Mysql支援的資料類型非常多，因此如何選擇正確的資料類型對於獲得高效能至關重要。不管要儲存的資料是什麼類型，我們都需要根據一些資料庫設計原則來考慮。

選擇資料類型的思考

更小的通常是更好的（一般情況下，應該盡可能使用正確儲存資料的最小資料類型。）

為什麼呢？

（1） 因為更小的資料類型通常更快，因為它們佔用更少的磁碟、記憶體和CPU緩存，並且處理時需要的CPU週期也更短。
（2） 要確保沒有低估需要儲存的值的範圍，更小是相對與資料類型的最大值範圍來講的。
（3） 如果無法確定哪個資料類型是最好的，就選擇你認為不會超過範圍的最小類型。

簡單就好（簡單資料型別的操作通常需要更短的CPU週期。）

為什麼呢？下面有幾個例子說明原因。

（1） 整數比字串操作代價更低，因為字串集和校對規則（排序規則）是的字元比較比整數比較更複雜。
（2） 儲存日期和時間應該使用Mysql內建的類型（date,time,datatime）。
（3） IP位址的儲存應該用整數(int)。

盡量避免 NULL （空值）

為什麼呢？

（1） 很多表格都包含可為NULL的資料列，就算程式並不需要儲存NULL也是如此，這是因為列的預設屬性就是可為NULL。通常情況下最好指定列NOT NULL，除非真的需要儲存NULL。
（2） 如果查詢中包含可為NULL的資料列，對於Mysql來說是很難最佳化的，因為NULL的欄位使得索引，索引統計和值比較都更複雜。可為NULL的欄位會使用更多的儲存空間，在Mysql裡也需要特殊處理。當可為NULL的欄位被索引時，每個索引記錄需要一個額外的位元組，在MyISAM裡甚至還可能導致固定大小的索引變成可變大小的索引。
（3） 通常將可為NULL的列改為NOTNULL帶來性能提升比較小，如果計劃在列上建索引的話，就應該盡量避免設計成可為NULL的列。 （也有一個例外，就是在InnoDB中，會使用單獨的位元（bit）來儲存NULL值，所以對稀疏資料有很好的空間效率。）

總結

在為列選擇資料類型時，第一步需要確定合適的大類型（數字、字串、時間等等），這通常是很簡單的，那麼下一步就是選擇具體的類型了。

很多Mysql的資料類型可以儲存相同類型的數據，只是儲存的長度和範圍不一樣、允許的精度不同，或者需要的物理空間（磁碟和記憶體空間）不同。相同大類型的不同子類型資料有時也有一些特殊的行為和屬性。如：DATATIME 和 TIMESAMP列都可以儲存相同類型的資料（時間和日期）並且精確到秒，然而TIMESTAMP只使用DATATIME一半的儲存空間，並且會根據時區變化，具有特殊的自動更新能力。另外TIMESTAMP允許的時間範圍要小得多，有時候它的特殊能力會成為障礙，這都是我們開發者需要考慮的。

整數類型

有兩個類型的數字：整數（whole number）和實數（real number）。

如果儲存整數，可以使用這幾種整數類型：TINNYINT（8）、SMALLINT（16）、MEDIUMINT（24）、INT（32）、BIGINT（64）。

整數型別有可選的UNSIGNED屬性，表示不允許為負值，大致可以是正數的上限增加一倍。

例如：TINYINT UNSIGNED可以儲存的範圍是0~255，而TINYINT的儲存範圍是-127~128.

有符號和無符號類型使用相同的儲存空間，並具有相同的功能.

因此可以根據實際情況選擇合適的類型。

你的選擇決定Mysql是怎麼在記憶體和磁碟中儲存資料的。

整數一般選擇64位元的BIGINT整數，即使在32位元環境下也是如此。 （但是有些聚合函數是例外，它們是使用DECIMAL或DOUBLE進行計算的）

Mysql可以為整數類型指定寬度。

例如：INT（11），對大多數應用這是沒有意義的：它不會限制值的合法範圍，只是規定了Mysql的一些交互工具（例如Mysql命令列客戶端）用來顯示字元的個數。對於儲存和運算來講，INT（1）和INT（20）是相同的。

某些第三方儲存引擎（例如Infobright）有時也有自訂的儲存格式和壓縮方案，不一定使用常見的Mysql內建引擎的方式。

實數型別

實數是帶有小數部分的數字。

它們不只是未來儲存小數部分，也可以使用DECIMAL儲存比BIGINT還要大的整數。 Mysql既支援精確類型，也支援不精確類型。 DECIMAL類型用於儲存精確的小數。

在Mysql5.0或更高版本支援精確運算，而在Mysql4.1以及更早版本中使用浮點運算會出現異常（主要是精度的損失導致的）FLOAT和DECIMAL類型都可以指定進度。

對於DECIMAL欄位可以指定小數點前後所允許的最大位數，這會影響列的空間消耗。有很多方法可以指定FLOAT（浮點）列所需的精度，這會使得Mysql悄悄選擇了不同的資料類型，或者在存儲時對值進行取捨，但是這些精度往往都是非標準的，所以一般建議只指定資料類型不指定精度。

由於需要額外的空間和計算開銷，所以應該盡量只在對小數進行精確計算時才使用DECIMAL。例如儲存財務數據，但是如果數據量比較大的時候，可以考慮使用BIGINT代替DECIMAL，將需要儲存的貨幣單位根據小數的位數乘以相應的倍數即可。 FLOAT和DOUBLE類型支援使用標準的浮點運算進行近似計算。

字串類型

Mysql支援多種字串類型，每種類型還有很多變種。其中VARCHAR和CHAR是兩個最主要的字串型別。

注意：Mysql儲存引擎儲存CHAR或VARCHAR值的方式在記憶體中和在磁碟上可能不一樣，所以Mysql伺服器從儲存引擎讀取的值可能需要轉換為另外一種存儲格式。

VARCHAR類型用於儲存可變長字串，是最常見的字串資料類型。

VARCHAR比定長型別更能節省空間，因為它只使用必要的空間（越短的字串使用越少的空間）。

VARCHAR需要使用1或2個額外位元組記錄字串的長度。

VARCHAR節省了儲存空間，所以對效能是有幫助的。

下面是一些VARCHAR適合使用的場景：
（1）字串列的最大長度比平均長度大很多。
（2）列的更新很少，所以碎片不是問題。
（3）使用了像UTF-8這樣複雜的字元集，每個字元都使用不同的位元組數進行儲存。

CHAR類型是定長的。 （Mysql總是根據定義的字串長度分配足夠的空間）

CHAR適合儲存很短的字串，或所有值都接近同一個長度。

和VARCHAR和CHAR類似的型別還有BINARY和VARBINARY，它們儲存的都是二進位字串。

注意：使用VARCAHR（5）和VARCHAR（200）儲存「hello」的空間開銷都是一樣的，那麼使用更短的欄位有什麼優點呢？ （事實證明有很大的優勢）

更長的列會消耗更多的內存，因為Mysql通常會分配固定大小的記憶體區塊來保存內部值。尤其是使用記憶體臨時表進行排序或操作時會特別糟糕。在利用磁碟臨時表進行排序時也同樣糟糕。

注意：歸根到底，最好的策略是只分配真正需要的空間。

BLOB和TEXT類型

BLOB和TEXT都是為儲存很大的資料而設計的字串資料類型，分別使用二進位和字元方式儲存。

實際上它們分別屬於兩組不同的資料類型家族：字串類型有TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT；

二進位類型有TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、 LONGBLOB；

ENUM型別

可以使用枚舉（ENUM）取代字串型別。很多時候建議使用枚舉列來代替常用的字串型別。

（1）枚舉列可以把一些不重複的字串儲存成一個預先定義的集合。
（2）Mysql在儲存枚舉時非常緊湊，會根據列表值的數量壓縮到一到兩個位元組中。
（3）Mysql在內部會將每個值在列表中的位置保存為整數，並且在表的.frm檔案中保存「數字-字串」映射關係的「查找表」。

注意：有一個令人驚訝的地方是，枚舉欄位是按照內部儲存的整數而不是定義的字串進行排序的。

注意：枚舉最不好的地方是：字串列表是固定的，添加或刪除字串必須使用ALTER TABLE，因此對於一系列未來可能會改變的字符串，使用枚舉並不是一個好主意，除非接受只能在列表末尾添加元素。

注意：由於Mysql把每個枚舉值存為整數，並且必須進行查找才能轉換為字串，所以枚舉列有一些開銷。

日期和時間類型

#

Mysql有很多類型可以保存日期和時間值，例如YEAR和DATE。

Mysql能儲存的最小時間粒度為秒（MariaDB支援微秒等級的事件類型）。但是Mysql也可以使用微秒級別的粒度進行臨時運算。

大部分時間類型都沒有替代品，因此沒有什麼是最佳選擇的問題。

接下來唯一的問題是保存日期和時間的時候需要做什麼。

DATETIME

（1）這個類型能保存大範圍的值，從1001年到9999年，精度為秒。
（2）DATETIME把時間和日期封裝到格式為YYYYMMDDHHMMSS的整數中，與時區無關。
（3）DATETIME使用8個位元組的儲存空間。

TIMESTAMP

（1）TIMESTAMP類型保存了從1970年1月1日午夜以來的秒數，它和UNIX時間戳相同。
（2）TIMESTAMP只使用4個位元組的儲存空間，因此它的範圍比DATETIME小得多。
（3）TIMESTAMP顯示的值依賴時區。

DATETIME和TIMESTAMP的比較:

（1）預設情況下，如果插入時沒有指定第一個TIMESTAMP欄位的值，Mysql則設定這個欄位的值為目前時間。 （這是DATETIME沒有的特性）
（2）插入一行記錄時，Mysql預設也會更新第一個TIMESTAMP欄位的值。
（3）TIMESTAMP欄位預設為NOT NULL，這與其他的資料型別不一樣。

總結

（1）除了特殊行為之外，通常也應該盡可能使用TIMESTAMP，因為它比DATETIME空間更有效率。
（2）一般來講不建議把UNIX時間戳存為整數值，這不會帶來任何收益，用整數保存時間戳格式通常不方便處理。
（3）如果需呀儲存比秒更小粒度的日期和時間值，可以使用BIGINT類型儲存微秒等級的時間戳，或是使用DOUBLE儲存秒之後的小數部分，也可以用MariaDB取代Mysql。

位元資料類型

BIT定義一個包含單位元的字段，BIT（2）儲存2個位，最大長度是64個位元。

注意：一般建議謹慎使用BIT類型，對於大部分應用來講最好避免使用這種類型。

選擇標識符

為identifier（標識列）選擇合適的資料類型非常重要。

一般來講更有可能用識別列與其他值比較，或是透過識別列來尋找其他欄位。

當選擇標識列的類型時，不僅需要考慮儲存類型，還需要考慮Mysql對此類型如何執行計算和比較。

一旦選取了一種類型，請確保在所有關聯表中都使用相同的類型。

在可以滿足值的範圍需求，並且預留未來成長空間的前提下，應該選擇最小的資料類型。

注意：整數通常是標識列最好的選擇，因為它們很快且可以使用AUTO_INCREMENT。注意：ENUM和SET是最糟糕的選擇了；如果可能也盡可能避免使用字串作為識別列，因為它們消耗空間並且通常比數字類別慢。

全文總結

對於資料庫設計，一定要三思而後行，選擇最適合的資料列類型還有決定資料列的大小都是很關鍵的一步。其實大可不必驚慌，無論對於任何類型需求的資料表設計，你只要記住一個原則，很重要很重要很重要的原則：盡可能使用正確儲存資料的最小資料類型。

以上就是對MySQL資料類型的認識的全部內容。

相關參考PHP中文網

#

以上是對MySQL資料類型的認識的詳細內容。更多資訊請關注PHP中文網其他相關文章！