MySQL字符集與排序規則：新手必知的基礎配置

一、什麼是字符集和排序規則？¶

字符集（Character Set）可以理解爲MySQL存儲字符時使用的“編碼規則”，比如我們熟悉的utf8mb4、latin1等，它們規定了MySQL如何將文字（如中文、英文、emoji）轉換爲二進制數據存儲。
排序規則（Collation）則是字符集的“排序規則”，決定了文字的比較和排序方式，比如utf8mb4_general_ci表示按字母順序不區分大小寫排序，utf8mb4_bin則按二進制值嚴格區分大小寫。

二、爲什麼要配置字符集和排序規則？¶

如果字符集或排序規則配置不當，最直接的問題是亂碼或排序錯誤：
- 亂碼：比如用latin1字符集存儲中文，MySQL會將中文轉爲無法識別的二進制，讀取時顯示亂碼。
- 排序錯誤：比如排序規則選錯，“張三”可能排在“李四”前面（如果排序規則按拼音首字母排序，需確認順序是否符合預期）。
- 兼容性問題：舊版utf8字符集僅支持部分Unicode字符（不支持emoji和某些罕見字符），而utf8mb4纔是完整的Unicode支持，推薦優先使用。

三、MySQL字符集與排序規則的配置層級¶

MySQL的字符集和排序規則有多個生效層級，優先級從高到低爲：列級 > 表級 > 數據庫級 > 服務器級。即：如果列、表、數據庫中均未指定，纔會使用服務器默認配置。
- 服務器級：全局生效，可通過配置文件（如my.cnf）修改，控制數據庫服務器的默認字符集。
- 數據庫級：創建數據庫時指定，對該數據庫下所有表生效（除非表或列單獨指定）。
- 表級：創建表時指定，對該表下所有列生效（除非列單獨指定）。
- 列級：創建表時對特定列單獨指定，優先級最高，覆蓋表級配置。

四、如何查看當前配置？¶

新手需先學會查看當前配置，避免配置錯誤。以下是常用命令：
1. 查看服務器級字符集：

   SHOW VARIABLES LIKE 'character_set_%';

常見結果：character_set_server（服務器字符集，默認utf8mb4）、character_set_database（數據庫默認字符集）等。

查看服務器級排序規則：

   SHOW VARIABLES LIKE 'collation_%';

常見結果：collation_server（服務器默認排序規則，如utf8mb4_general_ci）。

查看數據庫字符集：

   SHOW CREATE DATABASE your_database;

結果會顯示數據庫創建時指定的字符集和排序規則。

查看錶字符集：

   SHOW TABLE STATUS LIKE 'your_table';

結果中的Collation列顯示錶的排序規則，Row_format等信息可能需結合其他命令。

查看列字符集：

   SHOW COLUMNS FROM your_table;

結果中Character_set列顯示列的字符集（若未單獨設置，繼承表級配置）。

五、新手必學的配置方法¶

1. 推薦使用`utf8mb4`字符集¶

utf8mb4是MySQL支持完整Unicode字符的編碼（包括中文、英文、emoji、生僻字等），而舊版utf8（實際爲utf8mb3）僅支持3字節字符，無法存儲emoji和部分特殊字符。
- 服務器級配置（修改配置文件）：
在my.cnf（Linux）或my.ini（Windows）中添加以下內容：

  [mysqld]
  character-set-server = utf8mb4
  collation-server = utf8mb4_general_ci

保存後重啓MySQL服務（systemctl restart mysql或net stop mysql && net start mysql）。

2. 數據庫級配置¶

創建數據庫時指定字符集（推薦）：

CREATE DATABASE your_database 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

如果已創建數據庫，可修改數據庫字符集：

ALTER DATABASE your_database 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

3. 表級配置¶

創建表時指定字符集（若需覆蓋服務器默認）：

CREATE TABLE your_table (
  id INT,
  name VARCHAR(50)
) ENGINE=InnoDB 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

若需區分大小寫，可使用utf8mb4_bin排序規則（嚴格按二進制值比較，不依賴語言規則）：

COLLATE utf8mb4_bin

4. 列級配置（特殊需求）¶

若某列需單獨設置字符集（如存儲英文和中文混合，需兼容不同排序）：

CREATE TABLE your_table (
  id INT,
  english_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_english_ci,
  chinese_name VARCHAR(50)
) ENGINE=InnoDB;

（注：列級配置僅適用於需覆蓋表級的場景，優先推薦表級統一配置）

六、常見問題與解決¶

亂碼問題：
- 原因：列/表/數據庫字符集與插入數據的編碼不匹配（如用latin1存儲中文）。
- 解決：檢查數據來源（如程序代碼）是否使用utf8mb4編碼，確保所有層級字符集統一爲utf8mb4。
排序規則選錯導致中文排序錯誤：
- 場景：中文按拼音排序時，utf8mb4_general_ci可能不符合預期（如“李”和“王”的順序）。
- 解決：若需中文拼音排序，可選utf8mb4_unicode_ci（更精確，支持德語、法語等複雜排序），但性能略低於general_ci。
emoji無法顯示：
- 原因：使用舊版utf8（utf8mb3）字符集。
- 解決：修改爲utf8mb4字符集即可（utf8mb4支持所有Unicode字符，包括emoji）。

七、總結與最佳實踐¶

優先使用utf8mb4字符集，避免utf8（舊版）。
排序規則選utf8mb4_general_ci或utf8mb4_unicode_ci：前者性能好，後者排序更精確（需根據需求選擇）。
避免列級單獨配置，除非特殊需求，表級統一配置即可。
定期檢查配置：通過SHOW VARIABLES和SHOW CREATE DATABASE/ TABLE確認字符集是否生效。

掌握字符集與排序規則的配置，能從根本上避免數據亂碼、排序錯誤等問題，是MySQL使用的基礎技能。記住：統一的字符集和排序規則是數據一致性的關鍵！