SQL Auto Increment

SQL 自增字段：生成唯一ID

自增（也称为 identity 或 sequence）允许数据库在向表中插入新记录时自动生成一个唯一数字。这通常用于主键列。

为何使用自增字段？

通常，我们需要一个主键来唯一标识每一行，但它本身不带有任何业务含义（称为代理键）。自增字段非常适合此用途，因为：

• 它们保证唯一性。
• 它们简化了数据插入，因为您无需手动生成并提供ID。
• 它们高效且通常为数字类型，这对于索引和连接（JOIN）很有益。

创建自增字段的具体语法因数据库系统而异。

MySQL 语法: AUTO_INCREMENT

MySQL 使用 AUTO_INCREMENT 关键字。

CREATE TABLE Persons ( ID INT NOT NULL AUTO_INCREMENT, LastName VARCHAR(255) NOT NULL, FirstName VARCHAR(255), Address VARCHAR(255), City VARCHAR(255), PRIMARY KEY (ID) );

默认情况下，AUTO_INCREMENT 从 1 开始并递增 1。要设置不同的起始值，可以使用：ALTER TABLE Persons AUTO_INCREMENT = 100;

插入新记录（ID 会自动生成）：

INSERT INTO Persons (FirstName, LastName) VALUES (‘Lars’, ‘Monsen’);

SQL Server 语法: IDENTITY

SQL Server 使用 IDENTITY 关键字。

CREATE TABLE Persons ( ID INT IDENTITY(1,1) PRIMARY KEY, LastName VARCHAR(255) NOT NULL, FirstName VARCHAR(255), Address VARCHAR(255), City VARCHAR(255) );

IDENTITY(seed, increment): seed 是起始值，increment 是增量。IDENTITY(1,1) 从 1 开始并递增 1。IDENTITY(10,5) 将从 10 开始并递增 5。

插入新记录：

INSERT INTO Persons (FirstName, LastName) VALUES (‘Lars’, ‘Monsen’);

PostgreSQL 语法: SERIAL 或 GENERATED AS IDENTITY

PostgreSQL 提供几种方式。SERIAL 伪类型是一种传统的简写方式：

CREATE TABLE Persons ( ID SERIAL PRIMARY KEY, — 创建一个整型列和一个相关的序列 LastName VARCHAR(255) NOT NULL, FirstName VARCHAR(255), Address VARCHAR(255), City VARCHAR(255) );

现代的 SQL 标准方式（PostgreSQL 10+）是 GENERATED AS IDENTITY：

CREATE TABLE Persons ( ID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, — 或者 GENERATED BY DEFAULT AS IDENTITY LastName VARCHAR(255) NOT NULL, FirstName VARCHAR(255), Address VARCHAR(255), City VARCHAR(255) );

GENERATED ALWAYS AS IDENTITY: 系统总是生成值。您不能插入自己的值。 GENERATED BY DEFAULT AS IDENTITY: 如果未提供值，系统会生成一个。您可以覆盖它。

插入新记录：

INSERT INTO Persons (FirstName, LastName) VALUES (‘Lars’, ‘Monsen’);

Oracle 语法: IDENTITY 列 (12c+) 或 SEQUENCE

Oracle 12c 及更高版本支持 SQL 标准的 IDENTITY 列：

CREATE TABLE Persons ( ID NUMBER GENERATED AS IDENTITY PRIMARY KEY, LastName VARCHAR2(255) NOT NULL, FirstName VARCHAR2(255), Address VARCHAR2(255), City VARCHAR2(255) );

对于旧版本的 Oracle，或者需要更多控制时，您需要显式使用 SEQUENCE 对象：

CREATE SEQUENCE person_id_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20; — 缓存以提高性能

然后，在插入时使用 sequence_name.NEXTVAL：

INSERT INTO Persons (ID, FirstName, LastName) VALUES (person_id_seq.NEXTVAL, ‘Lars’, ‘Monsen’);

SQLite 语法: AUTOINCREMENT

SQLite 使用 INTEGER PRIMARY KEY，可选地带上 AUTOINCREMENT 关键字。如果使用 AUTOINCREMENT，它确保 ID 严格递增且永不重用。如果不使用，如果删除较大的 ID，ID 通常会被重用。

CREATE TABLE Persons ( ID INTEGER PRIMARY KEY AUTOINCREMENT, LastName TEXT NOT NULL, FirstName TEXT, Address TEXT, City TEXT );

插入新记录：

INSERT INTO Persons (FirstName, LastName) VALUES (‘Lars’, ‘Monsen’);

实际应用：几乎所有代表实体的表（例如 Users、Products、Orders）都会受益于自增主键，以实现稳定高效的引用。