MySQL 自增主鍵建表實戰：AUTO_INCREMENT 正確用法與避坑

標準建表語法

CREATE TABLE article (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

關鍵點解析

插入資料驗證

-- 方式一：顯式指定 NULL
INSERT INTO article (id, title) VALUES (NULL, '第一篇');

-- 方式二：省略 id 欄位
INSERT INTO article (title) VALUES ('第二篇');

-- 方式三：指定 0（等同 NULL）
INSERT INTO article (id, title) VALUES (0, '第三篇');

-- 查詢結果
SELECT * FROM article;
+----+------------+---------------------+
| id | title      | created_at          |
+----+------------+---------------------+
|  1 | 第一篇     | 2024-01-15 10:00:00 |
|  2 | 第二篇     | 2024-01-15 10:00:01 |
|  3 | 第三篇     | 2024-01-15 10:00:02 |
+----+------------+---------------------+

修改現有表新增自增主鍵

-- 1. 先加主鍵（若無）
ALTER TABLE article ADD PRIMARY KEY (id);

-- 2. 修改欄位屬性
ALTER TABLE article MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT;

避坑：直接 `ALTER TABLE ... CHANGE id id INT AUTO_INCREMENT` 若無主鍵會報錯 `ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key`

替代方案：唯一鍵 + 自增

ALTER TABLE article MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE;

不想設主鍵時可用，但 InnoDB 仍會隱式生成 6 字節 Row ID，不如顯式主鍵效能好。

自增值管理

-- 查看下一個自增值
SHOW TABLE STATUS LIKE 'article'\G
-- Auto_increment: 4

-- 手動設定下一個自增值
ALTER TABLE article AUTO_INCREMENT = 100;

-- 獲取剛插入的自增 ID
SELECT LAST_INSERT_ID();
-- 或應用層：mysqli_insert_id() / PDO::lastInsertId()

效能與設計建議

• **自增主鍵 + InnoDB** = 寫入效能最優（順序寫入、無頁分裂）
• 避免 UUID/隨機字串作主鍵（導致 B+ 樹頻繁分裂、索引膨脹）
• 大表考慮 `BIGINT`（40 億上限），中小表 `INT UNSIGNED`（42 億）足夠
• 業務層勿依賴自增 ID 連續性（刪除/回滾/重啟會產生間隙）

1. 上一篇文章： MySQL 報錯 "Can't create/write to file" 根因與解決方案
2. 下一篇文章： Ubuntu 下 MySQL 源碼編譯安裝完整教程

MySQL 綜合教學

MySQL Duplicate Entry 錯誤根因分析與三種高效解決方案

錯誤本質：唯一約束衝突 Duplicate entry xx

MySQL 連線報錯「無授權連線」？根因與標準修復流程

前言 剛安裝完 MySQL，透過 ODBC 或程式連線字串連

MySQL 子查詢深度解析：類型、相關性與效能優化實戰

MySQL 子查詢深度解析：類型、相關性與效能優化實戰 子查

Solr 4.9 同步 MySQL 資料完整部署指南：從安裝到全量匯入

Solr 4.9 同步 MySQL 資料完整部署指南：從安裝