您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 字符集遷移與版本升降級全攻略：latin1 → utf8mb4、4.1/5.0/8.0 兼容性深度解析

編輯：MySQL 資料庫相關知識

前言

字符集遷移是 MySQL 運維最頭疼的問題之一：latin1 偽裝存儲中文 → 升級到 4.1+ 真正多字節支持 → 亂碼爆發。本文系統講解字符集層級架構、遷移核心原理、三大遷移方案、版本升降級兼容性矩陣、Discuz/WordPress 實戰案例。

一、MySQL 字符集四層架構 (4.1+)

排序規則：collation_server/database/connection 決定比較/排序規則 (如 utf8mb4_general_ci vs utf8mb4_unicode_ci)。

二、經典亂碼成因：latin1 偽裝存儲

舊版本 (3.23/4.0) 無字符集概念，默認 latin1 (單字節)。
應用以 GBK/UTF-8 寫入，MySQL 按 latin1 逐字節存儲 (一個中文 2/3 字節 → 2/3 個 latin1 字符)。
讀取時應用按 GBK/UTF-8 解碼，**湊巧還原正確** (雙重錯誤抵消)。
升級到 4.1+，MySQL 引入真實字符集轉換：**latin1 → utf8 轉換將偽裝數據破壞**，導致亂碼。

三、三大遷移方案 (按風險從低到高)

方案 A：導出 → 文本轉碼 → 導入 (最安全、通用)

# 1. 導出 (不含建表語句的數據)
mysqldump -u root -p --no-create-info --default-character-set=latin1 db_name > data.sql

# 2. 文本轉碼 (latin1 → utf8)
iconv -f latin1 -t utf-8 data.sql > data_utf8.sql
# 或用 Emeditor/Notepad++ 另存為 UTF-8

# 3. 修改建表語句字符集
mysqldump -u root -p --no-data --default-character-set=latin1 db_name > schema.sql
# 將 schema.sql 中 latin1 替換為 utf8mb4

# 4. 建新庫、導入結構、導入數據
mysql -u root -p -e "CREATE DATABASE db_new CHARACTER SET utf8mb4;"
mysql -u root -p db_new < schema_utf8.sql
mysql -u root -p db_new < data_utf8.sql

方案 B：ALTER TABLE CONVERT TO CHARACTER SET (原地轉換、需停寫)

-- 單表
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 全庫腳本
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'db_name'
INTO OUTFILE '/tmp/convert.sql';
SOURCE /tmp/convert.sql;

風險：大表鎖表時間長、磁盤空間翻倍、外鍵約束需先刪除再建立。

方案 C：雙寫遷移 (零停機、適合大表)

新增 col_utf8 列 (utf8mb4)，應用雙寫 (舊列 + 新列)。
歷史數據分批回填：UPDATE t SET col_utf8 = CONVERT(col_latin1 USING utf8mb4) WHERE id BETWEEN ...
驗證數據一致性，切換讀取到新列，刪除舊列，重命名。

四、版本升降級兼容性關鍵點

層級	變量	作用範圍	修改方式
服務器	character_set_server	全局默認	my.cnf / SET GLOBAL
數據庫	character_set_database	庫級默認 (CREATE DATABASE)	CREATE/ALTER DATABASE
數據表	character_set_table	表級默認 (CREATE TABLE)	CREATE/ALTER TABLE
連接	character_set_client/connection/results	客戶端交互編碼	SET NAMES / 驅動配置

版本躍遷	關鍵變更	應對措施
4.0 → 4.1	引入字符集系統、密碼哈希升級 (41 字節)、TIMESTAMP 格式變更	運行 mysql_fix_privilege_tables、應用連接 SET NAMES、備份再升級
4.1 → 5.0	存儲過程/觸發器/視圖、嚴格模式、INFORMATION_SCHEMA	檢查 SQL 模式兼容、觸發器命名空間變更 (5.0.10+)
5.0 → 5.1	插件架構、分區表、事件調度器、行級複製	升級授權表 (mysql_upgrade)、檢查分區表語法
5.1 → 5.5/5.6	InnoDB 變為默認引擎、性能模式、GTID、ICP、全文索引	my.cnf 參數遷移、啟用 GTID、檢查全文索引重建
5.6 → 5.7	JSON、生成列、sys schema、只讀實例、在線 DDL 增強	JSON 列遷移、sys schema 部署、調整 innodb_page_cleaners
5.7 → 8.0	CTE/窗口函數、隱藏索引、函數索引、直方圖、默認 utf8mb4、認證插件 caching_sha2_password	應用驅動升級 (支援 caching_sha2_password)、SQL 模式調整、保留關鍵字檢查

五、Discuz/WordPress 實戰遷移清單

# 1. 備份
mysqldump -u root -p --single-transaction --routines --triggers --events discuz > discuz_backup.sql

# 2. 方案 A 執行 (推薦)
iconv -f gbk -t utf-8 discuz_backup.sql > discuz_utf8.sql
# 修改建表語句 CHARSET=gbk → utf8mb4
# 建新庫 utf8mb4，導入

# 3. 配置文件修正
# Discuz: config/config_global.php
$_config['db'][1]['dbcharset'] = 'utf8mb4';
# WordPress: wp-config.php
define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', 'utf8mb4_general_ci');

# 4. 應用層連接設置
# PHP PDO: charset=utf8mb4
# PHP mysqli: mysqli_set_charset($conn, 'utf8mb4');

# 5. 驗證
SELECT * FROM pre_forum_post WHERE message LIKE '%中文%' LIMIT 1;

六、總結

字符集遷移核心原理：**「存儲編碼 = 連接編碼 = 應用編碼」三者統一**。遷移策略選擇：
🟢 **小表/可停機** → 方案 B (ALTER CONVERT) 最簡單。
🟡 **中表/可接受短暫停寫** → 方案 A (導出轉碼導入) 最安全。
🔴 **大表/零停機** → 方案 C (雙寫遷移) 唯一選擇。
版本升級鐵律：**備份 → 閱讀 Release Notes → mysql_upgrade → 驗證應用 → 灰度發布**。切記：字符集遷移與版本升級分開做，單次變更單一維度，才能快速定位問題。