【MySQL】欄位編碼設定影響查詢的大小寫敏感

本篇重點

• 不同 collation 對查詢的影響
• 如何確認欄位 collation
• 設定 collation 的方法
• 查詢時強制區分大小寫

操作 MySQL 查詢時，有時候會發現輸入的條件大小寫不同卻能查到相同的結果，例如 WHERE name = 'Tom' 與 WHERE name = 'tom' 回傳的內容一樣。這並不是 MySQL 的「錯誤」，而是因為欄位的 collation 可能被設定為大小寫不敏感 (case-insensitive)，導致查詢忽略了大小寫差異。

不同 Collation 對查詢的影響

MySQL 中，collation (編碼、排序規則) 影響字串的比較與排序方式，以下是 collation 的重要特性：

是否區分大小寫 (case sensitivity)、是否區分重音 (accent sensitivity)

• ci → case-insensitive (不區分大小寫)，查詢時 Tom 與 tom 視為相同
• bin → binary (以二進位比較，嚴格區分內容)，查詢時 Tom 與 tom 或是 Eric 與 Éric 會被視為不同

語言排序規則

• utf8mb4_general_ci → 簡單快速，但會犧牲部分語言正確性
• utf8mb4_unicode_ci → 依 Unicode 規範排序，更精準，適合多語言

範例：

不同的 collation 得到不同的結果

1
2
3
4
5
6
7
8
9
10
11

-- utf8mb4_general_ci 大小寫不敏感
SELECT * FROM customers WHERE name_ci = 'tom';
SELECT * FROM customers WHERE name_ci = 'TOm';

-- utf8mb4_bin 區分大小寫
SELECT * FROM customers WHERE name_bin = 'tom';
SELECT * FROM customers WHERE name_bin = 'Tom';

-- 區分重音
SELECT * FROM customers WHERE name_ci = 'Eric';
SELECT * FROM customers WHERE name_bin = 'Eric';

如何確認欄位 Collation

透過指令檢查欄位的 collation

1
2
3
4
5
6
7
8

-- 查看資料表欄位的 collation
SHOW FULL COLUMNS FROM customers;

-- 或從 information_schema 查詢
SELECT COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'customers';

設定 Collation

哪些欄位需要大小寫敏感？

不區分大小寫 (使用 ci)

• 一般文字內容（標題、文章、備註）
• 使用者暱稱、搜尋功能中的關鍵字
• 多語言資料

區分大小寫 (使用 cs 或 bin)

• 使用者帳號
• 密碼欄位
• Token、驗證碼、序號等需嚴格比對的字串

建立欄位或資料表

建立整張資料表指定 Collation

1
2
3
4
5

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name_ci VARCHAR(50) COLLATE utf8mb4_general_ci,
  name_bin VARCHAR(50) COLLATE utf8mb4_bin
);

新增欄位指定 Collation

1
2
3

ALTER TABLE customers
ADD name_bin2 VARCHAR(50) CHARACTER SET utf8mb4
COLLATE utf8mb4_bin;

• CHARACTER SET 與 COLLATE 要一起指定

修改欄位或資料表

修改整張資料表指定 Collation

1
2
3

ALTER TABLE customers
CONVERT TO CHARACTER SET utf8
COLLATE utf8_bin;

• 修改資料表所有文字欄位（VARCHAR、TEXT 等）的 Character Set 與 Collation

修改欄位指定 Collation

1
2
3

ALTER TABLE customers
MODIFY name_ci VARCHAR(100)
COLLATE utf8mb4_general_ci;

查詢時強制區分大小寫

1
2

SELECT * FROM customers
WHERE name_ci COLLATE utf8mb4_bin LIKE 'Tom';

結論

MySQL 查詢是否區分大小寫，取決於 collation 的設定。

• ci 適合大部分一般文字內容
• bin 或 cs 適合帳號、密碼、Token 等需嚴格比對的欄位

不用每個欄位都個別設定，建議在資料庫層級先指定一個通用的 collation，再針對需要區分大小寫的欄位額外調整。

延伸閱讀

• 【官方文件】命名慣例介紹