設為首頁 加入收藏
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
您现在的位置： 程式碼之家 >> 數據庫知識 >  >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 字符集編碼完整操作指南：查看、創建、修改與驗證

     編輯：MySQL 綜合教學

MySQL 字符集編碼完整操作指南：查看、創建、修改與驗證

字符集編碼不一致是 MySQL 亂碼、數據截斷、排序錯誤的首要原因。本文系統整理 MySQL 字符集相關的所有核心操作：查看支援編碼、資料庫/表/欄位級別的創建與修改、會話級別動態設置、驗證方法，助您徹底解決編碼難題。

一、查看支援的字符集與排序規則

-- 列出所有支援字符集
SHOW CHARACTER SET;

-- 查看特定字符集詳細資訊
SHOW CHARACTER SET LIKE 'utf8%';
+---------+------------------+---------------------+--------+
| Charset | Description      | Default collation   | Maxlen |
+---------+------------------+---------------------+--------+
| utf8    | UTF-8 Unicode    | utf8_general_ci     |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_general_ci  |      4 |
+---------+------------------+---------------------+--------+

-- 查看字符集的所有排序規則
SHOW COLLATION LIKE 'utf8%';

二、查看當前實例/會話/資料庫/表/欄位編碼

-- 1. 實例/會話級別系統變量
SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |  -- 客戶端發送語句編碼
| character_set_connection | utf8mb4                    |  -- 連接層編碼
| character_set_database   | utf8mb4                    |  -- 當前資料庫預設編碼
| character_set_results    | utf8mb4                    |  -- 結果集返回編碼
| character_set_server     | utf8mb4                    |  -- 服務端預設編碼
| character_set_system     | utf8                       |  -- 系統標識符編碼(固定)
| character_sets_dir       | /usr/share/mysql/charsets/ |  -- 字符集文件目錄
+--------------------------+----------------------------+

-- 2. 資料庫編碼
SHOW CREATE DATABASE dbtest;
-- 或
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='dbtest';

-- 3. 表編碼
SHOW CREATE TABLE tbtest;

-- 4. 欄位編碼
SHOW FULL COLUMNS FROM tbtest;
-- 或
SELECT CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA='dbtest' AND TABLE_NAME='tbtest' AND COLUMN_NAME='email';

三、會話級別動態修改編碼（臨時生效）

-- 設置服務端預設編碼（需重啟永久生效，建議改配置檔）
SET character_set_server = utf8mb4;

-- 設置客戶端發送編碼
SET character_set_client = gbk;

-- 設置結果集返回編碼
SET character_set_results = gbk;

-- 一鍵設置三大核心變量（最常用）
SET NAMES utf8mb4;  
-- 等價於：
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

-- 僅設置連接編碼
SET CHARACTER SET utf8mb4;
-- 等價於：
SET character_set_client = utf8mb4;
SET character_set_results = utf8mb4;
SET collation_connection = utf8mb4_general_ci;

四、資料庫級別編碼操作

-- 1. 創建資料庫指定編碼（注意：utf8 不能寫成 utf-8）
CREATE DATABASE dbtest 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

-- 2. 修改資料庫編碼（僅影響後續新建表）
ALTER DATABASE dbtest 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

-- 3. 刪除資料庫
DROP DATABASE IF EXISTS dbtest;

五、表級別編碼操作

-- 1. 創建表指定編碼（表預設編碼 + 欄位覆蓋）
CREATE TABLE tbtest (
  id INT(10) AUTO_INCREMENT,
  user_name VARCHAR(60) CHARACTER SET gbk COLLATE gbk_chinese_ci,  -- 欄位級別覆蓋
  email VARCHAR(60),
  PRIMARY KEY (id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 2. 修改表預設編碼（僅影響後續新增欄位）
ALTER TABLE tbtest 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

-- 注意：修改表編碼不會自動轉換現有欄位數據！

六、欄位級別編碼修改與數據轉換（核心難點）

⚠ 關鍵區別：
MODIFY COLUMN ... CHARACTER SET utf8mb4 會嘗試轉換現有數據
CHANGE COLUMN ... CHARACTER SET utf8mb4 同樣會轉換數據
ALTER TABLE ... CHARACTER SET utf8mb4 僅修改表預設值，不轉換現有欄位 
-- 正確修改欄位編碼並轉換數據（推薦）
ALTER TABLE tbtest 
MODIFY email VARCHAR(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 或使用 CHANGE（需重複欄位名）
ALTER TABLE tbtest 
CHANGE email email VARCHAR(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 批次轉換全表所有字符串欄位（生產環境謹慎，先備份！）
-- 此腳本需根據實際表結構生成
SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' MODIFY ', COLUMN_NAME, ' ', 
  COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'dbtest' 
  AND CHARACTER_SET_NAME != 'utf8mb4'
  AND DATA_TYPE IN ('char','varchar','text','enum','set');

七、驗證編碼設置

-- 1. 查看欄位實際編碼
SELECT CHARSET(email) FROM tbtest;
-- 或
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='dbtest' AND TABLE_NAME='tbtest';

-- 2. 測試寫入/讀取
INSERT INTO tbtest (user_name, email) VALUES ('測試', 'test@example.com');
SELECT * FROM tbtest;

-- 3. 檢查十六進制內容（確認存儲編碼）
SELECT HEX(email), HEX(user_name) FROM tbtest;
-- utf8mb4 中文佔 3-4 bytes，gbk 佔 2 bytes

八、配置檔永久生效設置

[mysqld]
# 服務端預設編碼
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci

# 避免客戶端編碼不匹配
skip-character-set-client-handshake
# 強制忽略客戶端傳來的字符集聲明，統一使用服務端設置

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqldump]
default-character-set = utf8mb4

九、utf8 vs utf8mb4 選擇指南

特性utf8 (utf8mb3)utf8mb4
最大字符長度3 bytes4 bytes
Emoji 支援❌ 不支援✅ 完整支援
生僻字支援部分缺失完整 Unicode
索引長度限制767 bytes (單列)需配合 innodb_large_prefix
儲存空間略小略大 (ASCII 相同)
推薦版本MySQL 5.5 之前MySQL 5.5.3+ 強烈推薦

十、常見亂碼場景與解決

  1. 插入正常、查詢亂碼： 客戶端 character_set_results 錯誤 → SET NAMES utf8mb4
  2. 插入亂碼、查詢正常： 客戶端 character_set_client 錯誤 → 連接字串指定 charset=utf8mb4
  3. 存儲截斷/報錯： 欄位編碼為 latin1/gbk 存入 utf8 多字節字符 → 修改欄位編碼為 utf8mb4
  4. 排序錯誤： COLLATE 不匹配 → 統一使用 utf8mb4_general_ci 或 utf8mb4_unicode_ci
  5. JOIN 關聯失效： 關聯欄位字符集/排序規則不同 → 統一欄位編碼

字符集問題的核心原則：全鏈路一致。從客戶端連接、會話變量、資料庫預設、表預設、欄位實際、結果返回，每一環都必須統一為 utf8mb4。新專案請直接採用 utf8mb4，存量專案請謹慎規劃轉換窗口並充分測試。

  1. 上一篇文章：
  2. 下一篇文章：
MySQL 綜合教學
MySQL COLUMNS 分區深度解析：RANGE/LIST COLUMNS 實戰指南 MySQL COLUMNS 分區深度解析：RANGE/LIST COLUMNS 實戰指南

背景 MySQL 5.5 引入 COLUMNS 分區，支援

MySQL 利用 GROUP_CONCAT 實現字段分類匯總輸出 MySQL 利用 GROUP_CONCAT 實現字段分類匯總輸出

MySQL 利用 GROUP_CONCAT 實現字段分類匯總
MySQL 索引策略與優化實戰：從最左前綴到高性能設計 MySQL 索引策略與優化實戰：從最左前綴到高性能設計

前言：索引優化的核心邏輯 索引是數據庫性能的基石。理解 B+
MySQL 核心技術：InnoDB 存儲引擎內存管理深度解析 MySQL 核心技術：InnoDB 存儲引擎內存管理深度解析

MySQL 核心技術：InnoDB 存儲引擎內存管理深度解析
MySQL 免安裝版 (No-Install) 部署與服務管理完全指南 MySQL 查詢表索引命令詳解：SHOW INDEX 與字段含義完全指南 MySQL 觸發器創建與刪除：避坑指南（含 BEFORE/AFTER 陷阱） PyQt5 + MySQL 實戰：可視化數據庫查詢工具開發全記錄 (界面設計、動態 SQL、結果渲染) MySQL 直接 UPDATE mysql.user 修改用戶名導致無法登入的坑與正確姿勢 啟用 MySQL 查詢緩存：CPU 使用率從 120% 降至 10% 實戰 從 MSSQL 遷移 MySQL 語法差異全對比 (五)：運算符、正則、位運算與特殊字元
欄目導航
  • MySQL 入門
  • SQL 基礎
  • MySQL 資料庫相關知識
  • MariaDB 資料庫
  • MySQL 綜合教學
    • Copyright © 程式碼之家 All Rights Reserved