MySQL 大小寫敏感與字符集深度解析：BINARY、CHAR、VARCHAR、BLOB、TEXT 全面對比

一、BINARY 關鍵字：強制大小寫敏感比較

MySQL 預設字符集校對規則多為 _ci（Case Insensitive，不區分大小寫）。若需精確匹配大小寫，有兩種方式：

1. 查詢層面臨時指定

SELECT * FROM `usertest` WHERE `username` LIKE BINARY '%夏%';
SELECT * FROM `usertest` WHERE `username` = BINARY '夏';

BINARY 會將字符串轉為二進制字節逐位比較，效率略低於預設校對，但語義精確。

2. 表結構層面永久生效（推薦）

-- 建表時指定
CREATE TABLE `usertest` (
    `username` VARCHAR(30) BINARY NOT NULL DEFAULT ''
);

-- 修改既有表
ALTER TABLE `usertest` MODIFY `username` VARCHAR(32) BINARY;

這樣該欄位的所有比較、排序、分組預設都區分大小寫，避免應用層遺漏。

二、CHAR vs VARCHAR：存儲機制與選型藝術

實測驗證：尾部空格比較行為

CREATE TABLE `zcy` (
  `name` VARCHAR(4),
  `addr` CHAR(8)
);
INSERT INTO `zcy` VALUES ('a ', 'a'), ('a', 'a ');

SELECT CONCAT('$', `name`, '$') AS n, CONCAT('$', `addr`, '$') AS a FROM `zcy`;
-- 結果：
-- n: $a$   (varchar 存入 a 去尾空格 -> 'a')
-- a: $a$   (char 存入 a 补齐空格 -> 'a      ', 但比較時忽略尾部空格)
SELECT * FROM `zcy` WHERE `name` = 'a '; -- 命中 'a' 和 'a ' 兩行
SELECT * FROM `zcy` WHERE `addr` = 'a '; -- 命中 'a' 和 'a ' 兩行

三、BINARY / VARBINARY：真正的二進制字節存儲

• 無字符集概念，按字節比較，天然區分大小寫，效率最高。
• BINARY(N)：固定長度，不足右補 \0 (0x00)，非空格。
• VARBINARY(N)：可變長度，不補齊，存實際字節。

-- BINARY 比較需精確匹配 \0
SELECT * FROM `zcy` WHERE `b` = 'a\0\0\0\0\0\0\0'; -- 僅匹配存入 'a' 補齊 \0 的行
SELECT * FROM `zcy` WHERE `bn` = 'ab '; -- VARBINARY 嚴格匹配字節，'ab ' 包含空格字節 0x20

四、BLOB vs TEXT：大對象存儲選型指南

四大分級對應

• TINYBLOB / TINYTEXT   (255 字節)
• BLOB / TEXT   (64 KB)
• MEDIUMBLOB / MEDIUMTEXT   (16 MB)
• LONGBLOB / LONGTEXT   (4 GB)

關鍵限制與應對

1. 排序截斷：ORDER BY blob_col 僅用前 max_sort_length (預設 1024) 字節。解決：SET SESSION max_sort_length=2000; 或 ORDER BY SUBSTRING(blob_col, 1, 2000)。
2. 傳輸限制：受 max_allowed_packet 限制，客戶端與服務端需同步調大。
3. 內部存儲：每個值獨立分配對象，不像定長類型共享存儲區，大量小 BLOB/TEXT 會增加管理開銷。

五、最佳實踐總結

1. **業務主鍵/唯一鍵/定長代碼** → CHAR 或 BINARY。
2. **通用變長文本** → VARCHAR (配合 utf8mb4_general_ci 或 utf8mb4_0900_ai_ci)。
3. **需嚴格區分大小寫的代碼/Token/Hash** → 表結構層面加 BINARY 或用 VARBINARY。
4. **圖片/文件/加密數據** → BLOB 系列（建議存檔案路徑，DB 只存元數據）。
5. **長文本/日誌/文章** → TEXT 系列，注意索引前綴與排序截斷。
6. **統一字符集**：庫/表/欄位/連接全鏈路 utf8mb4，杜絕亂碼與隱式轉換效能損耗。