MySQL 給現有表新增自增主鍵：避開 1075 錯誤的正確姿勢

嘗試對現有表的欄位加 `AUTO_INCREMENT` 時，常遇到 **Error 1075 (ER_WRONG_AUTO_KEY): Incorrect table definition; there can be only one auto column and it must be defined as a key** 。本文解析錯誤原因，給出標準解法與變體方案。

錯誤復現

CREATE TABLE people (
  peopleid SMALLINT NOT NULL,
  name CHAR(50) NOT NULL
);

-- 直接嘗試加自增
ALTER TABLE people CHANGE peopleid peopleid SMALLINT AUTO_INCREMENT;
-- ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

根因：自增列必須是索引 (Key) 的一部分

MySQL 規定：`AUTO_INCREMENT` 欄位**必須被索引** (通常是主鍵或唯一索引的第一列)。單獨修改類型加 `AUTO_INCREMENT` 而不建立索引，違反此規則。

標準解法：先加主鍵，再加自增 (兩步走)

-- 步驟 1：將欄位設為主鍵 (隱含建立索引)
ALTER TABLE people ADD PRIMARY KEY (peopleid);

-- 步驟 2：修改欄位加上 AUTO_INCREMENT
ALTER TABLE people CHANGE peopleid peopleid SMALLINT AUTO_INCREMENT;

**關鍵點**：`CHANGE`/`MODIFY` 時**必須完整聲明類型** (`SMALLINT`)，否則會報錯或類型被重置。

替代方案：唯一索引 + 自增 (無主鍵場景)

-- 若表已有主鍵，或業務不想用此列做主鍵
ALTER TABLE people CHANGE peopleid peopleid SMALLINT AUTO_INCREMENT UNIQUE;

此語句同時建立 **唯一索引** 並設定自增，滿足「自增列必須是鍵」的要求。但注意：

• 表中**只能有一個** `AUTO_INCREMENT` 列。
• InnoDB 表若無顯式主鍵，會隱式產生 6 字節行 ID (GEN_CLUST_INDEX)，效能略遜於顯式主鍵。

進階場景：欄位已有資料、需重新編號

-- 1. 移除舊主鍵/自增屬性 (若有)
ALTER TABLE people DROP PRIMARY KEY;
ALTER TABLE people MODIFY peopleid SMALLINT NOT NULL; -- 移除 AUTO_INCREMENT

-- 2. 重新編號 (業務邏輯決定)
UPDATE people SET peopleid = 0; -- 歸零
-- 或按 name 排序重新編號
SET @rownum = 0;
UPDATE people SET peopleid = (@rownum := @rownum + 1) ORDER BY name;

-- 3. 重新加主鍵 + 自增
ALTER TABLE people ADD PRIMARY KEY (peopleid);
ALTER TABLE people MODIFY peopleid SMALLINT AUTO_INCREMENT;

-- 4. 設定自增起始值 (可選)
ALTER TABLE people AUTO_INCREMENT = 1000;

常見誤區與避雷

驗證腳本

-- 確認自增屬性生效
SHOW CREATE TABLE people\G
-- 輸出應包含: `peopleid` smallint NOT NULL AUTO_INCREMENT, PRIMARY KEY (`peopleid`)

-- 測試插入
INSERT INTO people (name) VALUES ('Alice'), ('Bob');
SELECT * FROM people;
-- peopleid 應自動分配 1, 2 (或指定起始值)

總結：三步驟肌肉記憶

1. **檢查現狀**：`SHOW CREATE TABLE`、`SHOW INDEX`，確認是否已有主鍵/自增列。
2. **補齊鍵**：`ADD PRIMARY KEY (col)` 或 `CHANGE col col TYPE AUTO_INCREMENT UNIQUE`。
3. **設定自增**：`MODIFY col TYPE AUTO_INCREMENT` (完整類型聲明) + 可選 `AUTO_INCREMENT = N`。

自增主鍵是關聯模型的基石。理解「自增列必須是鍵」這一核心規則，配合完整類型聲明習慣，即可輕鬆搞定所有加自增場景，徹底告別 1075 錯誤。

1. 上一篇文章： MySQL ALTER TABLE 修改欄位/索引/約束完整語法速查
2. 下一篇文章： MySQL 字符集衝突 (Illegal mix of collations) 根因與徹底解決方案

MySQL 綜合教學

擁抱 MySQL 資料庫技術：從玩具到核心基礎設施的演進與架構師必讀指南

MySQL 已從「玩具級資料庫」進化為互聯網架構不可或缺的核

Infobright：基於 MySQL 的開源列式數據倉庫解決方案深度解析

Infobright：基於 MySQL 的開源列式數據倉庫解

MySQL 圖解安裝教程（Windows 安裝版）：避坑指南與數據目錄備份還原實戰

前言：安裝向導背後的關鍵決策點 MySQL Windows

VMware 虛擬機 Linux 共享 MySQL 給宿主機：防火牆與用戶授權雙重設定

開發環境常見需求：Windows 宿主機用 Navicat