MySQL 亂碼問題徹底解決：字元集診斷、資料庫/資料表/欄位層級轉換實戰

一、快速診斷：目前字元集是什麼？

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

重點觀察：character_set_server、character_set_database、character_set_client/connection/results。

二、層級修正策略 (由大到小)

1. 伺服器級 (my.cnf) — 影響新建庫/表

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

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

systemctl restart mysqld 生效。

2. 資料庫級 — 僅影響後續新建表

ALTER DATABASE `db_name` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

3. 資料表級 — 轉換現有表結構與資料

-- 僅修改表預設字元集 (不轉換現有欄位資料)
ALTER TABLE `tbl_name` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 【關鍵】完整轉換：結構 + 現有資料重新編碼
ALTER TABLE `tbl_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

⚠️ 必先備份！ CONVERT TO 會重寫全表資料，大表耗時長、鎖表久。

4. 欄位級 — 精準修正單一欄位

ALTER TABLE `tbl_name`
MODIFY `col_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

三、連線層級 — 應用端必做

-- 連線建立後立即執行
SET NAMES utf8mb4;
-- 等同於
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

JDBC URL 範例：jdbc:mysql://host:3306/db?characterEncoding=utf8mb4&useSSL=false

四、常見亂碼場景對照表

五、驗證清單

1. SHOW CREATE TABLE tbl_name 確認 CHARSET=utf8mb4
2. SELECT HEX(col), col FROM tbl WHERE col LIKE '%中文%' 檢查位元模式
3. 應用端寫入/讀出 Emoji 😀 驗證 4-byte 支援
4. 備份檔 head -c 500 backup.sql | file - 確認 UTF-8 編碼