問題現象

ERROR 1366 (HY000): Incorrect string value: '\xB9\xD8\xD3\xF0' for column 'username'
-- 插入中文報錯
SELECT * FROM users;
-- 顯示：???? 或 鍾

編碼層層剖析

MySQL 涉及四個關鍵字符集層級：

層級	變數	作用
Server	character_set_server	新建庫/表預設字符集
Database	character_set_database	庫級預設，SHOW CREATE DATABASE 可見
Table	character_set_table	表級預設，SHOW CREATE TABLE 可見
Column	CHARSET 欄位	實際儲存編碼，**最關鍵**
Connection	character_set_client/connection/results	客戶端與 Server 交互編碼

診斷步驟

-- 1. 檢查服務端設定
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

-- 2. 檢查庫/表/欄位實際編碼
SHOW CREATE DATABASE db_name;
SHOW CREATE TABLE tbl_name;
SHOW FULL COLUMNS FROM tbl_name;

解決方案分級

級別 1：連線層臨時修正（不改資料）

-- 連線後立即執行
SET NAMES utf8mb4;
-- 等同於
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

適用：臨時查詢、無法修改 Server 配置的場景。

級別 2：修正 Server 預設（新建物件生效）

修改 my.cnf：

[client]
default-character-set = utf8mb4

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci

[mysql]
default-character-set = utf8mb4

重啟 MySQL 後，新建 資料庫/表/欄位將預設為 utf8mb4。

級別 3：遷移既有資料（徹底根治）

1. 修改庫預設：

   ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

2. 修改表預設 + 轉換欄位（關鍵）：

   ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

   CONVERT TO 會重建表、轉換所有字符欄位資料，**務必備份並離峰執行**。

3. 驗證欄位級編碼：

   SHOW FULL COLUMNS FROM tbl_name;
   -- 確認 Collation 皆為 utf8mb4_general_ci

PHP/Java 連線配置

PHP (PDO)

$dsn = "mysql:host=localhost;dbname=test;charset=utf8mb4";
$pdo = new PDO($dsn, $user, $pass, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
]);

Java (JDBC)

url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8mb4&connectionCollation=utf8mb4_general_ci&useSSL=false&serverTimezone=Asia/Shanghai";

常見誤區

誤區	正確認知
以為 ALTER TABLE ... DEFAULT CHARSET=utf8mb4 就轉換了資料	僅影響**新增欄位**，舊欄位不變。必須用 CONVERT TO
使用 utf8 而非 utf8mb4	utf8 = utf8mb3，不支援 Emoji，請直接用 utf8mb4
只改 Server 配置以為既有資料會自動轉碼	配置僅影響**新建**物件，既有資料需 CONVERT TO
匯出 SQL 檔未指定字符集	mysqldump --default-character-set=utf8mb4 ...

遷移檢核清單

• [ ] 完整備份（mysqldump --single-transaction --default-character-set=utf8mb4）
• [ ] 修改 my.cnf 並重啟驗證 Server 變數
• [ ] 執行 ALTER DATABASE ... CHARACTER SET utf8mb4
• [ ] 逐表執行 ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4
• [ ] 驗證 SHOW FULL COLUMNS 所有字符欄位 Collation 正確
• [ ] 更新應用連線字串加入 charset=utf8mb4
• [ ] 全鏈路測試：寫入/讀取中文、Emoji、生僻字

總結

latin1 亂碼本質是**編碼層級不一致**。徹底解決需「Server 配置 → 庫/表/欄位遷移 → 客戶端連線」全鏈路統一為 utf8mb4。CONVERT TO CHARACTER SET 是核心關鍵步驟，務必在維護窗口、備份後執行。遷移完成後，中文、Emoji、多語言將完美支援。