柚子快報邀請碼778899分享：數(shù)據(jù)庫 【MySQL】數(shù)據(jù)類型

http://yzkb.51969.com/

序言

?在上篇文章中提及了表的結(jié)構(gòu)的操作，不過關(guān)于表的創(chuàng)建部分，尤其是數(shù)據(jù)類型這一方面該如何設(shè)計并沒有提及，本篇文章就來補充這方面的知識。首先要明確為啥會有數(shù)據(jù)類型，在計算機的世界中本質(zhì)雖然都是二進制，但為了與現(xiàn)實世界接軌，方便進行識別與處理各種各樣的數(shù)據(jù)，比如小數(shù)，整數(shù)，字符串，視頻音頻圖形等二進制數(shù)據(jù)，出現(xiàn)了與之對應(yīng)的數(shù)據(jù)類型。其次數(shù)據(jù)類型可以對數(shù)據(jù)進一步的限制，比如可以規(guī)定數(shù)據(jù)的大小，存儲和讀取的方式，以及表示的范圍等。甚至相同類型還可以進一步的劃分出范圍更小類型，以用來減少空間的消耗?？偠灾?，在數(shù)據(jù)庫中數(shù)據(jù)類型可以幫助我們更好的豐富表結(jié)構(gòu)。

一、理論

此部分對常見的數(shù)據(jù)類型進行羅列和基本說明，方便在使用時進行查看，而對加粗的部分，則為本篇所要重點強調(diào)的部分，會在下文進行詳細地舉例和展示。

數(shù)值類型

比特位，bit(M)，M用于設(shè)置比特位，范圍為1~8，即最大表示64。MySQL中以ASCII碼的形式顯示出來。 整形

tinyint，占用一字節(jié)。smallint，占用兩字節(jié)。mediumint，占用三字節(jié)。int，占用四字節(jié)。bigint，占用八字節(jié)。說明：默認是有符號數(shù)的，無符號數(shù)得在手動設(shè)置時，手動在后面跟上unsigned。 ? 浮點數(shù)

float(M,D)，占用四個字節(jié)。double(M,D)，占用八個字節(jié)。decimal(M,D)，大小取決于定義的M和D，數(shù)學公式為

?

M

2

?

\lceil{ {M} \over {2}} \rceil

?2M??。說明：M指的是數(shù)據(jù)的總長度（不包含小數(shù)點），D指的是小數(shù)位的長度。默認是有符號的，無符號需手動設(shè)置。 ? 布爾，bool，使用0表示假，1表示真。在MySQL中實際使用tinyint(1)代替，1表示的是顯示時數(shù)據(jù)的寬度，即顯示1位。

文本，二進制類型

定長字符串，char(L)，L表示長度，最長為255。變長字符串，varchar(L)，L表示長度，最大字節(jié)數(shù)為65535。二進制數(shù)據(jù)，blob，最多存儲65535字節(jié)，即64KB。還有與之類似的tinyblob，mediunblob，longblob對應(yīng)不同容量的需求。大文本數(shù)據(jù)，text，最多存儲65535字節(jié)，即64KB。還有對應(yīng)不同容量需求的類型，與int，blob雷同。

時間類型

年月日，date，數(shù)據(jù)的格式為Y-M-D，以字符串的形式插入，通常要帶上''，占用三個字節(jié)。年月日時分秒，datetime，數(shù)據(jù)的格式為Y-M-D-H-M-S，同上，占用八個字節(jié)。時間戳，datestamp，一般采用的是從格林1970年1月1日00:00:00開始到當前時間的秒數(shù)，占用四個字節(jié)。

枚舉類型

只枚舉其中一個，enum(E)，E表示的是一個集合，即可能被選擇的所有情況。至少枚舉其中一個，set(E)，同理。

總的來說，分為數(shù)值，文本和二進制，時間，枚舉共四種類型。除此之外還是要再補充一點，即在MySQL中類型名與sql語句中的關(guān)鍵字一樣都是不區(qū)分大小寫的，只是個人認為小寫方便閱讀，所以上述呈現(xiàn)的都是小寫的，具體如何寫根據(jù)個人喜好來即可。

二、實踐

相信各位讀者看完上文的部分之后，已經(jīng)對數(shù)據(jù)類型有了一個整理的框架和基本的認識，那將在下面填充具體的使用細節(jié)，進一步完善與加深對數(shù)據(jù)類型的理解。

1.數(shù)值

tinyint

首先創(chuàng)建一張表，其中存放一個tinyint類型的數(shù)據(jù)，然后向這種表中全列插入

?

128

，

0

，

127

-128，0，127

?128，0，127，查看表中的數(shù)據(jù)。過程如下圖。

通過上圖驗證可以大致得到兩個結(jié)論：tinyint默認是有符號的；tinyint大小是一個字節(jié)，即范圍為

[

?

128

，

127

]

[-128，127]

[?128，127]，因插入邊界值未出錯。

然后再次向表中再次插入數(shù)據(jù)

?

129

，

128

-129，128

?129，128，觀察插入的數(shù)據(jù)不正常會導致什么效果。

通過以上的三條sql語句可以得知：sql語句的執(zhí)行是按照一定的順序運行的，當執(zhí)行到某句出錯就停止了；其次插入非法的值會報錯，在一定程度上反映了數(shù)據(jù)類型具有限制數(shù)據(jù)范圍的作用。

先將表中的數(shù)據(jù)清空，查看是否清空，然后修改表結(jié)構(gòu)中元素的類型為tinyint unsigned，查看修改后的表結(jié)構(gòu)。過程如下圖。

說明：tinyint 改為 unsigned tinyint，數(shù)據(jù)范圍呈現(xiàn)的不是包含的關(guān)系，因此改動時需要處理表中的數(shù)據(jù)，這里處理的動作是清空數(shù)據(jù)。

修改為unsigned類型之后，只改變了數(shù)據(jù)的范圍到[0，255]，其余的屬性并沒有發(fā)生變化。下面插入

128

，

255

128，255

128，255簡單地進行驗證。

除此之外要簡單驗證數(shù)據(jù)范圍是否準確，還需插入

?

1

，

256

-1，256

?1，256兩個非法的值。如下圖。

總而言之，上述實驗強調(diào)的是當插入的數(shù)據(jù)大于規(guī)定的范圍時，MySQL會以錯誤的形式進行呈現(xiàn)，從而限制和通知用戶要插入合法的數(shù)據(jù)，這其實可以被稱為一種簡單的約束。在下一篇文章中，將詳細地展開討論表的約束這一話題，嘿嘿，挖了一個坑。

bit

先添加一個bit類型的數(shù)據(jù)，然后把unsigned int類型的變量從上述表結(jié)構(gòu)中移除出去， 因為表中至少要有一種數(shù)據(jù)，如果這樣可行的話就相當于把表刪除的活搶了，drop table 可不愿意哦！如下圖。

說明：表結(jié)構(gòu)中只有一種數(shù)據(jù)類型進行刪除報錯的效果，這里就不做演示了，感興趣可以將上述兩個alter執(zhí)行的次序倒過來即可查看效果。除此之外，細心一點還可以得到插入bit類型時，沒有標明位數(shù)默認是1。

然后插入兩個數(shù)據(jù)

0

，

1

0，1

0，1，哈哈，好像也只能插入兩位，然后查看顯示效果，如下圖所示。

可以看出，bit類型的數(shù)據(jù)在博主的ubuntu下是按照16進制的方式進行顯示的，不同的機器可能顯示的效果不一樣，具體情況，具體分析即可，不用做過多的糾結(jié)。

說明：bit類型的是無符號的數(shù)據(jù)，不能插入負數(shù)。除此之外，超出邊界的數(shù)據(jù)的測試，感到有些冗余就不再演示了，感興趣可自行驗證。

將bit的位數(shù)增加到8位之后，再插入數(shù)據(jù)

16

，

32

，

64

16，32，64

16，32，64，查看效果，如下圖所示。

總而言之，bit將一個字節(jié)的數(shù)據(jù)以bit為單位再次進行了劃分，其目的是進一步提高空間的利用率，但是在不同的操作系統(tǒng)下針對bit類型的實現(xiàn)細節(jié)可能不相同，比如ubuntu下，是16進制顯示，而Centos下，是ASCII碼顯示，這會讓不方便程序員進行瀏覽。因此根據(jù)實際情況進行權(quán)衡，在對資源利用度要求較高的場景考慮，而不要強硬的追求。

float

將此表結(jié)構(gòu)的數(shù)據(jù)清空，增加一個數(shù)據(jù)長度為4，小數(shù)長度為2，名為fnum的float類型，然后將之前的bit類型刪除，方便之后進行測試，具體過程如下圖。

因為默認是有符號的，再根據(jù)數(shù)據(jù)總長度和小數(shù)長度，可以推斷數(shù)據(jù)范圍為

[

?

99.99

，

99.99

]

[-99.99，99.99]

[?99.99，99.99]，那么插入

?

99.99

，

0

，

99.99

-99.99，0，99.99

?99.99，0，99.99三個代表性的數(shù)據(jù)，然后查看效果，如下圖。

除此之外，小數(shù)一般來說還有一個四舍五入的特點，因此插入數(shù)字的小數(shù)位可以不止兩位，但因為要求兩位小數(shù)，所以最終在表中的數(shù)據(jù)呈現(xiàn)的是兩位小數(shù)，這其實也算是一種小小的約束。那么就可以再插入

?

99.994

，

0.005

，

0.006

，

99.994

-99.994，0.005，0.006，99.994

?99.994，0.005，0.006，99.994進行驗證。

細心的同學就發(fā)現(xiàn)了，在進行四舍五入時，

0.005

0.005

0.005實際上顯示的還是

0.00

0.00

0.00，而不是我們想的

0.01

0.01

0.01，但是對于

0.006

0.006

0.006來說卻遵循著四舍五入的玩法，其實這是由于精度丟失的原因，即在二進制的計算機中要表示十進制的小數(shù)會存在一定程度的誤差。因此可以得出一個結(jié)論，即float并不適用于高精度的場景，而下面所要舉例的decimal就可以對此場景做一個很好的補充說明。

補充：對于無符號的，數(shù)據(jù)總長度為4，小數(shù)位為2的float。因為有數(shù)據(jù)總長度的限制，只能表示

[

0.00

，

99.99

]

[0.00，99.99]

[0.00，99.99]之間的數(shù)據(jù)，相當于有一半的空間被浪費掉了，因此使用無符號時需要使用者考慮到這一點，同理感覺冗余此處就不做演示了。

decimal

將表數(shù)據(jù)清空，將上述float類型改為16位總長度，12位小數(shù)長度的，然后添加與此相同數(shù)據(jù)格式的decimal，如下圖。

然后插入

9876.543210987382

9876.543210987382

9876.543210987382，16位總長度，12位小數(shù)長度的小數(shù)，對比查看效果。

可見，float在小數(shù)點后兩位就開始精度丟失了，而decimal則到最后一位還依舊保持堅挺。因此可以得出大致這么一個結(jié)論，即在應(yīng)用場景小數(shù)范圍，即小數(shù)點后兩位都在兩位之內(nèi)的話使用float，超出兩位的使用decimal。

2.文本、二進制

char

首先創(chuàng)建一個str_test表，里面先放一個長度為2，且為char類型的變量ch，如下圖所示。

然后插入數(shù)據(jù)"nb"，“你好”，看具體的產(chǎn)生的效果。

簡單的討論一下字符，字節(jié)，長度之間的關(guān)系，一個英文字符和一個漢字字符的長度都為1，在utf8編碼下的一個英文字符對應(yīng)著一個字節(jié)，一個漢字字符對應(yīng)著三個字節(jié)?？偠灾L度是對語言單個字符的泛化，讓使用者不用再刻意在意每種語言一個字符的字節(jié)數(shù)。所以上圖中的"你好"，"nb"長度都為2，注意不要跟字節(jié)數(shù)搞混了。因此插入"abc"長度為3的字符串是會報錯的，如下圖所示。

varchar

先清空數(shù)據(jù)，然后添加一個長度為6，varchar類型的變量vch，接著將上述的討論過的類型從表結(jié)構(gòu)中移除，如下圖所示。

請注意varchar的最大字節(jié)數(shù)為65535，而最大長度則要根據(jù)選擇的字符集情況進行計算，實際上varchar中還要使用

3

3

3個字節(jié)表示字符串的屬性信息，方便讀取時解析，因此可用的最大字節(jié)數(shù)為65532。

在UTF-8mb3編碼下一個字符最大為3個字節(jié)，所以最大長度為

65532

÷

3

=

21844

65532 \div 3 = 21844

65532÷3=21844。在GBK編碼下一個字符的最大為2個字節(jié)，所以最大長度為

65532

÷

2

=

32766

65532 \div 2 = 32766

65532÷2=32766。

在下圖中將分別使用UTF-8mb3編碼和GBK編碼進行實驗，首先查看當前的表結(jié)構(gòu)中的字符集，此處為utf8編碼，然后將varchar的最大長度先后修改為

21844

，

21845

21844，21845

21844，21845查看結(jié)果。

很顯然超出邊界時會報錯，且可證明

21844

21844

21844是utf8編碼下的varchar的最大長度。下面將表結(jié)構(gòu)的字符集改為gbk編碼，然后同理將最大長度先后修改為

32766

，

32767

32766，32767

32766，32767查看效果，如下圖。

同理，此處就不過多贅述了，總而言之，所強調(diào)的只有兩點：不同字符集下varchar的最大長度可能不同；varchar需要額外的三個字節(jié)存放字符串的長度和控制信息。

對比

char是定長字符串，即規(guī)定出字符串的最大長度，插入的字符串的大小不管多長都是固定。數(shù)據(jù)的長度都固定時比較適用，比如身份證號，學號等，且方便存取，直接截取固定長度的數(shù)據(jù)即可。但如果不固定且只有數(shù)據(jù)的長度比較分散時，則空間的利用率比較低，不太適用。varchar是變長字符串，規(guī)定出字符串的最大長度，大小取決于實際插入的字符串的大小，不固定，即變長存儲。適用數(shù)據(jù)長度都是比較混亂的情況，可以有效提高空間利用率。但是變長字符串存儲在取數(shù)據(jù)時需要截取出屬性字段進行計算解析出數(shù)據(jù)，因此讀取效率較低。

3.日期

說明：日期此處涉及的不多就一并介紹了，date為表示從年到日的時間，datetime表示從年到秒的時間，timestamp表示時間戳。

首先創(chuàng)建一個date_test的表結(jié)構(gòu)，其中添加t1，t2，t3變量類型分別date，datetime，timestamp，過程如下圖。 然后插入與之對應(yīng)的日期數(shù)據(jù)，時間戳可使用系統(tǒng)命令 date +%s進行查看，同時注意date, datetime插入時應(yīng)以字符串的形式，即數(shù)據(jù)要在兩邊加上‘’，其次時間戳插入要使用from_unixtime()進行轉(zhuǎn)換，具體過程如下圖。

此處關(guān)于時間戳還有一個更有意思的玩法，不過涉及到表的約束，就放到下一篇文章中了，嘿嘿又挖一個坑。

4.枚舉

enum

眾所周知，我有一位故人會唱，跳，rap，籃球，此時我想向他學習這幾門專業(yè)技能，奈何沒有天賦，只能選擇其一進行勤學苦練，只為有朝一日證明我是小~，等等不對差點露餡了，應(yīng)該是坤流人物?；剡^頭來，只選其一那就是enum的專長了。

首先創(chuàng)建一張enum_test的表結(jié)構(gòu)，其中存放著一個kk的enum類型，其中包含著這位故人的所有技能。

請注意上圖中enum是可以放空值的，回過頭來，然后插入一些只包含一位的數(shù)據(jù)唱 , 跳, Rap, 籃球，進行勤學苦練，具體過程如下圖。

除此之外，還可以用數(shù)字的形式進行表示，根據(jù)位置來標記選擇的位置，比如選擇唱就是1，跳就是2，以此類推。下圖會將數(shù)據(jù)清空使用此方式再次插入一遍。

說明：從效率的角度出發(fā)，MySQL實現(xiàn)enum時采用選項標記每個字符串，方便進行查找，最多能夠放65536個選項。

set

在enum的約束下只能選擇一門，但是修煉了不一會，才發(fā)現(xiàn)自己原來是坤門的曠世奇才，于是將目光瞄向我這位故人的其余技能，勢必要將坤門發(fā)揚光大?；剡^頭來，選擇至少一門，那就是set的專長了。

首先把原來表中的數(shù)據(jù)清空，將set類型添加到表結(jié)構(gòu)enum_test中，然后將kk的enum類型從表結(jié)構(gòu)中移除，具體過程如下圖。

然后插入所有選擇三種技能的數(shù)據(jù)的情況，具體過程如下圖。

除此之外，也可以使用數(shù)字的形式插入，與enum略有不同可以選擇多位，所以采用了二進制的方式，對應(yīng)位置選或不選對應(yīng)1/0，比如選擇唱跳Rap，就對應(yīng)著1110，注意二進制要逆轉(zhuǎn)一下，即0111，轉(zhuǎn)換成十進制為7，選擇唱跳籃球，二進制對應(yīng)著1011，轉(zhuǎn)化為十進制為11，類比得跳Rap籃球為14，唱Rap籃球為13，下面清空表數(shù)據(jù)使用此方式再插入一遍。

可以看出此圖與上面使用字符插入的對應(yīng)，不過這樣操作性極差，實際操作是不建議這樣做的，一般還是按照字符串的形式插入即可。

補充：set有64位的二進制，用來表示情況，但是不要看這個位數(shù)小，能表示265 種情況。

如果我們想要知道選擇唱 / 跳 / Rap / 籃球的有哪幾種情況的話，這里就要介紹一下find_in_set(str,list)，具體展示如下圖。

為啥要介紹這一個接口呢，就是select根據(jù)一個條件是判斷不出來的，只有所有條件都符合才會進行判斷，具體看下圖。

總而言之，枚舉類型中enum只能從中選擇一種，而set中可以選擇多種，而且為了方便根據(jù)部分條件篩選出數(shù)據(jù)，還提供了find_in_set接口。

尾序

?本篇主要介紹了MySQL的數(shù)據(jù)類型，用以創(chuàng)建更加豐富的表結(jié)構(gòu)，主要從理論和實踐的角度出發(fā)，對數(shù)據(jù)類型進行了基本的說明，并選出加黑部分進行了實踐演示，理論并不難，主打就是一個手勤，干就完了。最后，我是舜華，期待與你的下一次相遇！

柚子快報邀請碼778899分享：數(shù)據(jù)庫 【MySQL】數(shù)據(jù)類型

http://yzkb.51969.com/

好文鏈接