MySQL 字符集設置呈層級繼承關係:服務器 → 數據庫 → 表 → 列 → 連接。下層未顯式指定時繼承上層默認值。
-- 1. 服務器級字符集變量
SHOW VARIABLES LIKE '%char%';
-- 2. 查看支持的字符集
SHOW CHARSET;
-- 3. 查看數據庫字符集
SHOW CREATE DATABASE db_name;
-- 4. 查看表字符集
SHOW CREATE TABLE tbl_name;
-- 5. 查看列字符集
SHOW FULL COLUMNS FROM tbl_name;
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
注意: utf8mb4 支持完整 Unicode(含 Emoji),utf8 僅支持 BMP 平面(3 字節),**強烈建議使用 utf8mb4**。
SET NAMES utf8mb4; -- 等同於下面三句
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;
-- 其他會話級變量
SET character_set_database = utf8mb4;
SET character_set_server = utf8mb4; -- 需 SUPER 權限
SET collation_connection = utf8mb4_general_ci;
ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE tbl_name CHANGE col_name col_name
VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
character_set_client 解碼character_set_connection 進行內部操作character_set_servercharacter_set_results 返回客戶端my.cnf 中 default-character-set **僅影響 mysql 命令行客戶端**,不影響使用 libmysqlclient 的應用(如 PHP、Java、Python)UPPER、LOWER、SUBSTRING)按內部操作字符集執行,不受連接字符集影響CONVERT TO 重寫數據SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
-- 期望全部為 utf8mb4 / utf8mb4_general_ci