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

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

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

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

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

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

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

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

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

數(shù)值類型

tinyint類型

有符號tinyint范圍測試

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

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

無符號tinyint范圍測試

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

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

建議：

盡量不使用unsigned，對于int類型可能存放不下的數(shù)據(jù)，int unsigned同樣可能存放不下，與其如此，還不如設(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)建一個表，表當(dāng)中包含用戶名name和用戶性別gender，其中g(shù)ender的類型可以指定為1位bit類型，因為性別只有男和女兩種取值，使用1個比特位來表示用戶的性別就可以節(jié)省空間。如下：

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

建議：

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

float類型

有符號float范圍測試

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

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

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

有符號float范圍測試

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

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

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

為什么這里和tinyint類型的無符號取值不一樣？

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

有符號 TINYINT：范圍是 -128 ~ 127，總共 256 個值。
無符號 TINYINT：范圍是 0 ~ 255，總共也是 256 個值。

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

浮點類型（FLOAT/DOUBLE）

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

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

換句話說：

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

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

decimal類型

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

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

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

存儲方式

FLOAT / DOUBLE

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

DECIMAL(p,s)

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

精度和范圍

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 精度問題不會提示。
在 8.0 里，MySQL 在插入 out-of-range 或者 DECIMAL ↔ FLOAT 轉(zhuǎn)換時，會給 warning，幫助開發(fā)者發(fā)現(xiàn)潛在風(fēng)險。

字符串類型

char類型

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

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

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

好處：

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

varchar類型

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

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

如果插入的字符個數(shù)超過了6個，那么在插入數(shù)據(jù)時就會產(chǎn)生報錯。如下：

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

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

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

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

char和varchar比較

實際存儲	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ù)超過長度	數(shù)據(jù)超過長度

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

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