查看字符集與排序規則
| 命令 | 說明 | 輸出示例 |
|---|
SHOW CHARSET; | 列出所有支援字符集 | Charset, Description, Default collation, Maxlen |
STATUS; 或 \s | 顯示當前連接字符集信息 | Server characterset: utf8mb4, Db characterset: utf8mb4... |
SHOW VARIABLES LIKE 'character_set_%'; | 詳細查看各層級字符集設置 | character_set_client, connection, database, results, server, system |
SHOW VARIABLES LIKE 'collation_%'; | 查看排序規則設置 | collation_connection, database, server |
建庫建表時指定字符集
-- 建庫指定
CREATE DATABASE db_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci; -- 或 utf8mb4_0900_ai_ci (8.0+)
-- 建表指定 (表級默認)
CREATE TABLE tbl_name (
id INT PRIMARY KEY,
name VARCHAR(50)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 列級指定 (覆蓋表級)
CREATE TABLE tbl_name (
id INT PRIMARY KEY,
name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin -- 區分大小寫
) DEFAULT CHARSET=utf8mb4;
查看現有對象字符集
| 對象 | 命令 | 關鍵輸出 |
|---|
| 資料庫 | SHOW CREATE DATABASE db_name; | DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci |
| 表 | SHOW CREATE TABLE tbl_name; | 表默認字符集、各列字符集 |
| 庫下所有表 | SHOW TABLE STATUS FROM db_name; | Collation 欄位顯示表字符集 |
| 表所有列 | SHOW FULL COLUMNS FROM tbl_name; | Collation 欄位顯示列字符集 (NULL 表繼承表級) |
| 篩選表 | SHOW TABLE STATUS FROM db_name LIKE 'prefix%'; | 同以上 |
修改字符集 (DDL 操作，大表慎重！)
修改資料庫默認字符集
ALTER DATABASE db_name
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
-- 僅影響後續新建表，現有表不變
修改表及所有列字符集 (推薦)
ALTER TABLE tbl_name
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
-- **核心差異**：CONVERT TO 會**轉換現有數據編碼**，並修改所有列定義
-- 適用於 latin1 → utf8mb4 等編碼遷移
僅修改表默認字符集 (不轉換數據)
ALTER TABLE tbl_name
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
-- 僅影響後續新增列，現有列字符集不變，**不轉換數據**
修改單列字符集
ALTER TABLE tbl_name
MODIFY col_name VARCHAR(100)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
-- 或 CHANGE 重命名同時修改
ALTER TABLE tbl_name
CHANGE old_col new_col VARCHAR(100)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
連接層字符集設置 (極其重要！)
**應用程序連接建立後，必須執行：**
SET NAMES 'utf8mb4';
-- 等效於：
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;
各語言驅動配置：
- JDBC： URL 參數
?characterEncoding=utf8mb4&connectionCollation=utf8mb4_general_ci (5.1.47+ 支援 utf8mb4)
- Python (PyMySQL/mysqlclient)：
charset='utf8mb4' 參數
- PHP (mysqli)：
mysqli_set_charset($conn, 'utf8mb4')
- Go (go-sql-driver/mysql)： DSN
charset=utf8mb4,utf8
- Node.js (mysql2)：
charset: 'utf8mb4' 配置項
常見字符集選型建議
| 字符集 | 最大字符長度 | 支援 Emoji | MySQL 版本 | 推薦場景 |
|---|
| latin1 | 1 字節 | ❌ | 所有 | 極舊系統、純 ASCII |
| gbk / gb2312 | 2 字節 | ❌ | 所有 | 中文舊系統、不支援繁體/生僻字/Emoji |
| utf8 (utf8mb3) | 3 字節 | ❌ (僅 BMP 面) | 所有 | **不推薦新項目使用** |
| utf8mb4 | 4 字節 | ✅ 完整 Unicode | 5.5.3+ | 所有新項目首選 |
排序規則選型
utf8mb4_general_ci：舊版默認，速度快，但部分字符排序不準確 (如 ß=ss)utf8mb4_unicode_ci：基於 UCA 4.0.0，準確但較慢utf8mb4_0900_ai_ci：**MySQL 8.0 默認**，基於 UCA 9.0.0，準確、支援補充字符、效能優化、**強烈推薦 8.0+ 使用**_bin 後綴：二進制比較，**區分大小寫、區分重音**，用於精確匹配 (如密碼、Token)
避坑指南
- utf8 ≠ utf8mb4： MySQL 的
utf8 別名實為 utf8mb3，**不支援 Emoji**，存入會截斷或報錯。**新項目務必用 utf8mb4**
- 修改大表字符集：
CONVERT TO 會鎖表重建，**大表必須用 pt-online-schema-change 或 gh-ost 在線變更**
- 連接字符集不一致： 客戶端 latin1 存入 utf8mb4 表 → 亂碼；utf8mb4 存入 latin1 表 → 數據截斷。**統一全鏈路 utf8mb4**
- 排序規則衝突： JOIN/UNION 兩表排序規則不同會報錯
Illegal mix of collations，統一用 utf8mb4_0900_ai_ci 或顯式 COLLATE
