前言

在 MySQL 數(shù)據(jù)庫設(shè)計(jì)中，數(shù)據(jù)類型的選擇直接影響存儲效率、查詢性能和數(shù)據(jù)準(zhǔn)確性。選對數(shù)據(jù)類型能避免存儲空間浪費(fèi)、數(shù)據(jù)溢出、精度丟失等問題，而選錯則可能導(dǎo)致系統(tǒng)隱患（如用INT存儲手機(jī)號導(dǎo)致截?cái)啵?。本文將全面拆?MySQL 核心數(shù)據(jù)類型，結(jié)合實(shí)戰(zhàn)案例講解選型技巧，幫你從 “能用” 升級到 “用好” 數(shù)據(jù)類型。

一. MySQL 數(shù)據(jù)類型分類總覽

MySQL 的數(shù)據(jù)類型豐富，按功能可分為五大類，覆蓋數(shù)值、字符串、日期、特殊類型等場景：

二. 數(shù)值類型：精準(zhǔn)匹配數(shù)字范圍與精度

數(shù)值類型是最常用的類型，核心關(guān)注范圍和精度，避免數(shù)據(jù)溢出或精度丟失。

2.1 整數(shù)類型（BIT/TINYINT/INT/BIGINT）

整數(shù)類型按占用字節(jié)和范圍分為 5 類，支持UNSIGNED（無符號）修飾（默認(rèn)有符號）：

關(guān)鍵實(shí)戰(zhàn)要點(diǎn)：

• 避免無符號類型（UNSIGNED）：雖然無符號類型能擴(kuò)大正數(shù)范圍，但可能導(dǎo)致溢出時(shí)報(bào)錯（如TINYINT UNSIGNED插入 - 1 直接報(bào)錯），且與有符號類型計(jì)算時(shí)容易出現(xiàn)邏輯問題。建議直接用更大的整數(shù)類型（如用INT替代TINYINT UNSIGNED）。
• BIT 類型的坑：BIT 字段存儲的是位數(shù)據(jù)，查詢時(shí)會按 ASCII 碼顯示（如bit(8)存儲 10 顯示為換行符，存儲 65 顯示為 ‘A’），僅適合存儲 0/1 等簡單標(biāo)識：

CREATE TABLE tt5(gender bit(1));
INSERT INTO tt5 VALUES(0); -- 成功
INSERT INTO tt5 VALUES(1); -- 成功
INSERT INTO tt5 VALUES(2); -- 越界報(bào)錯（bit(1)僅支持0/1）

2.1.1 TINYINT 類型測試

TINYINT 占用 1 字節(jié)，有符號范圍 - 128~127，無符號范圍 0~255：

-- 1. 創(chuàng)建有符號TINYINT表
CREATE TABLE test_tinyint1(age TINYINT);
-- 2. 插入合法值
INSERT INTO test_tinyint1 VALUES(127); -- 成功（最大值）
INSERT INTO test_tinyint1 VALUES(-128); -- 成功（最小值）
-- 3. 插入越界值（報(bào)錯）
INSERT INTO test_tinyint1 VALUES(128); 
-- 報(bào)錯：Out of range value for column 'age' at row 1
-- 4. 創(chuàng)建無符號TINYINT表
CREATE TABLE test_tinyint2(age TINYINT UNSIGNED);
-- 5. 插入無符號合法值
INSERT INTO test_tinyint2 VALUES(255); -- 成功（無符號最大值）
-- 6. 插入負(fù)數(shù)（無符號越界報(bào)錯）
INSERT INTO test_tinyint2 VALUES(-1); 
-- 報(bào)錯：Out of range value for column 'age' at row 1

2.1.2 BIT 類型測試

BIT 存儲位數(shù)據(jù)，默認(rèn) 1 位（僅支持 0/1），位數(shù) M 需≤64，查詢時(shí)按 ASCII 碼顯示（易踩坑）,但是我自己實(shí)際測試也有可能會是16進(jìn)制的，大家可以自己去試試：

-- 1. 創(chuàng)建BIT(1)字段的表
CREATE TABLE test_bit(gender BIT(1));
-- 2. 插入合法值（0/1）
INSERT INTO test_bit VALUES(0); -- 成功
INSERT INTO test_bit VALUES(1); -- 成功
-- 3. 插入越界值（報(bào)錯）
INSERT INTO test_bit VALUES(2); 
-- 報(bào)錯：Data truncation: Data too long for column 'gender' at row 1
-- 4. 查詢BIT字段（關(guān)鍵：直接查詢顯示ASCII字符，需轉(zhuǎn)成數(shù)字）
SELECT gender, bin(gender+0) FROM test_bit;

運(yùn)行結(jié)果：

+--------+---------------+
| gender | bin(gender+0) |
+--------+---------------+
|       | 0             | -- 0的ASCII顯示為空白，轉(zhuǎn)數(shù)字后是0
|       | 1             | -- 1的ASCII顯示為，轉(zhuǎn)數(shù)字后是1
+--------+---------------+

2.1.3 INT/BIGINT 對比測試

-- 1. INT存儲手機(jī)號（越界測試）
CREATE TABLE test_int(phone INT);
INSERT INTO test_int VALUES(13800138000); 
-- 報(bào)錯：Out of range value for column 'phone' at row 1（INT最大值2147483647 < 13800138000）
-- 2. BIGINT存儲手機(jī)號（成功）
CREATE TABLE test_bigint(phone BIGINT);
INSERT INTO test_bigint VALUES(13800138000); -- 成功
SELECT * FROM test_bigint;
+-------------+
| phone       |
+-------------+
| 13800138000 |
+-------------+

2.2 小數(shù)類型（FLOAT/DOUBLE/DECIMAL）

小數(shù)類型用于存儲帶小數(shù)點(diǎn)的數(shù)值，核心區(qū)別在于精度：
以下是您需要的表格，已整理為清晰格式：

2.2.1 FLOAT/DECIMAL 精度對比測試

-- 1. 創(chuàng)建小數(shù)測試表
CREATE TABLE test_decimal(
  salary_float FLOAT(10,8),
  salary_decimal DECIMAL(10,8)
);
-- 2. 插入相同數(shù)值
INSERT INTO test_decimal VALUES(23.12345612, 23.12345612);
-- 3. 查詢結(jié)果（精度差異）
SELECT * FROM test_decimal;

運(yùn)行結(jié)果：

+---------------+----------------+
| salary_float  | salary_decimal |
+---------------+----------------+
| 23.12345695   | 23.12345612    |
+---------------+----------------+

• 發(fā)現(xiàn)decimal的精度更準(zhǔn)確，因此如果我們希望某個數(shù)據(jù)表示高精度，選擇decimal
• 關(guān)鍵結(jié)論：FLOAT 是近似存儲，存在精度丟失；DECIMAL 是精確存儲，適合金額、稅率等場景。
• float表示的精度大約是7位，decimal整數(shù)最大位置m為65，支持小數(shù)最大位置d是30，如果d被省略，默認(rèn)位0，如果m被省略，默認(rèn)是10。建議如果希望小數(shù)的精度高，推薦使用decimal。

2.2.2 DECIMAL 范圍測試

CREATE TABLE test_decimal2(price DECIMAL(5,2)); -- 總長度5，小數(shù)位2 → 范圍-999.99~999.99
INSERT INTO test_decimal2 VALUES(999.99); -- 成功
INSERT INTO test_decimal2 VALUES(1000.00); -- 報(bào)錯：Out of range value for column 'price' at row 1

三. 字符串類型：CHAR 與 VARCHAR 的終極選型

字符串類型是最容易用錯的類型，核心是區(qū)分CHAR（固定長度）和VARCHAR（可變長度），以及TEXT（大文本）的適用場景。

3.1 核心字符串類型對比

以下是您需要的表格：

3.2 CHAR 與 VARCHAR 深度測試

-- 1. 創(chuàng)建CHAR/VARCHAR對比表（UTF8編碼，1字符=3字節(jié)）
CREATE TABLE test_char_varchar(
  c_char CHAR(4),
  c_varchar VARCHAR(4)
) CHARSET=utf8;
-- 2. 插入不同長度數(shù)據(jù)
INSERT INTO test_char_varchar VALUES('abcd', 'abcd'); -- 4字符
INSERT INTO test_char_varchar VALUES('A', 'A'); -- 1字符
INSERT INTO test_char_varchar VALUES('中國', '中國'); -- 2字符
-- 3. 查詢數(shù)據(jù)（查看實(shí)際存儲）
SELECT 
  c_char, LENGTH(c_char), -- CHAR長度：固定12字節(jié)（4×3）
  c_varchar, LENGTH(c_varchar) -- VARCHAR長度：實(shí)際字符×3 + 1字節(jié)
FROM test_char_varchar;

運(yùn)行結(jié)果：

+--------+---------------+------------+------------------+
| c_char | LENGTH(c_char)| c_varchar  | LENGTH(c_varchar)|
+--------+---------------+------------+------------------+
| abcd   |            12 | abcd       |               13 |
| A      |            12 | A          |                4 |
| 中國   |             6 | 中國       |                7 |
+--------+---------------+------------+------------------+

關(guān)鍵結(jié)論：

• CHAR (4) 無論存儲多少字符，都占用 12 字節(jié)（4×3），不足補(bǔ)空格；
• VARCHAR (4) 存儲 1 字符占用 4 字節(jié)（3+1），2 字符占用 7 字節(jié)（6+1），4 字符占用 13 字節(jié)（12+1），更節(jié)省空間。

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

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

3.3 VARCHAR 長度限制測試

-- UTF8編碼下，VARCHAR最大字符數(shù)=65532/3≈21844（預(yù)留1-3字節(jié)存長度）
CREATE TABLE test_varchar_limit1(name VARCHAR(21845)) CHARSET=utf8; 
-- 報(bào)錯：Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.
CREATE TABLE test_varchar_limit2(name VARCHAR(21844)) CHARSET=utf8; -- 成功

四. 日期時(shí)間類型：DATE/DATETIME/TIMESTAMP 選型

日期時(shí)間類型用于存儲時(shí)間相關(guān)數(shù)據(jù)，核心是區(qū)分三者的存儲范圍、時(shí)區(qū)特性和自動更新機(jī)制：

4.1 TIMESTAMP 自動更新測試

-- 1. 創(chuàng)建日期測試表
CREATE TABLE test_datetime(
  t1 DATE,
  t2 DATETIME,
  t3 TIMESTAMP
);
-- 2. 僅插入DATE/DATETIME，TIMESTAMP自動填充當(dāng)前時(shí)間
INSERT INTO test_datetime(t1,t2) VALUES('1997-07-01','2008-08-08 12:01:01');
-- 3. 查詢初始結(jié)果
SELECT * FROM test_datetime;

運(yùn)行結(jié)果 1：

+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 1997-07-01 | 2008-08-08 12:01:01 | 2024-05-20 15:30:22 | -- t3自動填充當(dāng)前時(shí)間
+------------+---------------------+---------------------+

-- 4. 更新數(shù)據(jù)，TIMESTAMP自動刷新
UPDATE test_datetime SET t1='2000-01-01';
-- 5. 查詢更新結(jié)果
SELECT * FROM test_datetime;

運(yùn)行結(jié)果 2：

+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2000-01-01 | 2008-08-08 12:01:01 | 2024-05-20 15:35:48 | -- t3自動更新為修改時(shí)間
+------------+---------------------+---------------------+

五. 特殊字符串類型：ENUM 與 SET（單選 / 多選場景）

ENUM（枚舉）和SET（集合）適用于固定選項(xiàng)的場景，避免手動校驗(yàn)輸入合法性。

5.1 ENUM（單選枚舉）測試

-- 1. 創(chuàng)建枚舉表
CREATE TABLE test_enum(
  username VARCHAR(30),
  gender ENUM('男','女')
);
-- 2. 插入合法值
INSERT INTO test_enum VALUES('雷鋒','男'); -- 成功
INSERT INTO test_enum VALUES('小紅',2); -- 成功（2對應(yīng)第二個選項(xiàng)'女'）
-- 3. 插入非法值（報(bào)錯）
INSERT INTO test_enum VALUES('Tom','未知'); 
-- 報(bào)錯：Data truncated for column 'gender' at row 1
-- 4. 查詢枚舉值
SELECT username, gender, gender+0 FROM test_enum; -- gender+0查看枚舉對應(yīng)的數(shù)字

• 運(yùn)行結(jié)果：

+----------+--------+-----------+
| username | gender | gender+0  |
+----------+--------+-----------+
| 雷鋒     | 男     |         1 |
| 小紅     | 女     |         2 |
+----------+--------+-----------+

5.2 SET（多選集合）測試

-- 1. 創(chuàng)建集合表
CREATE TABLE test_set(
  username VARCHAR(30),
  hobby SET('登山','游泳','籃球','武術(shù)')
);
-- 2. 插入多選值
INSERT INTO test_set VALUES('雷鋒','登山,武術(shù)'); -- 成功
INSERT INTO test_set VALUES('小紅','游泳'); -- 成功（單選）
-- 3. 插入非法值（自動忽略）
INSERT INTO test_set VALUES('Tom','登山,游戲'); -- '游戲'不是選項(xiàng)，僅插入'登山'
-- 4. 查詢包含某選項(xiàng)的記錄（find_in_set函數(shù)）
SELECT * FROM test_set WHERE FIND_IN_SET('登山', hobby);

• 運(yùn)行結(jié)果：

+----------+-----------+
| username | hobby     |
+----------+-----------+
| 雷鋒     | 登山,武術(shù) |
| Tom      | 登山      |
+----------+-----------+

• set的這個也可以用數(shù)字來代表，不過是由比特位來算的，比如1代表登山，2代表游泳，3代表的是登山和游泳(0011)

六. 數(shù)據(jù)類型選型避坑指南和總結(jié)

• 按 “最小必要” 原則選型：如年齡用TINYINT（0-255 足夠），不用INT；金額用DECIMAL(10,2)，不用FLOAT。
• 避免用字符串存儲數(shù)字：如手機(jī)號用BIGINT或CHAR(11)，不用VARCHAR(11)（CHAR查詢更快）；身份證用CHAR(18)（固定長度），不用VARCHAR。
• BIT 類型查詢需轉(zhuǎn)數(shù)字：直接查詢 BIT 字段顯示 ASCII 字符，需用gender+0或CAST(gender AS UNSIGNED)轉(zhuǎn)為數(shù)字。
• 慎用 TEXT/BLOB：大文本類型會降低查詢性能，若能拆分字段（如將文章摘要單獨(dú)存儲為VARCHAR），盡量避免直接用TEXT。
• 日期類型優(yōu)先選 DATETIME：除非明確需要時(shí)區(qū)轉(zhuǎn)換或自動更新，否則DATETIME的兼容性和范圍更優(yōu)，避免TIMESTAMP的 2038 限制。

總結(jié)： MySQL 數(shù)據(jù)類型的選擇核心是 “匹配場景 + 平衡性能與空間”，本文補(bǔ)充了 PPT 中所有核心測試案例，關(guān)鍵要點(diǎn)總結(jié)：

• BIT/TINYINT 有嚴(yán)格范圍限制，插入越界值會直接報(bào)錯，查詢 BIT 需轉(zhuǎn)數(shù)字；
• FLOAT 存在精度丟失，金額等精確場景必須用 DECIMAL；
• CHAR 適合固定長度字符串（如手機(jī)號），VARCHAR 適合可變長度字符串（如姓名）；
• TIMESTAMP 支持自動更新，但有 2038 年限制，普通場景優(yōu)先用 DATETIME；
• ENUM/SET 簡化固定選項(xiàng)存儲，SET 查詢需用 find_in_set 函數(shù)。

到此這篇關(guān)于MySQL 數(shù)據(jù)類型核心指南之選型、實(shí)戰(zhàn)與避坑的文章就介紹到這了,更多相關(guān)mysql數(shù)據(jù)類型選型內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論