MySQL 數據類型與常用字段屬性深度解析

前言

恰當的數據類型選型是高性能 Schema 設計的基石。本文系統梳理 MySQL 8.0 主流數據類型的存儲特性、適用場景與避坑指南，並詳解 9 大常用字段屬性的實戰意義。

一、日期時間類型

最佳實踐：
• created_at 用 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
• updated_at 用 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
• 歷史/未來日期業務用 DATETIME 避免 2038 年問題

二、數值類型

整數族

選型原則：
• 主鍵 ID 必用 BIGINT UNSIGNED
• 狀態/枚舉類用 TINYINT UNSIGNED
• 計數器預估 < 42億 用 INT UNSIGNED
• INT(11) 中的 11 僅為顯示寬度，不影響存儲範圍

小數族

血淚教訓：FLOAT(5,3) 插入 123.45678 實際存 99.999 (溢出截斷)。財務字段嚴禁使用浮點類型。

三、字符串類型

CHAR vs VARCHAR 核心差異

• 存儲：CHAR 固定佔用 N 字符空間；VARCHAR 按實際長度 + 1-2B 長度前綴。
• 性能：CHAR 定長利於固定偏移量快速定位，無碎片；VARCHAR 更新長度變化產生碎片，需定期 OPTIMIZE TABLE。
• 排序/比較：CHAR 尾部空格被忽略；VARCHAR 保留尾部空格。
• 內存臨時表：MEMORY 引擎將 VARCHAR 轉為定長 CHAR，過大定義會浪費內存。

編碼影響

utf8mb4 下：
• CHAR(32) 固定佔 32×4=128B
• VARCHAR(255) 最大佔 255×4+2=1022B
• 行大小限制 65,535B 含所有列開銷

四、枚舉與集合

慎用：修改成員需 ALTER TABLE 鎖表，不利擴展。推薦用 TINYINT + 應用層字典或查表。

五、IP 地址存儲方案

-- 方案一：無符號 INT (4B) + MySQL 內建函數
INSERT INTO t(ip) VALUES (INET_ATON('192.168.1.1'));  -- 3232235777
SELECT INET_NTOA(ip) FROM t;  -- '192.168.1.1'

-- 方案二：VARBINARY(16) 支持 IPv6
INSERT INTO t(ip) VALUES (INET6_ATON('2001:db8::1'));
SELECT INET6_NTOA(ip) FROM t;

-- PHP 端轉換
sprintf("%u", ip2long('192.168.1.1'));  -- 无符号整型
long2ip($int);

六、九大常用字段屬性詳解

七、外鍵約束

-- 僅 InnoDB 支持
ALTER TABLE `order` 
ADD CONSTRAINT fk_order_user 
FOREIGN KEY (user_id) REFERENCES `user`(id)
ON UPDATE CASCADE ON DELETE RESTRICT;

• CASCADE：級聯更新/刪除
• SET NULL：置空 (需列允許 NULL)
• RESTRICT/NO ACTION：拒絕父表操作

高併發系統常業務層維護引用完整性，避免外鍵鎖爭用與死鎖風險。

八、建表規範速查

1. 每表必有 BIGINT UNSIGNED AUTO_INCREMENT PK
2. 所有列 NOT NULL DEFAULT 'xxx'，業務無特殊含義禁用 NULL
3. 字符集統一 utf8mb4，校對 utf8mb4_unicode_ci 或 _0900_ai_ci
4. 引擎統一 InnoDB
5. 冗余字段：created_at, updated_at, deleted_at (軟刪除)
6. 命名：小寫 + 下劃線，tbl_ 前綴可選，索引 idx_/uk_
7. 單表行數超 2000 萬或體積超 10GB 考慮分表/分區

合理的類型選型與屬性約束，能從源頭避免 80% 的數據質量與性能問題。