MySQL 數據類型全面指南：詳細說明與關鍵注意事項

MySQL 提供了豐富的數據類型，合理選擇對數據庫性能、存儲效率和數據準確性至關重要。以下是所有數據類型的詳細說明及使用注意事項：

---

一、數值類型

整數類型

類型	字節	有符號范圍	無符號范圍	說明
TINYINT	1	-128 ~ 127	0 ~ 255	小整數（如狀態值）
SMALLINT	2	-32,768 ~ 32,767	0 ~ 65,535	較小整數
MEDIUMINT	3	-8M ~ 8M-1	0 ~ 16M-1	中等整數
INT	4	-2.1B ~ 2.1B-1	0 ~ 4.2B-1	標準整數（最常用）
BIGINT	8	-9.2E18 ~ 9.2E18-1	0 ~ 1.8E19-1	大整數（如主鍵ID）

注意事項：

1. 優先選擇能滿足需求的最小類型（TINYINT > SMALLINT > INT > BIGINT）
2. 無符號整數用 UNSIGNED 關鍵字：INT UNSIGNED
3. ZEROFILL 自動添加 UNSIGNED 并用0填充：INT(5) ZEROFILL
4. 顯示寬度(如INT(11))僅影響顯示，不影響存儲大小

浮點數類型

類型	字節	說明
FLOAT	4	單精度浮點數，約7位精度
DOUBLE	8	雙精度浮點數，約15位精度
DECIMAL	變長	精確小數（財務計算首選）

語法：

DECIMAL(M, D)  -- M=總位數(1-65), D=小數位數(0-30)

注意事項：

1. FLOAT/DOUBLE 有精度損失風險，財務計算必須用 DECIMAL

   -- 錯誤示例
   FLOAT: 0.1 + 0.2 = 0.30000001192092896-- 正確方案
   DECIMAL(10,2): 0.1 + 0.2 = 0.30
   

2. DECIMAL(5,2) 范圍：-999.99 ~ 999.99
3. 存儲空間計算：CEILING(M/9)*4 字節（如 DECIMAL(20,6) 占9字節）

---

二、日期時間類型

類型	格式	范圍	字節	說明
DATE	‘YYYY-MM-DD’	‘1000-01-01’ ~ ‘9999-12-31’	3	日期值
TIME	‘HH:MM:SS[.fraction]’	‘-838:59:59’ ~ ‘838:59:59’	3	時間值（可含毫秒）
DATETIME	‘YYYY-MM-DD HH:MM:SS’	‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’	5-8	日期+時間（推薦使用）
TIMESTAMP	‘YYYY-MM-DD HH:MM:SS’	‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC	4	時間戳（自動轉換時區）
YEAR	YYYY	1901 ~ 2155	1	年份值

注意事項：

1. TIMESTAMP 的 2038年問題：最大到 2038-01-19，新系統建議用 DATETIME
2. 時區處理：
   • TIMESTAMP 存 UTC 時間，檢索時轉當前時區
   • DATETIME 按字面值存儲，不轉換時區
3. 默認值和自動更新：

   -- 自動設置創建時間
   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,-- 自動更新修改時間
   updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   

4. 存儲空間優化：
   • 只需要日期用 DATE (3字節)
   • 只需要時間用 TIME (3字節)

---

三、字符串類型

定長字符串

類型	最大長度	特點
CHAR(n)	255字符	固定長度，空格填充，檢索快

適用場景：固定長度數據（MD5、國家代碼、郵編）

變長字符串

類型	最大長度	特點
VARCHAR(n)	65,535字節	按需存儲，節省空間

注意事項：

1. n 表示字符數而非字節數（UTF8MB4 中 1字符=4字節）
2. 實際存儲空間 = 字符數 × 字符集字節 + 長度前綴(1-2字節)
3. 最大長度限制：

   -- UTF8MB4下實際最大字符數
   65,535 / 4 ≈ 16,383 字符
   

文本類型

類型	最大長度	特點
TINYTEXT	255字節	短文本
TEXT	64KB (65,535字節)	標準文本（文章內容）
MEDIUMTEXT	16MB	較大文本（書籍章節）
LONGTEXT	4GB	超大文本（整個文檔）

注意事項：

1. 與 VARCHAR 區別：
   • TEXT 類型不能有默認值
   • 排序使用磁盤臨時表，VARCHAR 優先用內存
2. 使用建議：
   • < 255字符：VARCHAR
   • 255字符~64KB：TEXT

   • 64KB：MEDIUMTEXT/LONGTEXT

---

四、二進制數據類型

二進制字符串

類型	說明
BINARY(n)	定長二進制（最大255字節）
VARBINARY(n)	變長二進制（最大65,535字節）

適用場景：加密數據、哈希值

二進制大對象（BLOB）

類型	最大長度	說明
TINYBLOB	255字節	小二進制對象
BLOB	64KB	標準二進制對象
MEDIUMBLOB	16MB	中等二進制對象
LONGBLOB	4GB	超大二進制對象

注意事項：

1. 與 TEXT 類型區別：
   • BLOB 存儲二進制數據（如圖片、PDF）
   • TEXT 存儲字符數據
2. 性能影響：大 BLOB 會顯著增加 I/O 負載

---

五、枚舉與集合類型

枚舉類型 (ENUM)

gender ENUM('Male', 'Female', 'Other')

• 特點：單選值，內部存儲為整數（1-2字節）
• 注意事項：
  • 避免超過 65,535 個選項
  • 插入非列表值會報錯（嚴格模式）或存空字符串

集合類型 (SET)

permissions SET('Read', 'Write', 'Delete', 'Admin')

• 特點：多選值，按位存儲（1-8字節）
• 注意事項：
  • 最大64個選項
  • 查詢用 FIND_IN_SET()：WHERE FIND_IN_SET('Write', permissions)

---

六、JSON 類型 (MySQL 5.7+)

user_profile JSON

操作示例：

-- 插入
INSERT INTO users VALUES ('{"name": "John", "age": 30}');-- 查詢
SELECT user_profile->>"$.name" FROM users;-- 更新
UPDATE users SET user_profile = JSON_SET(user_profile, '$.age', 31);

注意事項：

1. 優勢：
   • 自動驗證JSON格式
   • 高效讀取（無需解析整個文檔）
2. 限制：
   • 最大大小同 LONGTEXT (4GB)
   • 不支持直接索引（需生成列+索引）

---

數據類型選擇最佳實踐

1. 精確數值計算
   ? 用 DECIMAL
   ? 避免 FLOAT/DOUBLE

2. 時間存儲
   ? 用 DATETIME（無2038限制）
   ? 避免 TIMESTAMP 長期存儲

3. 字符串優化
   ? 定長用 CHAR（如 UUID、MD5）
   ? 變長用 VARCHAR（<255字符）
   ? 大文本用 TEXT

4. 存儲空間敏感場景

   • 小整數：TINYINT UNSIGNED（1字節）
   • 狀態值：ENUM（1-2字節）
   • 日期：DATE（3字節）

5. 性能關鍵點：

   • CHAR vs VARCHAR：定長字段 CHAR 檢索更快
   • TEXT/BLOB：避免 SELECT *，單獨存儲大字段
   • 整型索引比字符型快

---

常見錯誤案例

錯誤1：用 VARCHAR 存數字

-- 錯誤：數字比較需隱式轉換
SELECT * FROM products WHERE product_id = 100; -- 優化：改為 INT
ALTER TABLE products MODIFY product_id INT;

錯誤2：日期范圍查詢

-- 錯誤：字符串比較低效
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';-- 優化：用日期函數
SELECT * FROM orders 
WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';

錯誤3：ENUM 濫用

-- 錯誤：選項過多
color ENUM('red','green','blue', ... 100+ colors);-- 優化：改用關聯表
CREATE TABLE colors (id TINYINT, name VARCHAR(20));

合理選擇數據類型是數據庫優化的基礎，需結合業務需求、存儲成本和性能要求綜合決策。