您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 主鍵操作 SQL 大全：添加、修改、刪除、自增、聯合主鍵一網打盡

編輯：MySQL 資料庫相關知識

主鍵基礎操作

1. 建表時定義主鍵


-- 單列主鍵 + 自增
CREATE TABLE t1 (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

-- 聯合主鍵（順序決定索引順序）
CREATE TABLE t2 (
    order_id BIGINT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NOT NULL,
    qty INT,
    PRIMARY KEY (order_id, item_id)
) ENGINE=InnoDB;

2. 為現有表添加主鍵


-- 添加自增主鍵列（放首列 FIRST）
ALTER TABLE t1 
    ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    ADD PRIMARY KEY (id);

-- 現有列設為主鍵（需非空、唯一）
ALTER TABLE t2 ADD PRIMARY KEY (order_id, item_id);

-- 指定主鍵名稱（便於管理）
ALTER TABLE t3 ADD CONSTRAINT pk_t3_id PRIMARY KEY (id);

3. 修改主鍵（必須先刪後加）


-- 步驟 1：查詢現有主鍵名
SHOW CREATE TABLE t1;
-- 或
SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME = 't1' AND CONSTRAINT_TYPE = 'PRIMARY KEY';

-- 步驟 2：刪除舊主鍵
ALTER TABLE t1 DROP PRIMARY KEY;

-- 步驟 3：添加新主鍵
ALTER TABLE t1 ADD PRIMARY KEY (new_col);
-- 或帶約束名
ALTER TABLE t1 ADD CONSTRAINT pk_t1_new PRIMARY KEY (new_col);

4. 刪除主鍵


ALTER TABLE t1 DROP PRIMARY KEY;
-- 注意：InnoDB 表必須有主鍵，刪除後會隱式創建 6 字節 row_id，性能較差

自增列專項操作

1. 修改自增起始值


ALTER TABLE t1 AUTO_INCREMENT = 10000;
-- 僅影響下次插入值，不重排現有數據

2. 重置自增值（緊湊化）


-- 方案 A：TRUNCATE 表（清空數據、重置自增、釋放空間）
TRUNCATE TABLE t1;

-- 方案 B：不清空數據，重新排列自增值（謹慎！長時間鎖表）
SET @num := 0;
UPDATE t1 SET id = @num := (@num + 1);
ALTER TABLE t1 AUTO_INCREMENT = 1;

3. 獲取最後插入自增 ID


-- 會話級（推薦）
SELECT LAST_INSERT_ID();

-- 連接級（同一連接多語句安全）
LAST_INSERT_ID()  -- 不受並發影響

進階場景：動態構建主鍵變更腳本

場景：未知表名、未知主鍵列，需程序化修改


-- 存儲過程示例：修改指定表主鍵為指定列
DELIMITER //
CREATE PROCEDURE sp_change_pk(IN p_schema VARCHAR(64), IN p_table VARCHAR(64), IN p_new_pk_cols VARCHAR(255))
BEGIN
    DECLARE v_old_pk_name VARCHAR(64);
    DECLARE v_sql TEXT;
    
    -- 1. 獲取現有主鍵名
    SELECT CONSTRAINT_NAME INTO v_old_pk_name
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = p_schema AND TABLE_NAME = p_table AND CONSTRAINT_TYPE = 'PRIMARY KEY';
    
    -- 2. 構建動態 SQL
    SET v_sql = CONCAT('ALTER TABLE `', p_schema, '`.`', p_table, '` ');
    IF v_old_pk_name IS NOT NULL THEN
        SET v_sql = CONCAT(v_sql, 'DROP PRIMARY KEY, ');
    END IF;
    SET v_sql = CONCAT(v_sql, 'ADD PRIMARY KEY (', p_new_pk_cols, ')');
    
    -- 3. 執行
    PREPARE stmt FROM v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

-- 調用
CALL sp_change_pk('mydb', 'orders', 'order_id, item_id');

主鍵信息查詢大全

1. 查詢表的主鍵列


-- 方法 1：information_schema（標準、跨版本）
SELECT COLUMN_NAME, ORDINAL_POSITION
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME = 't1' AND CONSTRAINT_NAME = 'PRIMARY'
ORDER BY ORDINAL_POSITION;

-- 方法 2：SHOW INDEX
SHOW INDEX FROM t1 WHERE Key_name = 'PRIMARY';

-- 方法 3：DESCRIBE（簡略）
DESC t1;  -- Key 欄顯示 PRI

2. 查詢無主鍵的表（巡檢腳本）


SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES t
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND NOT EXISTS (
      SELECT 1 FROM information_schema.TABLE_CONSTRAINTS tc
      WHERE tc.TABLE_SCHEMA = t.TABLE_SCHEMA
        AND tc.TABLE_NAME = t.TABLE_NAME
        AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
  );

3. 查詢主鍵類型與自增屬性


SELECT 
    c.COLUMN_NAME,
    c.DATA_TYPE,
    c.COLUMN_TYPE,
    c.IS_NULLABLE,
    c.COLUMN_KEY,  -- PRI
    c.EXTRA        -- auto_increment
FROM information_schema.COLUMNS c
JOIN information_schema.KEY_COLUMN_USAGE k
  ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
 AND c.TABLE_NAME = k.TABLE_NAME
 AND c.COLUMN_NAME = k.COLUMN_NAME
WHERE c.TABLE_SCHEMA = 'db' 
  AND c.TABLE_NAME = 't1'
  AND k.CONSTRAINT_NAME = 'PRIMARY';