MySQL 數據庫/表/字段編碼查看、創建與修改完整指南

編碼層級體系

MySQL 編碼分四級，優先級由高到低：

1. 實例級：character_set_server
2. 數據庫級：character_set_database
3. 表級：DEFAULT CHARSET
4. 列級：CHARACTER SET

修改上層不影響已存在對象，僅作為新對象的默認值。

查看當前編碼設置

實例/會話級變量

SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

數據庫級編碼

SHOW VARIABLES LIKE 'character_set_database';
-- 或
SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='db_name';

表級編碼

SHOW CREATE TABLE table_name\G
-- 輸出中 DEFAULT CHARSET=xxx

列級編碼

SHOW FULL COLUMNS FROM table_name;
-- Collation 列顯示字符集與校對規則

創建時指定編碼

數據庫

CREATE DATABASE db_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

表

CREATE TABLE tb_books (
    name VARCHAR(45) NOT NULL,
    price DOUBLE NOT NULL,
    bookCount INT NOT NULL,
    author VARCHAR(45) NOT NULL
) DEFAULT CHARSET = utf8mb4 COLLATE utf8mb4_general_ci;

列 (覆蓋表級默認)

CREATE TABLE t (
    c1 VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
    c2 VARCHAR(20) CHARACTER SET latin1
) DEFAULT CHARSET=utf8mb4;

修改現有對象編碼

數據庫

ALTER DATABASE db_name 
CHARACTER SET = utf8mb4 
COLLATE = utf8mb4_general_ci;

表 (僅影響新增列)

ALTER TABLE table_name 
CHARACTER SET = utf8mb4 
COLLATE = utf8mb4_general_ci;

列 (需指定類型，數據將轉換)

-- 修改單列
ALTER TABLE table_name 
MODIFY col_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 批量轉換全表所有字符列 (5.6+)
ALTER TABLE table_name 
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

CONVERT TO vs MODIFY：

• CONVERT TO：重建表，轉換所有字符列數據，推薦用於編碼升級 (latin1→utf8mb4)
• MODIFY：僅修改列定義，不轉換現有數據，慎用

外鍵管理速查

添加外鍵

ALTER TABLE tb_product 
ADD CONSTRAINT fk_factory 
FOREIGN KEY (factoryid) REFERENCES tb_factory(factoryid);

刪除外鍵

ALTER TABLE tb_people 
DROP FOREIGN KEY fk_1;

utf8 vs utf8mb4 選型建議

總結

編碼問題核心在於「存儲編碼與連接編碼一致」。新項目直接指定 utf8mb4，舊項目升級用 CONVERT TO CHARACTER SET utf8mb4 並同步修正應用層連接字符串 characterEncoding=utf8mb4。定期巡檢 SHOW VARIABLES LIKE 'character_set%' 確保各層級一致。