您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 字符集統一 utf8mb4 實戰：配置修改、建表規範、資料導入避坑

編輯：MySQL 綜合教學

MySQL 字符集統一 utf8mb4 實戰：配置修改、建表規範、資料導入避坑

中文亂碼 90% 來自「連線 ≠ 表 ≠ 資料庫 ≠ 欄位」字符集不一致。本文提供「全鏈路 utf8mb4」標準化流程：配置檔修改、Session 級臨時修改、建表/建庫規範、LOAD DATA 導入實戰腳本、驗證指令。

1. 永久配置 `/etc/mysql/my.cnf`

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'  # 非 super 用戶自動執行
skip-character-set-client-handshake  # 強制忽略客戶端請求字符集 (慎用，會破壞異構遷移)

重啟後驗證：

SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';
-- 期望全部顯示 utf8mb4 / utf8mb4_general_ci

2. 臨時修改 (Session 級，無需重啟)

SET NAMES utf8mb4;  -- 等同於下面三句
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

-- 亦可設定 collation
SET collation_connection = utf8mb4_general_ci;

3. 建庫建表規範

CREATE DATABASE biz_db
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_general_ci;

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL COMMENT '姓名',
  email VARCHAR(100) NOT NULL COMMENT '郵箱',
  bio TEXT COMMENT '簡介',
  PRIMARY KEY (id),
  UNIQUE KEY uk_email (email)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_general_ci
  COMMENT='使用者表';

鐵律：所有 CHAR/VARCHAR/TEXT/ENUM/SET 欄位必須顯式或繼承 utf8mb4，禁止 latin1/gbk/utf8 (utf8mb3)。

4. 資料導入實戰腳本 (`metadb.cresql` 風格)

-- metadb.cresql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS metadb
  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE metadb;

DROP TABLE IF EXISTS `city`;
DROP TABLE IF EXISTS `prov`;

CREATE TABLE `prov` (
  `prov_code` SMALLINT UNSIGNED NOT NULL COMMENT '省代碼',
  `prov_name` VARCHAR(30) NOT NULL COMMENT '省名稱',
  PRIMARY KEY (`prov_code`),
  UNIQUE KEY `uk_prov_name` (`prov_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='省表';

CREATE TABLE `city` (
  `city_code` INT UNSIGNED NOT NULL COMMENT '市代碼',
  `city_name` VARCHAR(30) NOT NULL COMMENT '市名稱',
  `prov_code` SMALLINT UNSIGNED NOT NULL COMMENT '省代碼',
  PRIMARY KEY (`city_code`),
  UNIQUE KEY `uk_city_name` (`city_name`),
  KEY `idx_city_prov` (`prov_code`),
  CONSTRAINT `fk_city_prov` FOREIGN KEY (`prov_code`) REFERENCES `prov`(`prov_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='市表';

-- 載入資料 (檔案必須為 UTF-8 無 BOM)
LOAD DATA LOCAL INFILE './metadb-prov.dat'
  INTO TABLE `prov`
  CHARACTER SET utf8mb4
  FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
  (`prov_code`, `prov_name`);

LOAD DATA LOCAL INFILE './metadb-city.dat'
  INTO TABLE `city`
  CHARACTER SET utf8mb4
  FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
  (`city_code`, `city_name`, `prov_code`);

SET FOREIGN_KEY_CHECKS = 1;

5. 資料檔編碼驗證

# Linux/Mac
file metadb-prov.dat
# 輸出: UTF-8 Unicode text

# 或
head -n 1 metadb-prov.dat | od -c | head -20
# 確認中文字節序列為 3 字節 (utf8mb4)

6. 常見導入錯誤對照

錯誤	原因	解決
Incorrect string value	資料檔編碼 ≠ CHARACTER SET 子句	檔案轉 UTF-8，或指定正確 CHARACTER SET
Row 1 doesn't contain data for all columns	欄位數不匹配、分隔符錯誤	檢查 FIELDS TERMINATED BY、ENCLOSED BY
Duplicate entry for key 'PRIMARY'	資料重複	清洗資料或用 REPLACE/IGNORE
Cannot add foreign key constraint	父表資料缺失、類型不符、字符集不一致	先導入父表、確認類型/字符集一致