萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 資料庫編碼轉換全流程:latin1 → utf8mb4 兩種標準化方案

方案一:重建庫表 + 應用層設定(新專案/可停機)

1. 建庫建表指定 utf8mb4

CREATE DATABASE dbname DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE test (
  id INT NOT NULL,
  name VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. PHP 連線設定

$pdo = new PDO("mysql:host=localhost;dbname=dbname;charset=utf8mb4", $user, $pass);
// 或 mysqli
$mysqli->set_charset('utf8mb4');

方案二:現有資料無縫轉碼(不可停機/數據量大)

步驟 1:以 latin1 導出(保持原始位元組)

mysqldump --opt -hlocalhost -uroot -p --default-character-set=latin1 dbname > /backup/dbname_latin1.sql

步驟 2:修改建表語句編碼宣告

# 將 CHARSET=latin1 替換為 utf8mb4
sed -i 's/CHARSET=latin1/CHARSET=utf8mb4/g' /backup/dbname_latin1.sql
sed -i 's/CHARSET=latin1/CHARSET=utf8mb4/g' /backup/dbname_latin1.sql
# 同時處理 COLLATE
sed -i 's/COLLATE=latin1_[^ ]*/COLLATE=utf8mb4_unicode_ci/g' /backup/dbname_latin1.sql

步驟 3:插入 SET NAMES 指令

sed -i '1i SET NAMES utf8mb4;' /backup/dbname_latin1.sql

步驟 4:檔案編碼轉換(關鍵)

# 方法 A:iconv(推薦)
iconv -f latin1 -t utf-8 /backup/dbname_latin1.sql > /backup/dbname_utf8.sql

# 方法 B:UltraEdit / Notepad++ / VS Code
# 另存為 UTF-8 無 BOM

步驟 5:以 utf8mb4 導入新庫

mysql -hlocalhost -uroot -p --default-character-set=utf8mb4 new_dbname < /backup/dbname_utf8.sql

驗證轉碼正確性

# 抽樣查詢中文欄位
SELECT col FROM new_dbname.table WHERE col LIKE '%中文%' LIMIT 10;

# 檢查編碼
SHOW CREATE TABLE new_dbname.table\G
# 應顯示 CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

常見坑點

  • 雙重編碼:導出時用 utf8mb4、導入時又用 utf8mb4,實際資料已是 latin1 存儲 → 亂碼。導出必須用 latin1。
  • BLOB/TEXT 欄位轉碼需 CONVERT TO CHARACTER SET,單純 ALTER TABLE ... CHARSET= 僅改預設值。
  • 外鍵約束可能阻礙 CONVERT TO,需先 SET FOREIGN_KEY_CHECKS=0。
  • 視圖/存儲過程/觸發器內的字串常量需手動檢查修正。

線上零停機轉碼(進階)

使用 pt-online-schema-change (Percona Toolkit) 或 gh-ost (GitHub) 進行線上表結構變更,配合 CONVERT TO CHARACTER SET 實現零停機轉碼。

copyright © 萬盛學電腦網 all rights reserved