• varchar和text兩種數(shù)據(jù)類型，使用建議是能用varchar就用varchar而不用text（存儲效率高），varchar(M)的M有長度限制，之前說過，如果大于限制，可以使用mediumtext（16M）或者longtext（4G）。
• 至于text和blob，簡單過一下就是text存儲的是字符串而blob存儲的是二進制字符串，簡單說blob是用于存儲例如圖片、音視頻這種文件的二進制數(shù)據(jù)的。
• ?
• float/double在db中存儲的是近似值，而decimal則是以字符串形式進行保存的
• decimal(M,D)的規(guī)則和float/double相同，但區(qū)別在float/double在不指定M、D時默認按照實際精度來處理而decimal在不指定M、D時默認為decimal(10, 0)
• datetime存儲的時間與時區(qū)無關(guān)，timestamp存儲的時間及顯示的時間都依賴于當前時區(qū)
• MySQL要求一個行的定義長度不能超過65535即64K
• 對于未指定varchar字段not null的表，會有1個字節(jié)專門表示該字段是否為null
• varchar(M)，當M范圍為0<=M<=255時會專門有一個字節(jié)記錄varchar型字符串長度，當M>255時會專門有兩個字節(jié)記錄varchar型字符串的長度，把這一點和上一點結(jié)合，那么65535個字節(jié)實際可用的為65535-3=65532個字節(jié)
• 所有英文無論其編碼方式，都占用1個字節(jié)，但對于gbk編碼，一個漢字占兩個字節(jié)，因此最大M=65532/2=32766；對于utf8編碼，一個漢字占3個字節(jié)，因此最大M=65532/3=21844，上面的結(jié)論都成立
• 舉一反三，對于utfmb4編碼方式，1個字符最大可能占4個字節(jié)，那么varchar(M)，M最大為65532/4=16383，可以自己驗證一下

  ---

  ?

前言

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

本文寫的內(nèi)容是MySQL數(shù)據(jù)類型，之前寫MySQL系列文章的時候一直忽略的一個知識點，現(xiàn)在想來，我們學習一門語言，無非從兩個方面入手：

• 基本語法，有了語法，我們才可以組織邏輯
• 數(shù)據(jù)類型，即在特定場景下選擇合適的數(shù)據(jù)類型，到底是用整型還是浮點型還是字符串，每種數(shù)據(jù)機構(gòu)占多少字節(jié)，最大值是多少。這點只針對強類型的語言，像js這種弱類型的語言，是不需要考慮這一點的

希望通過一篇文章的梳理，可以把MySQL數(shù)據(jù)結(jié)構(gòu)這塊都歸納清楚。

?

整型

先從最基本的數(shù)據(jù)類型整型說起，首先用一張表格歸納一下：

數(shù)據(jù)類型	字節(jié)數(shù)	帶符號最小值	帶符號最大值	不帶符號最小值	不帶符號最大值
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，其實也已經(jīng)是一個天文數(shù)字了，什么概念，9223372036854775807我們隨便舉下例子：

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

所以從實際開發(fā)的角度，我們一定要為合適的列選取合適的數(shù)據(jù)類型，即到底用不用得到這種數(shù)據(jù)類型？舉個例子：

• 一個枚舉字段明明只有0和1兩個枚舉值，選用TINYINT就足夠了，但在開發(fā)場景下卻使用了BIGINT，這就造成了資源浪費
• 簡單計算一下，假使該數(shù)據(jù)表中有100W數(shù)據(jù)，那么總共浪費了700W字節(jié)也就是6.7M左右，如果更多的表這么做了，那么浪費的更多

要知道，MySQL本質(zhì)上是一個存儲，以Java為例，可以使用byte類型的地方使用了long類型問題不大，因為絕大多數(shù)的對象在程序中都是短命對象，方法執(zhí)行完畢這塊內(nèi)存區(qū)域就被釋放了，7個字節(jié)實際上不存在浪不浪費一說。但是MySQL作為一個存儲，8字節(jié)的BIGINT放那兒就放那兒了，占據(jù)的空間是實實在在的。

最后舉個例子：

1 drop table if exists test_tinyint;
2 create table test_tinyint (
3     num tinyint
4 ) engine=innodb charset=utf8;
5 
6 insert into test_tinyint values(-100);
7 insert into test_tinyint values(255);

執(zhí)行第7行的代碼時候報錯"Out of range value for column 'num' at row 1"，即很清楚的我們可以看到插入的數(shù)字范圍越界了，這也同樣反映出MySQL中整型默認是帶符號的。

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

?

整型（N）形式

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

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

• 無論N等于多少，int永遠占4個字節(jié)
• N表示的是顯示寬度，不足的用0補足，超過的無視長度而直接顯示整個數(shù)字，但這要整型設置了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;

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

我們看一下結(jié)果：

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

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

?

浮點型

整型之后，下面是浮點型，在MySQL中浮點型有兩種，分別為float、double，它們?nèi)哂靡粡埍砀窨偨Y(jié)一下：

數(shù)據(jù)類型	字節(jié)數(shù)	備注
float	4	單精度浮點型
double	8	雙精度浮點型

下面還是用SQL來簡單看一下float和double型數(shù)據(jù)，以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;

顯示結(jié)果為：

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

• D表示浮點型數(shù)據(jù)小數(shù)點之后的精度，假如超過D位則四舍五入，即1.233四舍五入為1.23，1.237四舍五入為1.24
• M表示浮點型數(shù)據(jù)總共的位數(shù)，D=2則表示總共支持五位，即小數(shù)點前只支持三位數(shù)，所以我們并沒有看到1000.23、10000.233、100000.233這三條數(shù)據(jù)的插入，因為插入都報錯了

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

?

定點型

介紹完float、double兩種浮點型，我們介紹一下定點型的數(shù)據(jù)類型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);

?

運行結(jié)果為：

看到float、double類型存在精度丟失問題，即寫入數(shù)據(jù)庫的數(shù)據(jù)未必是插入數(shù)據(jù)庫的數(shù)據(jù)，而decimal無論寫入數(shù)據(jù)中的數(shù)據(jù)是多少，都不會存在精度丟失問題，這就是我們要引入decimal類型的原因，decimal類型常見于銀行系統(tǒng)、互聯(lián)網(wǎng)金融系統(tǒng)等對小數(shù)點后的數(shù)字比較敏感的系統(tǒng)中。

最后講一下decimal和float/double的區(qū)別，個人總結(jié)主要體現(xiàn)在兩點上：

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

?

日期類型

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

數(shù)據(jù)類型	字節(jié)數(shù)	格式	備注
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());

?

看一下插入后的結(jié)果：

MySQL的時間類型的知識點比較簡單，這里重點關(guān)注一下datetime與timestamp兩種類型的區(qū)別：

• 上面列了，datetime占8個字節(jié)，timestamp占4個字節(jié)
• 由于大小的區(qū)別，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存儲的時間與時區(qū)無關(guān)，timestamp存儲的時間及顯示的時間都依賴于當前時區(qū)

在實際工作中，一張表往往我們會有兩個默認字段，一個記錄創(chuàng)建時間而另一個記錄最新一次的更新時間，這種時候可以使用timestamp類型來實現(xiàn)：

create_time timestamp default current_timestamp comment "創(chuàng)建時間",
update_time timestamp default current_timestamp on update current_timestamp comment "修改時間",

?

char和varchar類型

最后看一下常用到的字符型，說到MySQL字符型，我們最熟悉的應該就是char和varchar了，關(guān)于char和varchar的對比，我總結(jié)一下：

1. char是固定長度字符串，其長度范圍為0~255且與編碼方式無關(guān)，無論字符實際長度是多少，都會按照指定長度存儲，不夠的用空格補足；varchar為可變長度字符串，在utf8編碼的數(shù)據(jù)庫中其長度范圍為0~21844
2. char實際占用的字節(jié)數(shù)即存儲的字符所占用的字節(jié)數(shù)，varchar實際占用的字節(jié)數(shù)為存儲的字符+1或+2或+3
3. MySQL處理char類型數(shù)據(jù)時會將結(jié)尾的所有空格處理掉而varchar類型數(shù)據(jù)則不會

關(guān)于第一點、第二點，稍后專門開一個篇幅解釋，關(guān)于第三點，寫一下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函數(shù)來看一下結(jié)果：

驗證了我們的結(jié)論，char類型數(shù)據(jù)并不會取最后的空格。

?

varchar型數(shù)據(jù)占用空間大小及可容納最大字符串限制探究

接上一部分，我們這部分來探究一下varchar型數(shù)據(jù)實際占用空間大小是如何計算的以及最大可容納的字符串為多少，首先要給出一個結(jié)論：這部分和具體編碼方式有關(guān)，且MySQL版本我現(xiàn)在使用的是5.7，當然5.0之后的都是可以的。

先寫一段SQL創(chuàng)建表，utf8的編碼格式：

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

執(zhí)行報錯：

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

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

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，那么為什么會有這樣的區(qū)別呢，分點詳細解釋一下：

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

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

?

varchar、text和blob

最后講一講text和blob兩種數(shù)據(jù)類型，它們的設計初衷是為了存儲大數(shù)據(jù)使用的，因為之前說了，MySql單行最大數(shù)據(jù)量為64K。

先說一下text，text和varchar是一組既有區(qū)別又有聯(lián)系的數(shù)據(jù)類型，其聯(lián)系在于當varchar(M)的M大于某些數(shù)值時，varchar會自動轉(zhuǎn)為text：

• M>255時轉(zhuǎn)為tinytext
• M>500時轉(zhuǎn)為text
• M>20000時轉(zhuǎn)為mediumtext

所以過大的內(nèi)容varchar和text沒有區(qū)別，同事varchar(M)和text的區(qū)別在于：

• 單行64K即65535字節(jié)的空間，varchar只能用63352/65533個字節(jié)，但是text可以65535個字節(jié)全部用起來
• text可以指定text(M)，但是M無論等于多少都沒有影響
• text不允許有默認值，varchar允許有默認值

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

至于text和blob，簡單過一下就是text存儲的是字符串而blob存儲的是二進制字符串，簡單說blob是用于存儲例如圖片、音視頻這種文件的二進制數(shù)據(jù)的。