PostgreSQL 序列(sequence)的使用

2023年 8月 15日 65.5k 0

PostgreSQL 中的序列与 Oracle 类似,是一个独立的对象,而 MySQL 的序列是绑定在一张表的字段上的,自增长序列只能用于表的一个字段,且不能被其他表共用,相对来说,PostgreSQL 的序列更加灵活和通用。

本文示例在 PostgreSQL 11.5 上测试。

一、PostgreSQL 序列的创建语法

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table.column | NONE } ]

  • TEMPORARY | TEMP,表示创建临时序列,会话结束,序列会自动删除
  • INCREMENT [ BY ] increment,序列的步长,默认值为 1
  • MINVALUE minvalue | NO MINVALUE,指定序列的最小值,对于递增序列,默认最小值为 1,对于递减序列,默认最小值为 -2^63 - 1,NO MINVALUE 相当于使用默认值
  • MAXVALUE maxvalue | NO MAXVALUE,指定序列的最大值,对于递增序列,默认最大值为 2^63 - 1,对于递减序列,最大默认值为 -1,NO MAXVALUE 相当于使用默认值
  • START [ WITH ] start,指定序列的起点值
  • CACHE cache,指定 cache 的数值,最小值同时也是默认值为 1,表示一次只能生成一个值,也就是没有缓存
  • [ NO ] CYCLE,在序列达到最大值或者最小值时,是否可以循环,如果设置为不循环,在达到极值时,下一次调用将会报错,默认为不循环
  • OWNED BY { table.column | NONE } ,将序列关联到一个特定的字段上,删除绑定的字段或者表时,对应的序列也会自动删除

二、序列相关的函数

  • nextval(sequence_name),递增序列,并返回新值
  • lastval(),返回最近一次使用 nextval() 获取的任一序列值
  • currval(sequence_name),返回最近一次使用 nextval() 获取的指定序列的数值
  • setval(sequence_name, value),设置序列的当前数值
  • setval(sequence_name, value,is_called),设置当前序列的值及 is_called 标记

以上序列相关函数中,nextval() 最常使用。

三、序列使用示例

pg=# create sequence seq1;
CREATE SEQUENCE
pg=# select nextval('seq1');
nextval
---------
1
(1 row)

pg=# select nextval('seq1');
nextval
---------
2
(1 row)

pg=# select currval('seq1');
currval
---------
2
(1 row)

pg=# select lastval();
lastval
---------
2
(1 row)

pg=# select setval('seq1',100);
setval
--------
100
(1 row)

pg=# select nextval('seq1');
nextval
---------
101
(1 row)

pg=# select setval('seq1',100,false);
setval
--------
100
(1 row)

pg=# select nextval('seq1');
nextval
---------
100
(1 row)

四、序列实现表字段自增

创建 serial 类型的字段,查看表定义,实际上 serial 类型就是一个序列关联到了表的字段上。

pg=# create table tb(id serial, name varchar(50));
CREATE TABLE
pg=# \d tb
Table "public.tb"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+--------------------------------
id | integer | | not null | nextval('tb_id_seq'::regclass)
name | character varying(50) | | |

显式关联序列:

create table tb1 (id int4 not null default nextval('seq1'), name varchar(50));

插入数据:

pg=# insert into tb(name) values ('abc');
INSERT 0 1
pg=# select * from tb;
id | name
----+------
1 | abc
(1 row)

pg=# insert into tb(name) values ('efg');
INSERT 0 1
pg=# select * from tb;
id | name
----+------
1 | abc
2 | efg
(2 rows)

pg=# insert into tb1 values (nextval('seq1'),'aaa');
INSERT 0 1
pg=# insert into tb1 values (nextval('seq1'),'bbb');
INSERT 0 1
pg=# select * from tb1;
id | name
-----+------
105 | aaa
106 | bbb
(2 rows)

五、序列的相关问题

  1. 在事务中使用序列,事务回滚,序列不会回滚。
  2. 序列 cache 值大于 1 ,并且该序列被用于多会话并发场景时,那么每个会话在访问序列的时候会缓存序列值,如果会话获得了缓存的序列值,却没有使用,就会导致序列出现空洞。另外将 cache 值设置为大于 1 时,只能保证序列值唯一,并不能保证其按顺序生成。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论