MySQL 字元集參數全解析：從 latin1 混亂到 UTF-8 統一配置實戰

徹底解決 MySQL 字元集不一致：七大參數含義、my.ini 統一配置、驗證與建表最佳實踐

安裝後雖指定 UTF-8，但 character_set_client/connection/results 常仍為 latin1，導致應用層亂碼。本文系統說明各參數職能、my.ini 完全配置法、Windows 複製生效機制、建表顯式指定。

現象重現

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_client     | latin1|  ← 需修正
| character_set_connection | latin1|  ← 需修正
| character_set_database   | utf8  |
| character_set_results    | latin1|  ← 需修正
| character_set_server     | utf8  |
| character_set_system     | utf8  |
+--------------------------+-------+

根因分析

• MySQL 讀取配置檔順序：/etc/my.cnf → DATADIR/my.cnf → --defaults-extra-file → ~/.my.cnf
• Windows 服務啟動若未讀取到 [client]/[mysql] 區段，連線層參數仍用預設 latin1

標準解法：my.ini 三區段全覆蓋

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
init_connect='SET NAMES utf8'

關鍵步驟：將修改後的 my.ini 複製至 Windows 系統目錄（C:\Windows\ 或 C:\WINNT\），重啟 MySQL 服務。

驗證成果

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_client     | utf8  |
| character_set_connection | utf8  |
| character_set_database   | utf8  |
| character_set_results    | utf8  |
| character_set_server     | utf8  |
| character_set_system     | utf8  |
| character_sets_dir       | ...   |
+--------------------------+-------+

建表顯式指定（雙重保險）

CREATE TABLE t_sysuser (
    USERID INT NOT NULL AUTO_INCREMENT,
    USERNAME VARCHAR(30),
    PASSWORDS VARCHAR(30),
    DEPTNO VARCHAR(30),
    HEADSHIP VARCHAR(30),
    SEX VARCHAR(4),
    TEL VARCHAR(18),
    EMAIL VARCHAR(30),
    URL VARCHAR(30),
    PCALL VARCHAR(20),
    USERPHOTO VARCHAR(255),
    FACEREFUTIME INT,
    FACELANGUAGE VARCHAR(1),
    LASTMODIF VARCHAR(30),
    LASTMDATE DATETIME,
    CREADTOR VARCHAR(30),
    CREATDATE DATETIME,
    PRIMARY KEY (USERID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

collation 參數同步檢查

mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

「配置檔三區段全覆蓋 + 複製至系統目錄 + 重啟服務 + 建表顯式 CHARSET」，徹底消除字元集不一致隱患。