詳解MySQL資料型別

原文地址http://www.cnblogs.com/xrq730/p/5260294.html，轉載請註明出處，謝謝！

 

前言

很久沒寫文章，也有博友在我的有些文章中留言，希望我可以寫一些文章，公司專案一直很忙，但是每天也儘量騰出一些時間寫一些東西，主要針對工作中一些常用的知識點系統性的梳理（可能我們在工作中只是純粹的使用而已，不會去進行總結、歸納）。

本文寫的內容是MySQL資料型別，之前寫MySQL系列文章的時候一直忽略的一個知識點，現在想來，我們學習一門語言，無非從兩個方面入手：

• 基本語法，有了語法，我們才可以組織邏輯
• 資料型別，即在特定場景下選擇合適的資料型別，到底是用整型還是浮點型還是字串，每種資料機構佔多少位元組，最大值是多少。這點只針對強型別的語言，像js這種弱型別的語言，是不需要考慮這一點的

希望通過一篇文章的梳理，可以把MySQL資料結構這塊都歸納清楚。

 

整型

先從最基本的資料型別整型說起，首先用一張表格歸納一下：

資料型別	位元組數	帶符號最小值	帶符號最大值	不帶符號最小值	不帶符號最大值
TINYINT	1	-128	127	0	255
SMALLINT	2	-32768	32767	0	65535
MEDIUMINT	3	-8388608	8388607	0	16777215
INT	4	-2147483648	2147483647	0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807	0	18446744073709551616

即使是帶符號的BIGINT，其實也已經是一個天文數字了，什麼概念，9223372036854775807我們隨便舉下例子：

• 以byte為例可以表示8589934592GB-->8388608TB-->8192PB
• 以毫秒為例可以表示292471208年

所以從實際開發的角度，我們一定要為合適的列選取合適的資料型別，即到底用不用得到這種資料型別？舉個例子：

• 一個列舉欄位明明只有0和1兩個列舉值，選用TINYINT就足夠了，但在開發場景下卻使用了BIGINT，這就造成了資源浪費
• 簡單計算一下，假使該資料表中有100W資料，那麼總共浪費了700W位元組也就是6.7M左右，如果更多的表這麼做了，那麼浪費的更多

要知道，MySQL本質上是一個儲存，以Java為例，可以使用byte型別的地方使用了long型別問題不大，因為絕大多數的物件在程式中都是短命物件，方法執行完畢這塊記憶體區域就被釋放了，7個位元組實際上不存在浪不浪費一說。但是MySQL作為一個儲存，8位元組的BIGINT放那兒就放那兒了，佔據的空間是實實在在的。

最後舉個例子：

drop table if exists test_tinyint;
create table test_tinyint (
    num tinyint
) engine=innodb charset=utf8;

insert into test_tinyint values(-100);
insert into test_tinyint values(255);

執行第7行的程式碼時候報錯"Out of range value for column 'num' at row 1"，即很清楚的我們可以看到插入的數字範圍越界了，這也同樣反映出MySQL中整型預設是帶符號的。

把第3行的num欄位定義改為"num tinyint unsigned"第7的插入就不會報錯了，但是第6行的插入-100又報錯了，因為無符號整型是無法表示負數的。

 

整型（N）形式

在開發中，我們會碰到有些定義整型的寫法是int(11)，這種寫法從我個人開發的角度看我認為是沒有多大用，不過作為一個知識點做一下講解吧。

int(N)我們只需要記住兩點：

• 無論N等於多少，int永遠佔4個位元組
• N表示的是顯示寬度，不足的用0補足，超過的無視長度而直接顯示整個數字，但這要整型設定了unsigned zerofill才有效

下面舉個例子，寫一段SQL：

drop table if exists test_int_width;
create table test_int_width (
    a int(5),
    b int(5) unsigned,
    c int(5) unsigned zerofill,
    d int(8) unsigned zerofill
) engine=innodb charset=utf8;

insert into test_int_width values(1, 1, 1, 1111111111);

select * from test_int_width;

從上面的兩點，我們應該預期結果應該是1,1,00001,1111111111

我們看一下結果：

不符合預期是吧，因為這個問題我也有過困擾，後來查了一下貌似是Navicat工具本身的問題，我們使用控制檯就不會有這個問題了：

不過實際工作場景中反正我是沒有碰到過指定zerofill的，也不知道具體應用場景，如果有使用這種寫法的朋友可以留言告知具體在哪種場景下用到了這種寫法。

 

浮點型

整型之後，下面是浮點型，在MySQL中浮點型有兩種，分別為float、double，它們三者用一張表格總結一下：

資料型別	位元組數	備註
float	4	單精度浮點型
double	8	雙精度浮點型

下面還是用SQL來簡單看一下float和double型資料，以float為例，double同理：

drop table if exists test_float;
create table test_float (
    num float(5, 2)
) engine=innodb charset=utf8;

insert into test_float values(1.233);
insert into test_float values(1.237);
insert into test_float values(10.233);
insert into test_float values(100.233);
insert into test_float values(1000.233);
insert into test_float values(10000.233);
insert into test_float values(100000.233);

select * from test_float;

顯示結果為：

從這個結果我們總結一下float(M,D)、double(M、D)的用法規則：

• D表示浮點型資料小數點之後的精度，假如超過D位則四捨五入，即1.233四捨五入為1.23，1.237四捨五入為1.24
• M表示浮點型資料總共的位數，D=2則表示總共支援五位，即小數點前只支援三位數，所以我們並沒有看到1000.23、10000.233、100000.233這三條資料的插入，因為插入都報錯了

當我們不指定M、D的時候，會按照實際的精度來處理。

 

定點型

介紹完float、double兩種浮點型，我們介紹一下定點型的資料型別decimal型別，有了浮點型為什麼我們還需要定點型？寫一段SQL看一下就明白了：

drop table if exists test_decimal;
create table test_decimal (
    float_num float(10, 2),
    double_num double(20, 2),
    decimal_num decimal(20, 2)
) engine=innodb charset=utf8;

insert into test_decimal values(1234567.66, 1234567899000000.66, 1234567899000000.66);
insert into test_decimal values(1234567.66, 12345678990000000.66, 12345678990000000.66);

執行結果為：

看到float、double型別存在精度丟失問題，即寫入資料庫的資料未必是插入資料庫的資料，而decimal無論寫入資料中的資料是多少，都不會存在精度丟失問題，這就是我們要引入decimal型別的原因，decimal型別常見於銀行系統、網際網路金融系統等對小數點後的數字比較敏感的系統中。

最後講一下decimal和float/double的區別，個人總結主要體現在兩點上：

• float/double在db中儲存的是近似值，而decimal則是以字串形式進行儲存的
• decimal(M,D)的規則和float/double相同，但區別在float/double在不指定M、D時預設按照實際精度來處理而decimal在不指定M、D時預設為decimal(10, 0)

 

日期型別

接著我們看一下MySQL中的日期型別，MySQL支援五種形式的日期型別：date、time、year、datetime、timestamp，用一張表格總結一下這五種日期型別：

資料型別	位元組數	格式	備註
date	3	yyyy-MM-dd	儲存日期值
time	3	HH:mm:ss	儲存時分秒
year	1	yyyy	儲存年
datetime	8	yyyy-MM-dd HH:mm:ss	儲存日期+時間
timestamp	4	yyyy-MM-dd HH:mm:ss	儲存日期+時間，可作時間戳

下面我們還是用SQL來驗證一下：

drop table if exists test_time;
create table test_time (
    date_value date,
    time_value time,
    year_value year,
    datetime_value datetime,
    timestamp_value timestamp
) engine=innodb charset=utf8;

insert into test_time values(now(), now(), now(), now(), now());

看一下插入後的結果：

MySQL的時間型別的知識點比較簡單，這裡重點關注一下datetime與timestamp兩種型別的區別：

• 上面列了，datetime佔8個位元組，timestamp佔4個位元組
• 由於大小的區別，datetime與timestamp能儲存的時間範圍也不同，datetime的儲存範圍為1000-01-01 00:00:00——9999-12-31 23:59:59，timestamp儲存的時間範圍為19700101080001——20380119111407
• datetime預設值為空，當插入的值為null時，該列的值就是null；timestamp預設值不為空，當插入的值為null的時候，mysql會取當前時間
• datetime儲存的時間與時區無關，timestamp儲存的時間及顯示的時間都依賴於當前時區

在實際工作中，一張表往往我們會有兩個預設欄位，一個記錄建立時間而另一個記錄最新一次的更新時間，這種時候可以使用timestamp型別來實現：

create_time timestamp default current_timestamp comment "建立時間",
update_time timestamp default current_timestamp on update current_timestamp comment "修改時間",

 

char和varchar型別

最後看一下常用到的字元型，說到MySQL字元型，我們最熟悉的應該就是char和varchar了，關於char和varchar的對比，我總結一下：

1. char是固定長度字串，其長度範圍為0~255且與編碼方式無關，無論字元實際長度是多少，都會按照指定長度儲存，不夠的用空格補足；varchar為可變長度字串，在utf8編碼的資料庫中其長度範圍為0~21844
2. char實際佔用的位元組數即儲存的字元所佔用的位元組數，varchar實際佔用的位元組數為儲存的字元+1或+2或+3
3. MySQL處理char型別資料時會將結尾的所有空格處理掉而varchar型別資料則不會

關於第一點、第二點，稍後專門開一個篇幅解釋，關於第三點，寫一下SQL驗證一下：

drop table if exists test_string;
create table test_string (
    char_value char(5),
    varchar_value varchar(5)
) engine=innodb charset=utf8;

insert into test_string values('a', 'a');
insert into test_string values(' a', ' a');
insert into test_string values('a ', 'a ');
insert into test_string values(' a ', ' a ');

使用length函式來看一下結果：

驗證了我們的結論，char型別資料並不會取最後的空格。

 

varchar型資料佔用空間大小及可容納最大字串限制探究

接上一部分，我們這部分來探究一下varchar型資料實際佔用空間大小是如何計算的以及最大可容納的字串為多少，首先要給出一個結論：這部分和具體編碼方式有關，且MySQL版本我現在使用的是5.7，當然5.0之後的都是可以的。

先寫一段SQL建立表，utf8的編碼格式：

drop table if exists test_varchar;
create table test_varchar (
    varchar_value varchar(100000)
) engine=innodb charset=utf8;

執行報錯：

Column length too big for column 'varchar_value' (max = 21845); use BLOB or TEXT instead

按照提示，我們把大小改為21845，執行依然報錯：

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

改為21844就不會有問題，因此在utf8編碼下我們可以知道varchar(M)，M最大=21844。那麼gbk呢：

drop table if exists test_varchar;
create table test_varchar (
    varchar_value varchar(100000)
) engine=innodb charset=gbk;

同樣的報錯：

Column length too big for column 'varchar_value' (max = 32767); use BLOB or TEXT instead

把大小改為32766，也是和utf8編碼格式一樣的報錯：

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

可見gbk的編碼格式下，varchar(M)最大的M=32765，那麼為什麼會有這樣的區別呢，分點詳細解釋一下：

• MySQL要求一個行的定義長度不能超過65535即64K
• 對於未指定varchar欄位not null的表，會有1個位元組專門表示該欄位是否為null
• varchar(M)，當M範圍為0<=M<=255時會專門有一個位元組記錄varchar型字串長度，當M>255時會專門有兩個位元組記錄varchar型字串的長度，把這一點和上一點結合，那麼65535個位元組實際可用的為65535-3=65532個位元組
• 所有英文無論其編碼方式，都佔用1個位元組，但對於gbk編碼，一個漢字佔兩個位元組，因此最大M=65532/2=32766；對於utf8編碼，一個漢字佔3個位元組，因此最大M=65532/3=21844，上面的結論都成立
• 舉一反三，對於utfmb4編碼方式，1個字元最大可能佔4個位元組，那麼varchar(M)，M最大為65532/4=16383，可以自己驗證一下

同樣的，上面是表中只有varchar型資料的情況，如果表中同時存在int、double、char這些資料，需要把這些資料所佔據的空間減去，才能計算varchar(M)型資料M最大等於多少。

 

varchar、text和blob

最後講一講text和blob兩種資料型別，它們的設計初衷是為了儲存大資料使用的，因為之前說了，MySql單行最大資料量為64K。

先說一下text，text和varchar是一組既有區別又有聯絡的資料型別，其聯絡在於當varchar(M)的M大於某些數值時，varchar會自動轉為text：

• M>255時轉為tinytext
• M>500時轉為text
• M>20000時轉為mediumtext

所以過大的內容varchar和text沒有區別，同事varchar(M)和text的區別在於：

• 單行64K即65535位元組的空間，varchar只能用63352/65533個位元組，但是text可以65535個位元組全部用起來
• text可以指定text(M)，但是M無論等於多少都沒有影響
• text不允許有預設值，varchar允許有預設值

varchar和text兩種資料型別，使用建議是能用varchar就用varchar而不用text（儲存效率高），varchar(M)的M有長度限制，之前說過，如果大於限制，可以使用mediumtext（16M）或者longtext（4G）。

至於text和blob，簡單過一下就是text儲存的是字串而blob儲存的是二進位制字串，簡單說blob是用於儲存例如圖片、音視訊這種檔案的二進位制資料的。