MySQL Schema 設計精髓（二）：精打細算，油鹽不斷——數據類型選型深度解析

前言：DBA 的「過日子」哲學

「再看紅英自己，那是連半個鐘頭的工都不肯耽誤的，也從沒見她吃過一口零食，一看就知道是個會精打細算、會過日子的人。」—— 茹志鵑《妯娌》

已婚身份最適合 DBA：不當家不知柴米貴，年底資源容量採購，每一分都是心頭肉。故障診斷與性能調優時，OS 層 APP 拖垮 DB 的案例比比皆是。**慎重選擇數據類型，持有斤斤計較的心思，是 DBA 的傳統美德。**

一、三大核心數據類型深度剖析

1.1 INT(M) —— M 到底有多「M」？

結論先行： INT(M) 中的 M 僅表示顯示寬度，與存儲大小、取值範圍無關。無論 M 為多少，INT 永遠佔用 4 字節，範圍 -2147483648 ~ 2147483647（無符號 0 ~ 4294967295）。

• M 默認 11，最大有效 255
• 離開 ZEROFILL 屬性，M 毫無意義
• INT(11) 與 INT(255) 存儲、計算完全相同

實驗驗證

mysql> CREATE TABLE t (id INT(2));
mysql> INSERT INTO t SELECT 10086;
mysql> SELECT * FROM t;        -- 正常顯示 10086
mysql> ALTER TABLE t CHANGE id id INT(16);
mysql> SELECT * FROM t;        -- 仍顯示 10086
mysql> ALTER TABLE t CHANGE id id INT(16) ZEROFILL;
mysql> SELECT * FROM t;        -- 顯示 0000000000010086（左補 0 到 16 位）
mysql> ALTER TABLE t CHANGE id id INT(5) ZEROFILL;
mysql> SELECT * FROM t;        -- 顯示 10086（寬度不足不補 0）
mysql> ALTER TABLE t CHANGE id id INT(6) ZEROFILL;
mysql> SELECT * FROM t;        -- 顯示 010086

啟示： 除非業務強制要求前導零展示（如訂單號固定位數），**不要給 INT 指定 M，更不要加 ZEROFILL**，增加解析開銷且易誤導開發以為影響範圍。

1.2 VARCHAR(N) —— N 最大值怎麼算？

面試題： 單表單字段 VARCHAR(N) CHARSET utf8，N 最大值？

約束條件梳理

1. 最大行長度 65,535 字節（NDB 引擎除外）
2. 字符集字節數： latin1=1, gbk=2, utf8=3, utf8mb4=4
3. 長度前綴開銷： 實際長度 < 255 用 1 字節，≥ 255 用 2 字節
4. 行頭開銷： 至少 1 字節（MySQL 內部記錄頭）

計算邏輯

-- utf8，單字符 3 字節，長度 > 255 需 2 字節前綴
可用字節 = 65535 - 1(行頭) - 2(長度前綴) = 65532
N_max = 65532 / 3 = 21844

實測驗證

mysql> CREATE TABLE max_len_varchar(col VARCHAR(65533) CHARSET latin1);
ERROR 1118 (42000): Row size too large...
mysql> CREATE TABLE max_len_varchar(col VARCHAR(65532) CHARSET latin1);
Query OK  -- latin1 單字節，65532 為極限

延伸題： CREATE TABLE t (col1 INT(11), col2 CHAR(50), col3 VARCHAR(N)) CHARSET=utf8; N 最大值？
INT: 4字節, CHAR(50) utf8: 150字節, 行頭1, 長度前綴2 → 65535-4-150-1-2 = 65378 → N=21792

1.3 TIMESTAMP 那些事兒

DATETIME 的「Bug」

mysql> CREATE TABLE t (start_time DATETIME, stop_time DATETIME);
mysql> INSERT INTO t VALUES ('2014-01-19 21:46:18', '2014-01-20 00:21:31');
mysql> SELECT stop_time - start_time FROM t;  -- 返回 787513（數字相減，非時間差）

DATETIME 不支持直接相減，會隱式轉為數字導致錯誤結果。應用 TIMESTAMPDIFF() 或 UNIX_TIMESTAMP() 計算差值。

為何首選 TIMESTAMP？

• 空間效率最高（4 字節）
• 自帶時區轉換，跨時區應用友好
• MySQL 提供 FROM_UNIXTIME()、UNIX_TIMESTAMP() 互轉，無需手工維護

版本差異巨大（5.1/5.5 vs 5.6+）

CREATE TABLE t (col TIMESTAMP);
-- 5.1/5.5:  `col` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-- 5.6+:     `col` TIMESTAMP NULL DEFAULT NULL

建議： 顯式指定所有屬性：col TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，避免版本升級行為變更。

跨時區備份陷阱與解決

mysqldump 會注入 /*!40103 SET TIME_ZONE='+00:00' */;，導致 WHERE col='2014-01-25 10:42:44' 導不出數據。

方案一： mysqldump --tz-utc=0 ...
方案二： 用 UNIX_TIMESTAMP() 轉換：

SELECT UNIX_TIMESTAMP(col) FROM t;  -- 得到 1390617764
mysqldump ... --where="col=FROM_UNIXTIME(1390617764)"

二、數據類型轉換：索引失效的隱形殺手

基本原則

1. 不要在列上加函數：WHERE DATE(created_at) = '2024-01-01' → 索引失效
2. 不要將列嵌入表達式：WHERE id + 1 = 100 → 索引失效

正確寫法

-- 範圍查詢利用索引
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'

-- 反向表達式移到常量側
WHERE id = 100 - 1

三、數據類型優化通用原則

1. 更小通常更好：能用 TINYINT 不用 INT，能用 CHAR(32) 存 MD5 不用 VARCHAR(255)
2. 越簡單越好：整數 > 字符串 > JSON/Blob；定長 > 變長（MyISAM）；變長 > 定長（InnoDB）
3. 多用 ENUM/SET：低基數枚舉類型（狀態、性別、類型）用 ENUM，1-2 字節存 65535 個值
4. IP 存 INT：INET_ATON('192.168.1.1') → 3232235777，INET_NTOA() 反向
5. 金額用 DECIMAL：拒絕 FLOAT/DOUBLE 精度陷阱，DECIMAL(19,4) 足夠大多數貨幣場景
6. 引擎適配：MyISAM 多用 CHAR（定長快），InnoDB 多用 VARCHAR（變長不佔用額外頁面）

四、總結

Schema 設計無小事，每一個字節的選擇都在為未來的查詢性能、存儲成本、維護難度投票。**精打細算，不是吝嗇，是對資源的敬畏，是對性能的負責。** 下次建表前，請三問自己：

1. 這個類型夠不夠小？
2. 這個類型會不會讓索引失效？
3. 這個類型跨時區、跨版本安不安全？