快捷導(dǎo)航

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

更新時(shí)間：2025年10月19日 13:06:34 作者：景風(fēng)~

本文詳細(xì)給大家介紹了MySQL數(shù)據(jù)類型的相關(guān)知識(shí),本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧

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

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

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

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

分類	數(shù)據(jù)類型	說(shuō)明
數(shù)值類型	`BIT(M)`	位類型：M 指定位數(shù)，默認(rèn)值為 1，范圍為 1–64
	`BOOL`	布爾類型：使用 1 表示真，使用 0 表示假
	`TINYINT [UNSIGNED]`	占用 1 字節(jié)，默認(rèn)為有符號(hào)
	`SMALLINT [UNSIGNED]`	占用 2 字節(jié)，默認(rèn)為有符號(hào)
	`MEDIUMINT [UNSIGNED]`	占用 3 字節(jié)，默認(rèn)為有符號(hào)
	`INT [UNSIGNED]`	占用 4 字節(jié)，默認(rèn)為有符號(hào)
	`BIGINT [UNSIGNED]`	占用 8 字節(jié)，默認(rèn)為有符號(hào)
	`FLOAT[(M,D)] [UNSIGNED]`	M 指定顯示長(zhǎng)度，D 指定小數(shù)位數(shù)，占用 4 字節(jié)
	`DOUBLE[(M,D)] [UNSIGNED]`	M 指定顯示長(zhǎng)度，D 指定小數(shù)位數(shù)，占用 8 字節(jié)
	`DECIMAL(M,D) [UNSIGNED]`	M 指定顯示長(zhǎng)度，D 指定小數(shù)位數(shù)；每 4 個(gè)字節(jié)表示 9 個(gè)數(shù)字，小數(shù)點(diǎn)占用 1 字節(jié)
文本、二進(jìn)制類型	`CHAR(L)`	固定長(zhǎng)度字符串：L 指定字符串長(zhǎng)度，最大為 255
	`VARCHAR(L)`	可變長(zhǎng)度字符串：L 指定字符串長(zhǎng)度上限，最多占用 65535 字節(jié)
	`BLOB`	用于存儲(chǔ)二進(jìn)制數(shù)據(jù)
	`TEXT`	用于存儲(chǔ)大文本數(shù)據(jù)
時(shí)間日期	`DATE / DATETIME`	日期類型：`YYYY-MM-DD` 格式 / `YYYY-MM-DD HH:MM:SS` 格式
時(shí)間日期	`TIMESTAMP`	時(shí)間戳：以 `YYYY-MM-DD HH:MM:SS` 格式進(jìn)行顯示
字符串類型	`ENUM`	枚舉類型：在定義字段時(shí)指定取值范圍；只能從成員中選取單個(gè)值；存儲(chǔ)空間由成員個(gè)數(shù)決定
字符串類型	`SET`	集合類型：在定義字段時(shí)指定取值范圍；可從成員中選取一個(gè)或多個(gè)值；存儲(chǔ)空間由成員個(gè)數(shù)決定

注：MySQL本身是不支持bool類型的，當(dāng)把一個(gè)數(shù)據(jù)設(shè)置成bool類型時(shí)，數(shù)據(jù)庫(kù)會(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范圍測(cè)試

由于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ò)。如下：

無(wú)符號(hào)tinyint范圍測(cè)試

由于tinyint類型占用1字節(jié)，因此無(wú)符號(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)開(kāi)啟了 --binary-as-hex。
只要列是二進(jìn)制類型（BIT / BINARY / VARBINARY / BLOB），客戶端就用十六進(jìn)制顯示，以避免亂碼。所以 BIT(8) 存了十進(jìn)制 10，我們看到的就是 0x0A。

bit類型的范圍測(cè)試

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

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

建議：

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

float類型

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

float[(m, d)] [unsigned] : M指定顯示長(zhǎng)度，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范圍測(cè)試

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

無(wú)符號(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類型的無(wú)符號(hào)取值不一樣？

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

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

?? 可以看到：總數(shù)相等，只是符號(hào)位的解釋不同。MySQL 對(duì)整數(shù)采用定長(zhǎng)存儲(chǔ)（補(bǔ)碼），有符號(hào)與無(wú)符號(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)位”重新拿來(lái)擴(kuò)展有效數(shù)值范圍。

換句話說(shuō)：

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

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

decimal類型

decimal(m, d) [unsigned] : 定點(diǎn)數(shù)m指定長(zhǎng)度，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ǔ)空間較?。?code>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ù)。
- 精度完全可控，不存在近似問(wèn)題。

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

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

字符串類型

char類型

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

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

說(shuō)明： char(2) 表示可以存放兩個(gè)字符，可以是字母或漢字，但是不能超過(guò)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): 可變長(zhǎng)度字符串，L表示字符長(zhǎng)度，最大長(zhǎng)度65535個(gè)字節(jié)

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

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

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

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

varchar長(zhǎng)度可以指定為0到65535之間的值，但是有1 - 3 個(gè)字節(jié)用于記錄數(shù)據(jù)大小，所以說(shuō)有效字節(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如果超過(guò)了21844，則會(huì)產(chǎn)生報(bào)錯(cuò)。如下：

char和varchar比較

實(shí)際存儲(chǔ)	char(4)	varchar(4)	char 占用字節(jié)	varchar 占用字節(jié)
abcd	abcd	abcd	4*3=12	4*3+1=13
A	A	A	4*3=12	1*3+1=4
Abcde	?	?	數(shù)據(jù)超過(guò)長(zhǎng)度	數(shù)據(jù)超過(guò)長(zhǎng)度

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

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

如何選取char和varchar類型？

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

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

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

日期和時(shí)間類型

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

date :日期 'yyyy-mm-dd' ，占用三字節(jié)
datetime 時(shí)間日期格式 'yyyy-mm-dd HH:ii:ss' 表示范圍從
timestamp ：時(shí)間戳，從1970年開(kāi)始的四字節(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類型，而人的愛(ài)好在提供的選項(xiàng)中可能存在多個(gè)，因此可以定義成set類型。

調(diào)查表案例

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

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

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

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

如下：

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

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

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

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

建議：

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

enum和set查找

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

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

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

通過(guò)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值。如下：