MySQL 字符集與排序規則完整指南：從亂碼根因到遷移實戰

字符集基礎知識

常見字符集分類

字符集與排序規則關係

• 字符集：字符編碼方式
• 排序規則：字符比較與排序規則，一個字符集對應多個排序規則
• 命名慣例：_ci（不分大小寫）、_cs（分大小寫）、_bin（二進制比較）

四層級字符集設定

優先級：服務器級 > 資料庫級 > 表級 > 列級

各層級設定語法

-- 服務器級（配置檔或啟動參數）
character_set_server=utf8mb4

-- 資料庫級
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 表級
CREATE TABLE tbl (...) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 列級
CREATE TABLE tbl (col VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci);

資料儲存字符集解析規則

1. 使用列級設定
2. 無列級則用表級預設值
3. 無表級則用資料庫級預設值
4. 無資料庫級則用服務器級 character_set_server

連線字符集三要素

統一設定最佳實踐

-- 會話級
SET NAMES utf8mb4;

-- 配置檔
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character_set_server = utf8mb4

亂碼常見原因與對策

兩大核心原因

1. 儲存字符集不支援客戶端資料（如客戶端 UTF-8 → 儲存 latin1）
2. 程式連線字符集與告知 MySQL 的字符集不一致

關鍵三要素必須一致

1. 程式/驅動程式字符集
2. 告知 MySQL 的字符集（SET NAMES）
3. 資料實際儲存字符集（表結構定義）

建議三者統一設為 utf8mb4

LOAD DATA 亂碼實戰

-- 文件為 UTF-8，但資料庫字符集為 GBK
LOAD DATA INFILE '/path/test.t' INTO TABLE t;
-- 結果亂碼，需調整 character_set_database 或確保三要素一致

線上資料庫字符集遷移實戰（不丟資料）

1. 導出結構：mysqldump -uroot -p --default-character-set=gbk -d db_name > createtab.sql
2. 修改結構檔：將 CHARSET=latin1 替換為 CHARSET=gbk
3. 導出資料：mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 db_name > data.sql
4. 修改資料檔：將 SET NAMES latin1 改為 SET NAMES gbk
5. 建立新庫：CREATE DATABASE new_db DEFAULT CHARSET gbk;
6. 匯入結構：mysql -uroot -p new_db < createtab.sql
7. 匯入資料：mysql -uroot -p new_db < data.sql

修改現有表字符集

-- 僅影響新資料
ALTER TABLE tbl CHARACTER SET utf8mb4;

-- 轉換現有資料（重建表）
ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;

總結重點

• 字符集與排序規則為 1:N 關係
• 表級轉換：ALTER TABLE ... CONVERT TO CHARACTER SET
• 連線三要素統一：SET NAMES utf8mb4
• LOAD DATA 需匹配資料庫級字符集
• 遷移核心：導出→修改編碼聲明→建立新庫→匯入

1. 上一篇文章： MySQL 資料庫物件全解析：索引、約束、視圖、觸發器與存儲程序
2. 下一篇文章： Linux 平台 MySQL 啟動與關閉六大方式深度對比

MySQL 綜合教學

Windows 平台 MySQL 5.1 MSI 安裝與圖形化配置完整教程

適用環境與前置需求 作業系統：Windows XP / 2

MySQL Archive 存儲引擎深度解析：高壓縮、僅追加、歸檔場景首選

MySQL Archive 存儲引擎深度解析：高壓縮、僅追加

MySQL 重裝失敗「Server PID file could not be found」完整排查與解決方案

重裝 MySQL 卡在「Starting Server」？殘

MySQL Index Condition Pushdown (ICP) 索引條件下推優化原理與實戰

MySQL Index Condition Pushdown