MySQL 預設字符集 latin1 不支援中文,導致中文存儲變「問號」或亂碼。本文提供 Linux (my.cnf) 與 Windows (my.ini) 雙平台完整修改流程,含服務重啟驗證、JSP 頁面編碼協同配置,徹底解決中文存儲顯示問題。
mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
關鍵項:character_set_server、character_set_database、character_set_client/connection/results 均為 latin1。
service mysql stop
# 或 systemctl stop mysqld
[mysqld]
# 核心:服務端字符集
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
# 連接層自動設定(客戶端無需指定)
init_connect = 'SET NAMES utf8mb4'
# 可選:彈性支援舊應用
# character_set_client = utf8mb4
# character_set_connection = utf8mb4
# character_set_results = utf8mb4
關鍵點:
utf8mb4 而非 utf8:支援 Emoji、生僻字(4 字節)collation_server 用 utf8mb4_unicode_ci(準確排序)或 utf8mb4_general_ci(速度稍快)init_connect 讓所有新連接自動執行 SET NAMES,免去應用層配置service mysql start
mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_server | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_results | utf8mb4 |
| ... | ... |
+--------------------------+--------------------+
典型路徑:C:\Program Files\MySQL\MySQL Server 5.x\my.ini 或 C:\ProgramData\MySQL\MySQL Server 5.x\my.ini
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
[client]
default_character_set = utf8mb4
[mysql]
default_character_set = utf8mb4
# 管理員 PowerShell/CMD
net stop mysql
net start mysql
# 或:服務管理器 (services.msc) → MySQL → 重新啟動
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
jdbc:mysql://host:3306/db?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai
若表已含亂碼資料,需雙重轉換:
-- 1. 修改表預設字符集(僅影響新資料)
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 2. 若資料已被錯誤存為 latin1(實際為 utf8 位元組),需「雙重轉換」
-- 步驟:latin1 欄位 → 二進制 → 正確解碼為 utf8mb4
UPDATE tbl_name SET col = CONVERT(CAST(CONVERT(col USING latin1) AS BINARY) USING utf8mb4);
-- 或分批處理大表,避免鎖表過久
--default-character-set=utf8mb4字符集配置是「一處設定,全鏈路生效」:Server 端 my.cnf/my.ini 定基調 → 連接層 init_connect/連接字串強制對齊 → 應用層頁面/驅動程式宣告一致。統一採用 utf8mb4 + utf8mb4_unicode_ci 是現代 MySQL 應用的標準配置,徹底告別中文亂碼時代。