MySQL全面瓦解3：資料類型介紹與分析

概述

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		(-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		(-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又稱為精度和标度。

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		'-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		不超過 255 個字元的二進制字元串
TINYTEXT		短文本字元串
BLOB	0-65 535 bytes	二進制形式的長文本資料
TEXT		長文本資料
MEDIUMBLOB	0-16 777 215 bytes	二進制形式的中等長度文本資料
MEDIUMTEXT		中等長度文本資料
LONGBLOB	0-4 294 967 295 bytes	二進制形式的極大文本資料
LONGTEXT		極大文本資料

注意點：

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)		byte[]
		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		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)]	MEDIUMINT [UNSIGNED]	java.lang.Integer, if UNSIGNED java.lang.Long
INT,INTEGER[(M)]	INTEGER [UNSIGNED]	java.lang.Integer , if
BIGINT[(M)] [UNSIGNED]	BIGINT [UNSIGNED]	java.lang.Long , if UNSIGNED java.math.BigInteger
FLOAT[(M,D)]		java.lang.Float
DOUBLE[(M,B)]		java.lang.Double
DECIMAL[(M[,D])]		java.math.BigDecimal a
		java.sql.Date Ja
		java.sql.Timestamp
TIMESTAMP[(M)]
		java.sql.Time
YEAR[(2|4)] 公	衆	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)		java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.
VARCHAR(M) [BINARY]
BINARY(M)	BINARY
VARBINARY(M)	VARBINARY
		java.lang.String
ENUM('value1','value2',...)
SET('value1','value2',...)

使用建議

不建議選擇較大的數值範圍，盡量選擇合适的資料範圍，越小的資料類型會更快，占用磁盤，記憶體和CPU緩存也更小。

越簡單的類型執行的成本越低，比如整型比字元類型操作代價要小得多，很多固定範圍的文本内容字段可以用整型表示。

盡量制定列為NOT NULL，有NULL的列值會使得索引、索引統計和值比較更加複雜。

浮點類型的建議統一選擇decimal，精度會好很多。

記錄時間的建議使用時間戳格式，存儲友善，索引高效

架構與思維·公衆号：撰稿者為bat、位元組的幾位高階研發/架構。不做廣告、不賣課、不要打賞，隻分享優質技術

碼字不易，歡迎關注，歡迎轉載

作者：翁智華

出處：https://www.cnblogs.com/wzh2010/

本文采用「CC BY 4.0」知識共享協定進行許可，轉載請注明作者及出處。