概述

MySQL支持很多數(shù)據(jù)類型，以便我們能在復(fù)雜的業(yè)務(wù)場(chǎng)景中支持各種各樣的數(shù)據(jù)格式，存儲(chǔ)適當(dāng)?shù)臄?shù)據(jù)內(nèi)容。我們?cè)谠O(shè)計(jì)數(shù)據(jù)庫時(shí)，正確的使用數(shù)據(jù)庫類型對(duì)整個(gè)數(shù)據(jù)庫的整潔和高效，會(huì)有很大的幫助。

目前常用的數(shù)據(jù)類型大致上可以分為4大類：整數(shù)類型、浮點(diǎn)數(shù)類型、字符串(字符)類型、日期/時(shí)間類型。詳細(xì)如下

數(shù)值類型

MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型,包括嚴(yán)格數(shù)值數(shù)據(jù)類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，還有近似數(shù)值數(shù)據(jù)類型(FLOAT、REAL和DOUBLE PRECISION)，所以MySQL中數(shù)據(jù)類型是豐富且完整的。

而作為SQL標(biāo)準(zhǔn)的擴(kuò)展，MySQL也支持整數(shù)類型TINYINT、MEDIUMINT和BIGINT。在下面的表格中，我們總結(jié)了5個(gè)整數(shù)類型的存儲(chǔ)大小和取值范圍。

我們創(chuàng)建表賦予的類型默認(rèn)是有符號(hào)類型的，無符號(hào)的需要在類型需要后面跟上unsigned 標(biāo)記，上面的數(shù)據(jù)范圍是根據(jù)字節(jié)對(duì)應(yīng)二進(jìn)制換算，比如一個(gè)字節(jié)轉(zhuǎn)化為十進(jìn)制最大為255（11111111），最小為0（00000000），具體可以去查下二進(jìn)制表示相關(guān)資料。
測(cè)試一下：

mysql> create table tinyintdemo(
  tiny TINYINT,
  tinyunsi TINYINT UNSIGNED
);
Query OK, 0 rows affected

mysql> insert into tinyintdemo values(-128,-128);
1264 - Out of range value for column 'tinyunsi' at row 1
mysql> insert into tinyintdemo values(-128,0);
Query OK, 1 row affected

mysql> insert into tinyintdemo values(255,255);
1264 - Out of range value for column 'tiny' at row 1
mysql> insert into tinyintdemo values(127,255);
Query OK, 1 row affected

上面我們故意輸入不正確的值，無符號(hào)下我們輸入-128，默認(rèn)有符號(hào)情況下我們輸入255，都會(huì)提示 Out of range，超出范圍。

mysql> select *  from  tinyintdemo;
+------+----------+
| tiny | tinyunsi |
+------+----------+
| -128 |        0 |
|  127 |      255 |
+------+----------+
2 rows in set

另外需注意的點(diǎn)：我們經(jīng)常在創(chuàng)建表的時(shí)候定義字段為int(n)，比如int(11) , 或者bitint(19) , 其實(shí)這不是描述字段長(zhǎng)度的意思，在int(n)中，int占據(jù)的字節(jié)是固定4個(gè)，所以他的范圍也固定是在 (-2 147 483 648，2 147 483 647) 之間，
并不會(huì)因?yàn)閚的長(zhǎng)度是多少而改變，而n的目的是為了表實(shí)顯示寬度用的，在顯示寬度不足的時(shí)候補(bǔ)充0。
再測(cè)試一下：

mysql> CREATE TABLE lengthdemo (
       `nor` int,
       `norlen` int(8),
       `norfill` int(8) zerofill
);
Query OK, 0 rows affected

mysql> insert into lengthdemo values(12345,12345,12345);
Query OK, 1 row affected

mysql> select * from lengthdemo;

+-------+--------+---------+
| nor   | norlen | norfill |
+-------+--------+---------+
| 12345 |  12345 |00012345 |
+-------+--------+---------+
1 row in set

mysql> show create table lengthdemo;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                   |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| lengthdemo | CREATE TABLE `lengthdemo` (
  `nor` int(11) DEFAULT NULL,
  `norlen` int(8) DEFAULT NULL,
  `norfill` int(8) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

從這個(gè)執(zhí)行結(jié)果中，我們可以總結(jié)如下：

1、n的值不管填寫多少，int類型只占據(jù)4個(gè)字節(jié)。

2、如果設(shè)置了n的顯示寬度之后，再加上unsigned zerofill，顯示寬度不足的用0補(bǔ)足，超過則直接顯示完整數(shù)值。

3、加 zerofill屬性的字段，會(huì)默認(rèn)為 unsigned 無符號(hào)類型。

浮點(diǎn)類型

float數(shù)值類型表示單精度浮點(diǎn)數(shù)值，double數(shù)值類型表示雙精度浮點(diǎn)數(shù)值，float和double都是浮點(diǎn)型，而decimal是定點(diǎn)型。浮點(diǎn)型和定點(diǎn)型可以用類型名稱后加（M，D）來表示，M表示該值的總共長(zhǎng)度，D表示小數(shù)點(diǎn)后面的長(zhǎng)度，M和D又稱為精度和標(biāo)度。

float和double在不指定精度時(shí)，默認(rèn)會(huì)按照實(shí)際的精度來顯示，而DECIMAL在不指定精度時(shí)，默認(rèn)整數(shù)為10，小數(shù)為0。

浮點(diǎn)型和定點(diǎn)型可以用類型名稱后加(M,D)來表示,M表示該值的總共長(zhǎng)度，D表示小數(shù)點(diǎn)后面的長(zhǎng)度，M和D又稱為精度和標(biāo)度。

float和double在不指定精度時(shí)，默認(rèn)會(huì)按照實(shí)際的精度來顯示，而DECIMAL在不指定精度時(shí)，默認(rèn)只取整數(shù)，小數(shù)丟棄。

同樣的，我們來測(cè)試下三個(gè)類型的使用：

mysql> insert into accuracy values (1,1,1),(2.124,2.124,2.124),(3.125,3.125,3.125),(4.115,4.115,4.115),(5.136,5.136,5.136);
Query OK, 5 rows affected, 4 warnings (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 4

mysql> select * from accuracy;
+------+------+------+
| a    | b    | c    |
+------+------+------+
| 1.00 | 1.00 | 1.00 |
| 2.12 | 2.12 | 2.12 |
| 3.12 | 3.12 | 3.13 |
| 4.12 | 4.12 | 4.12 |
| 5.14 | 5.14 | 5.14 |
+------+------+------+
5 rows in set (0.00 sec)

從這個(gè)執(zhí)行結(jié)果，總結(jié)如下：

1、c是decimal類型，采用的是四舍五入

2、a和b分別為float 和double，采用的是四舍六入五成雙

說明下四舍六入五成雙：5以下舍棄，5以上進(jìn)位，遇到5的時(shí)候，如果5后面還有不為0的任何數(shù)字，進(jìn)位，如果沒有，需要檢查5前面的數(shù)字，奇數(shù)進(jìn)位，偶數(shù)丟棄，觀察結(jié)果中 3.125和4.115，可以得到規(guī)律。

我們?cè)賹⒏↑c(diǎn)類型的(M,D)精度和標(biāo)度都去掉，結(jié)果如下：

mysql> create table accuracy2(a float,b double,c decimal);
Query OK, 0 rows affected

mysql> insert into accuracy2 values (1,1,1),(1.23,1.23,1.23);
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from accuracy2;
+------+------+---+
| a    | b    | c |
+------+------+---+
|    1 |    1 | 1 |
| 1.23 | 1.23 | 1 |
+------+------+---+
2 rows in set

總結(jié)如下：

如果浮點(diǎn)數(shù)float、double如果不寫精度和標(biāo)度，則會(huì)按照實(shí)際的結(jié)果顯示，而decimal會(huì)將小數(shù)四舍五入，并且插入時(shí)發(fā)出警告信息。

float，double等非標(biāo)準(zhǔn)類型，在DB中保存的是近似值，而decimal則以字符串的形式保存準(zhǔn)確的數(shù)值，比如銀行、金融系統(tǒng)之類的對(duì)統(tǒng)計(jì)精度要求比較高的，建議采用decimal。

日期和時(shí)間類型

表示時(shí)間值的日期和時(shí)間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。每個(gè)時(shí)間類型有一個(gè)有效值范圍和一個(gè)"零"值，當(dāng)指定不合法的MySQL不能表示的值時(shí)使用"零"值。

TIMESTAMP類型有專有的自動(dòng)更新特性，將在后面描述。

字符串類型

字符串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

注意點(diǎn)：

1、CHAR(N) 和 VARCHAR(N) 中N代表字符的個(gè)數(shù)，但并不代表字節(jié)個(gè)數(shù)，比如 CHAR(10) 代表可以存儲(chǔ) 10 個(gè)字符。

2、CHAR 和VARCHAR類型類似，但它們保存和檢索的方式不同，最大長(zhǎng)度和是否尾部空格被保留等方面也不同，同時(shí)在存儲(chǔ)或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換。

如果存放的數(shù)據(jù)為固定長(zhǎng)度的建議使用char類型，如：手機(jī)號(hào)碼、身份證等固定長(zhǎng)度的信息。

3、BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR，不同的是它們包含二進(jìn)制字符串而不要非二進(jìn)制字符串。它們包含字節(jié)字符串而不是字符字符串。這說明它們沒有字符集，并且排序和比較基于列值字節(jié)的數(shù)值。

4、BLOB是一個(gè)二進(jìn)制大對(duì)象，可以容納可變數(shù)量的數(shù)據(jù)，包含4 種類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，區(qū)別是可容納存儲(chǔ)范圍不同。

5、TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對(duì)應(yīng)的這 4 種 BLOB 類型，可存儲(chǔ)的最大長(zhǎng)度不同，可根據(jù)實(shí)際業(yè)務(wù)場(chǎng)景的選擇。

MySQL、Java類型對(duì)照

使用建議

不建議選擇較大的數(shù)值范圍，盡量選擇合適的數(shù)據(jù)范圍，越小的數(shù)據(jù)類型會(huì)更快，占用磁盤，內(nèi)存和CPU緩存也更小。

越簡(jiǎn)單的類型執(zhí)行的成本越低，比如整型比字符類型操作代價(jià)要小得多，很多固定范圍的文本內(nèi)容字段可以用整型表示。

盡量制定列為NOT NULL，有NULL的列值會(huì)使得索引、索引統(tǒng)計(jì)和值比較更加復(fù)雜。

浮點(diǎn)類型的建議統(tǒng)一選擇decimal，精度會(huì)好很多。

記錄時(shí)間的建議使用時(shí)間戳格式，存儲(chǔ)方便，索引高效