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;
$pdo = new PDO("mysql:host=localhost;dbname=dbname;charset=utf8mb4", $user, $pass);
// 或 mysqli
$mysqli->set_charset('utf8mb4');
mysqldump --opt -hlocalhost -uroot -p --default-character-set=latin1 dbname > /backup/dbname_latin1.sql
# 將 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
sed -i '1i SET NAMES utf8mb4;' /backup/dbname_latin1.sql
# 方法 A:iconv(推薦)
iconv -f latin1 -t utf-8 /backup/dbname_latin1.sql > /backup/dbname_utf8.sql
# 方法 B:UltraEdit / Notepad++ / VS Code
# 另存為 UTF-8 無 BOM
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
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 實現零停機轉碼。