字符集亂碼是 MySQL 遷移、開發高發坑點。兩條核心命令快速定位當前實例、庫、表、列的編碼與校對規則配置,排查不一致根因。
-- 查看實例級字符集相關變量
SHOW VARIABLES LIKE 'character%';
-- 查看實例級校對規則相關變量
SHOW VARIABLES LIKE 'collation%';
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 | -- 系統標識符編碼(固定 utf8)
| character_sets_dir | /usr/share/mysql/charsets/ | -- 字符集文件目錄
+--------------------------+----------------------------+
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci | -- 連接層校對規則
| collation_database | utf8mb4_general_ci | -- 當前庫默認校對
| collation_server | utf8mb4_general_ci | -- 服務器默認校對
+----------------------+--------------------+
-- 庫級
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'db_name';
-- 表級
SHOW CREATE TABLE tbl_name\G
-- 或
SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA='db' AND TABLE_NAME='tbl';
-- 列級
SHOW FULL COLUMNS FROM tbl_name;
-- 或
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='db' AND TABLE_NAME='tbl';
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci -- 或 utf8mb4_0900_ai_ci (8.0+)
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
utf8 實為 utf8mb3,不支持 Emoji/生僻字,**必須用 utf8mb4**character_set_client/connection/results 需與應用框架配置一致(JDBC characterEncoding、Python charset、Go charset)_ci 不區分大小寫,_bin 二進制比較,_0900_ai_ci 8.0 新版 Unicode 9.0 無口音不區分大小寫ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 實際轉碼收藏這兩條命令,編碼問題十分鐘定位。