Linux 下 MySQL 預設字符集 latin1 導致中文亂碼，需修改配置檔 /etc/my.cnf 統一設為 utf8mb4，並注意 character-set-server vs default-character-set 的關鍵區別，避免啟動失敗。
| 類型 | 路徑 | 說明 |
|---|---|---|
| 資料目錄 | /var/lib/mysql/ | 所有庫表檔案 |
| 配置檔/啟動腳本 | /usr/share/mysql/ | mysql.server、my-*.cnf 模板 |
| 二進位命令 | /usr/bin/ | mysql、mysqladmin 等 |
| 啟動腳本 | /etc/rc.d/init.d/ | System V 風格 |
| 主配置檔 | /etc/my.cnf | 生效配置編輯此處 |
mysql -u root -p
SHOW VARIABLES LIKE 'character_set%';
預設輸出多為 latin1，character_set_system 為 utf8 (系統標識符固定)。
[client]
default-character-set = utf8mb4
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user = mysql
symbolic-links = 0
# === 核心區別 ===
# character-set-server = utf8mb4 ✅ 正確：伺服器端字符集
# default-character-set = utf8mb4 ❌ 錯誤：放在 [mysqld] 會導致啟動失敗 "The server quit without updating PID file"
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'
[mysql]
no-auto-rehash
default-character-set = utf8mb4
[mysqld_safe]
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
| 參數 | 作用域 | 效果 | 放置區段 |
|---|---|---|---|
character-set-server | 伺服器端 | 決定庫/表/欄位預設字符集、數據儲存編碼 | [mysqld] |
default-character-set | 客戶端 | 決定客戶端連線發送/接收編碼 | [client]、[mysql]、[mysqldump] |
init_connect | 連線建立時 | 自動執行 SET NAMES utf8mb4，確保會話層一致 (超級用戶不觸發) | [mysqld] |
血淚教訓： 在 [mysqld] 寫 default-character-set=utf8 會導致 mysqld 啟動解析配置失敗，報錯 Starting MySQL...The server quit without updating PID file。
systemctl restart mysqld
# 或
service mysqld restart
# 或
/etc/init.d/mysqld restart
mysql -u root -p
SHOW VARIABLES LIKE 'character_set%';
-- 期望全部顯示 utf8mb4 (除 filesystem=binary、system=utf8)
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4; 重建表轉碼，不可僅改配置。?characterEncoding=utf8mb4&useUnicode=true，Python charset='utf8mb4'，Go DSN charset=utf8mb4。LANG=zh_TW.UTF-8 或 en_US.UTF-8，非 zh_TW.Big5。#!/bin/bash
echo "=== Server Character Set ==="
mysql -N -e "SHOW VARIABLES LIKE 'character_set_%';" | grep -v filesystem | grep -v system
echo -e "\n=== Database Defaults ==="
mysql -N -e "SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;"
echo -e "\n=== Table/Column Mismatch (sample) ==="
mysql -N -e "
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE CHARACTER_SET_NAME != 'utf8mb4' AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
LIMIT 20;"
統一字符集配置是中文系統上線前的「必修課」，/etc/my.cnf 一處搞定伺服器端，客戶端各自配妥，杜絕亂碼從源頭。
