MySQL 主鍵管理、自增屬性、字符集修改實戰筆記,含 PRIMARY KEY vs AUTO_INCREMENT 差異、字符集正確轉換姿勢 (避免雙重編碼錯誤)。
| 模式 | 定義語法 | INSERT 行為 | 重複風險 |
|---|---|---|---|
| PRIMARY KEY (無自增) | id VARCHAR(32) PRIMARY KEY | 必須顯式提供值 | 手動給同值 → Duplicate entry |
| NOT NULL AUTO_INCREMENT | id INT NOT NULL AUTO_INCREMENT PRIMARY KEY | 可省略/給 NULL/0,自動分配 | 自動避免重複 |
結論:業務鍵 (訂單號、身分證) 用 PRIMARY KEY 手動賦值;代理鍵 (內部 ID) 用 AUTO_INCREMENT。
-- 新增主鍵 (已有 id 欄位)
ALTER TABLE tb ADD PRIMARY KEY (id);
-- 將 id 改為自增主鍵 (需先刪除舊 PK)
ALTER TABLE tb DROP PRIMARY KEY;
ALTER TABLE tb MODIFY id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
-- 僅新增自增 (不設主鍵,罕見)
ALTER TABLE tb MODIFY id INT NOT NULL AUTO_INCREMENT;
-- 刪除自增屬性 (保留欄位)
ALTER TABLE tb MODIFY id INT; -- 移除 AUTO_INCREMENT
-- 刪除主鍵
ALTER TABLE tb DROP PRIMARY KEY;
SHOW CREATE TABLE tblname\G
-- 關注: DEFAULT CHARSET, 欄位 CHARACTER SET
ALTER DATABASE maildb DEFAULT CHARACTER SET utf8mb4;
ALTER TABLE mailtable DEFAULT CHARACTER SET utf8mb4;
-- 一鍵轉換表內所有字串欄位資料
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
原理:讀取每列資料 → 從舊編碼解碼 → 以新編碼編碼 → 寫回。**若原資料實際編碼與欄位聲明不一致 (如欄位 latin1 存 utf8),此操作會產生亂碼。**
-- 步驟: 轉為二進位中介 → 再轉目標編碼
ALTER TABLE t1 CHANGE c1 c1 BLOB; -- 1. 去除字符集標記
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8mb4; -- 2. 重新賦予正確編碼
-- 原因: BLOB 不做字符集轉換,保留原始位元組
CREATE TABLE maos_mail_batch (
ID VARCHAR(32) CHARACTER SET latin1 NOT NULL,
BATCH_TIME VARCHAR(30) CHARACTER SET latin1 NOT NULL,
BATCH_INTRODUCE VARCHAR(2000) CHARACTER SET latin1 DEFAULT NULL,
BATCH_SUM INT(11) DEFAULT NULL,
BATCH_STATE VARCHAR(1) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- 1. 修正字符集 (資料實為 latin1,欄位聲明 latin1,目標 utf8mb4)
ALTER TABLE maos_mail_batch CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 2. 若 ID 改為自增數字主鍵
ALTER TABLE maos_mail_batch DROP PRIMARY KEY;
ALTER TABLE maos_mail_batch MODIFY ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE ... DEFAULT CHARSET **不轉換現有資料**,僅影響後續 ADD COLUMNMODIFY col VARCHAR(100) CHARSET utf8mb4 若原資料非 latin1 會雙重編碼CONVERT TO 會鎖表重建,生產環境用 pt-online-schema-change 或 8.0 INSTANT (僅支援尾部加欄位)