sql-using-sequences

SQL - 序列和自增键

序列是一种数据库对象，用于生成一系列唯一的整数（例如，1、2、3…）。序列最常用于为表中的新行生成唯一的主键值，确保每行都有一个独特的标识符。

虽然底层机制通常是 SEQUENCE 对象，但大多数关系型数据库管理系统（RDBMS）都提供了方便的、简写语法来创建自增列。

自增列的常见实现

定义自增主键的语法因数据库而异。

标准 SQL：标识列

现代 SQL 标准使用 GENERATED AS IDENTITY。

CREATE TABLE Products (
    ProductID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ProductName VARCHAR(100) NOT NULL
);

GENERATED ALWAYS 意味着数据库总是生成值。GENERATED BY DEFAULT 允许您通过提供自己的值来覆盖它。

PostgreSQL：SERIAL

PostgreSQL 提供了方便的简写 SERIAL 伪类型，它自动创建一个整数列、一个序列，并将列的默认值设置为序列的下一个值。

CREATE TABLE Products (
    ProductID SERIAL PRIMARY KEY, -- 自增整数主键的简写
    ProductName VARCHAR(100) NOT NULL
);

MySQL：AUTO_INCREMENT

CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(100) NOT NULL
);

SQL Server：IDENTITY

SQL Server 使用 IDENTITY 属性，您可以在其中指定种子（起始值）和增量。

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1), -- 从 1 开始，每次递增 1
    ProductName VARCHAR(100) NOT NULL
);

使用自增列

当您插入新行时，只需在 INSERT 语句中省略自增列，数据库将自动为您生成下一个值。

-- 我们不为 ProductID 提供值
INSERT INTO Products (ProductName) VALUES ('Wireless Keyboard');

检索最后生成的 ID

在 INSERT 后，通常需要获取刚刚生成的 ID（例如，作为另一个表中的外键使用）。此方法因数据库而异。

• PostgreSQL / 标准 SQL： 使用 RETURNING 子句。这是最有效的方法，因为它在同一个数据库往返中检索 ID。
• MySQL： 使用 LAST_INSERT_ID() 函数。
• SQL Server： 使用 SCOPE_IDENTITY() 函数。

使用 RETURNING 的示例 (PostgreSQL)

INSERT INTO Products (ProductName) VALUES ('4K Monitor')
RETURNING ProductID;

直接使用序列

对于更高级的用例，您可以直接创建和交互序列对象。当您需要一个不与表列绑定的唯一数字时，这很有用。

-- 创建一个序列 (PostgreSQL/Oracle 语法)
CREATE SEQUENCE order_number_seq START WITH 1000;

-- 从序列中获取下一个值
SELECT nextval('order_number_seq');