数据库中的自增序列

PostgreSQL

数据库中的自增序列

业务开发中，经常会使用自增 ID 作为数据库表中的主键。有些时候就是单纯的为了表数据有一个主键ID，有时候则是业务上有要求需要自增且唯一数字的场景。

大多数情况下就可以使用数据库提供的一些特性来生成。

1. MySQL 中用法

MySQL 中没有序列这种说法，更没有没有专门的序列对象。通常都是建表的时候使用 AUTO_INCREMENT 实现自增：

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

简单使用，不需要额外的配置。但是有个问题是跟数据库表绑定了，不能多表共用，更不支持更复杂的序列操作。

当然也可以使用单独一张表去模拟序列。

CREATE TABLE sequences (
    name VARCHAR(50) PRIMARY KEY,
    current_value INT NOT NULL
);

2. Oracle 中用法

Oracle 的序列功能比较强，也是比较早提供序列特性的数据库。

先创建序列：

CREATE SEQUENCE my_sequence
    INCREMENT BY 1        -- 每次增加多少，可以是正数或负数
    START WITH 1         -- 起始值，一般都是1
    MINVALUE 1          -- 最小值
    MAXVALUE 999999999  -- 最大值
    CACHE 20           -- 缓存大小，就是提前生成多少个序列
    NOCYCLE            -- 是否循环
    ORDER;             -- 保证按请求顺序生成

常规的用法：

-- 获取下一个序列值
SELECT my_sequence.NEXTVAL FROM dual;

-- 获取当前序列值
SELECT my_sequence.CURRVAL FROM dual;

-- 在 INSERT 语句中使用
INSERT INTO test_table (id, name)
VALUES (my_sequence.NEXTVAL, 'John');

-- 建表时作为默认值中使用
CREATE TABLE test_table (
    id NUMBER DEFAULT my_sequence.NEXTVAL,
    name VARCHAR2(100)
);

可以看出来，Oracle 中序列单独剥离出来，不再跟数据库表绑定。使用起来比较灵活，就是更复杂了。

3. PostgreSQL 中的序列

PostgreSQL 的序列设计很大程度上参考了 Oracle，毕竟 Oracle 是最成熟的商业数据库之一。基本上可以说相似度极高。

序列定义：

CREATE SEQUENCE my_sequence
    INCREMENT BY 1    -- 每次增加多少，可以是正数或负数
    MINVALUE 1       -- 最小值
    MAXVALUE 999999  -- 最大值
    START WITH 1     -- 起始值
    CACHE 1         -- 缓存数量
    NO CYCLE;       -- 是否循环

基本用法：

-- 获取下一个值
SELECT nextval('my_sequence');  

-- 获取当前值
SELECT currval('my_sequence');  

-- 获取最后生成的值
SELECT lastval();

建表时指定序列：

-- 创建表时指定序列
CREATE TABLE test_table (
    id INTEGER PRIMARY KEY DEFAULT nextval('test_table_id_seq'),
    name VARCHAR(50)
);

-- 使用 SERIAL 类型会自动创建序列
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,  -- 等同于 INTEGER + sequence
    name VARCHAR(50)
);

不过这里需要注意的是：

PostgreSQL 的序列是不支持事务回滚，就算回滚了事务，序列的值也是不会还原回去的。
如果业务上不能接受这个情况，就不能依赖于这个自增的序列了。