承接上篇，本文深入探討 MySQL Schema 設計中資料型別的選擇藝術：INT(M) 顯示寬度迷思、VARCHAR(N) 最大長度計算、TIMESTAMP 版本行為差異、時區與備份陷阱、型別轉換導致索引失效、以及通用優化原則。
一、三大核心型別深度解析
1. INT(M) 的 M 到底有多大？
M 預設 11，最大 255，僅影響顯示寬度（搭配 ZEROFILL 時左補零）- 儲存大小固定 4 bytes，值範圍固定 -2147483648 ~ 2147483647
INT(2) 照樣能存 10086；INT(16) ZEROFILL 顯示 0000000000010086- 結論：離開
ZEROFILL，M 毫無意義，INT(11) 與 INT(255) 完全等價
mysql> CREATE TABLE t (id INT(2));
mysql> INSERT INTO t VALUES (10086);
mysql> SELECT * FROM t; -- 10086
mysql> ALTER TABLE t MODIFY id INT(16) ZEROFILL;
mysql> SELECT * FROM t; -- 0000000000010086
2. VARCHAR(N) 最大 N 怎麼算？
限制條件：
- 單行最大 65,535 bytes（不含 BLOB/TEXT 溢位頁）
- 字符集佔用：
latin1=1、gbk=2、utf8=3、utf8mb4=4 bytes/字符
- 長度前綴：<255 用 1 byte，≥255 用 2 bytes
- 額外 1 byte 行開銷
面試題：單表單欄 VARCHAR(N) CHARSET utf8，N 最大值？
(65535 - 1 - 2) / 3 = 21844
實測驗證：
mysql> CREATE TABLE t (c VARCHAR(65532) CHARSET latin1); -- OK
mysql> CREATE TABLE t (c VARCHAR(65533) CHARSET latin1); -- ERROR 1118 Row size too large
延伸題：CREATE TABLE t (col1 INT, col2 CHAR(50), col3 VARCHAR(N)) CHARSET=utf8; N 最大值？
65535 - 4 - 150 - 1 - 2 = 65378 bytes → 65378 / 3 = 21792
3. TIMESTAMP 那些事
DATETIME 直接相減的坑：
mysql> SELECT stop_time - start_time FROM t;
-- 2014-01-20 00:21:31 - 2014-01-19 21:46:18 = 787513 (數字相減，非時間差)
正確做法：TIMESTAMPDIFF(SECOND, start_time, stop_time)
為何首選 TIMESTAMP？
- 儲存 4 bytes vs DATETIME 8 bytes（5.6.4 前）
- 自帶時區轉換（儲存 UTC，讀取依 session time_zone）
INT UNSIGNED 存 Unix 時間戳無額外收益，反而失去時區語意
版本行為巨大差異（同一 DDL CREATE TABLE t (col TIMESTAMP)）：
| 版本 | SHOW CREATE TABLE 輸出 |
|---|
| 5.1 / 5.5 | col TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
| 5.6+ | col TIMESTAMP NULL DEFAULT NULL（需顯式指定預設值） |
最佳實踐：永遠顯式指定 DEFAULT CURRENT_TIMESTAMP、ON UPDATE CURRENT_TIMESTAMP、NULL/NOT NULL，勿依賴隱式預設值。
時區與 mysqldump 陷阱：
- mysqldump 預設輸出
SET TIME_ZONE='+00:00'
- 若客戶端
time_zone=SYSTEM，WHERE col='2014-01-25 10:42:44' 導不出資料
- 解法：
--tz-utc=0 或用 UNIX_TIMESTAMP() 轉數值再 WHERE col=FROM_UNIXTIME(1390617764)
二、型別轉換導致索引失效：兩大鐵律
- 不要在欄位上加函數：
WHERE DATE(create_time) = '2023-01-01' → 全表掃描
- 不要把欄位嵌入表達式：
WHERE id + 1 = 100 → 全表掃描
正確寫法：WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02'、WHERE id = 99
三、資料型別優化通用原則
- 越小越好：能用
TINYINT 別用 INT；能用 DATE 別用 DATETIME
- 越簡單越好：整數 > 字串；定長 > 變長（特定場景）
- 盡量 NOT NULL：NULL 需額外位圖、複雜優化器判斷、索引統計偏差
- 識別碼用整數：避免 UUID/字串做主鍵/外鍵
- 多用 ENUM/SET：低基數枚舉型別，儲存 1-2 bytes，可读性佳
- IP 存整數：
INET_ATON('192.168.1.1') → 3232235777，INET_NTOA() 反向
- 精確數值用 DECIMAL：
DECIMAL(19,4) 替代 FLOAT/DOUBLE 避免浮點誤差
- 引擎適配：MyISAM 多用
CHAR（定長利於位移）；InnoDB 多用 VARCHAR（變長不佔額外空間，配合頁壓縮更優）
四、實戰檢核清單
- [ ] 所有整數型別是否用最小足夠範圍？
- [ ] 字串長度是否按實際業務上限設定（非盡量大）？
- [ ] 時區敏感欄位是否統一用
TIMESTAMP 並顯式聲明預設值？
- [ ] 核心查詢欄位是否避免函數/表達式包裹？
- [ ] 是否有
ENUM 可替代的狀態/類型欄位？
- [ ] IP、金額、比率等特殊語意欄位型別是否專用化？
Schema 設計是系統效能的基因，每一個型別選擇都在為未來十年的查詢速度投票。斤斤計較，方得始終。
- 上一篇文章： MySQL FEDERATED 引擎實現異構資料庫遠程表訪問（DBLink 替代方案）
- 下一篇文章： Windows Server MySQL 服務啟動失敗 (錯誤 1067) 兩大類原因與解決方案
Copyright ©
程式碼之家 All Rights Reserved
