Ubuntu 15.04 MySQL 5.6.25 中文亂碼解決方案

Ubuntu apt安裝MySQL默認字符集常為latin1，導致中文存儲亂碼。本文提供配置文件修正、驗證、避坑指南，適用Ubuntu 14.04/16.04/18.04/20.04全系列。

一、修改配置文件

1. 客戶端配置

vi /etc/mysql/conf.d/mysql.cnf
# 或 /etc/mysql/mysql.conf.d/mysqld.cnf 中的 [client] 區塊
[client]
default-character-set=utf8mb4

2. 服務端配置

vi /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# 基礎
socket = /var/run/mysqld/mysqld.sock
port = 3306
# 字符集核心（注意：5.7+用 character-set-server，5.6用 character-set-server 同樣生效）
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
# 可選：強制客戶端字符集
skip-character-set-client-handshake

二、重啟服務生效

sudo systemctl restart mysql
# 或
sudo service mysql restart

三、驗證字符集設置

mysql -u root -p -e "SHOW VARIABLES LIKE '%char%';"
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb4                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

四、存量庫表字符集遷移（關鍵！）

配置文件修改僅影響新建庫表，存量數據需顯式轉換：

# 1. 庫級轉換
ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

# 2. 表級轉換（含數據轉碼）
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 3. 批量生成腳本
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA IN ('db1','db2') AND TABLE_COLLATION != 'utf8mb4_general_ci'
INTO OUTFILE '/tmp/convert_charset.sql';

# 4. 審核執行
mysql -u root -p < /tmp/convert_charset.sql

五、應用層配置檢查

六、常見誤區

• 用utf8而非utf8mb4：MySQLutf8僅支援BMP平面（3字節），無法存儲Emoji/罕見漢字，必須用utf8mb4
• 只改配置不轉存量表：新數據正常，舊數據亂碼依舊
• collation不統一：庫/表/欄位/連接四層collation不一致導致隱式轉換、索引失效
• skip-character-set-client-handshake濫用：強制服務端字符集可能破壞異構客戶端兼容性，慎用

一鍵檢查腳本：

mysql -u root -p -e "
SELECT 
  TABLE_SCHEMA, TABLE_NAME, 
  TABLE_COLLATION, 
  IF(TABLE_COLLATION LIKE 'utf8mb4%', 'OK', 'NEED_CONVERT') AS status
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY status, TABLE_SCHEMA, TABLE_NAME;"