MySQL知識樹-支援的資料類型

標籤：select   負數   將不   rom   浮點   定義   查詢   就會   名稱   

本篇學習筆記的主要內容：

介紹MySQL支援的各種資料類型（常用），並講解其主要特點。

 

MySQL支援多種資料類型，主要包括數實值型別、日期和時間類型、字串類型。

 

數實值型別

MySQL的數實值型別包括整數類型、浮點數類型、定點數類型、位類型。

 

整數類型

MySQL支援的整數類型有tinyint、smallint、mediumint、int、bigint（範圍從小到大）。

 

zerofill

我們在定義整數類型時可以在類型名稱後面的小括弧內指定顯示寬度，例如int(5)，當插入的數值寬度小於5位時，MySQL會在數值前面填充寬度。對於int類型如果不手動指定寬度，則預設為int(11)。

顯示寬度一般是配合zerofill來使用，即當插入的數值位元未達到指定的顯示寬度時，缺少幾位就會在數值前填充幾個0

圖1

圖1，我們建立表t_1，兩個欄位分別為id1和id2，都是int類型。其中id2我們指定了顯示寬度為5，而id1沒有手動指定顯示寬度，因此它的顯示寬度會取預設值11。

 

圖2

圖2，我們向表中插入一條資料後再將其查詢出來，雖然現在id1和id2查詢出來的數值都是1，但由於id1在定義時沒有指定顯示寬度，因此在插入數值1後，其前面10位都被填充了寬度。而id2由於指定了顯示寬度，因此其面前只有4位被填充寬度。

 

圖3

圖4

圖3、圖4，為了更加直觀的看到填充寬度的效果，我們將id1和id2的定義稍作修改，使用zerofill來填充寬度。

 

圖5

圖5，在使用了zerofill後，我們可以看到數值前面被0填充寬度的效果。那麼我們在進行查詢時使用1或00001作為條件可以得到結果嗎？

 

圖6

圖6，可以看到在使用1或00001作為查詢條件時，能查出id2對應的數值。但這裡要注意的是在MySQL中實際儲存的值仍然是1，而不是00001，因為00001並不是一種整數的表現形式，而是一種字串的表現形式，下面的圖7將證明這個問題。

 

圖7

圖7，我們在查詢時使用了hex()函數作為對比，可以看到使用hex()函數得到的值是1，假若hex()得到的值是3030303031（字串1的16進位為31，字串0的16進位為30），則可以肯定在MySQL中是以00001的字串形式進行儲存的，但很明顯這裡並不是。

 

註：hex()函數可以將一個數字或字串轉換為十六進位格式的字串

 

對於指定顯示寬度的做法，聯想到一個問題，在id2定義為int(5)的情況下，如果插入超過顯示寬度的值，會怎麼樣呢？

 

圖8

圖8，向id2插入長度為6位的數值111111時，MySQL沒有報任何錯誤也沒有將111111截斷。因此說明了顯示寬度並不會對插入的數值長度產生限制，兩者並沒有什麼關係，除非插入的數值超過了資料類型的範圍，見圖9。

 

圖9

 

圖9，可以看到雖然插入成功但MySQL有一個警告（當MySQL的SQL Mode為strict 模式時，該插入行為將不能夠被完成，同時MySQL會報ERROR），我們在將資料查詢出來時可以看到MySQL對原本插入的資料進行了截取，保留值為4294967295。

 

註：int資料類型有符號的最小值為-2147483648，最大值為2147483647，無符號的最小值為0，最大值為4294967295

 

知識點說明：

其實對於顯示寬度來說，只有配合使用了zerofill，顯示寬度才有意義，否則就讓顯示寬度為預設值就可以了。不要以為指定顯示寬度會對整數類型的取值範圍有什麼影響，兩者之間沒有任何關係，而說到整數類型的取值範圍只有unsigned才會對其產生影響。

 

 

unsigned

當我們在定義整數類型時使用了zerofill，MySQL會為我們自動對該列再添加unsigned（圖3、圖4對列添加了zerofill後，再查看錶的DDL [資料庫定義語句]，會發現列多了unsigned，詳見圖10）。這是因為當使用了zerofill後，插入該列的值就不可能為負數了，因此自動添加unsigned也是理所應當的，同時unsigned也會增加整數類型最大值的取值範圍。

 

圖10

 

 

auto_increment

整數類型還有一個屬性就是auto_increment，而且這個屬性還是整數類型特有的。auto_increment的作用就是使列值保持自動成長，auto_increment的值預設從1開始，也可以手動設定其初始值。對被設定為auto_increment的列插入null值時，實際插入的值是該列當前最大值加1（null並不會影響到被設定為auto_increment列的資料插入，列會正常的進行自動成長）。

當一個列被設定為auto_increment時，通常還需要為該列設定not null和primary key（主鍵，一般被設定為auto_increment的列會作為主鍵使用，這裡只是說一般，也有非主鍵的情況）。

另外需要提醒的是一張表中最多隻能有一個欄位被設定為auto_increment。

 

 

浮點數和定點數

這兩者都是用來表示小數的，浮點數包括float(單精確度)、double(雙精確度)，定點數僅為decimal。兩者在定義時都可以指定其精度和標度，精度是指一共顯示多少位元字(整數位+小數位)，標度是指精確到小數點後多少位，表現形式如：decimal(15,2)，這裡的精度是15位(整數13位，小數2位)，標度是2位。

需要說明的是定點數在MySQL內部是以字串的形式來儲存的，屬於準確儲存，但表現出來的是小數，它比浮點數更精確。

 

圖11

圖12

圖13

圖11，我們建立一張表，欄位id的資料類型為decimal(5,2)，12在向表裡插入超過標度的值時，雖然插入成功但是插入時的資料卻被截斷了，這裡發生了四捨五入。

圖13我們向表裡嘗試插入超過精度的值，難道也會發生截斷並四捨五入？兩個值會分別顯示為123.12和124.12嗎？從結果來看明顯不是，我們的猜測是完全錯誤的。在超過精度的情況下，雖然插入成功但插入的值卻是指定精度和標度下的最大值，例如(5,2)下的最大值為999.99。

若是在SQL Modestrict 模式下，上述這些插入操作將不能被執行成功且MySQL會報ERROR。

 

額外知識點：

單精確度和雙精確度的區別，這兩者的區別可別理解為單精確度是精確到小數點後一位，而雙精確度是精確到小數點後兩位，這明顯是錯誤的。實際上由於float的有效位元是7位，double的有效位元是16位，因此單精確度、雙精確度其實是指代這裡的有效位元。

另外需要注意的是有效位元並不等於精確位元，縱然float可以表示到小數點後7位，但只有前6位是精確的，第7位很可能造成資料誤差。而對於double來說只有前15位是精確的，第16位也很可能造成資料誤差。

 

額外知識點：

關於float、double精度丟失的問題，實際上就是被擴充或截斷了，究其緣由是因為存取時標度不一致所導致的。在錄入資料時若資料的標度與定義列資料類型時設定的標度不一致，則會導致存入時以近似的值來儲存，這就造成了我們上面說到的精度丟失。

那在什麼情況下float、double的精度不會丟失呢？其實根據上面出問題的情況，我們可以想到當資料標度與類型標度一樣時（錄入資料的標度與定義列資料類型時設定的標度一致），就不會發生精度丟失。

鑒於此，我們常選用decimal類型，小於等於其標度的資料都能被正確錄入，不會發生精度丟失，因為其是將資料以字串的形式來存進資料庫的，這就保證了精確性。但並不是說decimal就不會發生精度丟失，雖然它不會發生精度擴充但卻會發生精度截斷。例如當錄入資料的標度大於列資料類型設定的標度時，依然會發生四捨五入。

雖然我們說decimal將資料以字串的形式存入資料庫，同時又會存在精度截斷的問題（四捨五入），看似兩者有文字描述上的衝突，其實不然。我們這樣來理解：decimal將發生了四捨五入的資料以字串的形式存入了資料庫，但表現出來的是小數（一個是儲存形式，一個是表現形式），且這個小數的精度不會再發生變化，而不管是以什麼精度來擷取這個值，它都是四捨五入後以字串形式存入時的值。

 

 

位類型

位類型指的就是BIT，它是用來存放位元據的，bit(1)表示儲存長度為1位的位元據。

圖14

圖15

我們對圖14的表中插入超過位元的資料，從圖15的第二個查詢結果集中可以探索資料發生了截斷，數值2的二進位是10，3的二進位是11，它們的第二位都被截斷了。

在圖15的第一個查詢結果集中，需要說明的是在MySQL命令列視窗中使用select * from t_bit_test是無法看到我們需要的資料的，你只能看到有兩個笑臉被顯示出來，那既然bit中存放的是位元據，我們就使用bin()函數以二進位的形式來顯示它們。

 

---------------------未完待續---------------------

MySQL知識樹-支援的資料類型