MySQL Collation 校對規則導致中文查詢錯誤的根因與解決

問題現象

表字元集 latin1，查詢 WHERE to_id='cn象_王' 卻同時返回 cn陶_陶。HEX 編碼明顯不同（636ECCD55FCCD5 vs 636ECFF35FCDF5），MySQL 卻視為相同。

定位 Collation 問題

SHOW VARIABLES 顯示 collation_connection/database/server 均為 latin1_swedish_ci。手動改為 latin1_bin 仍不生效，原因在於建表時指定了 CHARACTER SET 但未指定 COLLATE，MySQL 依預設規則採用該字元集的預設校對規則（latin1_swedish_ci）。

Collation 命名規則

格式：字元集_語言_ci/cs/bin。latin1 常見：latin1_swedish_ci（預設）、latin1_bin（二進位）、latin1_general_ci 等。

解決方案

1. 重建表指定 Collation：CREATE TABLE ... COLLATE latin1_bin
2. ALTER TABLE 轉換（無需重建）：
   ALTER TABLE db_allot CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;
3. 表級別指定：
   ALTER TABLE comment_content_1_01 CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;
   所有欄位自動套用 collate latin1_bin。

心得

CHARACTER SET latin1 COLLATE latin1_bin 相當於舊版 VARCHAR BINARY 的改進版。建議所有中文欄位採用對應字元集的 _bin 校對規則，避免比較錯誤。