Linux 環境下 MySQL 編碼設定不當,會導致中文亂碼、存入變問號。本文提供 my.cnf 完整配置、驗證指令、Java Web 應用層協同設定。
# vi /etc/my.cnf
[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
關鍵點:
utf8mb4 而非 utf8(支援 Emoji、完整 Unicode)character-set-server 設定伺服器預設字元集init_connect 讓非超級用戶連線自動執行 SET NAMESsystemctl restart mysqld
mysql -u root -p -e "SHOW VARIABLES LIKE '%char%';"
預期輸出關鍵變數皆為 utf8mb4:
+--------------------------+----------------------------+
| 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 | utf8mb4 |
+--------------------------+----------------------------+
CREATE DATABASE `my_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE `t` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
<%@ page pageEncoding="UTF-8" contentType="text/html;charset=UTF-8" %>
<% request.setCharacterEncoding("UTF-8"); %>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
// Filter 統一處理
public class EncodingFilter implements Filter {
public void doFilter(ServletRequest req, ServletResponse res, FilterChain chain) {
req.setCharacterEncoding("UTF-8");
res.setContentType("text/html;charset=UTF-8");
chain.doFilter(req, res);
}
}
jdbc:mysql://localhost:3306/my_db?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Taipei
useUnicode=true + characterEncoding=utf8mb4 強制連線字元集serverTimezone 避免時區警告| 現象 | 可能原因 | 解決 |
|---|---|---|
| 存入 ??? 或 | 連線字元集非 utf8mb4 | 檢查 JDBC URL、SET NAMES |
| 讀出亂碼 | 頁面/回應編碼不一致 | 統一 JSP/Filter/HTTP Header 為 UTF-8 |
| Emoji 變 ?? | 資料庫/表仍為 utf8 | ALTER TABLE ... CONVERT TO CHARSET utf8mb4 |
從伺服器、連線、應用三層全面統一 UTF-8MB4,徹底解決中文編碼問題。