MySQL數據類型

1. 數據類型分類

紅色標注是我主要講解的內容

2. 數值類型

2.1 tinyint類型

tinyint有符號數據范圍測試：

首先創建一個數據庫，進入

進入后，創建一張表，表當中包含一個tinyint類型的列，默認其為有符號類型

注意: 在MySQL表中建立屬性列，列名稱在前，屬性在后

列名稱, 屬性

由于tinyint的取值范圍是-128~127, 邊界和范圍內的數據可以插入

在此范圍之外的數據無法插入

說明:

• 在MySQL中，整型可以指定是有符號的和無符號的，默認是有符號的。
• 可以通過UNSIGNED來說明某個字段是無符號的

tinyint無符號數據范圍測試：

創建t2表，并且指定為無符號類型

無符號tinyint范圍是0~255，一旦越界則無法插入

注意:

• 我們平時在語言上賦值超過范圍時(比如 char a =1234567)，可能會發生截斷后賦值，但是我們向MySQL特定的類型中插入不合法的數據，MySQL一般都是直接攔截我們，不讓我們做對應的操作！

• 反過來，如果MySQL我們已經有數據被成功插入到MySQL中了，一定在插入的時候是合法的！

• 所以，MySQL中，一般而言，數據類型本身也是一種：約束

• 這樣做的目的是: 倒逼程序員，讓程序員盡可能進行正確的插入，約束的是: 使用者，另外若果你不是一個很好的使用者，MySQL也能保證數據插入的合法性；進而就能數據庫中的數據是可預期，完整的

2.2 bit類型

基本語法

bit[(M)] : 位字段類型。M表示每個值的位數，范圍從1到64。如果M被忽略，默認為1。

先創建一張表，表當中包含一個int類型和bit類型的列

bit是一種位類型，一個bit位只允許插入0或1，并且超過位數的值是無法插入的

直接查看發現是不顯示的，因為bit類型在顯示時，是按照ASCII碼對應的值進行顯示的，有些字符是不可顯示的

強轉成16進制顯示

修改表，改成 bit(10)

向表中插入’a’和97，最后顯示都是’a’，再次驗證了是按ASCII碼對應的值進行存儲

bit(M)，如果超過M最大值64，創建表就會失敗

直接創建表，不指定bit中M的長度，默認值是1

2.3 小數類型

2.3.1 float

語法：

float[(m, d)] [unsigned] : M指定顯示長度，d指定小數位數，占用空間4個字節

創建一個表，包含一個float(4,2)類型的列，默認其為有符號類型

float(4,2)表示的范圍是-99.99 ~ 99.99，在該范圍內可以插入數據，超過范圍報錯

嘗試插入一些，超過小數位數的值，下面會成功插入，因為這些四舍五入后會變成99.99

而這些無法插入，因為四舍五入后會變成100.00超過了范圍

說明：MySQL在插入值時會進行四舍五入

創建一個無符號的表，還是一樣的列屬性

向其中中插入一些數據，發現負數和超過范圍的數據是無法插入的

說明: 無符號類型的float取值范圍是0~99.99

還有一個問題要注意，float類型會精度丟失問題

2.3.2 decimal

語法：

decimal(m, d) [unsigned] : 定點數m指定長度，d表示小數點的位數

• decimal(5,2) 表示的范圍是 -999.99 ~ 999.99
• decimal(5,2) unsigned 表示的范圍 0 ~ 999.99
• decimal和float很像，但是有區別: float和decimal表示的精度不一樣

創建表，兩個列類型分別是 float 和 decimal，長度和小數點位數都相同，向兩列中插入同樣高精度的值，float則會存在一定的精度損失，decimal則沒有

說明：float表示的精度大約是7位。

• decimal整數最大位數m為65。支持小數最大位數d是30。如果d被省略，默認為0.如果m被省略，默認是10。

建議：如果希望小數的精度高，推薦使用decimal。

2.4 字符串類型

2.4.1 char

語法：

char(L): 固定長度字符串，L是可以存儲的長度，單位為字符，最大長度值可以為255

• L代表一個字符，在MySQL里一個漢字也是一個字符

• 在不同編碼中，一個字符所占的字節個數是不同的，比如utf8中一個字符占3個字節，而gbk中一個字符占2個字節

先創建一張表

向其中插入一些字符，一旦字符數超過固定的長度則不會插入

也可插入漢字

2.4.2 varchar

varchar(L): 可變長度字符串，L表示字符長度，最大長度65535個字節

L代表字節

先創建表

插入數據，varchar在這與char沒什么區別

說明：
關于varchar(len),len到底是多大，這個len值，和表的編碼密切相關：

• varchar長度可以指定為0~65535之間的值，但是有1 - 3 個字節用于記錄數據大小，所以說有效字
  節數是65532。
• 當我們的表的編碼是utf8時，varchar(n)的參數n最大值是65532/3=21844[因為utf中，一個字符占
  用3個字節]，如果編碼是gbk，varchar(n)的參數n最大是65532/2=32766（因為gbk中，一個字符
  占用2字節）。

2.4.3 char和varchar比較

如何選擇定長或變長字符串？

• 如果數據確定長度都一樣，就使用定長（char），比如：身份證，手機號，md5
• 如果數據長度有變化,就使用變長(varchar), 比如：名字，地址，但是你要保證最長的能存的進去。
• 定長的磁盤空間比較浪費，但是效率高。
• 變長的磁盤空間比較節省，但是效率低。
• 定長的意義是，直接開辟好對應的空間
• 變長的意義是，在不超過自定義范圍的情況下，用多少，開辟多少

2.5 日期和時間類型

常用的日期有如下三個：

• date :日期’yyyy-mm-dd’ ，占用三字節
• datetime 時間日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范圍從1000 到9999 ，占用八字節
• timestamp ：時間戳，從1970年開始的 yyyy-mm-dd HH:ii:ss 格式和datetime 完全一致，占用四字節

先創建一張包含著3個類型的數據表

查看表的具體屬性

如果向表中插入數據，t3會自動更新到最新時間（無需手動插入）

修改一下t1的時間，同時發現t3時間也更新了

那么t3這種字段究竟有什么用呢?

假設一種場景，現在有一個論壇，大家在里評論，t3會記錄某人最新評論的時間

我們創建這樣一個表，兩列屬性分別代表用戶的評論內容和評論時間

現在我發表了評論，t3記錄了我當前的時間

過了一會，我又重新發表了評論，則t3會更新，記錄我最新發表評論的時間

2.6 enum和set

語法：

• enum：枚舉，“單選”類型；

enum('選項1','選項2','選項3',...);

該設定只是提供了若干個選項的值，最終一個單元格中，實際只存儲了其中一個值；而且出于效率考慮，這些值實際存儲的是“數字”，因為這些選項的每個選項值依次對應如下數字：1,2,3,…最多65535個；當我們添加枚舉值時也 可以添加對應的數字編號。

• set：集合，“多選”類型；

set('選項值1','選項值2','選項值3', ...);

該設定只是提供了若干個選項的值，最終一個單元格中，設計可存儲了其中任意多個值；而且出于效率考慮，這些值實際存儲的是“數字”，因為這些選項的每個選項值依次對應如下數字：1,2,4,8,16,32，…最多64個。

說明：不建議在添加枚舉值，集合值的時候采用數字的方式，因為不利于閱讀。

創建表，分別設置列數據類型為enum和set

以選擇屬性的形式插入性別和愛好，在范圍內可成功插入

以數字的形式插入性別，枚舉是從1開始的，則1代表’男’， 2代表’女’， 在這兩個之外的數無法插入

以選擇屬性的形式插入愛好，在范圍內可成功插入，如有多組值可逗號隔開插入

如果只插入username這一個，則其他兩個默認為NULL

set是一個位圖結構，比特位的0/1代表有無，比如上面hobby中的選項就可以這樣表示

hobby選項插入為0則代表空串，插入1就是將代碼的比特位置1

將上面hobby選項全部置為1則為數字31，則插入31就代表插入所有選項

集合查詢使用find_ in_ set函數：

• find_in_set(sub,str_list) ：如果 sub 在 str_list 中，則返回下標；如果不在，返回0；
• str_list字符串是用逗號分隔的字符串

直接去篩選發現是一種精確匹配，會將屬性只為羽毛球的顯示

使用find_ in_ set函數，會將屬性有羽毛球的顯示

篩選出屬性有羽毛球和代碼的