MySQL 主鍵操作 SQL 大全：創建、修改、刪除、查詢與自增重置

MySQL 主鍵操作 SQL 大全：創建、修改、刪除、查詢與自增重置

主鍵是關係型數據庫表的核心標識，正確管理主鍵結構直接關係數據完整性與查詢性能。本文匯總 MySQL 主鍵全生命周期的高頻 SQL，涵蓋建表、增刪改查、自增調整、元數據查詢等實戰場景，並說明常見坑點。

一、建表時定義主鍵

-- 單列主鍵 + 自增
CREATE TABLE `order` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_no` VARCHAR(32) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 聯合主鍵（無自增）
CREATE TABLE `order_item` (
    `order_id` BIGINT UNSIGNED NOT NULL,
    `sku_id` INT UNSIGNED NOT NULL,
    `qty` INT NOT NULL,
    PRIMARY KEY (`order_id`, `sku_id`)
) ENGINE=InnoDB;

二、事後添加 / 修改主鍵

-- 添加主鍵（表無主鍵時）
ALTER TABLE `test2` ADD PRIMARY KEY (`id`);

-- 修改主鍵：必須先刪除舊主鍵
ALTER TABLE `test2` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`);

-- 將現有列改為自增主鍵（需先確保列唯一非空）
ALTER TABLE `category`
    MODIFY COLUMN `id` INT(11) NOT NULL AUTO_INCREMENT FIRST,
    ADD PRIMARY KEY (`id`);

注意：InnoDB 表必須有主鍵，若不顯式定義，會隱式創建 6 字節 RowID，佔用空間且無業務含義，強烈建議顯式指定業務無關的自增 BIGINT。

三、刪除主鍵

ALTER TABLE `test2` DROP PRIMARY KEY;

刪除後表無顯式主鍵，InnoDB 將使用隱式 RowID，性能下降，生產環境極少這樣操作。

四、自增值管理

-- 查看當前自增值
SHOW TABLE STATUS LIKE 'test2'\G
-- Auto_increment: 1001

-- 重置自增值（需大於現有最大值）
ALTER TABLE `test2` AUTO_INCREMENT = 10000;

-- 歸零（僅限空表或 TRUNCATE 後）
TRUNCATE TABLE `test2`;  -- 同時重置自增為 1

五、查詢主鍵元數據

-- 方式 1：INFORMATION_SCHEMA（標準、跨版本穩定）
SELECT
    kcu.TABLE_NAME,
    kcu.COLUMN_NAME,
    kcu.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    ON kcu.TABLE_NAME = tc.TABLE_NAME
   AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
   AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
  AND tc.TABLE_SCHEMA = 'your_db';

-- 方式 2：SHOW INDEX（直觀）
SHOW INDEX FROM `test2` WHERE Key_name = 'PRIMARY';

-- 方式 3：DESCRIBE（簡易判斷 Key=PRI）
DESCRIBE `test2`;

六、主鍵相關常見操作

七、避坑指南

1. 業務字段作主鍵：手機號、身份證、訂單號等業務字段易變更、長度大、非單調遞增 → 導致頁面分裂、二級索引膨脹。堅持使用無業務含義的 BIGINT UNSIGNED AUTO_INCREMENT。
2. UUID 作主鍵：無序、36 字節、佔用巨大 → 極差。若需全局唯一，用 有序 UUID (ULID/TSID) 或雪花算法生成 64 位長整型。
3. 自增耗盡：BIGINT 上限 2^63-1 ≈ 9.22e18，實際不會耗盡；INT 上限 21 億，高並發長期運行需評估遷移 BIGINT。
4. 主鍵更新：InnoDB 主鍵值一旦寫入不可更新（會報錯 ERROR 1062 或觸發隱式刪除+插入），設計階段務必確認不可變性。

八、生產環境變更主鍵最佳實踐

1. 低峰期操作，提前備份
2. 使用 pt-online-schema-change 或 gh-ost 無鎖變更
3. 新建帶正確主鍵的表 → 同步數據 → 重命名切換
4. 驗證應用兼容性（ORM 實體映射、外鍵引用等）