字符集決定了數據如何編碼存儲，排序規則決定了字符比較與排序邏輯。選擇正確的字符集（如 utf8mb4）是避免亂碼、支援 Emoji、實現多語言存儲的前提。本文展示 SHOW CHARACTER SET 完整輸出，並解析核心欄位含義與選型建議。
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode (3-byte max) | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode (4-byte max) | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.00 sec)| 欄位 | 含義 | 關鍵洞察 |
|---|---|---|
| Charset | 字符集名稱，用於 CHARACTER SET / CHARSET 子句 | 區分 utf8 (別名 utf8mb3, 最大 3 字節) 與 utf8mb4 (真正 UTF-8, 最大 4 字節, 支援 Emoji、罕見漢字) |
| Description | 人類可讀描述：編碼標準 + 覆蓋語言範圍 | 如 gbk 覆蓋中日韓擴展，latin1 僅西歐語言 |
| Default collation | 該字符集的預設排序規則 | 後綴含義：_ci = Case Insensitive (大小寫不敏感)，_cs = Case Sensitive，_bin = Binary (按字節碼比較)，_general_ci = 通用規則（速度快，準確度略低），_unicode_ci = UCA 標準規則（準確，較慢） |
| Maxlen | 單字符最大字節數 | 直接影響列定義長度換算：VARCHAR(100) CHARSET utf8mb4 最大佔 400 字節 + 1-2 字節長度前綴。InnoDB 單行限制 ~8KB，單索引鍵長 767 字節 (或 3072 字節啟用 innodb_large_prefix) |
| 字符集 | Maxlen | 覆蓋範圍 | 排序規則示例 | 推薦度 |
|---|---|---|---|---|
| gb2312 | 2 | 6763 漢字 + 682 符號（基礎國標） | gb2312_chinese_ci | ❌ 過時，缺字嚴重 |
| gbk | 2 | 21886 字符（含 GB2312 + 擴展 + 日韓） | gbk_chinese_ci | ⚠️ 僅中文舊系統維護用 |
| utf8 (utf8mb3) | 3 | BMP 平面 (U+0000~U+FFFF)，無 Emoji、無生僻字 | utf8_general_ci | ❌ 偽 UTF-8，新項目禁用 |
| utf8mb4 | 4 | 完整 Unicode (U+0000~U+10FFFF)，含 Emoji、生僻字、各語言 | utf8mb4_general_ci / utf8mb4_0900_ai_ci (8.0+ 推薦) | ✅ 新項目唯一首選 |
[mysqld]
# 服務端級別：影響新建庫表的預設字符集
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci # 8.0+ 推薦，支援重音不敏感、準確排序
# 5.7 使用 utf8mb4_general_ci 或 utf8mb4_unicode_ci
[client]
# 客戶端默認字符集（影響連接握手）
default-character-set = utf8mb4
[mysql]
# mysql 命令行客戶端
default-character-set = utf8mb4驗證生效：
mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+utf8mb4_0900_ai_ci (8.0+) / utf8mb4_general_ci (5.7) —— 大小寫、重音不敏感，速度快。utf8mb4_unicode_ci / utf8mb4_0900_ai_ci —— 遵循 UCA 標準，德語 ß=ss、重音字母正確排序。utf8mb4_0900_as_cs / utf8mb4_bin —— 密碼驗證、代碼比對、精確去重。binary / utf8mb4_bin —— 按字節碼比較，最快，適合不需語義排序的場景。SHOW CHARACTER SET 列出的 40 種字符集中，utf8mb4 以 Maxlen=4 完整支撐 Unicode，是現代 MySQL 應用的唯一標準選擇。配合 utf8mb4_0900_ai_ci (8.0) 或 utf8mb4_general_ci (5.7) 作為預設排序規則，並在 my.cnf 服務端/客戶端雙端鎖定 character-set-server/client = utf8mb4，即可從源頭消除字符集亂碼、Emoji 存儲失敗、排序錯誤等經典痛點。
