MySQL 5.7常見數據類型
——《深入淺出MySQL（第二版）》筆記
數值類型
| 整數類型 | 字節 | 最小值 | 最大值 |
|---|---|---|---|
| TINYINT | 1 | 有符號 -128；無符號 0 | 有符號 127；無符號 255 |
| SMALLINT | 2 | 有符號 -2^16；無符號 0 | 有符號 2^16-1；無符號 2^17-1 |
| MEDIUMINT | 3 | 有符號 -2^24；無符號 0 | 有符號 2^24-1；無符號 2^25-1 |
| INT\INTEGER | 4 | 有符號 -2^32；無符號 0 | 有符號 2^32-1；無符號 2^33-1 |
| BIGINT | 8 | 有符號 -2^64；無符號 0 | 有符號 2^64-1；無符號 2^65-1 |
| 浮點類型 | 字節 | 最大值 | 最小值 |
|---|---|---|---|
| FLOAT | 4 | ||
| DOUBLE | 8 |
| 定點數類型 | 字節 | 描述 |
|---|---|---|
| DEC(M,D)\DECIMAL(M,D) | M+2 | 最大值取值范圍與DOUBLE相同，給定DECIMAL的有效取值范圍由M和D決定 |
| 位類型 | 字節 | 最小值 | 最大值 |
|---|---|---|---|
| BIT(M) | 1~8 | BIT(1) | BIT(8) |
整數類型
對於整數類型，MySQL支持在類型名稱後面使用(n)的方式指定顯示寬度，例如int(5)表示當數值寬度小於5位時，在數字前面填滿寬度，如果不顯示指定寬度則默認為int(11)。一般配合zerofill使用，就是用’0’填充。
示例：
--創建表t1，有id1和id2兩個字段，指定數值寬度分別為int和int(5) mysql> create table t1 (id1 int,id2 int(5)); mysql> desc t1;
--在id1和id2中都插入數值1 mysql> insert into t1 values (1,1); mysql> select * from t1;
--分別給id1和id2字段增加zerofill參數 mysql> alter table t1 modify id1 int zerofill; mysql> alter table t1 modify id2 int(5) zerofill;
設置瞭寬度限制後，如果插入大於寬度限制的值，不會對插入的數據有任何影響，還是按照類型的書記精度進行保存。這時，寬度格式實際已經沒有意義，左邊不會再填充任何的“0”字符。
--向表t1的id1中插入1，id2中插入12345678 mysql> insert into t1 values(1,12345678); mysql> select * from t1;
所有的整數類型都有一個可選屬性UNSIGNED（無符號），如果需要在字段裡面保存非負數或者需要較大的上限值，可以用此選項。而當一個列指定為zerofill，則MySQL自動為該列添加UNSIGNED屬性。
另外，整數類型還有一個獨有的AUTO_INCREMENT屬性，表示該列的值是自增型。AUTO_INCREMENT值一般從1開始，每行增加1。在插入NULL到一個AUTO_INCREMENT列時，MySQL插入一個比該列當前最大值大1的值。一個表中最多隻能有一個列是AUTO_INCREMENT的。
對於任何想要使用AUTO_INCREMENT的列，應該定義為NOT NULL，並定義為UNIQUE。
浮點類型
對於小數的表示，MySQL分為浮點數和定點數兩種。浮點數包括float（單精度）和double（雙精度），而定點數隻有decimal一種。定點數在MySQL內部以字符串形式存放，比浮點數更精確，適合用來表示貨幣等精度高的數據。
浮點數和定點數都可以用類型名稱後加”(M,D)”的方式來進行表示，”(M,D)”表示該值一共顯示M位，小數有D位。MySQL保存浮點值時對超出位采用的是四舍五入的方式。因此如果在float(5,3)中插入123.006，則保存的值為123.01。float和double在不指定精度時，默認按照實際硬件和操作系統來決定；而decimal不指定時，默認整數位為10，小數位為0。
示例：
--創建表tf，分別將id1，id2，id3字段設置為float(5,2),double(5,2),decimal(5,2) mysql> create table tf( id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
--向表中三個字段分別插入數據1.23 mysql> insert into tf values(1.23,1.23,1.23);
--向表中分別插入數據1234.005 mysql> insert into tf values(1234.005,1234.005,1234.005);
--向表中分別插入數據123.005 mysql> insert into tf values(123.005,123.005,123.005);
--向表中分別插入數據123.006,123.006,123.004 mysql> insert into tf values(123.006,123.006,123.004);
--去掉表tf字段的精度，並重新插入1.23 mysql> alter table tf modify id1 float; mysql> alter table tf modify id2 double; mysql> alter table tf modify id3 decimal; mysql> insert into tf values(1.23,1.23,1.23); mysql> desc tf;
--向表中分別插入1.234567123321,1.234567123321123321,1.23234233 mysql> insert into tf values(1.234567123321,1.234567123321123321,1.23234233);
位類型
對於BIT類型，用於存放位字段值，BIT(M)可以用來存放多位二進制數，M范圍從1~64，如果不寫，默認為1位。而直接使用SELECT命令不會看到查詢結果，需使用bin()（顯示為二進制）函數或者hex()（顯示為16進制）函數進行讀取。
示例：
--創建表tb，定義字段id1為bit(1),id2字段為bit mysql> create table tb(id1 bit(1),id2 bit);
--向tb中插入1,1 mysql> insert into tb values(1,1); mysql> select * from tb; mysql> select bin(id1),hex(id2) from tb;
數據插入bit兩類型字段時，首先轉換為二進制，如果位數允許，則成功插入；如果位數超出定義的位數，則插入失敗。
時間類型
MySQL中由多種數據類型可以用於日期和時間的表示，下表是MySQL5.0中所有支持的日期和時間類型。
| 日期和時間類型 | 字節 | 最小值 | 最大值 |
|---|---|---|---|
| DATE | 4 | 1000-01-01 | 9999-12-31 |
| DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
| TIMESTAMP | 4 | 19700101080001 | 2038年某個時刻 |
| TIME | 3 | -838:59:59 | 838:59:59 |
| YEAR | 1 | 1901 | 2155 |
每種時間類型都有一個有效范圍，如果超出這個范圍，在默認的SQLMode下，系統會進行錯誤提示，並將以零值來進行存儲。
數據類型
零值表示DATE0000-00-00DATETIME0000-00-00 00:00:00TIMESTAMP00000000000000TIME00:00:00YEAR0000
--創建表tdt mysql> create table tdt(dt date, dttm datetime, tms timestamp,tm time,yr year);
--使用now()函數向表中插入當前時間 mysql> insert into tdt values(now(),now(),now(),now(),now() );
--向tms中插入NULL，MySQL會自動賦值系統時間 mysql> insert into tdt(tms) values(null);
MySQL隻會給表中第一個TIMESTAMP字段賦值系統時間，如果有其他的，則賦值為0值。
--修改表tdt，增加TIMESTAMP類型的tms1列，並給tms，tms1賦值null mysql> alter table tdt add tms1 timestamp; mysql> desc tdt; mysql> insert into tdt(tms,tms1) values(null,null);
TIMESTAMP有一個重要特點，就是和失去相關。當插入提起時，會先轉換為本地時區後存放；而從數據庫取出時，需要將日期轉換為本地時區後顯示。
--創建表tdt1，包含字段tms，dt mysql> create table tdt1(tms timestamp,dt datetime);
--查看當前時區，並向表tdt1中插入系統時間 mysql> show variables like 'time_zone'; mysql> insert into tdt1 values(now(),now());
可以發現，時區的值為SYSTEM，這個值默認是和主機的時區一致的，因為處在中國，這裡實際是東八區（+8:00）。
--修改時區為東九區，再次查看tdt1中的時間 mysql> set time_zone='+9:00';
字符串類型
MySQL中提供瞭多種對字符串數據的存儲類型，不同版本有所差異。以5.0為例，MySQL包括瞭CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等多種。
| 數據類型 | 描述即存儲 |
|---|---|
| CHAR(M) | M為0~255之間的整數 |
| VARCHAR(M) | M為0~65535之間的整數，值的長度+1個字節 |
| TINYBLOB | 允許長度0~255字節，值的長度+1個字節 |
| BLOB | 允許長度0~65535字節，值的長度+2個字節 |
| MEDIUMBLOB | 允許長度0~167772150字節，值的長度+3個字節 |
| LONGBLOB | 允許長度0~4294967295字節，值的長度+4個字節 |
| TINYTEXT | 允許長度0~255字節，值的長度+1個字節 |
| TEXT | 允許長度0~65535字節，值的長度+2個字節 |
| MEDIUMTEXT | 允許長度0~167772150字節，值的長度+3個字節 |
| LONGTEXT | 允許長度0~4294967295字節，值的長度+4個字節 |
| VARBINARY(M) | 允許長度0~M個字節的變長字節字符串，值的長度+1個字節 |
| BINARY(M) | 允許長度0~M個字節的定長字節字符串 |
方法
CHAR和VARCHAR
兩者很類似，都用來保存MySQL中較短的字符串。二者主要區別在於存儲方式的不同：CHAR列的長度固定為創建表時聲明的長度；而VARCHAR列中的值為可變長字符串。在檢索時，CHAR會刪除尾部的空格，而VARCHAR不會。
--創建表tc，包含兩個字段ch（char(6)）和vc（varchar(6)）
mysql> create table tc (ch char(6),vc varchar(6));
--向ch和vc字段插入'abc '
mysql> insert into tc values('abc ','abc ');
--查詢字段長度
mysql> select length(ch),length(vc) from tc;
BINARY和VARBINARY
類似於CHAR和VARCHAR，不同的是它們存儲的是二進制的字符串。
--創建表tbc，包含字段bc（binary(6)）、vbc（varbinary(6)）
mysql> create table tbc (bc binary(6),vbc varbinary(6));
--向表中插入數據('a ','a ')
mysql> insert into tbc values('a ','a ');
--查看字段值長度
mysql> select length(bc),length(vbc) from tbc;
當保存BINARY值時，MySQL通過在值的最後填充0x00（零字節）以達到指定的字段定義長度。
--通過hex()函數查看tbc中數據的保存 mysql> select length(bc),length(vbc) from tbc;
ENUM類型
枚舉類型，它的值范圍需要在創建表時通過枚舉方式顯示指定，對1255個成員的枚舉，需要1個字節存儲；對於25665535個成員，需要2個字節存儲。最多允許有65535個成員。
--創建表te，包含字段f1（enum('a','b','c')）
mysql> create table te(f1 enum('a','b','c'));
--向表中插入幾條記錄
mysql> insert into te values('a'),('B'),('3'),(null);
ENUM是忽略大小寫的。還支持使用下標（從1開始，下標越界時報錯）的方式插入數據。特殊值'0'表示一個空值。
SET類型
SET和ENUM非常類似，也是一個字符串對象，裡面可以包含0~64個成員。根據成員個數不同，存儲也有所不同。
1~8個成員的集合，占1個字節
9~16個成員的集合，占2個字節
17~24個成員的集合，占3個字節
25~32個成員的集合，占4個字節
32~64個成員的集合，占8個字節
而且，SET類型一次可以選擇多個成員。
--創建表ts，包含字段f1（set()）
mysql> create table ts (f1 set('a','b','c','d'));
mysql> insert into ts values('a,b'),('a,d'),('b,c,d');
--向表中插入數據('a,b,a,c,d,d')
mysql> insert into ts values('a,b,a,c,d,d');
SET類型可以從允許值集合中選擇任意個元素進行組合，所以對於輸入的值隻要在允許值的組合范圍內，都可以正確的記錄到SET類型的列中。對於超出允許范圍的值，報錯。而有重復成員的集合，將自動去重。
到此這篇關於MySQL 5.7常見數據類型的文章就介紹到這瞭,更多相關MySQL數據類型內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！
推薦閱讀：
- MySQL 數據類型詳情
- Java面試題沖刺第十三天–數據庫(3)
- MySQL中建表與常見的類型設計陷阱詳解
- MySQL基礎快速入門知識總結(附思維導圖)
- 基於PostgreSQL和mysql數據類型對比兼容