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

MySQL 數據庫編碼亂碼徹底解決：從建庫到遷移全流程

編輯：MySQL 資料庫相關知識

編碼層級體系

MySQL 擁有四級字符集設置，優先級遞減：

實例級：character_set_server（啟動參數/配置文件）
庫級：CREATE DATABASE ... DEFAULT CHARSET utf8mb4
表級：CREATE TABLE ... DEFAULT CHARSET utf8mb4
列級：col VARCHAR(100) CHARACTER SET utf8mb4

連接層：SET NAMES utf8mb4 / 連接串 characterEncoding=utf8mb4 決定客戶端↔服務端傳輸編碼，必須與數據存儲編碼一致。

新建項目標準化建庫建表

-- 1. 庫級指定 utf8mb4（支持 Emoji、生僻字）
CREATE DATABASE my_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 2. 表級繼承或顯式指定
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  bio TEXT
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. 連接配置（各語言範例）
-- PHP: mysqli_set_charset($link, 'utf8mb4'); 或 PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
-- Java JDBC: jdbc:mysql://host/db?useUnicode=true&characterEncoding=utf8mb4&zeroDateTimeBehavior=convertToNull
-- Python: pymysql.connect(charset='utf8mb4')
-- Go: dsn "user:pass@tcp(host:3306)/db?charset=utf8mb4&parseTime=True&loc=Local"

存量數據遷移 latin1 → utf8mb4 三步走

# 1. 導出數據（指定源編碼 latin1，避免雙重轉換）
mysqldump --default-character-set=latin1 -u root -p --databases my_db --tables old_table > old_latin1.sql

# 2. 轉換文件編碼（Linux 環境）
iconv -f latin1 -t utf8mb4 old_latin1.sql > new_utf8mb4.sql
# 或自動檢測源編碼
iconv -t utf8mb4 old_latin1.sql > new_utf8mb4.sql

# 3. 導入前插入 SET NAMES，確保連接編碼正確
sed -i '1i SET NAMES utf8mb4;' new_utf8mb4.sql
mysql -u root -p my_db < new_utf8mb4.sql

在線無鎖轉換（MySQL 4.1+ ALTER TABLE）

-- 單表轉換（會鎖表，大表慎用）
ALTER TABLE old_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 大表在線轉換方案（pt-online-schema-change / gh-ost）
pt-online-schema-change --alter "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" \
  D=my_db,t=old_table --execute --user=root --password=xxx

常見亂碼場景診斷表

現象	根因	解決
插入中文報 `Incorrect string value`	列/表/庫編碼為 latin1，無法存儲多字節字符	ALTER TABLE ... CONVERT TO CHARSET utf8mb4
查出來是 ??? 或亂碼	連接編碼 (SET NAMES) 與存儲編碼不一致	統一設置 SET NAMES utf8mb4 / 連接串 charset
排序/比較錯誤	校對集不支持中文規則（如 utf8_general_ci）	改用 utf8mb4_unicode_ci 或 utf8mb4_zh_0900_as_cs
Emoji 存成 ????	使用 utf8（僅 3 字節）而非 utf8mb4（4 字節）	全鏈路升級 utf8mb4

客戶端工具編碼設置

MySQL Workbench / Navicat / DataGrip：連接屬性 → Encoding 選 UTF-8 / UTF-8MB4
命令行 mysql：啟動前 export LANG=zh_CN.UTF-8，連接後 SET NAMES utf8mb4;
Linux 終端：終端模擟器編碼設置為 UTF-8

一條龍檢查清單

-- 1. 檢查實例默認
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

-- 2. 檢查庫/表/列
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA='my_db';
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='my_db' AND TABLE_NAME='users';

-- 3. 檢查連接
SHOW VARIABLES LIKE 'character_set_%';  -- client/connection/results 應全為 utf8mb4

統一全鏈路 utf8mb4 + utf8mb4_unicode_ci，配合規範的連接編碼設置，可徹底杜絕 99% MySQL 中文亂碼問題。