MySQL 進階數據類型深度解析：ENUM、SET、二進制類型與選型指南

MySQL 進階數據類型深度解析：ENUM、SET、二進制類型與選型指南

MySQL 除常用數值/字串/日期類型外，還提供 ENUM、SET、多種二進制類型。本文詳細解析其存儲原理、索引映射、取值限制、尾部空格處理差異，並給出「如何選擇數據類型」六大維度決策框架。

ENUM 枚舉類型

• 語法：col ENUM('v1','v2',...,'vn')
• 最多 65,535 個元素
• 內部用整數索引存儲：NULL→NULL、''→0、'v1'→1...
• 插入值必須在定義列表中，否則存空串（索引 0）並產生 Warning
• 尾部空格自動截斷
• 排序按索引順序，空串 < NULL < 列表值

CREATE TABLE t (level ENUM('excellent','good','bad'));
INSERT INTO t VALUES ('good'), (1), (2), (3); -- 1/2/3 自動映射為對應枚舉值
-- INSERT INTO t VALUES ('best'); -- Warning: Data truncated, 存空串

SET 集合類型

• 語法：col SET('v1','v2',...,'vn')
• 最多 64 個成員
• 內部用位圖存儲：每個值佔 1 bit，組合值為位或運算結果
• 可存零個或多個值，值間用逗號分隔：'v1,v3'
• 插入重複值自動去重，順序不影響存儲（按定義順序輸出）
• 非法值被忽略並 Warning

CREATE TABLE t (tags SET('a','b','c'));
INSERT INTO t VALUES ('a,b'), ('b,a'), ('a,a,b'); -- 存儲均為 'a,b'
SELECT * FROM t; -- 輸出 a,b / a,b / a,b

二進制類型家族

對應非二進制文本類型：TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT，字符集相關，不直接存二進制。

六大維度選型決策框架

1. 整數 vs 浮點 vs 定點：無小數用整數；有小數且要求精確（金額）用 DECIMAL；科學計算可接受誤差用 FLOAT/DOUBLE。避免 FLOAT(M,D) 非標準語法。
2. 日期時間選型：僅年份用 YEAR；僅時間用 TIME；日期+時間用 DATETIME（範圍大）或 TIMESTAMP（自動更新、範圍小至 2038）。
3. CHAR vs VARCHAR：定長高頻更新用 CHAR（MyISAM 靜態表更快）；可變長用 VARCHAR 節省空間。InnoDB 不區分存儲格式，推薦 VARCHAR。
4. ENUM vs SET：單選用 ENUM；多選用 SET。
5. BLOB vs TEXT：二進制（圖片/音頻）用 BLOB；純文本用 TEXT。
6. JSON（5.7+）：半結構化、頻繁 schema 變更、需索引內部欄位時優先考慮 JSON 類型。

避坑清單

• ENUM 索引值從 1 起，ORDER BY enum_col 按索引排序而非字面值排序。
• SET 成員超過 64 會報錯，改用關聯表或 JSON。
• BLOB/TEXT 不能有默認值，GROUP BY/ORDER BY 只用前 max_sort_length 字節（預設 1024）。
• CHAR 尾部空格會被截斷，VARCHAR 保留，比較時需注意 PAD_CHAR_TO_FULL_LENGTH SQL 模式影響。