李守中
该站已迁往根域名 https://lishouzhong.com
需要注意,迁移后的文章的 url 可能会发生变化。
域名 https://note.lishouzhong.com 下的内容将不再更新,但已有内容会永久保留。

PostgreSQL 中的自增列

Table of Contents

1. 自增列推荐使用 int8 (bigint)

如果表规模较小,int4 够用,但即便使用了 int8,多存储的 4 个字节也不会产生多大影响。

如果表规模较大,int4 的最大值 2147483647 可能不够用。将活跃的大表的主键从 int4 改成 int8 的 SQL 语句很简单,但执行后该表不可用的时间会相当长。

int8 最大值 9223372036854775807,即使每秒使用 10000 个值,也需要 3000 万年才能超过最大值。另,PG 不支持无符号数。

2. sequence 是自增列的底层对象

sequence 是 PG 底层的一个 object,它起自增计数器的作用,可以创建多个。

sequence 被并发访问时不会产生两次同样的数值。如果担心高并发导致访问 sequence 出现瓶颈,可以针对 sequence 指定 Cache,它使 sequence 预先生成数值并保存在内存中,让取数速度更快。

sequence 与事务无关,即使事务产生回滚,sequence 也不会重置计数。这样不但能保证高性能,也不会带来问题。如希望 sequence 的值单调连续,那就不能用 sequence,要涉及效率更低且更复杂的方案。

sequence 对象可以被直接使用,或者通过 serial/bigserial/IDENTITY 被间接使用。

3. 用 bigserial 类型还是 IDENTITY 列

PG 11 引入了 IDENTITY 约束,相比 bigserial 有两个优势:

  • IDENTITY 列在 SQL 标准中,bigserial 是 PG 专有类型,迁移数据库时工作量小一些
  • 插入数据时,如果插入数据时明确在 IDENTITY 列上指定了一个值,PG 会报错,这避免了手动输入的值和此后生成的值发生冲突的问题

除非使用的是 PG 10 或更低版本,否则不推荐 bigserial。

4. 如何创建

4.1. 先创建 sequence 对象实例再设定列的默认值最后二者关联

CREATE SEQUENCE use_serial_id_seq;
CREATE TABLE use_serial (
    id int8 DEFAULT nextval('use_serial_id_seq') PRIMARY KEY,
    -- ...
);
ALTER SEQUENCE use_serial_id_seq OWNED BY some_table.id;
-- OWNED BY 将 use_serial_id_seq 这个 sequence 和 user_serial 表的 id 列绑定
-- DROP user_serial 表的 id 列,那么 use_serial_id_seq 也会被 drop

4.2. 先建表再创建 sequence 对象实例最后关联对象实例到列

CREATE TABLE use_serial (
    id int8 PRIMARY KEY,
    -- ...
);
CREATE SEQUENCE use_serial_id_seq OWNED BY use_serial.id;
ALTER TABLE use_serial ALTER id SET DEFAULT nextval('use_serial_id_seq');

它与前一节结果相同。

4.3. 直接使用 serial 或 bigserial 作为列的类型

-- serial 类型的基础类型是 int4
-- bigserial 类型的基础类型是 int8
CREATE TABLE use_serial (
    id bigserial PRIMARY KEY,
    -- ...
);

它是前两节的命令的简化版。数据库会自动创建 sequence 对象实例,并将对象实例绑定到表中的列。

4.4. 使用 IDENTITY 约束

-- 用户不能指定 id 的值
CREATE TABLE some_table (
    id int8 GRANTED ALWAYS      AS IDENTITY PRIMARY KEY,
    -- ...
)

--  用户可以指定 id 的值
CREATE TABLE some_table (
    id int8 GRANTED BY DEFAULT  AS IDENTITY PRIMARY KEY,
    -- ...
)

PG 会隐式地创建一个 sequence 对象实例绑定在指定的列上。

对于不能指定 identity 值的列来说,可以使用 INSERT INTO <table-name> (...) OVERRIDING SYSTEM VALUE VALUES(...); 来强制指定 identity 的值,但这可能导致后续生成的 identity 列的值与被插入的值冲突。比如,强制插入了 id 为 4 的数据,而插入前表中 id 列的最大值为 3,那么再插入一条新数据时,这条新数据会取到 id 为 4,和表中已有数据冲突。

4.5. 其他思路

使用 BEFORE INSERT 触发器。生成的值会直接覆盖要被插入的值,并且性能下降严重。



Last Update: 2023-08-20 Sun 15:09

Generated by: Emacs 28.2 (Org mode 9.5.5)   Contact: lsz.sino@outlook.com

若正文中无特殊说明,本站内容遵循: 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议