Mysql數據類型最細講解

• 2019 年 10 月 23 日
• 筆記

文章原創於公眾號：程式猿周先森。本平台不定時更新，喜歡我的文章，歡迎關注我的微信公眾號。

資料庫中事務是最重要的概念之一，所以上篇著重談了談資料庫中事務的使用，並且舉了實例如何在實際開發中去使用事務進行資料庫操作。而我們也知道，Mysql中列的數據類型很多，而大多數人可能只是知道這些不同的數據類型怎麼去使用，所以本篇文章主要詳細談談Mysql中各種不同的數據類型。

Mysql數據類型分類

在Mysql中常用數據類型一共有四種字元串數據類型、日期/時間數據類型、數值數據類型以及二進位數據類型。

• 字元串數據類型：主要包括下列幾種數據類型：char，varchar，tinytext，mediumtext，text，longtext，enum，set。

• 日期/時間數據類型：主要包含下列幾種數據類型：date，time，datetime，timestamp，year。

• 數值數據類型：主要包含下列幾種數據類型：tinyint，smallint，mediumint，int，bigint，float，double，decimal。

• 二進位數據類型：主要包含下列幾種數據類型：tityblob，blob，mediumblob，longblob。

約束條件

在開始正式講解數據類型之前，我們需要先了解下如何給欄位添加一些基本的約束條件。Mysql可以給欄位添加的常用約束條件有：unsigned，zerofill，not null，default，primary key，auto_increment，unique key，foreign key。

• unsigned：無符號類型，加上此約束值無法取負數，只能作用於數值類型數據列。

• zerofill：當數據顯示長度不夠時在數據前面補0至指定長度，欄位會自動添加unsigned約束。

• not null：這個很好理解，給欄位添加非空約束。

• default：如果插入數據沒有指定值，則使用默認值。

• primary key：給欄位添加主鍵約束，一個表只能有一個主鍵，但是可以和其他欄位形成組合主鍵，一般與auto_increment約束一併使用。

• auto_increment：只能作用於數值類型，欄位可以自動遞增，默認從1開始。一般和primary key配合使用。

• unique key：設置唯一約束，則欄位的值不能出現重複數據，null除外。

• foreign key：外鍵約束，保證數據完整性和唯一性，以及多表聯表操作。

字元串類型

char數據類型

定義：可以定義一個固定長度的字元串，長度範圍在1-255個字元之間，長度必須在表創建時指定，否則會默認定義為char(1)，在存儲時字元串如果未達到指定的長度則會填充空格到指定長度。
使用途徑：當我們需要涉及一些長度固定的數據列時可以使用char數據類型，比如手機號使用char(11)，身份證號使用char(18)，用戶性別使用char(3)。

varchar數據類型

定義：可以定義一個可變長度的字元串，理論情況下可存儲最多255個位元組的數據。但是如果創建時指定varchar(x)，則只能存儲不超過x個字元的數據。
對比char類型區別：實際上我們更經常使用的還是varchar數據類型，因為char類型不管存儲數據的長度多少，都會佔用定義的位元組數，但是varchar只會佔用實際字元串長度+1個位元組。但是char查詢效率相比於varchar會更高，所以存儲固定長度時我們可以優先選擇char數據類型。
使用途徑：一般長度不固定的數據列就可以使用varchar類型，比如姓名，一般中文名字2到5字，所以我們定義為varchar(15)。

tinytext數據類型

定義：tinytext也屬於變長字元串，最多可存儲不超過255位元組的變長文本。
缺點：tinyint數據類型定義時不允許設置默認值，在檢索也不存在大小寫轉換，而且效率低於char以及varchar。所以一般情況下比較少使用該數據類型。

mediumtext數據類型

定義：mediumtext也屬於變長字元串，最多可存儲不超過16k位元組的變長文本。
缺點：mediumtext和tinytext同屬於text系列數據類型，所以缺點都是一致的。
使用途徑：mediumtext一般用於varchar存儲範圍不符合時用來存儲長文本操作，一般情況下使用mediumtext存儲長文本就可以滿足操作。

text數據類型

定義：text也屬於變長字元串，最多可存儲不超過64k位元組的變長文本。
缺點：text也屬於text系列數據類型，所以缺點和上述都是一致的。
使用途徑：text一般用於mediumtext存儲範圍不符合時用來存儲長文本操作，一般用戶文章存儲。

longtext數據類型

定義：longtext也屬於變長字元串，最多可存儲不超過4G位元組的變長文本。
缺點：longtext也屬於text系列數據類型，所以缺點和上述都是一致的。
使用途徑：longtext最長長度可存儲不超過4G的純文本，但是一般情況下幾乎不需要使用這種數據類型。

enum數據類型

定義：ENUM是一個字元串對象，可以通過ENUM限制欄位的取值範圍。如果插入數據時欄位的取值並非可選值之一，則會空串或者NULL代替用戶想要插入的值。enum集合的長度最多不超過64K。
缺點：不建議在資料庫使用ENUM限制取值範圍，因為坑其實挺多的，比如ENUM通過角標取值，但是角標從1開始，因為0留給空串了，再或者在ENUM中0和"0"是不一樣的，如果將0當做角標去操作由於ENUM角標從1開始會報錯，如果使用"0"去操作，最後插入的是空串，因為角標0是預留給空串的。所以說在資料庫層次不建議使用ENUM限制欄位取值範圍。
使用途徑：比如用戶性別我們在建表時可以使用ENUM限制取值範圍只能為男或女。

set數據類型

定義：數據列定義為set則可以存儲集合，set集合最多不超過64k的長度。
set與enum的區別：enum定義數據列的取值範圍，但是插入值永遠只能有一個值。但是set可以設置數據列的取值範圍，但是插入值時可以插入多個值。
使用途徑：如果我們需要保存集合數據的時候可以將欄位設置為set集合然後設置一個取值範圍，然後插入時在取值範圍內取多個值形成集合進行插入。。

日期/時間數據類型

Mysql有多種可以用來存儲時間或日期的數據類型，比如我們可以使用date存儲日期，可以使用year存儲年份，可以使用time存儲時間。可以使用datetime或者timestamp來存儲日期和時間的組合，接下來我們來看下這幾個日期/時間數據類型。

date數據類型

定義：用來存儲日期，存儲範圍為’1000-01-01’到’9999-12-31’。
使用途徑：可用於存儲年月日的數據列，比如存儲用戶出生日期我們就可以使用date數據類型來進行存儲。

time數據類型

定義：用來存儲時間，不僅可以表示當天的時間，而且還可以用來表示兩個時間的時間間隔。取值範圍為’-838:58:59’到’838:59:59’。
使用途徑：可能很多朋友看到這裡懷疑自己的眼睛了，time取值範圍’-838:58:59’到’838:59:59’？實際上這個取值範圍是可以這麼理解的：D HH:MM:SS，也就是你如果要存儲時間間隔則會以時間間隔乘上小時作為小時進行存儲。因為這個time使用比較複雜，所以我們在這裡使用sql語句看看幾種不同格式時間的插入效果：

1. 簡單的插入一個當前時間效果就是保存時間：

1. 只有一個冒號則只保存時分，秒保存為00：

1. 不存在冒號則時分保存為00，只保存秒：

1. 時間前添加一個-2表示事件是在兩天前開始進行，所以保存結果小時就是-2 * 24 + 1最後保存為-49:00:00：

1. 時間前添加一個2表示事件是在兩天後開始進行，所以保存結果小時就是2 * 24 + 1最後保存為49:00:00：

1. 可以直接使用數字保存，也就是不輸入空格與冒號：

year數據類型

定義：這個很簡單，就是保存一個年份值。
使用途徑：比如我們需要記錄圖書出版年份，則可以使用year數據類型。

datetime數據類型

定義：可以使用datetime來保存時間與日期組合格式，存儲範圍為1000-01-01 00:00:00 到 2039-01-19 11:14:07。一般有兩種保存方式yyyy-mm-dd HH:MM:SS或者yyyymmddHHMMSS。
使用途徑：這個很常用，比如訂單下單時間或訂單付款時間。

timestamp數據類型

定義：timestamp實際上功能和datetime差不多，但是範圍更小，timestamp存儲範圍為1970-01-01 00:00:00 到 9999-12-31 23:59:59。
使用途徑：這個很常用，比如訂單下單時間或訂單付款時間。

數值數據類型

剛才其實提到了數值數據類型有很多種，不同類型有不同的存儲範圍，同樣所需的存儲空間也是不一樣的，數值類型都可以都是有符號，即可設置正負值。

tinyint數據類型

定義：存儲整型數據，大小為1位元組，如果保存有符號值則取值範圍為-128到127，如果保存無符號值大小為取值範圍為0到255。

smallint數據類型

定義：存儲整型數據，大小為2位元組，如果保存有符號值則取值範圍為-32768到32767，如果保存無符號值大小為取值範圍為0到65535。

mediumint數據類型

定義：存儲整型數據，大小為3位元組，如果保存有符號值則取值範圍為-8388608到8388607，如果保存無符號值大小為取值範圍為0到16777215。

int數據類型

定義：存儲整型數據，大小為4位元組，如果保存有符號值則取值範圍為-2147683648到2147683647，如果保存無符號值大小為取值範圍為0到4294967295。

bigint數據類型

定義：存儲整型數據，大小為8位元組，如果保存有符號值則取值範圍為-2^63到2^63-1，如果保存無符號值大小為取值範圍為0到2^64-1。

float數據類型

定義：存儲浮點數據，大小為4位元組，浮點型不能設置unsigned，取值範圍為-1.175494351e – 38到1.175494351e – 38。

double數據類型

定義：存儲浮點數據，大小為8位元組，浮點型不能設置unsigned，精度相比float會更高，取值範圍為-2.2250738585072014e-308到2.2250738585072014e-308。

decimal數據類型

定義：常用於存儲精確的小數，可以設置存儲的位元組數和保留的小數位數。存儲的位元組數最大為65，默認為10，小數位數最大為30，默認為0。
使用途徑：經常可以使用decimal保存金額或者積分值，因為金額一般保存都是固定小數位。

二進位數據類型

定義：二進位數據類型可存儲任何數據，既可存儲文本數據，也可存儲影像或者多媒體等數據。

二進位數據類型其實相對其他數據類型比較少用，因為文件現在一般都是上傳oss進行cdn加速，一共有四種數據類型：tinyblob，blob，mediumblob，longblob，這幾個數據類型的區別在於存儲範圍。

• tinyblob：存儲長度最大為255位元組。

• blob：存儲長度最大為64k。

• mediumblob：存儲長度最大為16M。

• longblob：存儲長度最大為4G。

缺點：存儲文件過大會影響資料庫的性能。
歡迎關注公眾號：程式猿周先森。文章原創於微信公眾號，本平台不定時更新。