您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 數據類型完全指南：選型原理、避坑指南與最佳實踐

編輯：MySQL 綜合教學

MySQL 數據類型完全指南：選型原理、避坑指南與最佳實踐

正確選擇數據類型是資料庫設計的基石，直接影響存儲空間、查詢性能、數據精度與維護成本。MySQL 提供豐富的類型體系，本文系統梳理六大類型，解析核心參數含義，給出選型決策樹與避坑清單。

一、整數類型

類型	存儲字節	有符號範圍	無符號範圍	默認顯示寬度
TINYINT	1	-128 ~ 127	0 ~ 255	4
SMALLINT	2	-32768 ~ 32767	0 ~ 65535	6
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215	9
INT / INTEGER	4	-2147483648 ~ 2147483647	0 ~ 4294967295	11
BIGINT	8	-2^63 ~ 2^63-1	0 ~ 2^64-1	20

關鍵點：

顯示寬度 INT(11) 不限制存儲範圍，僅影響 ZEROFILL 填充顯示。
優先使用 UNSIGNED 翻倍正數範圍（如用戶 ID、狀態碼）。
主鍵自增首選 BIGINT UNSIGNED，單表 40 億行後無需擔心耗盡。

二、定點數與浮點數

類型	存儲	精度	適用場景
FLOAT(M,D)	4 字節	~7 位有效數字	科學計算、非精確測量
DOUBLE(M,D)	8 字節	~15 位有效數字	高精度科學計算
DECIMAL(M,D) / NUMERIC	M+2 字節（字符串存儲）	精確到 D 位小數	金額、價格、比率（必選）

鐵律：涉及金錢、精確計算，絕對使用 DECIMAL。浮點數二進制表示天然存在精度誤差（如 0.1+0.2 != 0.3）。DECIMAL(10,2) 表示總長 10 位，小數 2 位，整數部分 8 位。

三、日期時間類型

類型	存儲	範圍	精度	時區感知	典型用途
DATE	3 字節	1000-01-01 ~ 9999-12-31	天	否	生日、入職日期
TIME	3 字節	-838:59:59 ~ 838:59:59	秒	否	時長、工時
YEAR	1 字節	1901 ~ 2155	年	否	年份標記
DATETIME	8 字節	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	秒	否	業務時間（不隨時區變）
TIMESTAMP	4 字節	1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC	秒	是	創建/更新時間戳（自動時區轉換）

選型建議：

需時區轉換、自動更新 → TIMESTAMP（注意 2038 年問題）
業務時間固定、跨世紀 → DATETIME
僅需日期/時間 → DATE/TIME 節省空間
TIMESTAMP 默認 NOT NULL，插入 NULL 自動填充當前時間

四、字符串類型

類型	最大長度	存儲特點	適用場景
CHAR(M)	255 字符	固定長度，右側填充空格	定長代碼（MD5、UUID、郵編、手機號）
VARCHAR(M)	65,535 字符（共享 64KB 行上限）	變長，實際長度+1-2 字節長度前綴	不定長文本（名稱、地址、郵箱）
TINYTEXT	255 字符	不存長度前綴，表外存儲	極少用，優先 VARCHAR
TEXT	64 KB	表外存儲，需 2 字節長度前綴	長文本（文章、描述）
MEDIUMTEXT	16 MB	表外存儲，3 字節長度前綴	大文本
LONGTEXT	4 GB	表外存儲，4 字節長度前綴	超大文本
ENUM	65,535 個元素	1-2 字節存儲索引值	固定集合單選（性別、狀態、類型）
SET	64 個元素	1-8 字節位掩碼存儲	固定集合多選（標籤、權限掩碼）

核心差異：

CHAR 固定長度，讀取快但浪費空間；VARCHAR 節省空間但需額外長度字節，讀取稍慢。
TEXT/BLOB 存儲在表外溢出頁，查詢需額外 IO，**嚴禁 SELECT * 含 TEXT 列**。
ENUM/SET 內部存數字索引，排序按定義順序而非字符順序。

五、二進制類型

類型	對應字符類型	特點	用途
BINARY(M)	CHAR(M)	固定長度，\0 填充	二進制標識、哈希值
VARBINARY(M)	VARCHAR(M)	變長二進制	短二進制數據
BIT(M)	-	位域，1-64 位	布爾標誌組合、權限掩碼
TINYBLOB ~ LONGBLOB	TINYTEXT ~ LONGTEXT	二進制大對象	圖片、文件、序列化對象

最佳實踐：文件/圖片存文件系統或對象存儲（S3/OSS），數據庫僅存路徑/URL。BLOB 會劇增備份恢復時間、緩衝池污染、複製延遲。

六、選型決策樹與避坑清單

決策樹

是數字嗎？→ 整數還是小數？→ 整數：最小容納範圍的 INT 系；小數：精確計算 → DECIMAL，科學計算 → DOUBLE。
是時間嗎？→ 需時區/自動更新 → TIMESTAMP；業務固定時間 → DATETIME；僅日期/時間 → DATE/TIME。
是文本嗎？→ 固定長度 → CHAR；不定長 ≤ 65535 → VARCHAR；超長 → TEXT（拆表）；固定集合單選 → ENUM；多選 → SET。
是二進制嗎？→ 固定長 → BINARY；變長 → VARBINARY；位標誌 → BIT；大對象 → 拆出存路徑。

避坑清單

坑點	後果	正確做法
金額用 FLOAT/DOUBLE	精度丟失，對賬差錯	DECIMAL(10,2) 或整數存分（單位：分）
手機號/身份證用 INT	超出範圍、丟失前導 0	CHAR(11)/CHAR(18) 或 VARCHAR
大量 VARCHAR(255) 實際存極短	索引過大、內存浪費	按實際最大長度定義，如 VARCHAR(20)
主鍵用 BIGINT 隨機值（UUID）	頁分裂、索引碎片、插入性能差	自增 BIGINT 或有序 UUID（COMB/TSID）
表中混用 TEXT/BLOB 且 SELECT *	緩衝池污染、網絡傳輸大、備份慢	拆分到獨立表，按需 JOIN 查詢
ENUM 值頻繁變更	ALTER TABLE 鎖表重建	改用查找表 + 外鍵/應用層字典
TIMESTAMP 用於 2038 年後業務	溢出報錯	改用 DATETIME 或 BIGINT 存毫秒時間戳

總結

「足夠小、足夠精、業務對齊」是選型三原則。每個字節的節省，在億級數據下都是 GB 級的空間與 IO 勝利。定期用 PROCEDURE ANALYSE() 或 SELECT * FROM table PROCEDURE ANALYSE() 讓 MySQL 建議優化類型，配合 EXPLAIN 驗證索引效果，持續演進表結構。