本篇學習筆記的主要內容： 介紹MySQL支持的各種數據類型（常用），並講解其主要特點。 MySQL支持多種數據類型，主要包括數值類型、日期和時間類型、字元串類型。 數值類型 MySQL的數值類型包括整數類型、浮點數類型、定點數類型、位類型。 整數類型 MySQL支持的整數類型有tinyint、sma ...
本篇學習筆記的主要內容：
介紹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為嚴格模式時，該插入行為將不能夠被完成，同時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 Mode嚴格模式下，上述這些插入操作將不能被執行成功且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()函數以二進位的形式來顯示它們。
日期時間類型
日期時間類型的主要區別如下：
①如果要用來表示年月日時分秒，一般使用datetime類型；
②如果要用來表示年月日，一般使用date類型；
③如果要表示時分秒，一般使用time類型；
④如果只是表示年份，一般使用year類型，需要註意的是5.5.27版本之前（不包含該版本）year類型有2位和4位格式這兩種表示，在5.5.27版本之後2位格式的year已經不再被支持，year類型的值都會以YYYY的格式顯示。
year(2)被棄用主要是因為兩位的顯示寬度使得該類型表示變得模糊、不明確（顯示的值僅僅是最後兩位數字），因此如果在設計資料庫時需要使用year數據類型，而MySQL的版本又是低於5.5.27版本的話，建議將year類型定義為year(4)以避免問題（從5.5.27版本開始若創建表時將year類型定義為year(2)，實則MySQL會強制將其定義為year(4)。詳見圖15）。
若在將低版本的MySQL升至5.5.27以後的版本時需要將這塊的數據做下處理；
⑤如果需要經常插入系統當前時間或將時間更新為系統當前時間，一般使用timestamp類型。
圖15
圖16
圖16，我們創建了一張表，列d1的數據類型為timestamp，通過查看表的定義可以看到MySQL自動將timestamp類型列的預設值設置為了current_timestamp，即系統當前時間，另外還加上了on update current_timestamp（當該行的數據發生變化時，該列的值會隨系統當前時間被更新，詳細效果見圖17、18）。
圖17
圖17，我們為t_dt_test表新增列i1，int類型顯示寬度為2，寬度不夠時用0填充。
圖18
圖18中我們先為表插入一條數據，可以看到插入的d1值為2016-10-22 15:25:31，接著我們將i1的值由1修改為2，再看d1發現原本存入的時間被更新了，此時的值為2016-10-22 15:26:27，這就是隨系統當前時間戳更新的效果。
---------------------未完待續---------------------
