概述

要想學好mysql，了解其支持的基本資料型別以及內部原理是極為重要的，只有這樣，我們才能根據不同的業務要求來選擇不同的資料型別，實作最佳的存盤效果和查詢性能，因而本文就著重總結一下mysql支持的資料型別以及內部的存盤原理，

總體來說，mysql一共分成了四類：數值型別、日期和時間型別、字串型別、二進制型別等，

數值型別

數值型別是最為基礎的型別，在業務開發中存盤遞增主鍵ID、金額、數量等屬性時，都會經常選擇數值型別來進行存盤，整體上將數值型別分為整形、浮點型和定點數型別三類，其中整型對應我們現實生活中常用的整數型別；而浮點型則是對應現實生活中的小數型別；定點數型別則是為了存盤精確的小數而被設計出來的，

整型

整型之中，根據是否有符號又被分為無符號數和有符號數，同時不同存盤空間以及整數表示范圍的考量，整數型別又被分成了TINYINT、SMALLINT、MEDIUMINT、INT以及BIGINT五類，各個類別的型別以及所占空間和含義如下表所示：

具體使用時，為某個變數設定變數型別時加上對應的型別關鍵字即可；在區分無符號數以及有符號數時，則需要加上UNSIGINED關鍵字來加以區分，加上該關鍵字后，則表示使用的無符號數，比如TINYINT UNSIGNED表示的就是無符號TINYINT型別的數，

浮點型

浮點型主要用來存盤小數，其實作和存盤范圍和我們在其他編程語言中學習的型別，分為單精度浮點型（FLOAT）和雙精度浮點型（DOUBLE）兩類，兩者的存盤空間和表示范圍如下表所示：

具體浮點數內部的存盤原理以及存盤范圍的確定，感興趣的同學可以參考之前寫的一篇文章《"從記憶體角度分析浮點數大小比較方法"》此處不再贅述，

從表中可以看到浮點型資料可以將大量十進制小數轉成二進制進行存盤，但實際存盤程序中許多小數存在誤差，即存在精度損失，為了解決該問題，在MYSQL中引入了"定點數型別"來進行實作對小數的精確存盤，

定點數型別

定點型別作為精確存盤小數的方式，它的設計思路和存盤原理和浮點數有較大不同，其具體的結構如下表所示：

從表中可以看出一個定點數型別的數有兩部分構成：

• M：表示該小數最多包含的有效數字個數，比如2.3有效數字個數為2；0.2,有效數字個數為1
• D：表示該小數保留小數點后十進制數字的個數，簡單來講就是小數的位數，比如2.3中D的值為1；8.321中D的值為3，

在存盤時，為了保證定點數不損失小數精度，因而采用如下存盤策略：

將十進制小數用小數點分隔開，分別把小數點左右的兩個十進制整數存盤起來

比如存盤8.32時，分別將8和32分開存盤，這樣就相當于保存了8.32這一精確的小數，

具體使用時，不同的M和D值會影響到存盤的小數范圍，本著“能少用存盤空間就少用存盤空間”的原則，mysql在設計時，采用如下策略來對DECIMAL(M,D)資料型別分配存盤空間，存盤資料，我們以DECIMAL(16,4)為例：

• 第一步 劃分位數：首先按照M和D的大小來劃分，整數位和小數位，在本例中，總的有效數字位數為16，可存盤的小數位數為4，可存盤的整數位數為12，劃分的示意圖如下圖所示：（注意在本圖中，每一個方格代表的是十進制位而不是二進制位，）

• 第二步 分組：從小數點位置開始，分別向兩邊進行分組操作，將每個整數每隔9個十進制位劃分成一組，劃分結果如下圖所示：

從圖中可以看到，在分組時，如果不夠9個十進制位，那么最終也會被單獨劃分成一組，比如第一組和第三組，

• 第三步 轉換二進制：針對每個組中的十進制數字，分別將其轉成二進制數字進行存盤，為了有效利用存盤空間，在存盤時組中包含的十進制數字位數同步，則所占用的存盤空間也不同，具體對應關系表如下：

從表中可以看出，在選擇存盤位數時，可表示的存盤范圍實際上是大于需要表示的數字范圍的，因此此種存盤方案是可行的的，當然有的小伙伴會問了，似乎這樣存盤是有空間浪費的？這也是沒辦法的事情，只能通過犧牲空間來快取存盤精度了，在工程實踐中經常也會有這種tradeoff ??~~

• 第四步 最高位置為1：按照上述方式轉換完成后將二進制序列的最高位設定成1（這一步有其他用途，具體用途此處先挖個坑~），然后將對應資料存盤，

此處以存盤小數1234567890.1234為例：

1. 首先將該十進制數進行劃分，分成如下三組：

1 234567890 1234

其中前兩組是整數部分，最后一組是小數部分，第一組存盤整數1；第二組存盤整數2345678890；第三組存盤1234；

2. 分別將每一組的十進制數轉成二進制，為了便于表示此處均用16進制來表示，最終三組數的結果如下：

0x0001 0x0DFB38D2 0x04D2

將三組16進制數連接起來之后的結果為0x00010DFB38D204D2，

3. 將最高位設定為1，獲取到的最終的待存盤的16進制數為0x80010DFB38D204D2，
4. 最終將該資料按照對應關系表來進行存盤，最終的存盤情況如下圖所示：

當然這時候可能就有小伙伴問了，如果存盤一個負數該咋辦？？比如不是存盤1234567890.1234而是要存盤-1234567890.1234

此時只需要將1234567890.1234對應的二進制存盤數0x80010DFB38D204D2每一位都對應執行取反操作即可，即得到0x7FFEF204C72DFB2D即可，

從上述內容中，我們可以得知，DECIMAL資料型別在存盤資料時，其存盤精度和范圍是可選的根據D和M值的變化而變化，而且不同的D值和M值最終實際占用的空間也是不同的，

在默認情況下，M的值為10，D的值為0；另外DECIMAL資料型別能夠表示的資料范圍也不是無限大的，其中允許M的最大值為65，D的最大值為30，而且D的值是小于等于M的值，

日期和時間型別

在許多業務場景下，我們需要在資料庫中存盤日期和時間等資訊，這時候就需要日期和時間格式來進行存盤，

MySQL提供了多種日期和時間型別，各種型別的表示范圍和占用空間情況如下表所示：

字串型別

在使用資料庫程序中，我們最為常用的型別應該就是字串型別，在MySQL中為了應對不同的應用場景，在設計字串時，提供了多種型別可供選擇：

其中M表示該資料型別最多能存盤的字符數量，M表示在特定字符集下編碼一個字符需要的位元組數；L代表實際向該型別的列中存盤字串在特定字符集下所占的位元組數，

CHAR(M)型別

CHAR(M)中的M表示最多可以存盤的字符數量，其取值范圍為0~255，默認值是1，其中CHAR(0)是一種比較特殊的型別只能存盤空字串''或者NULL值，

在不同的字符集編碼情況下，CHAR(M)的所占用的存盤空間是不同的，如果在某個字符集編碼型別下，存盤一個字符需要W個位元組則針對該編碼情況下CHAR(M)型別所占有的存盤空間便為M×W位元組，比如在ASCII字符集下的CHAR(5)型別，ASCII字符集編碼一個字符需要1個位元組，因此在該編碼集下，CHAR(5)型別占用的存盤空間為5×1=5位元組，

如果實際存盤的字串在特定編碼集下占用的存盤空間小于M×W位元組，剩余空間則會用空格字符（也就是' '）來進行填充，比如表中某一列的值使用ASCII字符集下面的CHAR(5)型別，當我們想要將字串'abc'存盤到該列時需要實際占用的存盤空間由3位元組小于該型別下的5位元組存盤空間，因而會把剩余的2個位元組用空格字符進行填充，

很明顯，這種存盤方式下，當M非常大，而實際存盤的字符卻比較短時，會對存盤空間有較大浪費，

VARCHAR(M)

在實際業務開發程序中，某個列存盤的字串它的大小是長短不一的，如果直接使用CHAR(M)型別存盤會有較大的空間開銷，因而MySQL中提供了VARCHAR型別來解決該問題，

VARCHAR(M)中的M也表示最多可以存盤的字符數量，理論上能夠存盤的范圍為1~65535，但MySQL中規定表的一行資料占用的存盤空間總共不得超過65535個位元組，也就是說VARCAHR(M)型別實際能夠容納的字串數量是小于65535的，

在實際存盤時VARCHAR(M)型別的資料實際占用的存盤空間是不確定的，需要針對不同情況進行分析，為什么這樣說呢？因為VARCHAR(M)型別所對應的存盤空間實際上是由如下兩部分構建成的：

• 第一部分：真正的字串內容，具體大小跟存盤的長度和所用編碼方式有關，此處假定采用特定字符編碼后所占用的位元組數為L，

• 第二部分：額外占用的位元組數，這部分位元組主要用來記錄VARCHAR(M)型別字串在存盤時所占用的位元組長度，即時L的2進制表示數，

  這部分額外占用的位元組數跟字串實際存盤的字串長度以及編碼方式有關，此處采用的編碼方式下，存盤一個字符所用位元組數為W則有L=M×W，那么額外位元組數為：

  • 當L < 256，1個位元組便可以存盤L的二進制數，需要1個位元組來表示所占用的位元組數量，此時，整個VARCHAR(M)型別所占用的位元組數量為L+1個位元組，
  • 當L >=256時，需要2個位元組來表示所占用的位元組數量，此時，整個VARCHAR(M)型別所占用的位元組數量為L+2個位元組，

  1個位元組占用8bit，能表示的最大無符號數為\(2^8-1\)即255，

其他TEXT型別

除了VARCHAR(M)型別外，MySQL提供了TINYTEXT、TEXT、MEDIUMTEXT以及LONGTEXT這4種可以存盤可變長度的字串，這些TEXT型別規定最大的存盤長度，可以讓我們根據實際的業務需要進行選擇，

• TINYTEXT：最多可以存盤\(2^8-1\)位元組，其中額外需要占用1個位元組來存盤長度，
• TEXT：最多存盤\(2^{16}-1\)位元組，其中額外需要2個位元組來存盤長度，
• MEDIUMTEXT：最多存盤\(2^{24}-1\)位元組，其中額外需要3個位元組來存盤長度，
• LONGTEXT：最多存盤\(2^{32}-1\)位元組，其中額外需要4個位元組來存盤長度，

在"VARCHAR(M)"章節中說到，“MySQL中規定表的一行資料占用的存盤空間總共不得超過65535個位元組”，但這一規定對TEXT型別的資料不起作用，因而當表中的資料較長時，可以考慮選擇TEXT型別來進行存盤，

列舉和集合型別

列舉(ENUM)型別和集合(SET)型別是我們在其他編程語言中常見的型別，比如當我們定義性別欄位的型別時便可以采用列舉型別，保證這一列只能填“男”或者“女”，其使用方式為ENUM('str1','str2',...)，

SET型別則標出該列中的值可以從給定字串串列中選擇一個或者多個，比如針對興趣列可以定義為('吃飯','睡覺','打豆豆')，這樣興趣列中的值則只能在這三個字串中選擇一個或者多個，

總的來說ENUM和SET型別都是一種特殊的字串型別，從字串串列中選擇單個值或者多個值時可能會用到，此處不再詳述，

二進制型別

在某些特殊的應用場景下，我們需要存盤二進制位，就用到了二進制型別，總的來講二進制型別分成四種：

- 二進制型別
	- BIT型別
	- BINAR(M)
	- VARBINARY(M)
	- BLOB型別

BIT型別

BIT型別主要是用來存盤單個或者多個二進制位所用到的型別：

其中M的取值范圍為1~64，而且M可以省略，它的默認值為1，

由于計算機在存盤資料的時候都是按照位元組來進行存盤，因而當使用BIT型別時，如果存盤的位元數不夠整數個位元組，則MySQL會對其進行填充，保證是整數個位元組，比如：

• BIT(1):僅僅存盤一個位元組，但實際占用空間為(1+7)/8=1位元組，填充了7bit，
• BIT(2):僅僅存盤了一個位元組，但實際占用空間(2+6)/8=1位元組，填充了6bit，
• BIT(9):僅僅存盤了9個位元組，但實際占用空間(9+7)/2=2位元組，填充了7bit，

BINARY(M)與VARBINARY(M)

這兩種型別與上一章節中的CHAR(M)和VARCHAR(M)型別對應關系相同，不同點在于CHAR(M)和VARCHAR(M)使用來存盤字符而BINARY(M)與VARBINARY(M)則是用來存盤二進制數，

BLOB型別

與TEXT型別類似，BLOB型別也分成了TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB型別四種，主要用來存盤位元組，常用來存盤不是非常大的圖片、音頻和視頻資料，如果比較大的二進制型別資料，一般不直接存盤到資料庫中，而是存盤到檔案系統中，在資料庫中僅僅存放其檔案路徑，

總結

本文總結了在MySQL資料庫中常用的資料型別及其相關原理，在MySQL中總體上支持的資料型別有數值型別、字串型別、二進制型別三大類，分別用于存盤三類常見的資料，

標籤：其他

上一篇：華為資料庫，安全的保障

下一篇：華為大資料云管理平臺實測