SQL 自增列（AUTO INCREMENT）详解

一、概述

自增列（Auto Increment）是一种特殊的列类型，能够在每次插入新数据时自动生成唯一的递增数值，常用于主键字段（Primary Key）。

应用场景：

• 主键自动编号（如用户ID、订单号）
• 防止重复输入，简化插入操作
• 提高数据唯一性和可读性

---

二、各数据库自增列语法

1️⃣ MySQL

• 创建表时添加自增列

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT
);

• 修改表后添加自增列

ALTER TABLE users MODIFY id INT AUTO_INCREMENT PRIMARY KEY;

• 删除自增属性

ALTER TABLE users MODIFY id INT;

• 重置(修改)自增起始值

ALTER TABLE users AUTO_INCREMENT = 1;

• 查看当前自增值

SHOW TABLE STATUS LIKE 'users';

• 插入验证

INSERT INTO users (name, age) VALUES ('Tom', 20), ('Alice', 22);
SELECT * FROM users;

---

2️⃣ SQL Server

-- 创建表时添加自增列

CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50),
    age INT
);

🔸 说明：IDENTITY(1,1) 表示从1开始，每次递增1。

• 修改表后添加自增列

SQL Server 不支持直接为已有列添加 IDENTITY，需重新创建列。

-- 创建新表
SELECT * INTO users_temp FROM users;
DROP TABLE users;

CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50),
    age INT
);

INSERT INTO users (name, age)
SELECT name, age FROM users_temp;
DROP TABLE users_temp;

• 重置自增值

DBCC CHECKIDENT ('users', RESEED, 1);

• 插入验证

INSERT INTO users (name, age) VALUES ('Tom', 20), ('Alice', 22);
SELECT * FROM users;

---

3️⃣ PostgreSQL

• 创建表时添加自增列（新版本推荐）

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

🔹 SERIAL 会自动创建序列对象并与列绑定。

• 修改表后添加自增列

ALTER TABLE users ADD COLUMN id SERIAL PRIMARY KEY;

• 重置序列值

ALTER SEQUENCE users_id_seq RESTART WITH 1;

• 插入验证

INSERT INTO users (name, age) VALUES ('Tom', 20), ('Alice', 22);
SELECT * FROM users;

---

4️⃣ Oracle

Oracle 早期版本不支持自增列，需手动创建 序列（SEQUENCE） 与 触发器（TRIGGER）。
从 Oracle 12c 起支持 GENERATED AS IDENTITY。

• Oracle 12c 及以上

直接在建表时定义：

CREATE TABLE users (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(50),
    age NUMBER
);

🔸 说明：

• GENERATED ALWAYS AS IDENTITY
  • 系统总是生成值，不能手动插入。

• GENERATED BY DEFAULT AS IDENTITY

  • 默认自动生成，但也可以手动指定值。

• 插入验证

INSERT INTO users (name, age) VALUES ('Tom', 20), ('Alice', 22);
SELECT * FROM users;

• Oracle 11g 及以下（手动方式）

CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE users (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    age NUMBER
);

• 重置序列

DROP SEQUENCE user_seq;
CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;

• 插入验证

INSERT INTO users (id, name, age) VALUES (user_seq.NEXTVAL，'Tom', 20), (user_seq.NEXTVAL，'Alice', 22);
SELECT * FROM users;

---

三、自增列与主键约束结合

以下为典型示例：主键列同时定义为自增列。

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL,
    total_amount DECIMAL(8,2),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

💡 说明：

• 自增列通常配合主键使用。
• 不能与复合主键一起使用。
• 若删除并重新插入数据，编号会继续递增。

---

四、对比表

数据库	自增关键字 / 机制	起始值设置	重置方法	特殊说明
MySQL	AUTO_INCREMENT	ALTER TABLE ... AUTO_INCREMENT=n;	✅ 支持	常用于主键
SQL Server	IDENTITY(seed, step)	创建时定义	DBCC CHECKIDENT	不能直接修改已有列
PostgreSQL	SERIAL / BIGSERIAL / IDENTITY	自动创建序列	ALTER SEQUENCE ... RESTART	SERIAL 是语法糖
Oracle	GENERATED AS IDENTITY / SEQUENCE + TRIGGER	序列控制	删除并重建序列	12c+ 才原生支持

---

五、使用建议

1. 主键列优先使用自增方式生成，避免重复与人工维护。
2. 若数据库需多表共享编号，可使用 序列（Sequence） 控制。
3. 插入数据时避免手动指定自增列值，以防主键冲突。
4. 删除数据不会回退自增编号；如需重新编号，可重建表或重置序列。

---

小结

主题	关键命令	作用
建表与结构	CREATE, ALTER, DROP	定义或修改表结构
约束控制	PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK	保证数据有效性
辅助机制	INDEX, AUTO_INCREMENT	提升性能与便利性

---