MySQL的數(shù)據(jù)類型介紹

由于Mysql獨有的特性和實現(xiàn)細節(jié)對性能的影響是很明顯的，因為做好Mysql數(shù)據(jù)庫的設(shè)計很關(guān)鍵。對于數(shù)據(jù)庫設(shè)計，我們不得不提表字段的類型選擇，由于Mysql支持的數(shù)據(jù)類型非常多，因此如何選擇正確的數(shù)據(jù)類型對于獲得高性能至關(guān)重要。不管要存儲的數(shù)據(jù)是什么類型，我們都需要根據(jù)一些數(shù)據(jù)庫設(shè)計原則來考慮。

選擇數(shù)據(jù)類型的思考

更小的通常是更好的（一般情況下，應該盡可能使用正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型。）

為什么呢？

（1） 因為更小的數(shù)據(jù)類型通常更快，因為它們占用更少的磁盤、內(nèi)存和CPU緩存，并且處理時需要的CPU周期也更短。
（2） 要確保沒有低估需要存儲的值的范圍，更小是相對與數(shù)據(jù)類型的最大值范圍來講的。
（3） 如果無法確定哪個數(shù)據(jù)類型是最好的，就選擇你認為不會超過范圍的最小類型。

簡單就好（簡單數(shù)據(jù)類型的操作通常需要更短的CPU周期。）

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

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

盡量避免 NULL （空值）

為什么呢？

（1） 很多表都包含可為NULL的列，就算程序并不需要保存NULL也是如此，這是因為列的默認屬性就是可為NULL。通常情況下最好指定列NOT NULL，除非真的需要存儲NULL。
（2） 如果查詢中包含可為NULL的列，對于Mysql來說是很難優(yōu)化的，因為NULL的列使得索引，索引統(tǒng)計和值比較都更復雜?？蔀镹ULL的列會使用更多的存儲空間，在Mysql里也需要特殊處理。當可為NULL的列被索引時，每個索引記錄需要一個額外的字節(jié)，在MyISAM里甚至還可能導致固定大小的索引變成可變大小的索引。
（3） 通常把可為NULL的列改為NOTNULL帶來性能提升比較小，如果計劃在列上建索引的話，就應該盡量避免設(shè)計成可為NULL的列。（也有一個例外，那就是在InnoDB中，會使用單獨的位（bit）來存儲NULL值，所以對稀疏數(shù)據(jù)有很好的空間效率。）

總結(jié)

在為列選擇數(shù)據(jù)類型時，第一步需要確定合適的大類型（數(shù)字、字符串、時間等等），這通常是很簡單的，那么下一步就是選擇具體的類型了。

很多Mysql的數(shù)據(jù)類型可以存儲相同類型的數(shù)據(jù)，只是存儲的長度和范圍不一樣、允許的精度不同，或者需要的物理空間（磁盤和內(nèi)存空間）不同。相同大類型的不同子類型數(shù)據(jù)有時候也有一些特殊的行為和屬性。比如：DATATIME 和 TIMESAMP列都可以存儲相同類型的數(shù)據(jù)（時間和日期）并且精確到秒，然而TIMESTAMP只使用DATATIME一半的存儲空間，并且會根據(jù)時區(qū)變化，具有特殊的自動更新能力。另外TIMESTAMP允許的時間范圍要小得多，有時候它的特殊能力會成為障礙，這都是我們開發(fā)者需要考慮的。

整數(shù)類型

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

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

整數(shù)類型有可選的的UNSIGNED屬性，表示不允許為負值，這大致可以是正數(shù)的上限提高一倍。

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

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

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

你的選擇決定Mysql是怎么在內(nèi)存和磁盤中保存數(shù)據(jù)的。

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

Mysql可以為整數(shù)類型指定寬度。

比如：INT（11），對大多數(shù)應用這是沒有意義的：它不會限制值的合法范圍，只是規(guī)定了Mysql的一些交互工具（例如Mysql命令行客戶端）用來顯示字符的個數(shù)。對于存儲和計算來講，INT（1）和INT（20）是相同的。

一些第三方存儲引擎（比如Infobright）有時也有自定義的存儲格式和壓縮方案，并不一定使用常見的Mysql內(nèi)置引擎的方式。

實數(shù)類型

實數(shù)是帶有小數(shù)部分的數(shù)字。

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

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

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

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

字符串類型

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

注意：Mysql存儲引擎存儲CHAR或者VARCHAR值的方式在內(nèi)存中和在磁盤上可能不一樣，所以Mysql服務(wù)器從存儲引擎讀取的值可能需要轉(zhuǎn)換為另外一種存儲格式。

VARCHAR類型用于存儲可變長字符串，是最常見的字符串數(shù)據(jù)類型。

VARCHAR比定長類型更節(jié)省空間，因為它僅使用必要的空間（越短的字符串使用越少的空間）。

VARCHAR需要使用1或2個額外字節(jié)記錄字符串的長度。

VARCHAR節(jié)省了存儲空間，所以對性能是有幫助的。

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

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

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

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

注意：使用VARCAHR（5）和VARCHAR（200）存儲“hello”的空間開銷都是一樣的，那么使用更短的列有什么優(yōu)勢呢？（事實證明有很大的優(yōu)勢）

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

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

BLOB和TEXT類型

BLOB和TEXT都是為存儲很大的數(shù)據(jù)而設(shè)計的字符串數(shù)據(jù)類型，分別使用二進制和字符方式存儲。

實際上它們分別屬于兩組不同的數(shù)據(jù)類型家族：字符串類型有TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT；

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

ENUM類型

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

（1）枚舉列可以把一些不重復的字符串存儲成一個預定義的集合。
（2）Mysql在存儲枚舉時非常緊湊，會根據(jù)列表值的數(shù)量壓縮到一到兩個字節(jié)中。
（3）Mysql在內(nèi)部會將每個值在列表中的位置保存為整數(shù)，并且在表的.frm文件中保存“數(shù)字-字符串”映射關(guān)系的“查找表”。

注意：有一個令人吃驚的地方是，枚舉字段是按照內(nèi)部存儲的整數(shù)而不是定義的字符串進行排序的。

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

注意：由于Mysql把每個枚舉值保存為整數(shù)，并且必須進行查找才能轉(zhuǎn)換為字符串，所以枚舉列有一些開銷。

日期和時間類型

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

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

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

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

DATETIME

（1）這個類型能保存大范圍的值，從1001年到9999年，精度為秒。
（2）DATETIME把時間和日期封裝到格式為YYYYMMDDHHMMSS的整數(shù)中，與時區(qū)無關(guān)。
（3）DATETIME使用8個字節(jié)的存儲空間。

TIMESTAMP

（1）TIMESTAMP類型保存了從1970年1月1日午夜以來的秒數(shù)，它和UNIX時間戳相同。
（2）TIMESTAMP只使用4個字節(jié)的存儲空間，因此它的范圍比DATETIME小得多。
（3）TIMESTAMP顯示的值依賴時區(qū)。

DATETIME和TIMESTAMP的對比:

（1）默認情況下，如果插入時沒有指定第一個TIMESTAMP列的值，Mysql則設(shè)置這個列的值為當前時間。（這是DATETIME沒有的特性）
（2）在插入一行記錄時，Mysql默認也會更新第一個TIMESTAMP列的值。
（3）TIMESTAMP列默認為NOT NULL，這與其他的數(shù)據(jù)類型不一樣。

總結(jié)

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

位數(shù)據(jù)類型

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

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

選擇標識符

為identifier（標識列）選擇合適的數(shù)據(jù)類型非常重要。

一般來講更有可能用標識列與其他值進行比較，或者通過標識列尋找其他列。

當選擇標識列的類型時，不僅僅需要考慮存儲類型，還需要考慮Mysql對這種類型怎么執(zhí)行計算和比較。

一旦選定了一種類型，要確保在所有關(guān)聯(lián)表中都使用同樣的類型。

在可以滿足值的范圍需求，并且預留未來增長空間的前提下，應該選擇最小的數(shù)據(jù)類型。

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

以上就是對MySQL數(shù)據(jù)類型的認識的詳細內(nèi)容，更多請關(guān)注億速云其它相關(guān)文章！