Java 連線 MySQL 亂碼終極解決方案：五層編碼鏈路完整配置

編碼鏈路五關鍵點

1. 資料庫/表/欄位字符集
2. JDBC 連線參數
3. 應用層請求/回應編碼
4. JSP/Servlet 容器設定
5. MySQL 伺服器配置

方案一：MySQL 伺服器與資料庫層面 (根基)

-- my.ini / my.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

-- 建表指定
CREATE TABLE Company (
    Cname VARCHAR(10) NOT NULL,
    Caddr VARCHAR(100),
    Ctel VARCHAR(16)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

方案二：JDBC 連線 URL 明確指定 (最關鍵)

String url = "jdbc:mysql://localhost:3306/test?"
    + "user=root&password=1"
    + "&useUnicode=true"
    + "&characterEncoding=utf-8"
    + "&autoReconnect=true"
    + "&useSSL=false"
    + "&serverTimezone=Asia/Taipei";

Connection conn = DriverManager.getConnection(url);

關鍵參數：useUnicode=true&characterEncoding=utf-8 (或 utf8mb4)

方案三：JSP/Servlet 請求回應編碼統一

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>

// Filter 統一處理 (web.xml)

    EncodingFilter
    com.example.EncodingFilter
    
        encoding
        UTF-8
    


    EncodingFilter
    /*

// EncodingFilter.java 核心
public void doFilter(ServletRequest req, ServletResponse res, FilterChain chain) {
    req.setCharacterEncoding("UTF-8");
    res.setCharacterEncoding("UTF-8");
    res.setContentType("text/html;charset=UTF-8");
    chain.doFilter(req, res);
}

方案四：強制轉碼補救 (不推薦常態使用)

// GET 參數亂碼 (ISO-8859-1 → UTF-8)
String name = request.getParameter("name");
name = new String(name.getBytes("ISO-8859-1"), "UTF-8");

// 結果集亂碼
String name = new String(rs.getString("name").getBytes("ISO-8859-1"), "UTF-8");

方案五：Tomcat 連接器設定 (GET 亂碼根治)

排查清單 (由基礎到應用)

1. SHOW VARIABLES LIKE 'character_set_%'; 全 utf8mb4
2. 表/欄位 SHOW CREATE TABLE 確認 CHARSET
3. JDBC URL 含 characterEncoding=utf-8
4. JSP/FILTER 設定 UTF-8
5. Tomcat URIEncoding="UTF-8"
6. 瀏覽器檢視編碼 (F12 Network → Headers → Content-Type)

版本提醒

• MySQL Connector/J 5.1.x：characterEncoding=utf-8 實為 utf8mb3
• Connector/J 8.0+：支援 characterEncoding=utf8mb4，推薦升級
• Java 8+ / Tomcat 8.5+ / MySQL 5.7+ / Connector/J 8.0+ 為現代基準棧