MySQL 存儲過程中文亂碼與 WHERE 條件比較失效：兩大核心問題徹底解決

問題背景

MySQL 存儲過程中頻發兩類中文問題：

1. 寫入亂碼：通過 SQL 腳本/應用調用存儲過程插入中文，數據庫存儲變為「???」或亂碼符號。
2. WHERE 比較失效：存儲過程內部用中文參數做 WHERE name = param 條件查詢，明明有數據卻返回空結果。

根因統一指向：**字符集鏈路不一致**，特別是**連接層字符集 (character_set_connection) 與數據存儲字符集脫節**。

核心原理：MySQL 四層字符集體系

關鍵認知：存儲過程在 Server 端運行，但其參數傳遞、SQL 構建、結果返回都經過**連接層字符集轉換**。若連接層為 latin1 而數據為 utf8mb4，中文會在「客戶端→連接層→存儲引擎」兩次轉換中損壞。

問題一：存儲過程寫入中文亂碼 - 解決方案

方案 1：連接建立後立即執行 SET NAMES (最常用、最有效)

-- 在調用存儲過程的客戶端代碼中 (PHP/Java/Python/C++/Shell)
-- 連接成功後、調用存儲過程前，執行一次：
SET NAMES 'utf8mb4';
-- 等價於：
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

各語言具體實現：

• PHP PDO: DSN 添加 charset=utf8mb4
• PHP mysqli: $mysqli->set_charset('utf8mb4')
• Java JDBC: URL 添加 ?characterEncoding=utf8mb4 (需驅動 5.1.47+)
• Python PyMySQL: charset='utf8mb4' 參數
• Go sqlx: DSN charset=utf8mb4
• MySQL Shell: mysql --default-character-set=utf8mb4

方案 2：服務器級強制統一 (一勞永逸，推薦生產環境)

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'           -- 非 SUPER 權限用戶連接自動執行
skip-character-set-client-handshake = ON     -- 忽略客戶端聲明字符集，強制使用服務端設置

重啟 MySQL 後，所有新連接自動使用 utf8mb4，無需客戶端代碼修改。

方案 3：存儲過程內部顯式聲明 (不推薦，治標不治本)

CREATE PROCEDURE `sp_insert_user`(IN p_name VARCHAR(100))
BEGIN
    -- 強制設置當前會話字符集 (僅對當前會話有效)
    SET NAMES 'utf8mb4';
    INSERT INTO users(name) VALUES (p_name);
END

缺陷：若調用者連接層已錯誤，參數 p_name 在進入存儲過程前已損壞，內部 SET NAMES 無法挽回。

問題二：WHERE 條件中文比較失效 - 解決方案

現象復現

-- 表結構
CREATE TABLE t (name VARCHAR(50) CHARSET utf8mb4) CHARSET=utf8mb4;
INSERT INTO t VALUES ('张三');

-- 存儲過程
CREATE PROCEDURE sp_find(IN p_name VARCHAR(50))
BEGIN
    SELECT * FROM t WHERE name = p_name;  -- 傳入 '张三' 卻查無結果
END

根因：隱式排序規則衝突

• 列 name 排序規則：utf8mb4_unicode_ci (或表默認)
• 參數 p_name / 字符串常量 排序規則：collation_connection (連接層)，若連接層為 latin1_swedish_ci 或 utf8mb4_general_ci，與列排序規則不同，MySQL **無法使用索引**，且可能導致比較語義差異 (如大小寫、重音、全半角敏感度)。

方案 1：統一連接層排序規則 (根治)

-- 客戶端連接後執行
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
-- 或 my.cnf
init_connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'

方案 2：SQL 顯式指定 COLLATE (針對性修復)

-- 修正存儲過程
CREATE PROCEDURE sp_find(IN p_name VARCHAR(50))
BEGIN
    -- 方法 A: 參數側指定
    SELECT * FROM t WHERE name = p_name COLLATE utf8mb4_unicode_ci;

    -- 方法 B: 列側指定 (同等效果)
    SELECT * FROM t WHERE name COLLATE utf8mb4_unicode_ci = p_name;

    -- 方法 C: 兩側都指定 (最保險)
    SELECT * FROM t WHERE name COLLATE utf8mb4_unicode_ci = p_name COLLATE utf8mb4_unicode_ci;
END

原理：COLLATE 子句強制指定比較操作使用的排序規則，消除隱式推導歧義，且**不影響索引使用** (前提是列索引排序規則與指定一致)。

方案 3：修正列/表默認排序規則 (Schema 級治理)

-- 修改表默認排序規則 (僅影響後續新增列)
ALTER TABLE t DEFAULT COLLATE utf8mb4_unicode_ci;

-- 修改現有列排序規則 (需重建表/列，會鎖表)
ALTER TABLE t MODIFY name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 或
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  -- 全表轉換

避坑清單：存儲過程中文問題自查表

最佳實踐總結

1. 基礎設施層：my.cnf 強制 character-set-server=utf8mb4 + collation-server=utf8mb4_unicode_ci + init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci' + skip-character-set-client-handshake=ON。
2. Schema 層：所有表/列顯式 CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci，遷移腳本包含 CONVERT TO CHARACTER SET。
3. 應用層：驅動 DSN 顯式 charset=utf8mb4，不依賴服務端默認。
4. 存儲過程層：不再手動 SET NAMES，信任基礎設施層；必要時在比較條件顯式 COLLATE utf8mb4_unicode_ci。
5. 監控層：定期掃描 information_schema.COLUMNS 發現非 utf8mb4 列，告警修復。

總結

MySQL 存儲過程中文問題 90% 源於**連接層字符集/排序規則與存儲層不一致**。徹底解決之道是：**基礎設施層強制統一 utf8mb4 + utf8mb4_unicode_ci，消除隱式轉換**。存儲過程內部僅作為業務邏輯容器，不應承擔字符集糾錯職責。遵循「配置下沉、顯式優於隱式、全鏈路一致」原則，即可從根源消滅中文亂碼與比較失效顽疾。