postgres序列mysql_PostgreSQL 序列（sequence）的使用

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)

五、序列的相关问题

在事务中使用序列，事务回滚，序列不会回滚。

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