目次
1. 介紹
MySQL 是最廣泛使用的資料庫管理系統之一，其關鍵功能之一，Collation 設定在決定字串資料的比較與排序方式上扮演關鍵角色。
Collation 的重要性
若沒有正確的 Collation 設定，搜尋結果可能不正確，或資料庫效能可能下降。
在多語系系統中尤其重要，準確的字元比較與搜尋對於系統正常運作至關重要。
本文將詳細說明 MySQL Collation——其設定、類型與關鍵考量，協助您更有效地管理資料庫。
2. Collation 是什麼？
Collation 定義了 MySQL 用來比較與排序字串值的規則。
Collation 的角色
- 字串排序： 決定字串的排列方式。
- 字串比較： 用於像
WHERE name = 'Sagawa'之類的表達式。 - 搜尋準確度： 影響多語系相容性。
與字元集的關係
Collation 與字元集密切相關。例如，utf8 字元集包含以下 Collation：
utf8_general_ci：不區分大小寫的比較。utf8_bin：二進位（區分大小寫）比較。
命名慣例
character_set_name_comparison_method
範例：
utf8_general_ci：不區分大小寫（ci= case insensitive）。utf8_bin：二進位比較。
3. MySQL 中的 Collation 級別
MySQL 允許在五個層級設定 Collation：
伺服器層級
SHOW VARIABLES LIKE 'collation_server';
要變更此設定，請編輯 my.cnf 並重新啟動伺服器：
[mysqld]
collation_server=utf8mb4_unicode_ci
資料庫層級
ALTER DATABASE db_name DEFAULT COLLATE utf8mb4_unicode_ci;
資料表層級
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
欄位層級
ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(255) COLLATE utf8mb4_unicode_ci;
字串文字層級
SELECT * FROM table_name WHERE column_name = 'value' COLLATE utf8mb4_bin;
4. 主要 Collation 類型與其特徵
utf8_general_ci
- 特徵： 不區分大小寫且快速比較。
- 備註： 精度較低；不完全符合 Unicode 標準。
utf8_unicode_ci
- 特徵： 基於 Unicode 標準的高精度比較。
- 備註： 比
utf8_general_ci稍慢。
utf8_bin
- 特徵： 區分大小寫；要求完全匹配。
- 使用情境： 密碼與識別碼。
utf8mb4_unicode_ci
- 特徵： 遵循現代 Unicode 標準；支援多種語言。
- 使用情境： 處理表情符號與特殊符號的應用程式。
5. 如何檢查與更改 Collation
您可以在資料庫、資料表或欄位層級檢查或修改 Collation。
檢查 Collation
資料庫 Collation
SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';
資料表 Collation
SHOW TABLE STATUS WHERE Name = 'table_name';
欄位 Collation
SHOW FULL COLUMNS FROM table_name;
更改 Collation
資料庫
ALTER DATABASE database_name
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;
資料表
ALTER TABLE table_name
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
欄位
ALTER TABLE table_name
MODIFY COLUMN column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
臨時 Collation 更改
在比較不同 Collation 的欄位時，請使用 COLLATE 子句以避免錯誤。
SELECT * FROM table_name
WHERE column1 COLLATE utf8mb4_unicode_ci = column2;
始終備份資料並在更改前檢查應用程式相容性。
6. 注意事項與最佳實踐
在 MySQL 中配置 Collation 時，請考慮以下預防措施與實踐，以確保最佳效能。
重要備註
混合不同的排序規則
比較或連接使用不同排序規則（collation）的欄位，可能會觸發錯誤。
- 錯誤範例：
ERROR 1267 (HY000): Illegal mix of collations for operation '='
- 解決方案： 使用
COLLATE子句將排序規則標準化。
SELECT * FROM table_name
WHERE column1 COLLATE utf8mb4_unicode_ci = column2;
改變排序規則及現有資料
更改排序規則可能影響現有資料的比較或搜尋方式。
性能影響
utf8mb4_unicode_ci更精確但速度較慢，相較於utf8_general_ci。- 對於大型資料集，排序規則的選擇會顯著影響查詢速度。
遷移問題
在修改排序規則時，請確保應用程式和其他系統的相容性。
最佳實務
1. 根據需求選擇排序規則
- 精確度： 使用
utf8_unicode_ci或utf8mb4_unicode_ci以獲得精確比較。 - 效能： 若精確度不那麼關鍵，使用
utf8_general_ci以取得較快的比較速度。 - 多語言支援： 為表情符號及特殊字元，建議使用
utf8mb4為基礎的排序規則。
2. 保持一致性
- 在資料庫、表格及欄位之間使用相同的排序規則，避免不匹配。
- 只在必要時暫時使用
COLLATE。
3. 變更前先測試與備份
- 在測試環境中先測試配置變更。
- 在執行任何更改前，務必備份正式資料。
4. 優化效能
- 對於經常查詢的欄位（例如已建立索引的欄位），使用高效能排序規則。
- 如有需要，按查詢調整排序規則。
5. 採用最新 Unicode 標準
對於多語系系統，使用 utf8mb4_0900_ai_ci 可提升精確度與相容性。
7. 結論
MySQL 的排序規則決定了字串資料在資料庫中的比較與排序方式。本指南涵蓋了排序規則的概念、設定方法、類型，以及有效排序規則管理的最佳實務。
重點回顧
- 定義： 排序規則控制字串比較與排序。選擇正確的排序規則可提升精確度與效能。
- 多層級： 排序規則可以設定於伺服器、資料庫、表格、欄位以及文字字面值等層級。
- 主要類型：
utf8_general_ci：速度快，但精確度較低。utf8_unicode_ci：精確度高，但速度較慢。utf8mb4_unicode_ci：符合 Unicode，且支援表情符號。
- 設定： 文章提供了查詢與修改排序規則的 SQL 範例。
- 最佳實務： 變更前務必測試並備份。
優化排序規則使用
排序規則直接影響字串比較與排序行為。遵循本文所述的方法與最佳實務，可在 MySQL 資料庫系統中同時維持精確度與效能。
