MySQL數(shù)據(jù)類型

數(shù)據(jù)類型的作用：

• 決定了存儲(chǔ)數(shù)據(jù)時(shí)應(yīng)該開辟的空間大小。
• 決定了如何識(shí)別一個(gè)特定的二進(jìn)制序列。
• 決定了數(shù)據(jù)的取值范圍。

數(shù)據(jù)類型分類

注：MySQL本身是不支持bool類型的，當(dāng)把一個(gè)數(shù)據(jù)設(shè)置成bool類型時(shí)，數(shù)據(jù)庫會(huì)自動(dòng)將其轉(zhuǎn)換成tinyint(1)的數(shù)據(jù)類型，其實(shí)這個(gè)就是變相的bool類型，因?yàn)閠inyint(1)只有1和0兩種取值，可以分別對(duì)應(yīng)bool類型的true和false。

數(shù)值類型

tinyint類型

有符號(hào)tinyint范圍測試

由于tinyint類型占用1字節(jié)，因此有符號(hào)tinyint的取值范圍為-128~127，插入該范圍內(nèi)的數(shù)據(jù)時(shí)都能成功插入。如下：

如果插入的數(shù)據(jù)不在-128~127范圍內(nèi)，那么插入數(shù)據(jù)時(shí)就會(huì)產(chǎn)生報(bào)錯(cuò)。如下：

無符號(hào)tinyint范圍測試

由于tinyint類型占用1字節(jié)，因此無符號(hào)tinyint的取值范圍為0~255，插入該范圍的數(shù)據(jù)時(shí)都能成功插入。如下：

如果插入的數(shù)據(jù)不在0~255范圍內(nèi)，那么插入數(shù)據(jù)時(shí)就會(huì)產(chǎn)生報(bào)錯(cuò)。如下：

建議：

• 盡量不使用unsigned，對(duì)于int類型可能存放不下的數(shù)據(jù)，int unsigned同樣可能存放不 下，與其如此，還不如設(shè)計(jì)時(shí)，將int類型提升為bigint類型。

bit類型

bit類型的顯示方式

mysql 客戶端默認(rèn)開啟了 --binary-as-hex。
只要列是二進(jìn)制類型（BIT / BINARY / VARBINARY / BLOB），客戶端就用十六進(jìn)制顯示，以避免亂碼。所以 BIT(8) 存了十進(jìn)制 10，我們看到的就是 0x0A。

bit類型的范圍測試

創(chuàng)建一個(gè)表，表當(dāng)中包含用戶名name和用戶性別gender，其中g(shù)ender的類型可以指定為1位bit類型，因?yàn)樾詣e只有男和女兩種取值，使用1個(gè)比特位來表示用戶的性別就可以節(jié)省空間。如下：

如果插入gender列的數(shù)據(jù)不是0或1，那么插入數(shù)據(jù)時(shí)就會(huì)產(chǎn)生報(bào)錯(cuò)。如下：

建議：

• 雖然MySQL提供了位類型bit，但一般不建議將數(shù)據(jù)類型設(shè)置成位類型，除非將來這個(gè)數(shù)據(jù)本身就只是給程序看的，并且數(shù)據(jù)本身非常占用資源。
• 因?yàn)椴樵兾活愋蛿?shù)據(jù)時(shí)，默認(rèn)會(huì)按照ASCII碼對(duì)應(yīng)的值進(jìn)行顯示，這對(duì)于將來數(shù)據(jù)庫管理員維護(hù)數(shù)據(jù)庫或程序員調(diào)試程序都是不太方便的。

float類型

有符號(hào)float范圍測試

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

小數(shù)：float(4,2)表示的范圍是-99.99 ~ 99.99，MySQL在保存值時(shí)會(huì)進(jìn)行四舍五入。

此外，由于MySQL在保存值時(shí)會(huì)進(jìn)行四舍五入，因此實(shí)際可插入float(4,2)的范圍為-99.994~99.994，如果插入的數(shù)據(jù)不在該范圍內(nèi)，那么插入數(shù)據(jù)時(shí)就會(huì)產(chǎn)生報(bào)錯(cuò)。如下：

有符號(hào)float范圍測試

創(chuàng)建一個(gè)表，表當(dāng)中包含一個(gè)float(4,2)類型的列，并指定其為無符號(hào)類型。

無符號(hào)float類型的取值范圍，實(shí)際就是把對(duì)應(yīng)有符號(hào)float類型中的負(fù)數(shù)部分拿走了，因此float(4,2)的取值范圍為0~99.99，實(shí)際可插入的范圍是0~99.994。如下：

如果插入的數(shù)據(jù)不在0~99.994范圍內(nèi)，那么插入數(shù)據(jù)時(shí)就會(huì)產(chǎn)生報(bào)錯(cuò)。如下：

為什么這里和tinyint類型的無符號(hào)取值不一樣？

整數(shù)類型（如 TINYINT）

• 有符號(hào) TINYINT：范圍是 -128 ~ 127，總共 256 個(gè)值。
• 無符號(hào) TINYINT：范圍是 0 ~ 255，總共也是 256 個(gè)值。

?? 可以看到：總數(shù)相等，只是符號(hào)位的解釋不同。MySQL 對(duì)整數(shù)采用定長存儲(chǔ)（補(bǔ)碼），有符號(hào)與無符號(hào)在存儲(chǔ)層面占用 相同的 1 個(gè)字節(jié)，所以值的“個(gè)數(shù)”完全一致。

浮點(diǎn)類型（FLOAT/DOUBLE）

浮點(diǎn)數(shù)遵循 IEEE 754 標(biāo)準(zhǔn)，存儲(chǔ)方式不同于整數(shù)：

• 浮點(diǎn)數(shù)由三部分組成：
  • 符號(hào)位（sign bit）
  • 指數(shù)（exponent）
  • 尾數(shù)（mantissa/fraction）
• 當(dāng)你使用 UNSIGNED 時(shí)，只是告訴 MySQL：不允許負(fù)數(shù)。
• 但浮點(diǎn)的存儲(chǔ)格式本身并不會(huì)把“符號(hào)位”重新拿來擴(kuò)展有效數(shù)值范圍。

換句話說：

• 有符號(hào) FLOAT：能表示負(fù)數(shù)和正數(shù)，范圍是 -3.402823466E+38 ~ +3.402823466E+38。
• 無符號(hào) FLOAT：范圍只是 0 ~ 3.402823466E+38。

?? 因?yàn)?IEEE 754 并沒有定義“把符號(hào)位轉(zhuǎn)換成額外的數(shù)值空間”，所以無符號(hào)浮點(diǎn)數(shù)的取值范圍不是兩倍，而只是去掉負(fù)數(shù)部分。

decimal類型

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

創(chuàng)建decimal為(5，2)的表

mysql 8.0以后的版本float和decimal有什么區(qū)別

存儲(chǔ)方式

FLOAT / DOUBLE

• 存儲(chǔ)為 IEEE 754 二進(jìn)制浮點(diǎn)數(shù)（單精度/雙精度）。
• 值是近似的，很多十進(jìn)制數(shù)不能被精確表示（比如 0.1）。
• 占用存儲(chǔ)空間較小（FLOAT 4 字節(jié)，DOUBLE 8 字節(jié)）。

DECIMAL(p,s)

• 存儲(chǔ)為 精確的十進(jìn)制數(shù)，按字符串拆分存儲(chǔ)再轉(zhuǎn)成定點(diǎn)數(shù)。
• 能夠完全保持插入時(shí)的數(shù)值，不存在浮點(diǎn)誤差。
• 占用空間取決于 p（精度），一般比浮點(diǎn)類型大。

精度和范圍

• FLOAT
  • 大約 7 位有效十進(jìn)制數(shù)字。
  • 范圍大，±3.402823466E+38。
• DECIMAL(p,s)
  • 精度由 p（總位數(shù)）和 s（小數(shù)位數(shù)）決定。
  • 范圍取決于定義，比如 DECIMAL(20,6) 可支持 20 位數(shù)，6 位小數(shù)。
  • 精度完全可控，不存在近似問題。

注：MySQL8.0以后的版本使用float警告信息更嚴(yán)格

• 在 5.7 里，很多 float 精度問題不會(huì)提示。
• 在 8.0 里，MySQL 在插入 out-of-range 或者 DECIMAL ↔ FLOAT 轉(zhuǎn)換時(shí)，會(huì)給 warning，幫助開發(fā)者發(fā)現(xiàn)潛在風(fēng)險(xiǎn)。

字符串類型

char類型

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

由于char(2)中最多可存儲(chǔ)2個(gè)字符，因此只要插入的字符個(gè)數(shù)不超過2個(gè)都是能夠成功插入的。如下：

說明： char(2) 表示可以存放兩個(gè)字符，可以是字母或漢字，但是不能超過2個(gè)， 最多只能是255

好處：

• 在不同編碼中，一個(gè)字符所占的字節(jié)個(gè)數(shù)是不同的，比如utf8中一個(gè)字符占3個(gè)字節(jié)，而gbk中一個(gè)字符占2個(gè)字節(jié)。MySQL限定字符的概念不是字節(jié)，這樣用戶就不用關(guān)心復(fù)雜的編碼細(xì)節(jié)了。

varchar類型

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

創(chuàng)建一個(gè)表，由于varchar(6)中最多可存儲(chǔ)6個(gè)字符，因此只要插入的字符個(gè)數(shù)不超過6都是能夠成功插入的。如下：

如果插入的字符個(gè)數(shù)超過了6個(gè)，那么在插入數(shù)據(jù)時(shí)就會(huì)產(chǎn)生報(bào)錯(cuò)。如下：

varchar類型可指定的字符個(gè)數(shù)上限

關(guān)于varchar(len),len到底是多大，這個(gè)len值，和表的編碼密切相關(guān)：

• varchar長度可以指定為0到65535之間的值，但是有1 - 3 個(gè)字節(jié)用于記錄數(shù)據(jù)大小，所以說有效字 節(jié)數(shù)是65535-3=65532。
• 當(dāng)我們的表的編碼是utf8時(shí)，varchar(n)的參數(shù)n最大值是65532/3=21844[因?yàn)閡tf中，一個(gè)字符占 用3個(gè)字節(jié)]，如果編碼是gbk，varchar(n)的參數(shù)n最大是65532/2=32766（因?yàn)間bk中，一個(gè)字符 占用2字節(jié)）。

因此在定義編碼格式為utf8的表時(shí)，varchar(L)中的L如果超過了21844，則會(huì)產(chǎn)生報(bào)錯(cuò)。如下：

char和varchar比較

char和varchar的區(qū)別如下：

• char類型可存儲(chǔ)字符上限為255，varchar類型可存儲(chǔ)字符上限與表的編碼格式有關(guān)。
• char(L)定義后，無論存儲(chǔ)的字符串長度是否到達(dá)L，都會(huì)開辟用于存儲(chǔ)L個(gè)字符的定長空間，如果存儲(chǔ)的字符串長度超過L則會(huì)報(bào)錯(cuò)。
• varchar(L)定義后，會(huì)根據(jù)存儲(chǔ)字符串的長度按需開辟空間，并且需要使用1-3字節(jié)的空間用于表示存儲(chǔ)字符串的長度以及其他控制信息，如果存儲(chǔ)的字符串長度超過L則會(huì)報(bào)錯(cuò)。

如何選取char和varchar類型？

char和varchar的優(yōu)缺點(diǎn)如下：

• char類型的數(shù)據(jù)是定長的，因此磁盤空間比較浪費(fèi)，但是效率高（直接訪問定長的空間）。
• varchar類型的數(shù)據(jù)是變長的，因此磁盤空間比較節(jié)省，但是效率低（需要先讀取存儲(chǔ)字符串的長度，再訪問指定長度的空間）。

如果要存儲(chǔ)的數(shù)據(jù)是定長的，那就使用char類型進(jìn)行存儲(chǔ)，比如身份證號(hào)碼、手機(jī)號(hào)、md5等。如果要存儲(chǔ)的數(shù)據(jù)是變長的，那就使用varchar類型進(jìn)行存儲(chǔ)，比如名字、地址等。

日期和時(shí)間類型

常用的日期有如下三個(gè)：

• date :日期 'yyyy-mm-dd' ，占用三字節(jié)
• datetime 時(shí)間日期格式 'yyyy-mm-dd HH:ii:ss' 表示范圍從
• timestamp ：時(shí)間戳，從1970年開始的 四字節(jié)

創(chuàng)建一個(gè)表，表當(dāng)中包含date、datetime和timestamp三種時(shí)間日期類型的列。

查看表結(jié)構(gòu)可以看到，timestamp類型的t3列是不允許為空的，它的默認(rèn)值為CURRENT_TIMESTAMP。

但明顯這里的值不是CURRENT_TIMESTAMP，而是NULL；

這是因?yàn)镸ySQL 8.0 不會(huì)再自動(dòng)給 TIMESTAMP 列加上 NOT NULL DEFAULT CURRENT_TIMESTAMP，現(xiàn)在需要 你自己明確指定。如下圖：

插入數(shù)據(jù)后t3就會(huì)自動(dòng)顯示當(dāng)前的時(shí)間戳

更新數(shù)據(jù)：

enum和set類型

enum和set類型的區(qū)別如下：

• 在定義enum字段時(shí)需要提供若干個(gè)選項(xiàng)的值，在設(shè)置enum字段值時(shí)只允許選取其中的一個(gè)值。
• 在定義set字段時(shí)需要提供若干個(gè)選項(xiàng)的值，在設(shè)置set字段值時(shí)可以選取其中的一個(gè)或多個(gè)值。

比如人的性別只能從男和女中進(jìn)行二選一，因此可以定義成enum類型，而人的愛好在提供的選項(xiàng)中可能存在多個(gè)，因此可以定義成set類型。

調(diào)查表案例

有一個(gè)調(diào)查表votes，需要調(diào)查人的喜好， 比如（登山，游泳，籃球，武術(shù)）中去選擇(可以多選)， （男，女）[單選]

向表中插入記錄時(shí)，被調(diào)查人的性別只能從男和女中進(jìn)行二選一，被調(diào)查人的愛好可以從提供的若干個(gè)選項(xiàng)中進(jìn)行多選一或多選多，多個(gè)愛好之間需要通過英文逗號(hào)隔開。

通過數(shù)字設(shè)置enum

在插入記錄時(shí)，除了通過指明男女來設(shè)置性別，還可以通過插入數(shù)字1和2來設(shè)置性別。

如下：

根本原因在于，MySQL出于效率考慮，在存儲(chǔ)enum值時(shí)實(shí)際存儲(chǔ)的都是數(shù)字，enum中提供的選項(xiàng)值依次對(duì)應(yīng)數(shù)字1、2、3、…，最多65535個(gè)，因此在設(shè)置enum值時(shí)可以通過數(shù)字的方式進(jìn)行設(shè)置。

通過數(shù)字設(shè)置set

在插入記錄時(shí)，除了通過指明多個(gè)選項(xiàng)來設(shè)置愛好，還可以通過數(shù)字的方式來設(shè)置。如下：

set數(shù)字設(shè)置的規(guī)則：

建議：

• 雖然enum和set可以通過數(shù)字的方式進(jìn)行設(shè)置，但嚴(yán)重不推薦這種做法，因?yàn)檫@樣的SQL可讀性太差，導(dǎo)致后期維護(hù)成本變高。

enum和set查找

如果想要篩選出調(diào)查表中所有男同志的信息，那么直接在篩選時(shí)指明gender='男'即可，因?yàn)閑num類型的值只能多選一。如下：

但如果要篩選出調(diào)查表中愛好包含登山的人的信息就比較麻煩了，如果繼續(xù)使用上述方式，那么最終篩選出來的是愛好僅為登山的人的信息。如下：

這時(shí)需要借助find_in_set(str,strlist)函數(shù)，該函數(shù)的作用是查詢strlist中是否包含str，如果包含則返回str在strlist中的位置（從1開始），否則返回0。

通過select可以對(duì)find_in_set函數(shù)進(jìn)行驗(yàn)證，依次查找集合a,b,c中是否包含字符a、b、d，這時(shí)在查找字符a和b時(shí)就會(huì)得到其在集合中的下標(biāo)，而在查找字符d時(shí)就會(huì)得到0值。如下：

這時(shí)就可以通過select搭配find_in_set函數(shù)，來篩選出愛好包含登山的人的信息了。如下：

到此這篇關(guān)于MySQL數(shù)據(jù)類型的文章就介紹到這了,更多相關(guān)mysql數(shù)據(jù)類型內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！