MySQL 數(shù)據(jù)類型選擇原則

小而美

通常來說，盡可能使用占用存儲空間小的數(shù)據(jù)類型來存儲數(shù)據(jù)。這類數(shù)據(jù)類型通常也會更快，并且占用的磁盤空間、內(nèi)存乃至緩存都更小，而且占用的 CPU 處理周期也少。

但是，務(wù)必準(zhǔn)確估計要存儲的數(shù)據(jù)值的范圍。因為在數(shù)據(jù)表結(jié)構(gòu)的多個地方擴充數(shù)據(jù)范圍會是一個痛苦且耗時的過程。如果在猶豫哪種數(shù)據(jù)類型合適，那就選擇你認為不會超出范圍的最小空間的類型（在系統(tǒng)早期或者數(shù)據(jù)表 數(shù)據(jù)不多的情況下也可以進行調(diào)整）。

簡單至上

數(shù)據(jù)類型越簡單意味著處理數(shù)據(jù)的 CPU 周期越少。例如，整型相比字符型而言，處理起來更容易，這是因為字符集和比對使得字符的比較更復(fù)雜。舉兩個例子：應(yīng)該使用 MySQL 內(nèi)置的類型來存儲時間和日期，而不是字符串。IP 地址也應(yīng)該使用整型存儲。

避免空值

很多數(shù)據(jù)表都是要可為空的列，雖然在應(yīng)用中并不需要存儲缺省值NULL。通常來說，指定列為 NOT NULL 會比存儲 NULL 要更優(yōu)。

MySQL 對于涉及到可為空的列優(yōu)化起來更為困難，這是因為空值列使得索引、索引統(tǒng)計和值比較都變得復(fù)雜。而且，可為空的列占據(jù)的存儲空間更大，且需要特殊的處理。如果在可為空的列上指定了索引，這會需要每個索引入口多一個額外的字節(jié)，甚至?xí)?dǎo)致 MyISAM 引擎固定大小的索引轉(zhuǎn)換為可變大小的索引（例如對整數(shù)型字段做單列索引）。 不過，將 NULL 列轉(zhuǎn)換為 NOT NULL列的性能改進通常并不大。因此，除非已經(jīng)發(fā)現(xiàn)了 NULL 列對性能有很大的影響，否則不要優(yōu)先去對已有的數(shù)據(jù)表結(jié)構(gòu)進行改動。但是，如果需要對列構(gòu)建索引，那應(yīng)該盡量避免該列值可以為空，通常好的習(xí)慣是直接設(shè)置該列為 NOT NULL。

當(dāng)然，也有例外，例如在 InnoDB 中僅僅使用了一個 bit 來存儲 NULL 值，因此對大量數(shù)據(jù)存儲來說可以有效節(jié)省空間，但是如果是 MyISAM 引擎就不是這樣了。

選擇數(shù)據(jù)類型的步驟

選擇數(shù)據(jù)類型的第一步是決定數(shù)據(jù)列使用哪種常用的數(shù)據(jù)類型來表示，是數(shù)值型、字符串型還是時間類型。通常直接選擇就挺不錯的，但是在某些情況下會有特殊（比如金額、時間戳）。

第二步就是選擇具體的類型。MySQL對于同一種數(shù)據(jù)類型會有多種存儲方式，基于數(shù)據(jù)值范圍、精度以及存儲的物理空間，而還有些數(shù)據(jù)類型有一些特殊的屬性。

例如，DATETIME 和 TIMESTAMP 都可以存儲時間和日期，都可以精確到秒。然而，TIMESTAMP 類型只需要一半的存儲空間，并且包括了時區(qū)信息，還支持自動更新。但另一方面，它存儲的時間范圍更小，它的這些特殊特性可能變成障礙。

再來看看基本數(shù)據(jù)類型。MySQL 支持數(shù)據(jù)類型的別名，例如 INTEGER，BOOL 和 NUMERIC。這些僅僅是別名，雖然看起來會讓人困惑，但是實際上對性能沒有影響。如果使用了別名數(shù)據(jù)類型創(chuàng)建數(shù)據(jù)表，可回憶使用 SHOW CREATE TABLE，可以看到實際上 MySQL 會轉(zhuǎn)換為基礎(chǔ)數(shù)據(jù)類型，而不是別名。

數(shù)據(jù)類型：定義列中可以存儲什么數(shù)據(jù)以及該數(shù)據(jù)實際怎樣存儲的基本規(guī)則。

數(shù)據(jù)類型用于以下目的：

1、允許限制可存儲在列中的數(shù)據(jù)。如：數(shù)值數(shù)據(jù)類型列只能接受數(shù)值。

2、允許在內(nèi)部更有效地存儲數(shù)據(jù)。如：用比文本串更簡潔的格式存儲數(shù)值和日期時間值。

3、允許變換排序順序。如：數(shù)據(jù)都作為串處理，則1位于10前，10位于2前（串以字典順序排序，從左邊開始比較，一次一個字符）；作為數(shù)值數(shù)據(jù)類型，數(shù)值才能正確排序。

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

一、串?dāng)?shù)據(jù)類型

最常用的數(shù)據(jù)類型，存儲串，如名字、地址、電話號碼等。

兩種基本的串類型：定長串和變長串。

定長串：接受長度固定的字符串，其長度是在創(chuàng)建表時指定的。定長列不允許多于指定的字符數(shù)目，它們分配的存儲空間與指定的一樣多。如：CHAR。

變長串：存儲可變長度的文本。有些變長數(shù)據(jù)類型具有最大的定長，有些則是完全變長的，不管是哪種，只有指定的數(shù)據(jù)會得到保存（額外的數(shù)據(jù)不保存），如：TEXT。

PS：MySQL處理定長列遠比處理變長列快得多。且MySQL不允許對變長列（或一個列的可變部分）進行索引。

數(shù)據(jù)類型說明：

•  CHAR：1～255個字符的定長串。長度必須在創(chuàng)建時指定，否則MySQL假定為CHAR(1)。
•  ENUM：接受最多64K個串組成的一個預(yù)定義集合的某個串。
•  LONGTEXT：與TEXT相同，但最大長度為4GB。
•  MEDIUMTEXT：與TEXT相同，但最大長度為16 K。
•  SET：接受最多64個串組成的一個預(yù)定義集合的零個或多個串。
•  TEXT：最大長度為64 K的變長文本。
•  TINYTEXT：與TEXT相同，但最大長度為255字節(jié)。
•  VARCHAR：長度可變，最多不超過255字節(jié)。如創(chuàng)建時指定為VARCHAR(n)，則可存儲0到n個字符的變長串（其中n≤255）。

PS：

1、引號：使用何種形式的串?dāng)?shù)據(jù)類型，串值都必須括在引號內(nèi)（通常使用單引號）。

2、須遵守的基本規(guī)則：如果數(shù)值是計算（求和、平均等）中使用的數(shù)值，則存儲在數(shù)值數(shù)據(jù)類型列中。如果數(shù)值作為字符串使用，則保存在串?dāng)?shù)據(jù)類型列中。如：在數(shù)值字段中存儲郵政編碼01234，保存的是數(shù)值1234，丟失了一位數(shù)字。

二、數(shù)值數(shù)據(jù)類型

存儲數(shù)值。MySQL支持多種數(shù)值數(shù)據(jù)類型，每種存儲的數(shù)值具有不同的取值范圍。

支持的取值范圍越大，所需存儲空間越多。此外，有的數(shù)值數(shù)據(jù)類型支持使用十進制小數(shù)點（和小數(shù)），而有的則只支持整數(shù)。表D-2列出了常用的MySQL數(shù)值數(shù)據(jù)類型。

PS：

1、所有數(shù)值數(shù)據(jù)類型（除BIT和BOOLEAN外）都可以有符號或無符號。有符號數(shù)值列可以存儲正或負的數(shù)值，無符號數(shù)值列只能存儲正數(shù)。

2、默認情況為有符號，若不需要存儲負值，可以使用UNSIGNED，這樣做將允許你存儲兩倍大小的值。

3、與串不同，數(shù)值不應(yīng)該在引號內(nèi)。

4、MySQL中沒有專門存儲貨幣的數(shù)據(jù)類型，一般情況下使用DECIMAL(8, 2)。

數(shù)據(jù)類型說明：

•  BIT：位字段，1～64位。在MySQL 5之前，BIT在功能上等價于TINYINT。
•  BIGINT：整數(shù)值，支持-9223372036854775808～9223372036854775807。如果是UNSIGNED，為0～18446744073709551615的數(shù)。
•  BOOLEAN（或BOOL）：布爾標(biāo)志，為0或者為1，主要用于開/關(guān)（on/off）標(biāo)志。
•  DECIMAL（或DEC）：精度可變的浮點值。
•  DOUBLE：雙精度浮點值
•  FLOAT：單精度浮點值
•  INT（或INTEGER）：整數(shù)值，支持-2147483648～2147483647，UNSIGNED同上。
•  MEDIUMINT：整數(shù)值，支持-8388608～8388607，UNSIGNED同上。
•  REAL：4字節(jié)的浮點值。
•  SMALLINT：整數(shù)值，支持-32768～32767，UNSIGNED同上。
•  TINYINT：整數(shù)值，支持-128～127，UNSIGNED同上。

三、日期和時間數(shù)據(jù)類型

數(shù)據(jù)類型說明：

•  DATE：表示1000-01-01～9999-12-31的日期，格式為YYYY-MM-DD。
•  DATETIME：DATE和TIME的組合。
•  TIMESTAMP：功能和DATETIME相同，但范圍較小。
•  TIME：格式為HH:MM:SS。
•  YEAR：2位數(shù)字表示，范圍是70～69（1970～2069）；4位數(shù)字表示，范圍是1901～2155

四、二進制數(shù)據(jù)類型

可存儲任何數(shù)據(jù)（甚至包括二進制信息），如圖像、多媒體、字處理文檔等。

數(shù)據(jù)類型說明：

•  BLOB：Blob最大長度為64KB。
•  MEDIUMBLOB：Blob最大長度為16 MB。
•  LONGBLOB：Blob最大長度為4GB。
•  TINYBLOB：Blob最大長度為255字節(jié)。

結(jié)語：

MySQL 的數(shù)據(jù)表示方式很多，建議了解常用的數(shù)據(jù)類型的存儲范圍，占據(jù)的字節(jié)數(shù)，盡可能地根據(jù)產(chǎn)品預(yù)估數(shù)據(jù)值范圍或長度，選擇合適的數(shù)據(jù)類型，從而在創(chuàng)建表一開始就注重性能。后期再來調(diào)整的代價往往超出設(shè)計之初付出的細致思考的時間成本。

以上就是MySQL 為什么要選擇合適的數(shù)據(jù)類型的詳細內(nèi)容，更多關(guān)于MySQL 數(shù)據(jù)類型的資料請關(guān)注腳本之家其它相關(guān)文章！