萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 新增/刪除主鍵、自增主鍵、字符集修改完整操作指南

MySQL 主鍵管理、自增屬性、字符集修改實戰筆記,含 PRIMARY KEY vs AUTO_INCREMENT 差異、字符集正確轉換姿勢 (避免雙重編碼錯誤)。

一、主鍵與自增的兩種模式

模式定義語法INSERT 行為重複風險
PRIMARY KEY (無自增)id VARCHAR(32) PRIMARY KEY必須顯式提供值手動給同值 → Duplicate entry
NOT NULL AUTO_INCREMENTid 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;

三、字符集修改:避開「雙重編碼」陷阱

1. 檢視現狀

SHOW CREATE TABLE tblname\G
-- 關注: DEFAULT CHARSET, 欄位 CHARACTER SET

2. 修改資料庫/表預設字符集 (僅影響新增欄位)

ALTER DATABASE maildb DEFAULT CHARACTER SET utf8mb4;
ALTER TABLE mailtable DEFAULT CHARACTER SET utf8mb4;

3. 正確轉換現有資料字符集 (核心操作)

-- 一鍵轉換表內所有字串欄位資料
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

原理:讀取每列資料 → 從舊編碼解碼 → 以新編碼編碼 → 寫回。**若原資料實際編碼與欄位聲明不一致 (如欄位 latin1 存 utf8),此操作會產生亂碼。**

4. 修正「欄位聲明編碼 ≠ 實際儲存編碼」的終極招

-- 步驟: 轉為二進位中介 → 再轉目標編碼
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 COLUMN
  • 直接 MODIFY col VARCHAR(100) CHARSET utf8mb4 若原資料非 latin1 會雙重編碼
  • 大表 CONVERT TO 會鎖表重建,生產環境用 pt-online-schema-change 或 8.0 INSTANT (僅支援尾部加欄位)
  • 外鍵關聯表必須**同步轉換**,否則 FK 比較失敗
copyright © 萬盛學電腦網 all rights reserved