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

小而美

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

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

簡(jiǎn)單至上

數(shù)據(jù)類(lèi)型越簡(jiǎn)單意味著處理數(shù)據(jù)的 CPU 周期越少。例如，整型相比字符型而言，處理起來(lái)更容易，這是因?yàn)樽址捅葘?duì)使得字符的比較更復(fù)雜。舉兩個(gè)例子：應(yīng)該使用 MySQL 內(nèi)置的類(lèi)型來(lái)存儲(chǔ)時(shí)間和日期，而不是字符串。IP 地址也應(yīng)該使用整型存儲(chǔ)。

避免空值

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

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

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

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

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

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

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

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

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

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

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

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

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

數(shù)據(jù)類(lèi)型介紹

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

最常用的數(shù)據(jù)類(lèi)型，存儲(chǔ)串，如名字、地址、電話號(hào)碼等。

兩種基本的串類(lèi)型：定長(zhǎng)串和變長(zhǎng)串。

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

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

PS：MySQL處理定長(zhǎng)列遠(yuǎn)比處理變長(zhǎng)列快得多。且MySQL不允許對(duì)變長(zhǎng)列（或一個(gè)列的可變部分）進(jìn)行索引。

數(shù)據(jù)類(lèi)型說(shuō)明：

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

PS：

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

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

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

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

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

PS：

1、所有數(shù)值數(shù)據(jù)類(lèi)型（除BIT和BOOLEAN外）都可以有符號(hào)或無(wú)符號(hào)。有符號(hào)數(shù)值列可以存儲(chǔ)正或負(fù)的數(shù)值，無(wú)符號(hào)數(shù)值列只能存儲(chǔ)正數(shù)。

2、默認(rèn)情況為有符號(hào)，若不需要存儲(chǔ)負(fù)值，可以使用UNSIGNED，這樣做將允許你存儲(chǔ)兩倍大小的值。

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

4、MySQL中沒(méi)有專(zhuān)門(mén)存儲(chǔ)貨幣的數(shù)據(jù)類(lèi)型，一般情況下使用DECIMAL(8, 2)。

數(shù)據(jù)類(lèi)型說(shuō)明：

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

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

數(shù)據(jù)類(lèi)型說(shuō)明：

•  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

四、二進(jìn)制數(shù)據(jù)類(lèi)型

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

數(shù)據(jù)類(lèi)型說(shuō)明：

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

結(jié)語(yǔ)：

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

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