| 現象 | 典型原因 |
|---|---|
| 資料庫存儲正常,命令列查詢顯示亂碼 | 客戶端字符集與終端機編碼不匹配 |
插入中文報錯 Incorrect string value |
Server/資料庫/表/欄位字符集不支援該字符 |
| 讀出中文變 ???? | 連線字符集與存儲字符集不一致 |
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
no-auto-rehash
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
# 關鍵:避免 latin1 干擾
# skip-character-set-client-handshake -- 強制忽略客戶端請求,慎用
[mysqldump]
default-character-set = utf8mb4
net stop MySQL80 && net start MySQL80 (Windows) / systemctl restart mysqld (Linux)mysql -u root -p -e "SHOW VARIABLES LIKE 'character_set%';"# 連線時指定
mysql -u root -p --default-character-set=utf8mb4
# 連線後執行
SET NAMES utf8mb4;
-- 等價於
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;
# 臨時切換 UTF-8
chcp 65001
# 需配合字體:屬性 → 字體 → 選 TrueType (Consolas/Lucida Console)
預設支援 UTF-8,無需切換,直接配合 --default-character-set=utf8mb4 使用。
$dsn = "mysql:host=localhost;dbname=test;charset=utf8mb4";
$pdo = new PDO($dsn, $user, $pass, [
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
]);
conn = pymysql.connect(host='localhost', user='root', password='pass',
db='test', charset='utf8mb4')
url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8mb4&connectionCollation=utf8mb4_general_ci&useSSL=false&serverTimezone=Asia/Shanghai";
-- 1. Server 端
SHOW VARIABLES LIKE 'character_set_%';
-- 期望:client/connection/results/database/server 皆為 utf8mb4
-- 2. 表/欄位級
SHOW CREATE TABLE t;
SHOW FULL COLUMNS FROM t;
-- 期望:CHARSET=utf8mb4, COLLATE=utf8mb4_general_ci
-- 3. 實測寫入讀取
INSERT INTO t (col) VALUES ('測試中文😀');
SELECT col, HEX(col) FROM t;
-- HEX 應顯示正確 UTF-8 編碼位元組
命令列中文亂碼核心在於**三端一致**:Server 配置、客戶端連線字符集、終端機顯示編碼。一鍵解決:my.cnf 統一設 utf8mb4 + 重啟 + 使用 Windows Terminal + 連線加 --default-character-set=utf8mb4。避開 utf8 (mb3) 坑,直接上 utf8mb4 支援完整 Unicode。