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數值類型表示雙精度浮點數值,floatdouble都是浮點型,而decimal是定點型。浮點型和定點型可以用類型名稱後加(MD)來表示,M表示該值的總共長度,D表示小數點後面的長度,MD又稱為精度和標度。

floatdouble在不指定精度時,默認會按照實際的精度來顯示,而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,精度會好很多。

記錄時間的建議使用時間戳格式,存儲方便,索引高效