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

MySQL 將既有欄位改為 AUTO_INCREMENT 主鍵操作指南

編輯：MySQL 資料庫相關知識

業務常遇場景：表已建立、資料已入庫，後來決定某欄位 (如 userid) 改為自增主鍵。MySQL 支援單條 ALTER TABLE ... CHANGE 完成「修改類型+設自增+設主鍵」原子操作。

核心語句

ALTER TABLE `user` 
CHANGE `userid` `userid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

完整演示流程

1. 初始表結構 (無主鍵、無自增)

CREATE TABLE `user` (
  `username` VARCHAR(20) NOT NULL,
  `pw` VARCHAR(20) NOT NULL,
  `userid` INT(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 執行變更

ALTER TABLE `user` 
CHANGE `userid` `userid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

效果： userid 成為主鍵、非空、自增，現有資料保留，新插入自動分配遞增 ID。

3. 變更後表結構

CREATE TABLE `user` (
  `username` VARCHAR(20) NOT NULL,
  `pw` VARCHAR(20) NOT NULL,
  `userid` INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

擴展：常見 DDL 操作速查表

操作	語句範例	關鍵點
新增欄位並設主鍵	`ALTER TABLE t ADD new_id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (new_id);`	新欄位必須 NOT NULL、整數類型
新增主鍵索引	`ALTER TABLE t ADD PRIMARY KEY (id);`	欄位需非空唯一
新增普通索引	`ALTER TABLE t ADD INDEX idx_name (name);`	支援多欄位 `(col1,col2)`
新增唯一索引	`ALTER TABLE t ADD UNIQUE uk_card (card_no);`	值唯一、允許多 NULL
刪除索引	`ALTER TABLE t DROP INDEX idx_name;`	需知索引名 (非欄位名)
新增欄位	`ALTER TABLE t ADD col_name col_type;`	預設末尾，可 `FIRST`/`AFTER col`
刪除欄位	`ALTER TABLE t DROP col_name;`	同步刪除相關索引/約束
重命名欄位	`ALTER TABLE t CHANGE old_name new_name col_type;`	必須給完整類型定義
調整欄位順序	`ALTER TABLE users CHANGE user_pwd user_pwd VARCHAR(20) NOT NULL AFTER user_name;`	同 CHANGE 語法
修改欄位類型	`ALTER TABLE t CHANGE col col BIGINT NOT NULL;`	數據截斷風險，先評估
修改欄位名+類型	`ALTER TABLE t CHANGE old_name new_name new_type;`	同上
重命名表	`ALTER TABLE t RENAME new_t;`	或 `RENAME TABLE t TO new_t;`
外鍵級聯	`FOREIGN KEY (rid) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE`	InnoDB 支援，MyISAM 忽略

注意事項與避坑

資料唯一性前提： 設主鍵前必須確認欄位值唯一且無 NULL，否則報錯 Duplicate entry / Column cannot be null。
自增起始值： 現有最大值為 N，下一條插入為 N+1。可手動重設 ALTER TABLE t AUTO_INCREMENT = 1000;。
大表鎖表風險： InnoDB 5.6+ Online DDL 支援大部分操作無鎖，但 CHANGE ... AUTO_INCREMENT PRIMARY KEY 仍需重建表 (複製數據)，大表建議 pt-online-schema-change。
外鍵引用： 若被其他表外鍵引用，需先刪除外鍵 → 變更主鍵 → 重建外鍵。
主從複制： 主庫執行 DDL 會同步到從庫，大表操作導致從庫延遲，擇低峰期執行。

驗證腳本

-- 1. 確認結構
SHOW CREATE TABLE `user`;

-- 2. 確認自增值
SHOW TABLE STATUS LIKE 'user'\G
-- 關注 Auto_increment 值

-- 3. 測試插入
INSERT INTO `user` (`username`,`pw`) VALUES ('test','pass');
SELECT * FROM `user` ORDER BY `userid` DESC LIMIT 3;

掌握 CHANGE ... AUTO_INCREMENT PRIMARY KEY 單條語句原子化變更，配合大表線上變更工具，讓結構演進安全、高效、無感知。