概述
MySQL支援很多資料型別，以便我們能在複雜的業務場景中支援各種各樣的資料格式，儲存適當的資料內容。我們在設計資料庫時，正確的使用資料庫型別對整個資料庫的整潔和高效，會有很大的幫助。
目前常用的資料型別大致上可以分為4大類：整數型別、浮點數型別、字串(字元)型別、日期/時間型別。詳細如下
| 4大類 | 主要型別 |
| 整數型別 | tinyint 、 smallint 、 mediumint 、 int 、 bigint |
|
浮點數型別 |
float 、 double 、 decimal |
| 字串(字元)型別 | char 、 varchar 、 tinyblob 、 blob 、 mediumblob 、 longblob 、tinytext 、 text 、 mediumtext 、 longtext
|
| 日期/時間型別 | Date 、 DateTime 、 TimeStamp 、 Time 、 Year |
數值型別
MySQL支援所有標準SQL數值資料型別,包括嚴格數值資料型別(INTEGER、SMALLINT、DECIMAL和NUMERIC)，還有近似數值資料型別(FLOAT、REAL和DOUBLE PRECISION)，所以MySQL中資料型別是豐富且完整的。
而作為SQL標準的擴充套件，MySQL也支援整數型別TINYINT、MEDIUMINT和BIGINT。在下面的表格中，我們總結了5個整數型別的儲存大小和取值範圍。
| 型別 | 位元組數 | 有符號值範圍 | 無符號值範圍 | 作用 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128，127) | (0，255) | 小整數值 |
| SMALLINT | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整數值 |
| MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整數值 |
| INT或INTEGER | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整數值 |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 極大整數值 |
我們建立表賦予的型別預設是有符號型別的，無符號的需要在型別需要後面跟上unsigned 標記，上面的資料範圍是根據位元組對應二進位制換算，比如一個位元組轉化為十進位制最大為255（11111111），最小為0（00000000），具體可以去查下二進位制表示相關資料。
測試一下：
1 mysql> create table tinyintdemo( 2 tiny TINYINT, 3 tinyunsi TINYINT UNSIGNED 4 ); 5 Query OK, 0 rows affected 6 7 mysql> insert into tinyintdemo values(-128,-128); 8 1264 - Out of range value for column 'tinyunsi' at row 1 9 mysql> insert into tinyintdemo values(-128,0); 10 Query OK, 1 row affected 11 12 mysql> insert into tinyintdemo values(255,255); 13 1264 - Out of range value for column 'tiny' at row 1 14 mysql> insert into tinyintdemo values(127,255); 15 Query OK, 1 row affected
上面我們故意輸入不正確的值，無符號下我們輸入-128，預設有符號情況下我們輸入255，都會提示 Out of range，超出範圍。
1 mysql> select * from tinyintdemo; 2 +------+----------+ 3 | tiny | tinyunsi | 4 +------+----------+ 5 | -128 | 0 | 6 | 127 | 255 | 7 +------+----------+ 8 2 rows in set
另外需注意的點：我們經常在建立表的時候定義欄位為int(n)，比如int(11) , 或者bitint(19) , 其實這不是描述欄位長度的意思，在int(n)中，int佔據的位元組是固定4個，所以他的範圍也固定是在 (-2 147 483 648，2 147 483 647) 之間，
並不會因為n的長度是多少而改變，而n的目的是為了表實顯示寬度用的，在顯示寬度不足的時候補充0。
再測試一下：
1 mysql> CREATE TABLE lengthdemo ( 2 `nor` int, 3 `norlen` int(8), 4 `norfill` int(8) zerofill 5 ); 6 Query OK, 0 rows affected 7 8 mysql> insert into lengthdemo values(12345,12345,12345); 9 Query OK, 1 row affected 10 11 mysql> select * from lengthdemo; 12 13 +-------+--------+---------+ 14 | nor | norlen | norfill | 15 +-------+--------+---------+ 16 | 12345 | 12345 |00012345 | 17 +-------+--------+---------+ 18 1 row in set 19 20 mysql> show create table lengthdemo; 21 +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 22 | Table | Create Table | 23 +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 24 | lengthdemo | CREATE TABLE `lengthdemo` ( 25 `nor` int(11) DEFAULT NULL, 26 `norlen` int(8) DEFAULT NULL, 27 `norfill` int(8) unsigned zerofill DEFAULT NULL 28 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 29 +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 30 1 row in set
從這個執行結果中，我們可以總結如下：
1、n的值不管填寫多少，int型別只佔據4個位元組。
2、如果設定了n的顯示寬度之後，再加上unsigned zerofill，顯示寬度不足的用0補足，超過則直接顯示完整數值。
3、加 zerofill屬性的欄位，會預設為 unsigned 無符號型別。
浮點型別
float數值型別表示單精度浮點數值，double數值型別表示雙精度浮點數值，float和double都是浮點型，而decimal是定點型。浮點型和定點型可以用型別名稱後加（M，D）來表示，M表示該值的總共長度，D表示小數點後面的長度，M和D又稱為精度和標度。
float和double在不指定精度時，預設會按照實際的精度來顯示，而DECIMAL在不指定精度時，預設整數為10，小數為0。
| 型別 | 大小 | 有符號值範圍 | 無符號值範圍 | 作用 |
|---|---|---|---|---|
| FLOAT | 4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 單精度 浮點數值 |
| DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 雙精度 浮點數值 |
| DECIMAL | 對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2 | 依賴於M和D的值 | 依賴於M和D的值 | 小數值 |
浮點型和定點型可以用型別名稱後加(M,D)來表示,M表示該值的總共長度，D表示小數點後面的長度，M和D又稱為精度和標度。
float和double在不指定精度時，預設會按照實際的精度來顯示，而DECIMAL在不指定精度時，預設只取整數，小數丟棄。
同樣的，我們來測試下三個型別的使用：
1 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); 2 Query OK, 5 rows affected, 4 warnings (0.07 sec) 3 Records: 5 Duplicates: 0 Warnings: 4 4 5 mysql> select * from accuracy; 6 +------+------+------+ 7 | a | b | c | 8 +------+------+------+ 9 | 1.00 | 1.00 | 1.00 | 10 | 2.12 | 2.12 | 2.12 | 11 | 3.12 | 3.12 | 3.13 | 12 | 4.12 | 4.12 | 4.12 | 13 | 5.14 | 5.14 | 5.14 | 14 +------+------+------+ 15 5 rows in set (0.00 sec)
從這個執行結果，總結如下：
1、c是decimal型別，採用的是四捨五入
2、a和b分別為float 和double，採用的是四捨六入五成雙
說明下四捨六入五成雙：5以下捨棄，5以上進位，遇到5的時候，如果5後面還有不為0的任何數字，進位，如果沒有，需要檢查5前面的數字，奇數進位，偶數丟棄，觀察結果中 3.125和4.115，可以得到規律。
我們再將浮點型別的(M,D)精度和標度都去掉，結果如下：
1 mysql> create table accuracy2(a float,b double,c decimal); 2 Query OK, 0 rows affected 3 4 mysql> insert into accuracy2 values (1,1,1),(1.23,1.23,1.23); 5 Query OK, 2 rows affected 6 Records: 2 Duplicates: 0 Warnings: 1 7 8 mysql> select * from accuracy2; 9 +------+------+---+ 10 | a | b | c | 11 +------+------+---+ 12 | 1 | 1 | 1 | 13 | 1.23 | 1.23 | 1 | 14 +------+------+---+ 15 2 rows in set
總結如下：
如果浮點數float、double如果不寫精度和標度，則會按照實際的結果顯示，而decimal會將小數四捨五入，並且插入時發出警告資訊。
float，double等非標準型別，在DB中儲存的是近似值，而decimal則以字串的形式儲存準確的數值，比如銀行、金融系統之類的對統計精度要求比較高的，建議採用decimal。
日期和時間型別
表示時間值的日期和時間型別為DATETIME、DATE、TIMESTAMP、TIME和YEAR。每個時間型別有一個有效值範圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。
TIMESTAMP型別有專有的自動更新特性，將在後面描述。
| 型別 | 大小 | 值範圍 | 格式 | 作用 |
|---|---|---|---|---|
| DATE | 3 bytes | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 bytes | '-838:59:59'/'838:59:59' | HH:MM:SS | 時間值或持續時間 |
| YEAR | 1 bytes | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 bytes | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和時間值 |
| TIMESTAMP | 4 bytes |
1970-01-01 00:00:00/2038 結束時間是第 2147483647 秒，北京時間 2038-1-19 11:14:07，格林尼治時間 2038年1月19日 凌晨 03:14:07 |
YYYYMMDD HHMMSS | 混合日期和時間值，時間戳 |
字串型別
字串型別指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
| 型別 | 大小 | 作用 |
|---|---|---|
| CHAR | 0-255 bytes | 定長字串 |
| VARCHAR | 0-65535 bytes | 變長字串 |
| TINYBLOB | 0-255 bytes | 不超過 255 個字元的二進位制字串 |
| TINYTEXT | 0-255 bytes | 短文字字串 |
| BLOB | 0-65 535 bytes | 二進位制形式的長文字資料 |
| TEXT | 0-65 535 bytes | 長文字資料 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二進位制形式的中等長度文字資料 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等長度文字資料 |
| LONGBLOB | 0-4 294 967 295 bytes | 二進位制形式的極大文字資料 |
| LONGTEXT | 0-4 294 967 295 bytes | 極大文字資料 |
注意點：
1、CHAR(N) 和 VARCHAR(N) 中N代表字元的個數，但並不代表位元組個數，比如 CHAR(10) 代表可以儲存 10 個字元。
2、CHAR 和VARCHAR型別類似，但它們儲存和檢索的方式不同，最大長度和是否尾部空格被保留等方面也不同，同時在儲存或檢索過程中不進行大小寫轉換。
如果存放的資料為固定長度的建議使用char型別，如：手機號碼、身份證等固定長度的資訊。
3、BINARY 和 VARBINARY 類似於 CHAR 和 VARCHAR，不同的是它們包含二進位制字串而不要非二進位制字串。它們包含位元組字串而不是字元字串。這說明它們沒有字符集，並且排序和比較基於列值位元組的數值。
4、BLOB是一個二進位制大物件，可以容納可變數量的資料，包含4 種型別：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，區別是可容納儲存範圍不同。
5、TEXT 型別：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應的這 4 種 BLOB 型別，可儲存的最大長度不同，可根據實際業務場景的選擇。
MySQL、Java型別對照
| MySQL Type Name | Return value ofGetColumnClassName |
Returned as Java Class |
| BIT(1) (new in MySQL-5.0) | BIT | java.lang.Boolean |
| BIT( > 1) (new in MySQL- 5.0) |
BIT | byte[] |
| TINYINT | TINYINT | java.lang.Boolean if the configuration property tinyInt1isBit is set to true (the default) and the storage size is 1, or java.lang.Integer if not. |
| BOOL, BOOLEAN | TINYINT | See TINYINT, above as these are aliases forTINYINT(1), currently. |
| SMALLINT[(M)] [UNSIGNED] |
SMALLINT [UNSIGNED] | java.lang.Integer (regardless if UNSIGNED or not) |
| MEDIUMINT[(M)] [UNSIGNED] |
MEDIUMINT [UNSIGNED] | java.lang.Integer, if UNSIGNED java.lang.Long |
| INT,INTEGER[(M)] [UNSIGNED] |
INTEGER [UNSIGNED] | java.lang.Integer , if UNSIGNED java.lang.Long |
| BIGINT[(M)] [UNSIGNED] | BIGINT [UNSIGNED] | java.lang.Long , if UNSIGNED java.math.BigInteger |
| FLOAT[(M,D)] | FLOAT | java.lang.Float |
| DOUBLE[(M,B)] | DOUBLE | java.lang.Double |
| DECIMAL[(M[,D])] | DECIMAL | java.math.BigDecimal a |
| DATE | DATE | java.sql.Date Ja |
| DATETIME | DATETIME | java.sql.Timestamp |
| TIMESTAMP[(M)] | TIMESTAMP | java.sql.Timestamp |
| TIME | TIME | java.sql.Time |
| YEAR[(2|4)] 公 |
YEAR 眾 |
If yearIsDateType configuration property is set to false, then the returned object type is java.sql.Short . If set to true (the default) then an object of type java.sql.Date (with the date set to January 1st, at midnight). |
| CHAR(M) | CHAR | java.lang.String (unless the character set for the column is BINARY, then byte[] is returned. |
| VARCHAR(M) [BINARY] | VARCHAR | java.lang.String (unless the character set for the column is BINARY, then byte[] is returned. |
| BINARY(M) | BINARY | byte[] |
| VARBINARY(M) | VARBINARY | byte[] |
| TINYBLOB | TINYBLOB | byte[] |
| TINYTEXT | VARCHAR | java.lang.String |
| BLOB | BLOB | byte[] |
| MySQL Type Name | Return value ofGetColumnClassName |
Returned as Java Class |
| TEXT | VARCHAR | java.lang.String |
| MEDIUMBLOB | MEDIUMBLOB | byte[] |
| MEDIUMTEXT | VARCHAR | java.lang.String |
| LONGBLOB | LONGBLOB | byte[] |
| LONGTEXT | VARCHAR | java.lang.String |
| ENUM('value1','value2',...) | CHAR | java.lang.String |
| SET('value1','value2',...) | CHAR | java.lang.String |
使用建議
不建議選擇較大的數值範圍，儘量選擇合適的資料範圍，越小的資料型別會更快，佔用磁碟，記憶體和CPU快取也更小。
越簡單的型別執行的成本越低，比如整型比字元型別操作代價要小得多，很多固定範圍的文字內容欄位可以用整型表示。
儘量制定列為NOT NULL，有NULL的列值會使得索引、索引統計和值比較更加複雜。
浮點型別的建議統一選擇decimal，精度會好很多。
記錄時間的建議使用時間戳格式，儲存方便，索引高效