【重學MySQL】九十三、MySQL字符集與比較規則完全解析
- 一、字符集概述
- 1.1 支持的字符集
- 1.2 UTF8與UTF8MB4的區別
- 二、比較規則（Collation）
- 2.1 比較規則分類
- 2.2 常見比較規則差異
- 三、配置層級與繼承關系
- 3.1 配置層級
- 3.2 繼承關系
- 四、最佳實踐與問題解決
- 4.1 統一字符集
- 4.2 數據遷移與轉換
- 4.3 性能優化
- 4.4 常見問題
- 五、示例配置
- 5.1 服務器級配置（`my.cnf`）
- 5.2 創建數據庫與表
- 5.3 修改現有表
- 六、總結
一、字符集概述
字符集是MySQL中用于定義字符如何存儲、表示和比較的一組規則。它決定了數據庫如何存儲和檢索文本數據，并處理不同語言和特殊字符。
1.1 支持的字符集
MySQL支持多種字符集，常見字符集如下：
| 字符集 | 描述 |
|---|---|
| UTF8/UTF8MB4 | UTF8支持1-3字節字符，適合大多數場景；UTF8MB4擴展至4字節，支持emoji等特殊字符。 |
| Latin1 | 西歐語言，兼容ASCII，適用于英文和西歐語言。 |
| GBK/GB2312 | 簡體中文，GBK覆蓋更多字符，GB2312是其前身。 |
| ASCII | 僅支持英文字符，每個字符占1字節。 |
| UTF16 | 使用16位編碼，適合存儲復雜字符集（如中文、日文、韓文）。 |
1.2 UTF8與UTF8MB4的區別
- UTF8：最大支持3字節字符，無法存儲emoji等4字節字符。
- UTF8MB4：支持4字節字符，是UTF8的超集，推薦用于現代應用。
二、比較規則（Collation）
比較規則決定字符串的排序和比較方式，它與字符集密切相關。
2.1 比較規則分類
| 規則類型 | 描述 |
|---|---|
| 大小寫敏感 | 如utf8_bin（二進制比較），區分大小寫。 |
| 大小寫不敏感 | 如utf8_general_ci（通用規則），不區分大小寫。 |
| 語言特定 | 如utf8_spanish_ci（西班牙語規則），按特定語言排序。 |
| Unicode標準 | 如utf8_unicode_ci（符合Unicode標準），排序更準確但性能略低。 |
| 最新規則 | MySQL 8.0+支持utf8mb4_0900_ai_ci，基于Unicode 9.0，支持更復雜的排序。 |
2.2 常見比較規則差異
utf8_general_ci：速度快，但排序可能不準確（如某些特殊字符）。utf8_unicode_ci：遵循Unicode標準，排序更準確，但性能略低。utf8mb4_0900_ai_ci：MySQL 8.0+默認規則，支持擴展的排序和比較功能。
三、配置層級與繼承關系
MySQL的字符集和比較規則可配置于四個層級，優先級從高到低為：列級 > 表級 > 數據庫級 > 服務器級。
3.1 配置層級
| 層級 | 配置方法 |
|---|---|
| 服務器級 | 通過my.cnf配置文件或啟動參數（如--character-set-server=utf8mb4）。 |
| 數據庫級 | 創建數據庫時指定：CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
| 表級 | 創建表時指定：CREATE TABLE table (...) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
| 列級 | 定義字段時指定：name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
3.2 繼承關系
- 若未顯式指定，低層級繼承高層級的設置。例如：
- 表未指定字符集時，使用數據庫的字符集。
- 列未指定字符集時，使用表的字符集。
四、最佳實踐與問題解決
4.1 統一字符集
- 避免亂碼：確保服務器、數據庫、表、列的字符集一致，并與客戶端連接字符集匹配。
- 客戶端連接設置：
或在配置文件SET NAMES utf8mb4;my.cnf中設置：[client] default-character-set = utf8mb4
4.2 數據遷移與轉換
- 修改現有表的字符集：
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; - 備份數據：修改字符集前務必備份，避免數據損壞。
4.3 性能優化
- 選擇合適的比較規則：
- 對性能敏感的場景，選擇
utf8_general_ci。 - 需要準確排序時，使用
utf8_unicode_ci或utf8mb4_0900_ai_ci。
- 對性能敏感的場景，選擇
4.4 常見問題
- 亂碼問題：檢查所有層級的字符集設置是否一致，并確保客戶端連接字符集正確。
- 排序錯誤：確認比較規則是否符合需求，例如中文排序需選擇支持中文的規則（如
utf8mb4_unicode_ci）。
五、示例配置
5.1 服務器級配置（my.cnf）
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
5.2 創建數據庫與表
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5.3 修改現有表
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
六、總結
- 字符集決定存儲方式，比較規則決定排序和比較行為。
- 優先使用
UTF8MB4以支持更廣泛的字符，搭配utf8mb4_unicode_ci或utf8mb4_0900_ai_ci確保準確排序。 - 統一各層級的字符集設置，避免亂碼和性能問題。
通過合理配置字符集和比較規則，可確保MySQL高效、正確地處理多語言數據。
)
