安裝 MySQL 後默認字符集可能為 latin1,導致中文存儲亂碼。本文說明如何通過修改配置文件永久變更默認字符集為 UTF-8(或 utf8mb4)。
/etc/my.cnf 或 /etc/mysql/my.cnfmy.inimysql/bin/my.ini編輯配置文件,在對應區段添加/修改以下參數:
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
skip-character-set-client-handshake
關鍵參數說明:
character-set-server:服務端默認字符集collation-server:默認校對規則,推薦 utf8mb4_unicode_ci(準確排序)或 utf8mb4_general_ci(性能較好)init_connect:客戶端連接時自動執行 SET NAMESskip-character-set-client-handshake:忽略客戶端字符集聲明,強制使用服務端設置utf8 僅支援 3 字節字符(BMP 平面),無法存儲 Emoji、生僻漢字等 4 字節字符。utf8mb4 完整支援 Unicode,是現代應用的最佳選擇。
# Linux systemd
systemctl restart mysqld
# 或
service mysqld restart
# Windows
net stop mysql
net start mysql
mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| 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 | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+----------------------+--------------------+
配置修改僅影響新建對象,既有對象需手動轉換:
-- 數據庫級別
ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
-- 表級別(含列轉換)
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
characterEncoding=utf8mb4 (JDBC) / charset=utf8mb4 (Python/PHP)Specified key was too long 錯誤:MySQL 5.7.7 前版本 utf8mb4 索引長度限制,需調整列長度或升級版本