MySQL 提供很多種數據類型來對不同的常量、變量進行區分,MySQL 中的數據類型主要是 數值類型、日期和時間類型、字符串類型 選擇合適的數據類型進行數據的存儲非常重要,在實際開發過程中,選擇合適的數據類型也能夠提高 SQL 性能,所以有必要認識一下這些數據類型。
數值類型
MySQL 支持所有標準的 SQL 數據類型,這些數據類型包括嚴格數據類型的嚴格數值類型,這些數據類型有
- INTEGER
- SMALLINT
- DECIMAL
- NUMERIC。
近似數值數據類型 并不用嚴格按照指定的數據類型進行存儲,這些有
- FLOAT
- REAL
- DOUBLE PRECISION
還有經過擴展之后的數據類型,它們是
- TINYINT
- MEDIUMINT
- BIGINT
- BIT
其中 INT 是 INTEGER 的縮寫,DEC 是 DECIMAL 的縮寫。
下面是所有數據類型的匯總

整數
在整數類型中,按照取值范圍和存儲方式的不同,分為
- TINYINT ,占用 1 字節
- SMALLINT,占用 2 字節
- MEDIUMINT,占用 3 字節
- INT、INTEGER,占用 4 字節
- BIGINT,占用 8 字節
五個數據類型,如果超出類型范圍的操作,會發生錯誤提示,所以選擇合適的數據類型非常重要。
還記得我們上面的建表語句么
我們一般會在 SQL 語句的數據類型后面加上指定長度來表示數據類型許可的范圍,例如
int(7)
表示 int 類型的數據最大長度為 7,如果填充不滿的話會自動填滿,如果不指定 int 數據類型的長度的話,默認是 int(11)。
我們創建一張表來演示一下
create table test1(aId int, bId int(5));
/* 然后我們查看一下表結構 */
desc test1;

整數類型一般配合 zerofill 來使用,顧名思義,就是用 0 進行填充,也就是數字位數不夠的空間使用 0 進行填充。
分別修改 test1 表中的兩個字段
alter table test1 modify aId int zerofill;
alter table test1 modify bId int(5) zerofill;

然后插入兩條數據,執行查詢操作

如上圖所示,使用zerofill 可以在數字前面使用 0 來進行填充,那么如果寬度超過指定長度后會如何顯示?我們來試驗一下,向 aId 和 bId 分別插入超過字符限制的數字

會發現 aId 已經超出了指定范圍,那么我們對 aId 插入一個在其允許范圍之內的數據

會發現,aId 已經插進去了,bId 也插進去了,為什么 bId 顯示的是 int(5) 卻能夠插入 7 位長度的數值呢?
所有的整數都有一個可選屬性 UNSIGNED(無符號),如果需要在字段里面保存非負數或者是需要較大上限值時,可以使用此選項,它的取值范圍是正常值的下限取 0 ,上限取原值的 2 倍。如果一個列為 zerofill ,會自動為該列添加 UNSIGNED 屬性。
除此之外,整數還有一個類型就是 AUTO_INCREMENT,在需要產生唯一標識符或者順序值時,可利用此屬性,這個屬性只用于整數字符。一個表中最多只有一個 AUTO_INCREMENT 屬性,一般用于自增主鍵,而且 NOT NULL,并且是 PRIMARY KEY 和 UNIQUE 的,主鍵必須保證唯一性而且不為空。
小數
小數說的是啥?它其實有兩種類型;一種是浮點數類型,一種是定點數類型;

浮點數有兩種
- 單精度浮點型 - float 型
- 雙精度浮點型 - double 型
定點數只有一種 decimal。定點數在 MySQL 內部中以字符串的形式存在,比浮點數更為準確,適合用來表示精度特別高的數據。
浮點數和定點數都可以使用 (M,D) 的方式來表示,M 表示的就是 整數位 + 小數位 的數字,D 表示位于 . 后面的小數。M 也被稱為精度 ,D 被稱為標度。
下面通過示例來演示一下
首先建立一個 test2 表
CREATE TABLE test2 (aId float(6,2) default NULL, bId double(6,2) default NULL,cId decimal(6,2) default NULL)
然后向表中插入幾條數據
insert into test2 values(1234.12,1234.12,1234.12);
這個時候顯示的數據就是

然后再向表中插入一些約束之外的數據
insert into test2 values(1234.123,1234.123,1234.123);

發現插入完成后還顯示的是 1234.12,小數位第三位的值被舍去了。
現在我們把 test2 表中的精度全部去掉,再次插入
alter table test2 modify aId float;
alter table test2 modify bId double;
alter table test2 modify cId decimal;
先查詢一下,發現 cId 舍去了小數位。

然后再次插入 1.23,SQL 語句如下
insert into test2 values(1.23,1.23,1.23);
結果如下

這個時候可以驗證
- 浮點數如果不寫精度和標度,會按照實際的精度值進行顯示
- 定點數如果不寫精度和標度,會按照 decimal(10,0) 來進行操作,如果數據超過了精度和標題,MySQL 會報錯
位類型
對于位類型,用于存放字段值,BIT(M) 可以用來存放多位二進制數,M 的范圍是 1 - 64,如果不寫的話默認為 1 位。
下面我們來掩飾一下位類型
新建一個 test3 表,表中只有一個位類型的字段
create table test3(id bit(1));
然后隨意插入一條數據
insert into test3 values(1);
發現無法查詢出對應結果。

然后我們使用 hex() 和 bin() 函數進行查詢

發現能夠查詢出對應結果。
也就是說當數據插入 test3 時,會首先把數據轉換成為二進制數,如果位數允許,則將成功插入;如果位數小于實際定義的位數,則插入失敗。如果我們向表中插入數據 2
insert into test3 values(2);
那么會報錯

因為 2 的二進制數表示是 10,而表中定義的是 bit(1) ,所以無法插入。
那么我們將表字段修改一下

然后再進行插入,發現已經能夠插入了

日期時間類型
MySQL 中的日期與時間類型,主要包括:YEAR、TIME、DATE、DATETIME、TIMESTAMP,每個版本可能不同。下表中列出了這幾種類型的屬性。

下面分別來介紹一下
YEAR
YEAR 可以使用三種方式來表示
- 用 4 位的數字或者字符串表示,兩者效果相同,表示范圍 1901 - 2155,插入超出范圍的數據會報錯。
- 以 2 位字符串格式表示,范圍為 ‘00’~‘99’。‘00’~‘69’ 表示 2000~2069,‘70’~‘99’ 表示1970~1999。‘0’ 和 ‘00’ 都會被識別為 2000,超出范圍的數據也會被識別為 2000。
- 以 2 位數字格式表示,范圍為 1~99。1~69 表示 2001~2069, 70~99 表示 1970~1999。但 0 值會被識別為0000,這和 2 位字符串被識別為 2000 有所不同
下面我們來演示一下 YEAR 的用法,創建一個 test4 表
create table test4(id year);
然后我們看一下 test4 的表結構

默認創建的 year 就是 4 位,下面我們向 test4 中插入數據
insert into test4 values(2020),('2020');
然后進行查詢,發現表示形式是一樣的

使用兩位字符串來表示
delete from test4;
insert into test4 values ('0'),('00'),('11'),('88'),('20'),('21');

使用兩位數字來表示
delete from test4;
insert into test4 values (0),(00),(11),(88),(20),(21);

發現只有前兩項不一樣。
TIME
TIME 所表示的范圍和我們預想的不一樣
我們把 test4 改為 TIME 類型,下面是 TIME 的示例
alter table test4 modify id TIME;
insert into test4 values ('15:11:23'),('20:13'),('2 11:11'),('3 05'),('33');
結果如下

DATE
DATE 表示的類型有很多種,下面是 DATE 的幾個示例
create table test5 (id date);
查看一下 test5 表

然后插入部分數據
insert into test5 values ('2020-06-13'),('20200613'),(20200613);

DATE 的表示一般很多種,如下所示 DATE 的所有形式
- 'YYYY-MM-DD'
- 'YYYYMMDD'
- YYYYMMDD
- 'YY-MM-DD'
- 'YYMMDD'
- YYMMDD
DATETIME
DATETIME 類型,包含日期和時間部分,可以使用引用字符串或者數字,年份可以是 4 位也可以是 2 位。
下面是 DATETIME 的示例
create table test6 (id datetime);
insert into test4 values ('2020-06-13 11:11:11'),(20200613111111),('20200613111111'),(20200613080808);

TIMESTAMP
TIMESTAMP 類型和 DATETIME 類型的格式相同,存儲 4 個字節(比DATETIME少),取值范圍比 DATETIME 小。
下面來說一下各個時間類型的使用場景
- 一般表示年月日,通常用 DATE 類型;
- 用來表示時分秒,通常用 TIME 表示;
- 年月日時分秒 ,通常用 DATETIME 來表示;
- 如果需要插入的是當前時間,通常使用 TIMESTAMP 來表示,TIMESTAMP 值返回后顯示為 YYYY-MM-DD HH:MM:SS 格式的字符串,
- 如果只表示年份、則應該使用 YEAR,它比 DATE 類型需要更小的空間。
每種日期類型都有一個范圍,如果超出這個范圍,在默認的 SQLMode 下,系統會提示錯誤,并進行零值存儲。
下面來解釋一下 SQLMode 是什么
MySQL 中有一個環境變量是 sql_mode ,sql_mode 支持了 MySQL 的語法、數據校驗,我們可以通過下面這種方式來查看當前數據庫使用的 sql_mode
select @@sql_mode;
一共有下面這幾種模式

字符串類型
MySQL 提供了很多種字符串類型,下面是字符串類型的匯總

下面我們對這些數據類型做一個詳細的介紹
CHAR 和 VARCHAR 類型
CHAR 和 VARCHAR 類型很相似,導致很多同學都會忽略他們之間的差別,首先他倆都是用來保存字符串的數據類型,他倆的主要區別在于存儲方式不同。CHAR 類型的長度就是你定義多少顯示多少。占用 M 字節,比如你聲明一個 CHAR(20) 的字符串類型,那么每個字符串占用 20 字節,M 的取值范圍是 0 - 255。VARCHAR 是可變長的字符串,范圍是 0 - 65535,在字符串檢索的時候,CHAR 會去掉尾部的空格,而 VARCHAR 會保留這些空格。下面是演示例子
create table vctest1 (vc varchar(6),ch char(6));
insert into vctest1 values("abc ","abc ");
select length(vc),length(ch) from vctest1;
結果如下

可以看到 vc 的字符串類型是 varchar ,長度是 5,ch 的字符串類型是 char,長度是 3。可以得出結論,varchar 會保留最后的空格,char 會去掉最后的空格。
BINARY 和 VARBINARY 類型
BINARY 和 VARBINARY 與 CHAR 和 VARCHAR 非常類似,不同的是它們包含二進制字符串而不包含非二進制字符串。BINARY 與 VARBINARY 的最大長度和 CHAR 與 VARCHAR 是一樣的,只不過他們是定義字節長度,而 CHAR 和 VARCHAR 對應的是字符長度。
BLOB 類型
BLOB 是一個二進制大對象,可以容納可變數量的數據。有 4 種 BLOB 類型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區別在于可容納存儲范圍不同。
TEXT 類型
有 4 種 TEXT 類型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應的這 4 種 BLOB 類型,可存儲的最大長度不同,可根據實際情況選擇。
ENUM 類型
ENUM 我們在 JAVA 中經常會用到,它表示的是枚舉類型。它的范圍需要在創建表時顯示指定,對 1 - 255 的枚舉需要 1 個字節存儲;對于 255 - 65535 的枚舉需要 2 個字節存儲。ENUM 會忽略大小寫,在存儲時都會轉換為大寫。
SET 類型
SET 類型和 ENUM 類型有兩處不同
- 存儲方式
SET 對于每 0 - 8 個成員,分別占用 1 個字節,最大到 64 ,占用 8 個字節
- Set 和 ENUM 除了存儲之外,最主要的區別在于 Set 類型一次可以選取多個成員,而 ENUM 則只能選一個。