sql-auto-increment

SQL 自增键

自增键（auto-incrementing key）是一种列，它为插入到表中的每一新行自动生成一个唯一、递增的数值。这是创建主键（primary keys）最常用的策略，确保每行都有一个唯一的标识符，而无需手动生成。

当列设置为自增时，你不应将其包含在 INSERT 语句的列列表中。数据库将自动处理其值的生成。

跨不同数据库的实现

创建自增键的语法因数据库系统而异。以下是一些最常见的实现方式。

MySQL: AUTO_INCREMENT

MySQL 使用 AUTO_INCREMENT 属性。默认情况下，它从 1 开始，每次递增 1。

CREATE TABLE PRODUCTS (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2)
);

-- 插入新行。注意省略了 ID 列。
INSERT INTO PRODUCTS (ProductName, Price) VALUES ('Laptop', 1200.00);
-- 新行将自动获得 ID = 1。

你可以更改现有表新插入行的起始值：

ALTER TABLE PRODUCTS AUTO_INCREMENT = 100;

SQL Server: IDENTITY

SQL Server 使用 IDENTITY 属性，它允许你指定一个种子（起始值）和递增值。

-- IDENTITY(种子, 增量)
CREATE TABLE LOGS (
    LogID INT PRIMARY KEY IDENTITY(1,1),
    LogMessage VARCHAR(MAX) NOT NULL,
    LogTime DATETIME DEFAULT GETDATE()
);

-- 从 1 开始，递增 1
INSERT INTO LOGS (LogMessage) VALUES ('User logged in.');

-- 示例：从 1000 开始，每次递增 5
CREATE TABLE SENSORS (
    SensorID INT PRIMARY KEY IDENTITY(1000,5),
    Reading FLOAT
);

PostgreSQL 和 SQL 标准: GENERATED AS IDENTITY

创建自增列的现代、标准 SQL 方式是使用 GENERATED AS IDENTITY。PostgreSQL、Oracle 和最新版本的 SQL Server 都支持此方式。

CREATE TABLE EMPLOYEES (
    ID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL
);

-- `GENERATED ALWAYS` 禁止用户插入自己的值。
-- 使用 `GENERATED BY DEFAULT` 允许覆盖系统生成的值。

PostgreSQL 还支持更旧、更方便的 SERIAL 伪类型，它是创建与序列（sequence）关联的整型列的快捷方式。

-- PostgreSQL 传统语法
CREATE TABLE USERS (
    ID SERIAL PRIMARY KEY,
    Username VARCHAR(50) UNIQUE NOT NULL
);

高级主题：使用 UUID 作为主键

虽然自增整数简单高效，但一种日益流行的替代方案是全局唯一标识符（Universally Unique Identifier, UUID）。UUID 是一个 128 位的数值，统计上是唯一的，允许不同的系统独立生成 ID 而不发生冲突。

PostgreSQL 示例

-- 首先，请确保 PostgreSQL 中已启用 pgcrypto 扩展
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE ARTICLES (
    ID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    Title VARCHAR(255) NOT NULL,
    Content TEXT
);

选择主键策略

因素	自增整数	UUID
简洁性	实现和理解都非常简单。人类可读。	可读性较差。需要函数生成。
性能	优秀。小而固定大小。由于是顺序的，非常适合索引。	较大（16 字节 vs 4/8 字节）。可能导致索引碎片，尽管现代 UUID 类型缓解了这一点。
分布式系统	较差。需要中央数据库生成键，造成瓶颈。	优秀。键可以在任何应用服务器上生成，无需协调。
安全性	可能会暴露信息（例如，.../users/123 泄露用户数量）。可猜测。	优秀。ID 不可猜测，可防止枚举攻击。